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

[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!

 

[Piet Linden]

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!

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

 

[kealaz]

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.

 

[John W. Vinson]

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.

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

 

[kealaz]

Thank you SO MUCH! This worked great!

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.

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