Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2002
    Location
    Calgary
    Posts
    7

    Unhappy Unanswered: Can't update MySql table from Form/subform

    Hi,
    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!

    Any suggestions?

    Terry

  2. #2
    Join Date
    Oct 2002
    Location
    UK
    Posts
    21

    Re: Can't update MySql table from Form/subform

    Try taking the local references out of the SQL statement:

    Code:
    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
    DoCmd.RunSQL strSQL
    Just to make sure that these references are resolved before the query is passed to MySQL

  3. #3
    Join Date
    Oct 2002
    Location
    Calgary
    Posts
    7
    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.

    Terry

  4. #4
    Join Date
    Oct 2002
    Location
    Calgary
    Posts
    7
    Fixed it!
    '" & Forms![Blue Smoke Picker]![Picker Detail Subform].Form![prod_id] & "'
    Works, so that Access knows where the data on the subform is!

    Terry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •