Need help with access Form

[ismith]

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

Dim prodno As Recordset

Private Sub Form_load()
 Set dbs = CurrentDb
 
 Dim mysql As String
 mysql = "SELECT MAX(ProductID) FROM tblProduct;"
 
 Set prodno = dbs.OpenRecordset("tblProduct", dbOpenDynaset)
 
 
 txtProdID.Value = prodno![ProductID]

Ive tried this and its not working.

Any help??

[pod]

try this as your SQL statement

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