Syntax error When transferring records with checkbox filter

[ajochung]

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

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

 

[Mihail]

Show us the strSQL string

 

[ajochung]

Show us the strSQL string

I'm not sure what you mean, isn't the strSQL string the:

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;"

 

[Galaxiom]

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

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

strSQL = "INSERT INTO Forms![Extra Work Report Checksheet]![Equipment Input Subform1].;

SQL cannot be used to put anything into a form. It is only for interaction with tables and queries. In fact CurrentDb doesn't even know anything about Access objects.

You could write it to the table/query that is the RecordSouce of the second subform then requery it.

Alternatively read the values from the controls on the first subform and write them to the new record in the second subform.

 

[ajochung]

SQL cannot be used to put anything into a form. It is only for interaction with tables and queries. In fact CurrentDb doesn't even know anything about Access objects.

You could write it to the table/query that is the RecordSouce of the second subform then requery it.

Alternatively read the values from the controls on the first subform and write them to the new record in the second subform.

Thank you for that clarity! This problem just seems so easy to solve. I want to add checked records in subform1 to the table in the subform2 when I click the review button. Could you please suggest a method to do this.

Thank you.

 

[Mihail]

Sorry. Is now when I see the external quotes.

 

[Mihail]

I am not very skilled in SQL but this looks wrong for me:

"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;"

 

[CJ_London]

Presumably your checkbox is bound to a field in your form? if it is unbound, then you cannot use it to select items.

So if both forms have the same recordsource with one subform set to show records where the checkbox is ticked and one where it is not ticked (albeit maybe showing different fields within that recordset), then all you need to do is requery the other subform.
__________________________

Your SQL will not work - regardless of the fact of how you are trying to use it, the syntax is wrong in that you are either inserting values or you are inserting from a table - you have combined the two.

Your brackets are also all over the place.

You need to take into account galaxioms points - you cannot insert into a form, only a table - in this case the table which is the recordsource for subform2.

Other inconsistencies include that you want to insert into a form (Forms![Extra Work Report Checksheet]![Equipment Input Subform1]) items which have been checked in that form (WHERE [Extra Work Report Checksheet]![Equipment Input Subform1].Form[EquipmentUsed]=True) - which means they are already there.