HOW do I insert a number in a field....

  • Thread starter Thread starter kealaz
  • Start date Start date
K

kealaz

Hello,

HOW do I insert a number into a field that is one more than the last number
in a field in a different table? I have a form that I am designing (for
purchase orders). What I need is for it to look at the last record in the
existing purchase order table (tblPOHIST) in the field, P_O_NO, and take that
value, add one and insert that number into the field. i.e., if the last PO #
is 001653, then I need the next one to be 001654.

Thank you in advance for any and all help!
 
Hello,

HOW do I insert a number into a field that is one more than the last number
in a field in a different table?  I have a form that I am designing (for
purchase orders).  What I need is for it to look at the last record in the
existing purchase order table (tblPOHIST) in the field, P_O_NO, and take that
value, add one and insert that number into the field.  i.e., if the last PO #
is 001653, then I need the next one to be 001654.

Thank you in advance for any and all help!
Click to expand...

You don't do this at table level, but at form level. If you're
entering data directly into tables, you're asking for a LOT of
trouble. No complex validation is possible...

Read this:
http://www.mvps.org/access/strings/str0007.htm
 
Hello,
Thank you for your response. Yes, I am using a form. How do get a field in
a form to look at a value in the last record of a table and increase that
value by one and "auto-number" the field? Sorry I didn't clarify that in my
original post. Thank you for your help.
 
Hello,
Thank you for your response. Yes, I am using a form. How do get a field in
a form to look at a value in the last record of a table and increase that
value by one and "auto-number" the field? Sorry I didn't clarify that in my
original post. Thank you for your help.
Click to expand...

If it's a one-user application (so you don't have to worry about two users
adding new records at the same time) you can use the Form's BeforeInsert
event:

Private Sub Form_BeforeInsert()
Me!IDfield = NZ(DMax("[IDfield]", "[tablename]")) + 1
End Sub

If it's a multiuser app, post back for more elaborate suggestions...
 
Thank you SO MUCH! This worked great!

John W. Vinson said:
Hello,
Thank you for your response. Yes, I am using a form. How do get a field in
a form to look at a value in the last record of a table and increase that
value by one and "auto-number" the field? Sorry I didn't clarify that in my
original post. Thank you for your help.
Click to expand...

If it's a one-user application (so you don't have to worry about two users
adding new records at the same time) you can use the Form's BeforeInsert
event:

Private Sub Form_BeforeInsert()
Me!IDfield = NZ(DMax("[IDfield]", "[tablename]")) + 1
End Sub

If it's a multiuser app, post back for more elaborate suggestions...
Click to expand...
 
Back
Top