Help with form

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

[hertfordkc]

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)
Prodno is what?? Dim prodno as recordset

txtProdID.Value = prodno([ProductID])
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.

Let me know if this helps.

[ismith]

ive tried what youve said.

Now im getting run time error '2465'

And when i debug its highlighting this line:

addprod([ProductID]) = txtProdID.Value

and when i put my mouse over the txtProdID.Value it shows as NULL.

Any help?

Thanks.

[hertfordkc]

ive tried what youve said.

Now im getting run time error '2465'

And when i debug its highlighting this line:

addprod([ProductID]) = txtProdID.Value

and when i put my mouse over the txtProdID.Value it shows as NULL.

Any help?

Thanks.

The addprod([ProductID]) isn't correct. Assuming that addprod is dim as recordset, I believe you need addprod.[ProductID]
You didn't show the dim for txtProdID. Is it a control, a field,....
I suspect the reference is incorrect, but without more info, I can't tell what's needed to fix it.

[ismith]

this is my whole class.

Option Compare Database
Option Explicit
Dim dbs As Database
Dim addprod As DAO.Recordset
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("mysql", dbOpenDynaset)


txtProdID.Value = prodno![ProductID]

End Sub

Private Sub btnAdd_Click()

If Trim("" & txtDescription.Value) = "" Then
MsgBox "Please enter a Description", vbExclamation, "Cannot Save"
ElseIf Trim("" & cbCategory.Value) = "" Then
MsgBox "Please select a Category", vbExclamation, "Cannot Save"
ElseIf Trim("" & txtQuantity.Value) = "" Then
MsgBox "Please enter a Quantity", vbExclamation, "Cannot Save"
ElseIf Not IsNumeric(txtQuantity.Value) Then
MsgBox "Quantity can only contain numbers", vbExclamation, "Cannot Save"
ElseIf txtQuantity.Value = 0 Then
MsgBox "The quantity must be over Zero", vbExclamation, "Cannot Save"
ElseIf txtQuantity.Value < 0 Then
MsgBox "The quantity must be over Zero", vbExclamation, "Cannot Save"
ElseIf txtPrice.Value = 0 Then
MsgBox "The price must be over Zero", vbExclamation, "Cannot Save"
ElseIf txtPrice.Value < 0 Then
MsgBox "The price must be over Zero", vbExclamation, "Cannot Save"
ElseIf Trim("" & txtPrice.Value) = "" Then
MsgBox "Please enter a Price", vbExclamation, "Cannot Save"
ElseIf MsgBox("Save Product Details?", vbOKCancel + vbQuestion, "Confirmation") = vbOK Then
'get a reference to the record set in the main form

Set addprod = Form_frmProduct.getRecordSet

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

txtDescription.SetFocus
txtDescription = ""
cbCategory.SetFocus
cbCategory = ""
txtSize.SetFocus
txtSize = ""
txtQuantity.SetFocus
txtQuantity = ""
txtPrice.SetFocus
txtPrice = ""
txtDescription.SetFocus
End If
Exit Sub

'Once the product is entered this clears all the fields so a new product
'can be entered.

End Sub

Private Sub btnClear_Click()
'This clears the fields when the Clear button is pressed.
txtDescription.SetFocus
txtDescription = ""
cbCategory.SetFocus
cbCategory = ""
txtSize.SetFocus
txtSize = ""
txtQuantity.SetFocus
txtQuantity = ""
txtPrice.SetFocus
txtPrice = ""
txtDescription.SetFocus
End Sub

Private Sub btnClose_Click()
'If this button is pressed the AddProd form is closed and it opens the
'Product form for the user.
DoCmd.Close acForm, "frmAddProduct"
DoCmd.OpenForm "frmProduct"
End Sub




So what im trying to do is look into my tblProduct and get the next ProductID and put it into the txtProdID. This i have set as uneditable.
so when the user adds the product they can see the Product ID that it will have.

After that im adding it into the database but its not letting me do that aswell.

Thanks and really appreciate your help.

[hertfordkc]

this is my whole class.

