DMAX problem

[richard]

Hi
I have a form (frmeeaquote), based on tbleeaquote, for inputting details of
quotes. I wish to have an incrementing order number (field 'quoteref').
I have a command button on the form to create new records and have the
following code but I am not getting an incrementing value in the 'quoteref'
field.
Could someone please advise where I am going wrong

Private Sub cmdnewquote_Click()
On Error GoTo Err_cmdnewquote_Click


DoCmd.GoToRecord , , acNewRec

Exit_cmdnewquote_Click:
Exit Sub

Err_cmdnewquote_Click:
MsgBox Err.description
Resume Exit_cmdnewquote_Click

Me!quoteref = Nz(DMax("[quoteref]", "[tbleeaquote]"), 0) + 1
End Sub

thanks

Richard

 

[Baz]

Under normal circumstances (i.e. no error) the procedure terminates at the
Exit Sub statement i.e. it never gets anywhere near your DMax statement.
Presumably this is what you meant:

Private Sub cmdnewquote_Click()
On Error GoTo Err_cmdnewquote_Click


DoCmd.GoToRecord , , acNewRec
Me!quoteref = Nz(DMax("[quoteref]", "[tbleeaquote]"), 0) + 1

Exit_cmdnewquote_Click:
Exit Sub

Err_cmdnewquote_Click:
MsgBox Err.description
Resume Exit_cmdnewquote_Click

End Sub

However, if this is a multi-user application I would strongly advise you
against doing this, because there is nothing to stop another user doing
exactly the same thing and getting exactly the same number. You can reduce
the likelihood of this by grabbing the number in the form's BeforeUpdate
event instead, but it's still not 100% foolproof. Constructing a completely
bulletproof incrementing number is not at all easy - it might be better to
accept the risk (which is large if you do it your way, small if you move it
to the BeforeUpdate event) and to trap/handle the potential error.

Hi
I have a form (frmeeaquote), based on tbleeaquote, for inputting details of
quotes. I wish to have an incrementing order number (field 'quoteref').
I have a command button on the form to create new records and have the
following code but I am not getting an incrementing value in the 'quoteref'
field.
Could someone please advise where I am going wrong

Private Sub cmdnewquote_Click()
On Error GoTo Err_cmdnewquote_Click


DoCmd.GoToRecord , , acNewRec

Exit_cmdnewquote_Click:
Exit Sub

Err_cmdnewquote_Click:
MsgBox Err.description
Resume Exit_cmdnewquote_Click

Me!quoteref = Nz(DMax("[quoteref]", "[tbleeaquote]"), 0) + 1
End Sub

thanks

Richard

 

[richard]

I have actually worked out the placement of the line of code. But thanks for
the advice on the events

Thanks

Richard

Under normal circumstances (i.e. no error) the procedure terminates at the
Exit Sub statement i.e. it never gets anywhere near your DMax statement.
Presumably this is what you meant:

Private Sub cmdnewquote_Click()
On Error GoTo Err_cmdnewquote_Click


DoCmd.GoToRecord , , acNewRec
Me!quoteref = Nz(DMax("[quoteref]", "[tbleeaquote]"), 0) + 1

Exit_cmdnewquote_Click:
Exit Sub

Err_cmdnewquote_Click:
MsgBox Err.description
Resume Exit_cmdnewquote_Click

End Sub

However, if this is a multi-user application I would strongly advise you
against doing this, because there is nothing to stop another user doing
exactly the same thing and getting exactly the same number. You can reduce
the likelihood of this by grabbing the number in the form's BeforeUpdate
event instead, but it's still not 100% foolproof. Constructing a completely
bulletproof incrementing number is not at all easy - it might be better to
accept the risk (which is large if you do it your way, small if you move it
to the BeforeUpdate event) and to trap/handle the potential error.

Hi
I have a form (frmeeaquote), based on tbleeaquote, for inputting details of
quotes. I wish to have an incrementing order number (field 'quoteref').
I have a command button on the form to create new records and have the
following code but I am not getting an incrementing value in the 'quoteref'
field.
Could someone please advise where I am going wrong

Private Sub cmdnewquote_Click()
On Error GoTo Err_cmdnewquote_Click


DoCmd.GoToRecord , , acNewRec

Exit_cmdnewquote_Click:
Exit Sub

Err_cmdnewquote_Click:
MsgBox Err.description
Resume Exit_cmdnewquote_Click

Me!quoteref = Nz(DMax("[quoteref]", "[tbleeaquote]"), 0) + 1
End Sub

thanks

Richard