By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,704 Members | 1,114 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,704 IT Pros & Developers. It's quick & easy.

Need help with access Form

P: 1
Ive got a Product table that has a productID.
This is incremented insdie the access table itself with auto increment.

Now ive got a AddProduct Form which has the necessary txt boxes for the user to input the information. What im trying to do is show the ProductID on the Add form.

The product ID is located in the tblProduct and i want to display the next value in it.

This is my code atm

Expand|Select|Wrap|Line Numbers
  2. Dim prodno As Recordset
  4. Private Sub Form_load()
  5.  Set dbs = CurrentDb
  7.  Dim mysql As String
  8.  mysql = "SELECT MAX(ProductID) FROM tblProduct;"
  10.  Set prodno = dbs.OpenRecordset("tblProduct", dbOpenDynaset)
  13.  txtProdID.Value = prodno![ProductID]
Ive tried this and its not working.

Any help??
Dec 14 '11 #1
Share this Question
Share on Google+
1 Reply

P: 298
try this as your SQL statement

Expand|Select|Wrap|Line Numbers
  1. mysql = "SELECT MAX(ProductID) as ProductID FROM tblProduct;"
but this will give you the last existing record ID, NOT the ID of the next record...

you could add 1 to this number, and it might be right only if you have not deleted any record after this record was created (the one with MAX ID).
Dec 14 '11 #2

Post your reply

Sign in to post your reply or Sign up for a free account.