Need help editing Database via Form

[ismith]

Hello,

Ive got a form with bound txt boxes that displays the information in the database. Now ive put an edit button which when its clicked it allow the txt boxes and combo boxes to be edited.

Ive got the edits and so forth to work.

This is the coding for my Save Button

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

             editprod("[Description]") = Description.Value
             editprod("[Category]") = Category.Value
             editprod("[Size]") = Size.Value
             editprod("[Quantity]") = Quantity.Value
             editprod("[Price]") = "£" + Price.Value
             
             editprod.Update
             
             MsgBox ("Updated")
           End If
    Exit Sub
End Sub

Now that code does add to the product table but it dont change that specific Record. It just overwrites the information in the 1st Record.
And if i edit another record, the 1st Record in the database gets overwritten again.

Any ideas how i can stop this and change the values of the product i have chosen?

Each product does have a Uniwue ProductID but i cant get it to change the Record that is chosen.

All help is welcome..


Thanks..

 

[Mr. B]

Ive got a form with bound txt boxes that displays the information in the database. Now ive put an edit button which when its clicked it allow the txt boxes and combo boxes to be edited

If the text boxes are truly "bound" then you do not need to open the record set and try to update the values. You would simple need to check to see if the current record is dirty (changed) and if so, save the changes.

You are opening the recordset to the first record in the "tblProduct" table because are are not specifically moving to any specific record.

You do no say how you are moving from one record to the other in your form.

The only time you need to open a recordset and update records is when your form is an "Unbound" type form. Otherwise, with the form "bound" to the data, you are actually editing the currently displayed record and only have to save the changes.

Hope this helps.

 

[missinglinq]

Mr. B is right on the money, here! And all of your Validation codes needs to go in the Form_BeforeUpdate event. Here's a short example of the Syntax:

Private Sub Form_BeforeUpdate(Cancel As Integer)

 If Nz(Me.Control1,"") = "" Then
   MsgBox "Control1 Must Not Be Left Blank!"
   Cancel = True
   Control1.SetFocus
   Exit Sub
 End If
 
If Nz(Me.Control2, "") = "" Then
   MsgBox "Control2 Must Not Be Left Blank!"
   Cancel = True
   Control2.SetFocus
   Exit Sub
 End If

End Sub

Linq ;0)>