Code to increment/update a field

[Michael Curry]

Hi,

I have a form (tabular layout). One of the text boxes has it's
control source set to lngOurAESSampleID in tbl_SMP_AESSamples. Upon
opening the form, this field is empty. Also on the form is a combo
box (cboSelectSuite).

What I would like to to is when a selection is made from the combo
box, I want to create a number in lngOurAESSampleID which is +1 higher
than the previous value.

I got as far as this code:

Private Sub cboSelectSuite_AfterUpdate()

On Error GoTo NextID_Err

Dim lngNextID As Long

'Find highest lngOurAESSampleID in the tbl_SMP_AESSamples table
and add 1
lngNextID = DMax("[lngOurAESSampleID]", "tbl_SMP_AESSamples") + 1
'Display lngNextID in a message box
MsgBox lngNextID

NextID_Err:
MsgBox "Error " & Err & ": " & Error$

End Sub

The value that I want to go in lngOurAESSampleID is displayed
correctly in the MsgBox. So from the code above, how to I get the
value to update to lngOurAESSampleID in tbl_SMP_AESSamples. Or is
there a better way to achieving this?

Thanks

Michael

 

[Marshall Barton]

I have a form (tabular layout). One of the text boxes has it's
control source set to lngOurAESSampleID in tbl_SMP_AESSamples. Upon
opening the form, this field is empty. Also on the form is a combo
box (cboSelectSuite).

What I would like to to is when a selection is made from the combo
box, I want to create a number in lngOurAESSampleID which is +1 higher
than the previous value.

I got as far as this code:

Private Sub cboSelectSuite_AfterUpdate()

On Error GoTo NextID_Err

Dim lngNextID As Long

'Find highest lngOurAESSampleID in the tbl_SMP_AESSamples table
and add 1
lngNextID = DMax("[lngOurAESSampleID]", "tbl_SMP_AESSamples") + 1
'Display lngNextID in a message box
MsgBox lngNextID

NextID_Err:
MsgBox "Error " & Err & ": " & Error$

End Sub

The value that I want to go in lngOurAESSampleID is displayed
correctly in the MsgBox. So from the code above, how to I get the
value to update to lngOurAESSampleID in tbl_SMP_AESSamples. Or is
there a better way to achieving this?

As long as the form is bound to the table
tbl_SMP_AESSamples, you can use:

lngOurAESSampleID = lngNextID

The DMax approach is fine in a single user application, but,
if multiple users might be doing this at the same time,
there is a (small?) chance that some of them could get the
same number.