var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Can't update MySql table from Form/subform
Eviron: Access app that uses a MySql db.
Problem: After order is picked on a subform, I cannot get an update or insert to work on the appropriate table.
Access prompts user to input valid values for form fields that are updating the table. When acceptable values are inserted update works.
Why can't Access find these values through the SQL query?
Code is as follows,
Referencing main form not subform controls:
DoCmd.RunSQL "Update tbl_pick Set prod_id = Forms![Blue Smoke Picker].prod_id, quantity = Forms![Blue Smoke Picker].quantity, price = Forms![Blue Smoke Picker].price Where show_number = Forms![Blue Smoke Picker].show_number"
This is the only query that I cannot get to work!
Re: Can't update MySql table from Form/subform
Try taking the local references out of the SQL statement:
Just to make sure that these references are resolved before the query is passed to MySQL
Dim strSQL As String
strSQL = "UPDATE tbl_pick SET" & _
"prod_id=" & Forms![Blue Smoke Picker].prod_id & ", " & _
"quantity=" & Forms![Blue Smoke Picker].quantity & ", " & _
"price=" & Forms![Blue Smoke Picker].price & _
"WHERE show_number=" & Forms![Blue Smoke Picker].show_number
Well, I know that I havn't tried everything yet!
The aformentioned query doesn't work.
Variations of above query plus using ' " & and FORMS object give various errors. Like: application-defined or object-defined error (solution-use FORMS! to create object and put field in .
Then error becomes: unable to locate "field_name"
If I use ''' or ''" to quote field then input to table becomes the code snippet within the quotes not it's value.
Please help with this, any suggestions that have not been tried are welcome.
'" & Forms![Blue Smoke Picker]![Picker Detail Subform].Form![prod_id] & "'
Works, so that Access knows where the data on the subform is!