Need help! Please

[ismith]

Hi,

Im making a form which allows the user to add products to the database i have created.

Now in my table (tblProduct) ive got a ProductID field which auto incrmements for every product added. What im trying to do it display the next ProductID on the form so that the user can see what number product it is on the system.

this is how im trying to do it:

Private Sub Form_load()
Set dbs = CurrentDb

Dim mysql As String
Set mysql = "SELECT MAX(ProductID) FROM tblProduct;"

Set prodno = dbs.OpenRecordset("mysql", dbOpenDynaset)


txtProdID.Value = prodno([ProductID])


End Sub

Now with this im trying to add it to my Access table but its showing as NULL Value.

This is the way im trying to add it:

addprod.AddNew
addprod([ProductID]) = txtProdID.Value
addprod([Description]) = txtDescription.Value
addprod([Category]) = cbCategory.Value
addprod([Size]) = txtSize.Value
addprod([Quantity]) = txtQuantity.Value
addprod([Price]) = "£" + txtPrice.Value
addprod.Update

addprod.Bookmark = addprod.LastModified

MsgBox ("Prouct successfully added")


Could anyone help and tell me why its not working??


I get Error code '91'.

Need urgent help Please.

 

[Uncle Gizmo]

What im trying to do it display the next ProductID

The next product ID will only be created when you actually start entering a record. It is not safe to look at the list of previous Id's and predict that the next ID will be one greater, for example if a record has been deleted then this will not be the case.

 

[ismith]

So what do you suggest i should do to get round this?

im fairly new to access and vba so aint got my knowledge..