Option Compare Database
Option Explicit
Dim dbs As Database
Dim addprod As DAO.Recordset
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("mysql", dbOpenDynaset)

debug.print prodno![ProductID] ' Looks like you are getting nulls here and I wouldn't expect it, and I don't know what you might do to correct it.

txtProdID.Value = prodno![ProductID]

End Sub

Private Sub btnAdd_Click()

If Trim("" & txtDescription.Value) = "" Then
MsgBox "Please enter a Description", vbExclamation, "Cannot Save"
ElseIf Trim("" & cbCategory.Value) = "" Then
MsgBox "Please select a Category", vbExclamation, "Cannot Save"
ElseIf Trim("" & txtQuantity.Value) = "" Then
MsgBox "Please enter a Quantity", vbExclamation, "Cannot Save"
ElseIf Not IsNumeric(txtQuantity.Value) Then
MsgBox "Quantity can only contain numbers", vbExclamation, "Cannot Save"
ElseIf txtQuantity.Value = 0 Then
MsgBox "The quantity must be over Zero", vbExclamation, "Cannot Save"
ElseIf txtQuantity.Value < 0 Then
MsgBox "The quantity must be over Zero", vbExclamation, "Cannot Save"
ElseIf txtPrice.Value = 0 Then
MsgBox "The price must be over Zero", vbExclamation, "Cannot Save"
ElseIf txtPrice.Value < 0 Then
MsgBox "The price must be over Zero", vbExclamation, "Cannot Save"
ElseIf Trim("" & txtPrice.Value) = "" Then
MsgBox "Please enter a Price", vbExclamation, "Cannot Save"
ElseIf MsgBox("Save Product Details?", vbOKCancel + vbQuestion, "Confirmation") = vbOK Then
'get a reference to the record set in the main form

Set addprod = Form_frmProduct.getRecordSet
addprod.AddNew
addprod([ProductID]) = txtProdID.Value 'addprod![ProductID] = [txtProdID].value
addprod([Description]) = txtDescription.Value ' see above syntax
addprod([Category]) = cbCategory.Value ' see above syntax
addprod([Size]) = txtSize.Value ' see above syntax
addprod([Quantity]) = txtQuantity.Value ' see above syntax
addprod([Price]) = "£" + txtPrice.Value ' see above syntax
addprod.Update

addprod.Bookmark = addprod.LastModified

MsgBox ("Prouct successfully added")

txtDescription.SetFocus
txtDescription = ""
cbCategory.SetFocus
cbCategory = ""
txtSize.SetFocus
txtSize = ""
txtQuantity.SetFocus
txtQuantity = ""
txtPrice.SetFocus
txtPrice = ""
txtDescription.SetFocus
End If
Exit Sub

'Once the product is entered this clears all the fields so a new product
'can be entered.

End Sub

Private Sub btnClear_Click()
'This clears the fields when the Clear button is pressed.
txtDescription.SetFocus
txtDescription = ""
cbCategory.SetFocus
cbCategory = ""
txtSize.SetFocus
txtSize = ""
txtQuantity.SetFocus
txtQuantity = ""
txtPrice.SetFocus
txtPrice = ""
txtDescription.SetFocus
End Sub

Private Sub btnClose_Click()
'If this button is pressed the AddProd form is closed and it opens the
'Product form for the user.
DoCmd.Close acForm, "frmAddProduct"
DoCmd.OpenForm "frmProduct"
End Sub




So what im trying to do is look into my tblProduct and get the next ProductID and put it into the txtProdID. This i have set as uneditable.
so when the user adds the product they can see the Product ID that it will have.
I would have expected you to retrieve the MAX PID already in tblProduct, but not the one for the record being entered. You might be able to use Max + 1, but I'm not sure that you can count on it. As noted above, I'm wondering if you are actually retrieving the MAX PID.

After that im adding it into the database but its not letting me do that aswell.

Thanks and really appreciate your help.

BTW, since you are using the recordset methods to manipulate the data and load it into a temp recordset, I don't see why you didn't just go ahead and deal with the target recordset and leave out the temporary recordset.