qestion about form_dirty function

M

Matt

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.
 
P

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
 
M

Matt

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

Paul Overway said:
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


Matt said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top