When checked, move to table

[valgore]

I have a table with a field called CheckBox. it has a checkbox in it. i want to say if it is checked, then import 3 other fields into a different table. so far i have

Private Sub CheckBox_Click()
On Error GoTo Err_SomeName
DoCmd.RunSQL "INSERT INTO TestCheckboxtable SELECT [AmexCurrent]![Merchant Name/Location] as [Merchant Name/Location], [AmexCurrent]![Carholder Name] as [Cardholder Name], [AmexCurrent]![CheckBox] as [CheckBox] FROM AmexCurrent WHERE Trim([AmexCurrent!CheckBox!] & '')<>''"
Exit_SomeName:
    Exit Sub
Err_SomeName:
     MsgBox Err.Number & Err.Description
     Resume Exit_SomeName
End Sub

but nothing is happening. any help would be appreciated

 

[bubba100]

How about

If YourFormName.Checkbox=-1 then
docmd.runSQL
end if
to replace the where statement.

 

[valgore]

still nothing. i tried changed the -1 to a 1 and a yes, and still nothing went.

valgore

 

[randy700]

WHERE Trim([AmexCurrent!CheckBox!]

Does your field name REALLY contain an exclamation point?

CheckBox is a reserved word. You really should find something else.


Randy

 

[valgore]

no. but i thought that was the syntax. i have another process that does exactly what this does, and that works. but i can't seem to figure out why this isnt working. i changed the field to ChexBox and it still didnt work

 

[randy700]

Did you remove the exclamation point?


Randy

 

[valgore]

yes. still nothing

 

[dhookom]

Have you tried to use a breakpoint in your code so you can step through to find your issue?
I prefer to be explicit with the field names. Are your field names spelled correctly?

Private Sub CheckBox_Click()
On Error GoTo Err_SomeName
Dim strSQL As String
strSQL = "INSERT INTO TestCheckboxtable ([Merchant Name/Location], [Cardholder Name], [CheckBox]) " & _
  "SELECT [Merchant Name/Location], [Carholder Name], [CheckBox] " & _
  "FROM AmexCurrent WHERE Trim([CheckBox] & '')<>'' "

DoCmd.RunSQL strSQL

Exit_SomeName:
    Exit Sub
Err_SomeName:
     MsgBox Err.Number & Err.Description
     Resume Exit_SomeName
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[valgore]

i tried your code Duane and when i clicked it, it gave me a parameter value input msgbox

 

[randy700]

From your original post...

[AmexCurrent]![Carholder Name] as [Cardholder Name],

Should this be...

[AmexCurrent]![Cardholder Name] as [Cardholder Name],

Randy

 

[dhookom]

vlagore,
How about answering my questions

Have you tried to use a breakpoint in your code so you can step through to find your issue?
Are your field names spelled correctly?

Duane
Hook'D on Access
MS Access MVP

 

[valgore]

i fixed that part already Randy. and i did set a break point. i set it on the DoCmd.RunSQL and i didnt get anything.

 

[dhookom]

What is the value of strSQL prior to attempting to run the sql? When the code stops, open the debug window and enter:

? strSQL

"checkbox" generally suggests a yes/no datatype. What is the datatype of your field?

What is your current code?


Duane
Hook'D on Access
MS Access MVP

 

[valgore]

ok my current code is

Private Sub ChexBox_Click()
On Error GoTo Err_SomeName
Dim strSQL As String
strSQL = "INSERT INTO TestCheckboxtable ([Merchant Name/Location], [CategoryCode], [ChexBox]) " & _
  "SELECT [Merchant Name/Location], [CategoryCode], [ChexBox] " & _
  "FROM AmexCurrent WHERE Trim([ChexBox] & '')<>'' "

DoCmd.RunSQL strSQL

Exit_SomeName:
    Exit Sub
Err_SomeName:
     MsgBox Err.Number & Err.Description
     Resume Exit_SomeName
End Sub

i got it so when i check the checkbox, it says do you want to append "X" number of rows. but it wants to append all the rows. so now i need to let it know to only copy the ones with the checkbox's checked. also, can this be done in the background without having the append window pop up? i dont want the data to be moved to the other table, i want it to copy. this whole thing is to make sure an automatic query is working correctly.

 

[PHV]

strSQL = "INSERT INTO TestCheckboxtable([Merchant Name/Location],CategoryCode,ChexBox)" & _
  " SELECT [Merchant Name/Location],CategoryCode,ChexBox" & _
  " FROM AmexCurrent WHERE ChexBox=True"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

Again, you didn't answer my question

"checkbox" generally suggests a yes/no datatype. What is the datatype of your field?

Duane
Hook'D on Access
MS Access MVP

 

[valgore]

yeah, i was getting to it dhookom. sorry. my boss called me into his office and i wanted to submit as much as i could before i went in. the field is a yes/no. and i tried your code PHV and its still copying everything in the table over and making all the checkbox's checked

 

[dhookom]

If the code is copying all records, then the ChexBox field for every record in the AmexCurrent table must be checked.

Set a breakpoint after the strSQL has been given a value so you can use the immediate window to grab the value. Then open a new, blank query and paste the strSQL value into the SQL view. Go to the datasheet and design views to see what's happening.

Duane
Hook'D on Access
MS Access MVP

 

[vbajock]

Man, what a pile of nasty looking code.

 

[valgore]

ok dhook, for you first statement, what it does when i check the checkbox on the form, it copies all the records in AmexCurrent and moves them to the other table and then makes all the check boxes checked. so yeah, i dont know why its doing that. and for the breakpoint, i put it at the strSQL statement, and i didnt get anything in the immediate window when i ran it, and i also put a breakpoint at the DoCmd.RunSQL strSQL and i didnt get anything either. so i probably doing something wrong here....

Valgore