Autonumber Increment

  • Thread starter Thread starter chopper57 via AccessMonster.com
  • Start date Start date
C

chopper57 via AccessMonster.com

I have a MainForm called MainForm, on the main form I have two datasheet
forms.
The first datasheet Form called SovItems and is linked to the MainForm with a
field called Division
The second datasheet is called SovInstalled and is linked to the first
datasheet using a field called ItemNo by using a unbound txtbox called
txtLink (this Link works great everything is in sync.)

What I need to do is have a field in the second datasheet Form auto increment
a number.
The field name is PayAppNo.

This is what I have tried:

Me.PayAppNo = Format(Nz(DMax("[PayAppNo]", "tbl_SovInstalled", "[ItemNo]=" &
"Forms![frm_MainForm]![Division]"), 0) + 1, "00")

I get the first number to enter (1) but each new entry is also (1), it's not
adding a number, every entry is the number (1).
 
Try removing the quotes from around "Forms![frm_MainForm]![Division]".

You're trying to compare ItemNo to a string: you want to compare it to the
value that's contained in that control.
 
I tried what you suggested, but it doesn't work.
After I made the change I receive the Run-time error 3464, Data type mismatch
in criteria expression.
Try removing the quotes from around "Forms![frm_MainForm]![Division]".

You're trying to compare ItemNo to a string: you want to compare it to the
value that's contained in that control.
I have a MainForm called MainForm, on the main form I have two datasheet
forms.
Click to expand...
[quoted text clipped - 19 lines]
not
adding a number, every entry is the number (1).
Click to expand...
Click to expand...
 
That implies that ItemNo is a text field, not a number, in which case you
need quotes around the value (but not around the control name):

Me.PayAppNo = Format(Nz(DMax("[PayAppNo]", "tbl_SovInstalled", _
"[ItemNo]='" & Forms![frm_MainForm]![Division] & "'"), 0) + 1, "00")

Exagerated for clarity, that's


Me.PayAppNo = Format(Nz(DMax("[PayAppNo]", "tbl_SovInstalled", _
"[ItemNo]= ' " & Forms![frm_MainForm]![Division] & " ' "), 0) + 1, "00")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


chopper57 via AccessMonster.com said:
I tried what you suggested, but it doesn't work.
After I made the change I receive the Run-time error 3464, Data type
mismatch
in criteria expression.
Try removing the quotes from around "Forms![frm_MainForm]![Division]".

You're trying to compare ItemNo to a string: you want to compare it to the
value that's contained in that control.
I have a MainForm called MainForm, on the main form I have two datasheet
forms.
Click to expand...
[quoted text clipped - 19 lines]
not
adding a number, every entry is the number (1).
Click to expand...
Click to expand...
Click to expand...
 
Thanks Douglas, that worked!
That implies that ItemNo is a text field, not a number, in which case you
need quotes around the value (but not around the control name):

Me.PayAppNo = Format(Nz(DMax("[PayAppNo]", "tbl_SovInstalled", _
"[ItemNo]='" & Forms![frm_MainForm]![Division] & "'"), 0) + 1, "00")

Exagerated for clarity, that's

Me.PayAppNo = Format(Nz(DMax("[PayAppNo]", "tbl_SovInstalled", _
"[ItemNo]= ' " & Forms![frm_MainForm]![Division] & " ' "), 0) + 1, "00")
I tried what you suggested, but it doesn't work.
After I made the change I receive the Run-time error 3464, Data type
Click to expand...
[quoted text clipped - 11 lines]
Click to expand...
 
Back
Top