Problems adding new record from form / subform

[Irene_58]

I have a table (tPNos) containing Project info. The ProjectNo is a text
field because we have sub-projects with descriptive suffixes (e.g. 1400,
1400-01, 1400os). The first 4 digits are always numeric and equate to a
BasePno. I also have an auto-number PNumId field, that I use to link to
other tables.

I have a form based on tPNos, with a sub form on the left that has a list of
all the ProjectNos & descriptions. As the user moves up & down the list, I
use the OnCurrent event to filter to the appropriate record in the main form
– so the form fields fill in correctly.

I originally had the sub-form with AllowAdditions enabled, but the users
aren’t very used to Access and asked for buttons to add new records. 2
flavours - cmdNewPno to create a new record with the Pno based on the next
free BaseNo and cmdAddStroke to create a new record based on the selected PNo.

I disabled AllowAdditions on the sub-form and created buttons on the main
form as requested. The OnClick event of the buttons simply did a
Me.Recordset.AddNew and set the text boxes on the main form to the
appropriate values. The Save button did a DoCmd.DoMenuItem acFormBar,
acRecordsMenu, acSaveRecord, , acMenuVer70.
This seemed to work ok, and has been running like this for some months.

However, I recently tried to modify this form so that under certain
circumstances of adding a new Project it also creates a record in a separate
(quote) table and pre-fills a field in that with the new PNumId. Which is
when I realised that my approach isn’t working properly. My auto-id is going
up in steps of 2. The PNumId I have when moving through the form fields and
creating the quote record is one greater than the last auto-num as I expect,
but when the save is complete there is no record with this number, but a
properly completed record but with an auto-num of plus 2.

I did try changing the save to Me.Recordset.Update – but this only made it
clearer that I’m dealing with 2 different records, as the update doesn’t have
the data on my form.

Any help greatfully appreciated.

Code Extracts:

Private Sub cmdNewPno_Click()
On Error GoTo ErrHandler
Me.Recordset.AddNew
txtProjectNo = GetLastPno() + 1
Exit Sub
ErrHandler:
MsgBox Err.Description
End Sub

Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Me.sfPnos.SetFocus
Exit Sub
Err_cmdSave_Click:
MsgBox Err.Description
End Sub

 

[BruceM via AccessMonster.com]

If the second chunk of code is just to force a save you can use:
Me.Dirty = False

It would help to know what GetLastPno() is. I expect it is a user-derfined
function, but what is the actual code?

Another thing is that it may be better rather than trying to increment the
number in the Click event to use the Default Value property of txtProjectNo
in the form's Current event:

=GetLastPno() + 1

Without knowing more about GetLastPno there is no way of knowing why you are
incrementing by two. Does it happen all the time, or just under the "certain
circumstances" you mentioned?

 

[Daryl S]

Irene -

Is txtProjectNo your autonumber field? If so, do not assign it a value - it
will be created automatically on the AddNew method. Then you are updating it
by one more in your GetLastPno() + 1.

 

[Irene_58]

Thanks to you both for your interest:

txtProjectNo is the name of a textbox on the main form linked to the
ProjectNo field.

My function GetLastPno() opens the tPnos table, finds the highest
BaseProject (ignoring everything > 7000 which relates to internal charge
codes), closes tPnos and returns the BaseProject - see below. This is then
incremented to give the first free project num and set into the textbox.

I don't do anything to the auto-num field (PNoId) - it's not displayed on
the form or explicitly referenced in my code. All that happens between the
AddNew and the Save is the user filling in the rest of the form fields.

For info:

Public Function GetLastPno() As String
On Error GoTo ErrHandle
Dim strSQL As String
Dim recPno As Recordset

strSQL = "SELECT * FROM tPnos WHERE ([Project No] < '7000') ORDER BY
[Project No] DESC"
Set recPno = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
GetLastPno = recPno![BaseProject]
recPno.Close
Exit Function
ErrHandle:
MsgBox Err.Description
End Function

 

[John W. Vinson]

I have a table (tPNos) containing Project info. The ProjectNo is a text
field because we have sub-projects with descriptive suffixes (e.g. 1400,
1400-01, 1400os). The first 4 digits are always numeric and equate to a
BasePno. I also have an auto-number PNumId field, that I use to link to
other tables.

This whole exercise would be a lot easier if you had this field properly
normalized. Fields should be "Atomic" - contain only one indivisible piece of
information. Yours contains two - a BasePno and a suffix. Can you consider
having a BasePno field and a Suffix field? That would let you have a table of
BasePnos, which would be simple and numeric, related one to many to this
table. You can always concatenate the BasePno field to the Suffix field to
display the combination, and you can (and probably should) also define a
unique two-field index on the combination.

 

[Irene_58]

That's a very big change at this stage, I've implemented the company's
timesheet system & project management reporting based on the tPNos table as
it stands.

I'm self-taught and whenever I need multiple fields to be a unique key - I
always add an auto-num instead. Clearly my bad, and I will go and delve into
this in my own time - promise.

