Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2014

    Unanswered: Syntax error When transferring records with checkbox filter

    For the life of me I can't figure this out

    I'm trying to transfer records from a table in one subform to another subform (on same form) using a checkbox as a filter:

    Private Sub TransferAndReview_Click()
    Dim db As dao.Database
    Dim strSQL As String

    strSQL = "INSERT INTO Forms![Extra Work Report Checksheet]![Equipment Input Subform1].Form[(QuantityUsed[, HoursUsed])] VALUES (QuantityUsed[, HoursUsed]) FROM Forms![Extra Work Report Checksheet]![Equipment Checksheet Table Subform] WHERE [Extra Work Report Checksheet]![Equipment Input Subform1].Form[EquipmentUsed]=True;"

    Set db = CurrentDb
    db.Execute strSQL, dbFailOnError

    End Sub

    The Main form is: [Extra Work Report Checksheet]

    Subforms are: [Equipment Input Subform1] & [Equipment Checksheet Table Subform]

    Checkbox field is: [EquipmentUsed]

    I keep getting the error "Syntax error in INSERT INTO statement" which points to 'db.Execute strSQL, dbFailOnError' and I don't understand why. Neither of the fields [QuanityUsed] or [HoursUsed] are lookup fields, etc. Could someone please help out.

    Thank you

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    insert into, unsurprisingly inserts the following values into a specific table or view it doens't insert them into a form

    you can push values from a form to another form using
    forms!destinationformname!destinationcontrolname.v alue = sourcecontrolname.value
    or you can pull values from a form to another form using
    destinationformname!destinationcontrolname.value = forms!sourceformname!sourcecontrolname.value

    if youwant to use an insert query to add values to another table then do so, but afterwards force a refresh of the other forms data

    another approach would be to add a new record to the other form, then add the value from this form, then update the other form.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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