Form won't update table

[Master Klick]

I created a new form using form design. This form will not allow me to make changes to my table.



When I use "Form" instead of "Form Design" it will allow me to set up controls to edit the table.

Does anyone know what the difference is there? Is there anything I can do to fix that?

[ItsMe]

That does not make sense. A form's properties can dictate if a field or all fields can be edited, etc.

Start with the form's recordset. Is it based on a table or a query? If it is a query, open the query object and see if you can edit the fields. Then move to the form's properties; Allow Edits, Allow Additions, Allow Deletions. Also control properties; Locked, Enabled

[Master Klick]

That does not make sense. A form's properties can dictate if a field or all fields can be edited, etc.

Start with the form's recordset. Is it based on a table or a query? If it is a query, open the query object and see if you can edit the fields. Then move to the form's properties; Allow Edits, Allow Additions, Allow Deletions. Also control properties; Locked, Enabled

The recordset is based on a query and I can edit the fields in it. In the form, edits, additions and deletions are allowed. The control is enabled.

I tried having VBA edit the quantity in both the query:

Private Sub PlusOne_Click()
DoCmd.SetWarnings False
Dim strSQL As String
strSQL = "UPDATE [InvQuery] SET [Quantity] = [Quantity] + 1 " & _
"WHERE [InvQuery].[ID] = " & Me!
[List] & ";"
DoCmd.RunSQL strSQL
Me!List.Requery
DoCmd.SetWarnings True
End Sub

And the table:

Private Sub PlusOne_Click()
DoCmd.SetWarnings False
Dim strSQL As String
strSQL = "UPDATE [Inventory] SET [Quantity] = [Quantity] + 1 " & _
"WHERE [Inventory].[ID] = " & Me!
[List] & ";"
DoCmd.RunSQL strSQL
Me!List.Requery
DoCmd.SetWarnings True
End Sub

In both cases a window pops up asking me to enter a quantity. When I hit ok I get the error.

[ItsMe]

Now I am more confused. Your action queries should work with the exception of...

"WHERE [InvQuery].[ID] = " & Me!

[List] & ";"

when I paste it into a text file it has a return. Should be in one line.

"WHERE [InvQuery].[ID] = " & Me!
[List] & ";"

If that is the problem, the error you are getting should be something about missing operator or something SQL. So, line continuation is probably not it.

EDIT: The line continuation thing is the bracket and how HTML handles it. Never mind line continuation


Not sure I know what "pops up asking me to enter a quantity" is. Does your form have a control bound to a field name that does not exist in the query? I do not understand the error.

Your post does not add up when you state "edits, additions and deletions are allowed. The control is enabled". This means that all is well and you should be able to open the form and edit records. The only other property to consider would be the form's Data Entry property. I do not see any problems. Maybe you can describe the error in more detail.

Those click events should function autonomously.

[Master Klick]

Help.zip

Ok, I attached the file. Had to delete most of the records but it still works the same.

In the InvForm, the + and - buttons next to the picture, that's what I can't get working.

[ItsMe]

Couple of things.

First, when you have a desire to edit fields or UPDATE fields, make syre the query you are using is updateable. In this case, there is not a need to use a query since you are only updating one field. Suggest you use the table object in your UPDATE query statement.

Second, there is not a Quantity field in your table or query that I could see.

Private Sub PlusOne_Click()
If IsNull(Me.List) Then
MsgBox "Please make a selection before proceeding.", vbCritical, "Request Cancelled"
Exit Sub
End If

DoCmd.SetWarnings False
Dim strSQL As String
strSQL = "UPDATE [Inventory] SET [Qty] = [Qty] + 1 " & _
          "WHERE [Inventory].[ID] = " & Me!
[List] & ";"
'strSQL = "UPDATE Inventory SET Inventory.Qty = [Qty] +1 " & _
'"WHERE [Inventory].[ID] = 24325"
DoCmd.RunSQL strSQL
Me!List.Requery
DoCmd.SetWarnings True
End Sub

I added some code to make sure a selection in the listbox was made. Also, notice the commented out code. Hardcoding values can be useful when troubleshooting. In this case, nothing was wrong with your listbox properties, etc. but it allowed me to stay focussed on the problem at hand.

[Master Klick]

OMG! I'm such an idiot. In the table I named it "Qty". I suppose I should probably set to the code to refer to what I actually named the field.

Thanks for all the help.

[ItsMe]

Not a problem. Glad you got it sorted.