Previous Command No Longer Works

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pushrodengine - 20 Dec 2007 01:12 GMT

I’m using coding below to insert “0000-yy” into a textbox.
The numbering system works great except that I have lost the ability to move
back to the previous record. It must be something to do with the code.

How can I fix it?

Private Sub Form_Current()
If Me.NewRecord Then
Dim strWhere As String
Dim varResult As Variant

strWhere = "IncidentID Like """ & Format(Date, "yy") & "*"""
varResult = DMax("IncidentID", "tblIncidentLog", strWhere)

If IsNull(varResult) Then
Me.IncidentID = Format(Date, "yy") & "-0001"
Else
Me.IncidentID = Left(varResult, 3) & _
Format(Val(right(varResult, 4)) + 1, "0000")
End If
End If
End Sub

Reply to this Message

Allen Browne - 20 Dec 2007 01:55 GMT

You *never* want to assign a value to a bound control in Form_Current. It
makes no sense to alter the data in the record just because somebody visited
it. That would imply that the data would be invalid if nobody visited the
record.

In your case, you don't want to dirty the record until you know the user
intends entering something. Ideally, you want to run this at the last
possible moment before the new record is saved, to reduce the possibility
that 2 users entering data together are given the same number. Move the code
into Form_BeforeUpdate().

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> I’m using coding below to insert “0000-yy” into a textbox.
> The numbering system works great except that I have lost the ability to
[quoted text clipped - 19 lines]
> End If
> End Sub

Reply to this Message

pushrodengine - 20 Dec 2007 03:57 GMT

Thank You for The Help!

Reply to this Message