Does this relate to getting the double increment? I don't see why setting up
a text field in the record affects the auto-num. Can you offer any insight
into why I seem to be accessing 2 different records when I do my AddNew?

 

[BruceM via AccessMonster.com]

You would probably do better just to use DMax to find the highest value, but
another point is that trying to use greater than or less than (> or <) in a
text field will likely cause problems. 100 and 10000 are both less than 7000
in a text sort order.

Here is DMax used to find the highest value in the number field Num1 in the
table Table1:

DMax("[Num1]","[Table1]")

As John Vinson mentioned, you would do better to have a related table where
suffixes are needed for sub-projects. For that matter, a sub-project table
may be called for. In any case, a unique index is set in table design view.
It can involve several fields, but it is not necessarily the primary key.
There is nothing wrong with using an autonumber for a one-field primary key.
Some people argue against using "artificial" keys, but most developers agree
that an autonumber can be a perfectly good choice for a primary key field.

Thanks to you both for your interest:

txtProjectNo is the name of a textbox on the main form linked to the
ProjectNo field.

My function GetLastPno() opens the tPnos table, finds the highest
BaseProject (ignoring everything > 7000 which relates to internal charge
codes), closes tPnos and returns the BaseProject - see below. This is then
incremented to give the first free project num and set into the textbox.

I don't do anything to the auto-num field (PNoId) - it's not displayed on
the form or explicitly referenced in my code. All that happens between the
AddNew and the Save is the user filling in the rest of the form fields.

For info:

Public Function GetLastPno() As String
On Error GoTo ErrHandle
Dim strSQL As String
Dim recPno As Recordset

strSQL = "SELECT * FROM tPnos WHERE ([Project No] < '7000') ORDER BY
[Project No] DESC"
Set recPno = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
GetLastPno = recPno![BaseProject]
recPno.Close
Exit Function
ErrHandle:
MsgBox Err.Description
End Function

If the second chunk of code is just to force a save you can use:
Me.Dirty = False

[quoted text clipped - 69 lines]

 

[Irene_58]

Hi,

I've resolved my problem - albeit empirically. I needed 2 changes:

1) I replaced the recordset .addnew / .update pair with RunCommand
acCmdRecordsGoToNew / acCmdSaveRecord

2) I set the focus to a textbox on the main form before setting any values
in the textboxes on the main form.

Neither change worked on its own. I'm guessing that the subform and the
main form were both trying to create a new record each causing the auto-num
to increment, and the save from the main form won?

Thanks for the comments - I'm off to invest some time in looking at multiple
keys and indexing.

 

[BruceM via AccessMonster.com]

The problem with the incrementing is that it cannot work with the field
values you have described and a text field. In a text sort, 100 and 10000
are less than 7000, and 77 is greater. For values with suffixes you will
probably get an error, but certainly nothing meaningful, from something like
1400-01 + 1. The main form "winning" does not enter into it.

Hi,

I've resolved my problem - albeit empirically. I needed 2 changes:

1) I replaced the recordset .addnew / .update pair with RunCommand
acCmdRecordsGoToNew / acCmdSaveRecord

2) I set the focus to a textbox on the main form before setting any values
in the textboxes on the main form.

Neither change worked on its own. I'm guessing that the subform and the
main form were both trying to create a new record each causing the auto-num
to increment, and the save from the main form won?

Thanks for the comments - I'm off to invest some time in looking at multiple
keys and indexing.

That's a very big change at this stage, I've implemented the company's
timesheet system & project management reporting based on the tPNos table as

[quoted text clipped - 22 lines]

 

[Irene_58]

No. I haven't explained well enough, and you've mixed up the 2 fields being
incremented.

The text field I manually increment works perfectly well (agreed only
because our project numbers start at 1000 and currently run to 15nn; also the
base project is always 4 digits so the text / numeric conversions aren't a
problem). I am definitely going to follow your advice and change the
BaseProject to a number, to future-proof the application and add data-entry
checking to enforce the numeric requirements on the first 4 characters.

BUT, it was the auto-num field generated by Access when a record is added
that was my problem. I just want to be clear in case anyone else runs up
against this.
--
Irene

The problem with the incrementing is that it cannot work with the field
values you have described and a text field. In a text sort, 100 and 10000
are less than 7000, and 77 is greater. For values with suffixes you will
probably get an error, but certainly nothing meaningful, from something like
1400-01 + 1. The main form "winning" does not enter into it.

Hi,

I've resolved my problem - albeit empirically. I needed 2 changes:

1) I replaced the recordset .addnew / .update pair with RunCommand
acCmdRecordsGoToNew / acCmdSaveRecord

2) I set the focus to a textbox on the main form before setting any values
in the textboxes on the main form.

Neither change worked on its own. I'm guessing that the subform and the
main form were both trying to create a new record each causing the auto-num
to increment, and the save from the main form won?

Thanks for the comments - I'm off to invest some time in looking at multiple
keys and indexing.

That's a very big change at this stage, I've implemented the company's
timesheet system & project management reporting based on the tPNos table as

[quoted text clipped - 22 lines]

display the combination, and you can (and probably should) also define a
unique two-field index on the combination.