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

 

[dhookom]

There is some information on debugging your code at faq705-7148.

Duane
Hook'D on Access
MS Access MVP

 

[valgore]

i dont understand.... i put a breakpoint and i clicked the checkbox and it finished the code and didnt stop. this is getting frustrating. im not mad at you dhookom your really trying to help me and i appreciate that.

 

[dhookom]

If you put a checkpoint and the code didn't stop then are you sure your code is attached to the event of a control?

Your code

Private Sub ChexBox_Click()
 ....

What is the ChexBox? Are you sure this is where you want to run the code and don't you want to run it for the current record or all records that are checked?

Duane
Hook'D on Access
MS Access MVP

 

[valgore]

yes, the event is in the onclick of the checkbox. so your saying, i could something different like an OnClose event so when they close the form, it looks for any checked boxes and then it runs that code?

 

[dhookom]

Absolutely. I assumed you wanted to provide the opportunity for a user to make a bunch of selections and then run a "batch" process on the checked records. If this is the case, I generally have a separate button with code to run the query and do whatever else is required.

Duane
Hook'D on Access
MS Access MVP

 

[valgore]

alright. so i made the change from an OnClick to an OnClose. and i tried it, at it STILL copied all the records. im still really confused. i tried another breakpoint and it still went right through the code

 

[dhookom]

As I stated, I would provide a command button for the user to click after updating the chxbox field values. Are you using 2007 with VBA enabled?

Duane
Hook'D on Access
MS Access MVP

 

[valgore]

no. im using Access 2003. front end and backend

 

[dhookom]

What is your current code? Did you try as I suggested regarding the command button?

Try add a line in your code like:

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

Duane
Hook'D on Access
MS Access MVP

 

[newguy86]

valgore,
Go to the part of your code that calls this sub in the first place (I would guess this is located in the Microsoft Office Access Class Objects folder under your form inside of the OnClose sub code) and put the debugger there. Then run your code again and if it still doesn't stop then the issue is not with this sub it is in the process to get to this sub.

P.S. If you paste your code for the OnClose sub in here, then I will show you where you should put the debugger.

Travis

http://www.enterprise.com

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement?!

 

[valgore]

dhookom, i did try your button and it didnt do anything. my current code is

Private Sub Command13_Click()
On Error GoTo Err_SomeName
Dim str As String
strSQL = "INSERT INTO TestCheckboxtable([Merchant Name/Location],CategoryCode)" & _
  " SELECT [Merchant Name/Location],CategoryCode" & _
  " FROM AmexCurrent WHERE ChexBox=True"
MsgBox "strSQL: " & strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Exit_SomeName:
    Exit Sub
Err_SomeName:
     MsgBox Err.Number & Err.Description
     Resume Exit_SomeName
End Sub

The message box doesn't even pop up now.

 

[dhookom]

If you don't get a message box then you have some serious issues with your MDB. I would try compact and repair. I would then try to run any code in any form to see if the code runs.

Duane
Hook'D on Access
MS Access MVP

 

[valgore]

alright. i tried compacting a repair and it still didn't show the message box. but it did copy the table to the other table, but its still copying everything. so, my company is doing a massive CRM switch so im going to have to stop working on this project for awhile. so thank you dhookom for all your help, but i cant go any further right now on this project.

 

[newguy86]

I am kind of new at using SQL statements so I would like someone else to confirm this for me, but you may want to try this:

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

Again if someone else can confirm this code for me that would be great.

Travis

http://www.enterprise.com

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement?!

 

[dhookom]

Travis,
I can confirm your code won't work.

Duane
Hook'D on Access
MS Access MVP

 

[newguy86]

Thank you dhookom for your very inciteful response.

My theory still stands that the issue is with the syntax of the SQL statement which might explain why the Msgbox is not appearing.

So based on dhookom's constructive criticism here is an updated version of my solution.

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

Also shouldn't this line of code

Dim str As String

instead be this

Dim strSQL As String

Just something that I noticed.

Travis

http://www.enterprise.com

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement?!

 

[dhookom]

Travis,
Good catch on the str and strSQL. All modules should have "Option Explicit" in the general declarations.

However, your SQL syntax won't work unless you have memory variables named [Merchant Name/Location] and CategoryCode. This is the SQL that should work unless we don't know all the information.

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

Duane
Hook'D on Access
MS Access MVP

 

[newguy86]

Thank you dhookom for the explination. To me it looks like [Merchant Name/Location] & CategoryCode are two seperate fields which is why I put them as seperate variables.

Travis

http://www.enterprise.com

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement?!

 

[valgore]

they are two separate fields in the table

 

[newguy86]

Go ahead and try both and see if either one solves your problem.

If one of them works then post the code so that we know what worked for you.

Here are both of them in case you need it.
my example:

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

dhookom's example:

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

Although you may want to try dhookom's code first granted he most likely knows more about this than I do, but first be sure to change this line of code

Dim str As String

to this

Dim strSQL As String

If you have any questions you know how to reach us.

Travis

http://www.enterprise.com

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement?!