By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,264 Members | 1,087 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,264 IT Pros & Developers. It's quick & easy.

Transfer Records using a checkbox as a filter

P: 2
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub TransferAndReview_Click()
  2. Dim db As dao.Database
  3. Dim strSQL As String
  5. 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;"
  7. Set db = CurrentDb
  8. db.Execute strSQL, dbFailOnError
  10. 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
Jan 8 '14 #1
Share this Question
Share on Google+
4 Replies

Expert Mod 10K+
P: 12,366
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.
Jan 8 '14 #2

P: 2
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.
Jan 8 '14 #3

Expert Mod 10K+
P: 12,366
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.
Jan 8 '14 #4

Expert Mod 5K+
P: 5,397
humor me
Insert the following after line 5 - call it line 5a for now.
Expand|Select|Wrap|Line Numbers
  1. 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
Jan 8 '14 #5

Post your reply

Sign in to post your reply or Sign up for a free account.