What's wrong with this OnCurrent code?

[dylan03]

I have a Sales form which needs to automatically enter a TransactionID. I want the number entered to be in the format of 2002-### )the second portion being the next sequential number.

In [Sales.TransactionID] I have set the input mask to >0000\-00;0

The field name on the form is [TransID]

In the OnCurrent property of the form, I have the following code:

Private Sub Form_Current()

If Me.NewRecord = True Then
If IsNull(DMax(Sales.TransactionID)) Then
TransID = Right(Year()) + DMax(Sales.TransactionID)
Else
TransID = Sales.TransactionID
End If

End If


End Sub



PLEASE HELP! I really got myself into a pickle trying to figure this out!

 

[danvlas]

Sorry to disappoint you, but everything is wrong:
1. Current event is NOTsuitable for your purpose, as it will always write to a new record, even though you're just navigating... Correct event for this: BeforeInsert for single user applications and Form_BeforeUpdate (together with some locking routine) for multi-user

2. DMax syntax is wrong. Correct:
DMax("FldName", "TblQryName", "CriteriaIfAny&quot
And you should use the criteria for what you need.

3. The Right syntax is wrong. Correct:
Right(Expression, NoOfChars)

So...
Private Sub Form_BeforeInsert(Cancel As Integer)
TransID = Val(Year(Date) & Format(nz(DMax("TransactionID","Sales","Left(TransactionID & " ", 4) = Year(Date())),1),"000&quot)
End Sub


Or, with BeforeUpdate:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Start:
'If Locking routine of your choice allows
If Not Locked Then
TransID = Val(Year(Date) & Format(nz(DMax("TransactionID","Sales","Left(TransactionID & " ", 4) = Year(Date())),1),"000&quot)
Else
'Else go back to start until locking routine allows
Cancel = True
GoTo Start
End If
End Sub

This may get you into an infinite loop, but you can add a counter to the retries... It's just an idea...


HTH

Dan
Daniel Vlas
Systems Consultant

 

[dylan03]

Thanks for your help, Daniel!! One quick thing....

When I insert the Before Insert Code into my form, I receive a Microsoft Visual Basic compile error that states "Expected: list seperator or )". In the VB window, it highlights this section of the code:

", 4) = Year(Date())),1,"

Can you help me again?? Thanks, Daniel!!!!!!