Transfer Records using a checkbox as a filter

[ajochung]

Hey Everyone and thank you looking at my problem (which is very frustrating).

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
ajochung is online now Add to ajochung's Reputation Report Post

[Rabbit]

You can't refer to form controls in a SQL string like that. You can when defining a query using the query editor, but not in code like that.

But also, if this checkbox is unbound, there's no way for it to know which record is checked because controls on a form do not belong to a record. You would need to create a new field and bind the checkbox to that field. In which case, you won't need to refer to the checkbox control in your SQL.

[ajochung]

Hey Rabbit,
The checkbox is bound to the field in the table. Also, do you have an alternative method to perform the transfer. I tried to get everything to transfer when a button is clicked to allow the user to make any corrections prior to the transfer.

[Rabbit]

If it's bound to a field in the table, then use the field in the table and not a reference to the form control.

Also, I just took a closer look, you can't SELECT FROM a form, you need to SELECT FROM the table.

[zmbd]

humor me
Insert the following after line 5 - call it line 5a for now.

Debug.Print strSQL

Run your code as normal

Once the error occurs,
press <ctrl><g>
In the immediates window, you will see the result of your strSQL string.
Please cut and paste it back here.
I think once you do, you'll see the error
(^_^)