Populating One Table With Another Data Using Checkbox

[KLahvic01]

I hope I can explain this properly:

I have two tables, one (tblMasterList) which has all the HotWheels Cars made with information about each car including a check box for 'Owned'.

I have a second table called tblPersonalCollection which has all of the same fields as the Master List, but it also has a condition grading section for individual cars as well as a few other pieces of information that are specific to the car in my collection.

What I want to do, is if I find I have a car from the Master List in my collection, I click on owned and it will add the data form a few fields to the Personal Collection table allowing me to process the grading and other key information unique to that car. Now I may have duplicates of one car, so I need to be able to add another of the same car to the Personal Collection table so after I click a save button or something, the owned checkbox needs to refresh to false.

I saw a couple of examples like

Private Sub CheckBoxName_AfterUpdate() 
Dim strSQL As String 
strSQL = "INSERT INTO TableB (FieldB1, FieldB2, FieldB3) " & _ 
"SELECT " & Me.Field1 & " As Expr1, " & Me.Field2 & _ 
" As Expr2, " & Me.Field3 & " As Expr3;" 
CurrentDb.Execute strSQL, dbFailOnError   
End Sub

which errored out because of the CurrentDb.Execute line and I dont think this would have done what I wanted to do anyhow. I hope this explained the request well enough. Please let me know if you can help me accomplish this task.

Thank you

Ken

 

[KLahvic01]

I also should note, I have tblMasterList attached to one Form (frmSearch) and tblPersonalCollection is driven by a different form (frmPersonalCollection). Didnt know if that was relevant but thought I would share.

 

[ketbdnetbp]

Ken - You could try something like:


Private Sub CheckBoxName_AfterUpdate()

If Me![NameofchkBox] = -1 then 'Checks that item was selected

Dim dbs as DAO.Database
Set dbs = CurrentDB
Dim strSQL As String

strSQL = "INSERT INTO TableB (FieldB1, FieldB2, FieldB3) " & _
"Values (Me!FieldName1, Me!FieldName2, Me!FieldName3);”

dbs.Execute strSQL, dbFailOnError
else
Exit Sub 'Exits sub if item wasn't selected
End if

End Sub

So, you would be adding a record to the personal collection table, based upon the values of the matching controls on the form. You would, of course, have to change the names of the table fields and form controls to match your's. If I remember correctly, the order would have to be the same.

Hope this gets you started,

Jim

 

[KLahvic01]

Thanks, I will try this out on Monday and let you know how it goes.

 

[KLahvic01]

I added the code to the After Update event and when checking the Owned Box to test I recieve the following error:

Run Time Error: 3142
Characters Found after end of SQL Statement

When I debug it highlights:

dbs.Execute strSQL, dbFailOnError

Any ideas on what can cause this?

 

[ketbdnetbp]

Ken - Copy and paste the entire code in which the error was generated.

Thanks,

Jim