qestion about form_dirty function

[Guest]

I have a simple form that needs to be numbered automatically, so I created
the following function.
Private Sub Form_Dirty(Cancel As Integer)
Dim lnum As Long

lnum = DLookup("auto_ticket_num", "auto_num")
[ticket #] = CStr(lnum)
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE auto_num SET auto_ticket_num = " & lnum + 1
DoCmd.SetWarnings True
End Sub

This grabs the current number in auto_num table, puts it on the form, and
then updates the table by adding one to the number. This creates an auto
number system which is simple and easy, or so I though.
The data is put into the form, and then next record is clicked and process
is repeated. The problem I run into is if you go back to a previous form to
make a change, it will change the autonumber because it is dirty. I can't
have that happening. So I tried to change the above function by adding an if
statement.

Private Sub Form_Dirty(Cancel As Integer)
Dim lnum As Long
If [ticket #].Value = Null Then
lnum = DLookup("auto_ticket_num", "auto_num")
[ticket #] = CStr(lnum)
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE auto_num SET auto_ticket_num = " & lnum + 1
DoCmd.SetWarnings True
End If
End Sub

I figured if the Ticket # field was null, then an auto number should be
added, and if its full, it wouldn't change the autonumber. Yet now this
function won't even run. I tried putting in a break point, and it never gets
stopped. It seems to skip right over it. Is that because of the if statement
I added, or are you not able to do certain thing swith the dirty sub function?

p.s. This is the laat piece of coding I need to do on a long project, and
would appreciate any help to get this done.

 

[Lynn Trapp]

Try putting your code in the AfterInsert event of your form.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[Paul Overway]

You cannot do any math comparison against Null. Null has no value...so,
your If statement will always be skipped.

The proper way would be...

If IsNull([ticket #].Value) Then

 

[Guest]

Thank you Paul. The function is working. I really appreciate it.
And Lynn, thanks for the repeated help this morning.
Project is done

You cannot do any math comparison against Null. Null has no value...so,
your If statement will always be skipped.

The proper way would be...

If IsNull([ticket #].Value) Then

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com

I have a simple form that needs to be numbered automatically, so I created
the following function.
Private Sub Form_Dirty(Cancel As Integer)
Dim lnum As Long

lnum = DLookup("auto_ticket_num", "auto_num")
[ticket #] = CStr(lnum)
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE auto_num SET auto_ticket_num = " & lnum + 1
DoCmd.SetWarnings True
End Sub

This grabs the current number in auto_num table, puts it on the form, and
then updates the table by adding one to the number. This creates an auto
number system which is simple and easy, or so I though.
The data is put into the form, and then next record is clicked and process
is repeated. The problem I run into is if you go back to a previous form
to
make a change, it will change the autonumber because it is dirty. I can't
have that happening. So I tried to change the above function by adding an
if
statement.

Private Sub Form_Dirty(Cancel As Integer)
Dim lnum As Long
If [ticket #].Value = Null Then
lnum = DLookup("auto_ticket_num", "auto_num")
[ticket #] = CStr(lnum)
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE auto_num SET auto_ticket_num = " & lnum + 1
DoCmd.SetWarnings True
End If
End Sub

I figured if the Ticket # field was null, then an auto number should be
added, and if its full, it wouldn't change the autonumber. Yet now this
function won't even run. I tried putting in a break point, and it never
gets
stopped. It seems to skip right over it. Is that because of the if
statement
I added, or are you not able to do certain thing swith the dirty sub
function?

p.s. This is the laat piece of coding I need to do on a long project, and
would appreciate any help to get this done.