Can't get variable to resolve in a do loop (VBA)

I have some code that contains a loop.  I need to update three tables, but I can't get the variable to for the table name to resolve (strActiveTable).  My current code and the results are shown in the attachment.  Any help is appreciated.
Private Sub cbo_Department_AfterUpdate()

Dim strUpdateTbl_1 As String
Dim strUpdateTbl_2 As String
Dim strUpdateTbl_3 As String
Dim strSQL_Update As String
Dim intCounter As Integer
Dim strActiveTable As String

'Declare tables as variables
  strUpdateTbl_1 = "tbl_CommitmentForecast"
  strUpdateTbl_2 = "tbl_Commitment_AnnualBudget"
  strUpdateTbl_3 = "tbl_Allocations"


intCounter = 1
  
For intCounter = 1 To 3

  strActiveTable = "strUpdateTbl_" & intCounter
  Debug.Print strActiveTable

  strSQL_Update = "UPDATE " & _
    strActiveTable & " SET " & _
    strActiveTable & ".[Department] = """ & [Forms]![frm_EditRecipients_ChangeRec]![cbo_Department] & """" & _
    " WHERE " & strActiveTable & ".PersonNum = " & _
     [Forms]![frm_EditRecipients_ChangeRec]![txt_PersonNum] & _
    " AND " & strActiveTable & ".Program = """ & [Forms]![frm_EditRecipients_ChangeRec]![txt_Program] & """"

    Debug.Print strSQL_Update

Next

End Sub
___________________________

strUpdateTbl_1
UPDATE strUpdateTbl_1 SET strUpdateTbl_1.[Department] = "Cell Biology" WHERE strUpdateTbl_1.PersonNum = 1xxxx AND strUpdateTbl_1.Program = "Department Chair"

strUpdateTbl_2
UPDATE strUpdateTbl_2 SET strUpdateTbl_2.[Department] = "Cell Biology" WHERE strUpdateTbl_2.PersonNum = 1xxxx AND strUpdateTbl_2.Program = "Department Chair"

strUpdateTbl_3
UPDATE strUpdateTbl_3 SET strUpdateTbl_3.[Department] = "Cell Biology" WHERE strUpdateTbl_3.PersonNum = 1xxxx AND strUpdateTbl_3.Program = "Department Chair"

Open in new window

UTSWPedsBudget ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ambidextrousCommented:
strActiveTable = "strUpdateTbl_" & intCounter

I think you need to cast your intCounter variable.

strActiveTable = "strUpdateTbl_" & cStr(intCounter)
0
GeoffHarperCommented:
I see what you're trying to do, and I don't think you can do quite that.

I suggest the below (which also includes changing some of your double quotes to single quotes):


Private Sub cbo_Department_AfterUpdate()

Dim strUpdateTbl(1 To 3) As String
Dim strSQL_Update As String
Dim intCounter As Integer
Dim strActiveTable As String

'Declare tables as variables
  strUpdateTbl(1) = "tbl_CommitmentForecast"
  strUpdateTbl(2) = "tbl_Commitment_AnnualBudget"
  strUpdateTbl(3) = "tbl_Allocations"


intCounter = 1
  
For intCounter = 1 To 3

  strActiveTable = strUpdateTbl(i)
  Debug.Print strActiveTable

  strSQL_Update = "UPDATE " & _
    strActiveTable & " SET " & _
    strActiveTable & ".[Department] = '" & [Forms]![frm_EditRecipients_ChangeRec]![cbo_Department] & "'" & _
    " WHERE " & strActiveTable & ".PersonNum = " & _
     [Forms]![frm_EditRecipients_ChangeRec]![txt_PersonNum] & _
    " AND " & strActiveTable & ".Program = '" & [Forms]![frm_EditRecipients_ChangeRec]![txt_Program] & "'"

    Debug.Print strSQL_Update

Next

End Sub

Open in new window

0
GRayLCommented:
From what I can see, strActiveTable is resolved to strUpdateTable_1, strUpdateTable_2, and strUpdateTable_3 correctly.  What else is wrong?  
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

GeoffHarperCommented:
Did you try my code?

If you indeed have tables actually named strUpdateTbl_1, strUpdateTbl_2, strUpdateTbl_3 then maybe I misunderstood, but, when you print out your string result like:

UPDATE strUpdateTbl_1 SET strUpdateTbl_1.[Department] = "Cell Biology" WHERE strUpdateTbl_1.PersonNum = 1xxxx AND strUpdateTbl_1.Program = "Department Chair"

That is updating a table actually named strUpdateTbl_1  - not the value that the variable strUpdateTbl_1  contains.
0
Nick67Commented:
I don't see the attachment
Am I missing it?
0
GRayLCommented:
Nick67:  I think the asker means the code window.
0
dagesiCommented:
If you insist on the double-quotation marks:

strSQL_Update = "UPDATE " & _
  strActiveTable & " SET " & _
  strActiveTable & ".[Department] = """ & [Forms]![frm_EditRecipients_ChangeRec]![cbo_Department] & """" & _
  " WHERE " & strActiveTable & ".PersonNum = " & _
  [Forms]![frm_EditRecipients_ChangeRec]![txt_PersonNum] & _
  " AND " & strActiveTable & ".Program = """ & [Forms]![frm_EditRecipients_ChangeRec]![txt_Program] & """

It looks like you had one too few double-quotes prior to WHERE and one too many at the end.

GeoffHarper's code is less complicated (using the single-quote marks)...

You should probably consider using the Immediate screen to test your quoting in the future - just substitute dummy strings like "(some form code)" where the pieces are that call from the Form! controls.
0
GeoffHarperCommented:
I changed the double quotes to single because I don't think the query will execute.
0
Nick67Commented:
Bazillions of quotes!
Which I hate to debug

I get it now.  You want strUpdateTbl_1 to resolve to tbl_CommitmentForecast but it isn't
@GeoffHarper is on the right track.
Load those table names into an array,and call them out in your iteration.
You can't get the VBA to see

strActiveTable = "strUpdateTbl_" & intCounter

as a variable.  It sees it as just the string

strUpdateTbl_x

where x is the intCounter

For me, I like breaking up the concatenation into chunks, and not using & _
I call then throw msgboxes in in stages and not have one huge messy string come flying back.
I also religiously avoid mega-quotes.
Chr(34) is much cleaner to debug

Dim strUpdateTbl(2) As String 'array starting at 0
Dim strSQL_Update As String
Dim x As Integer 'I like x as my counter.  It's short and I don't use it in any other way
Dim strActiveTable As String

'Declare tables as variables
strUpdateTbl(0) = "tbl_CommitmentForecast"
strUpdateTbl(1) = "tbl_Commitment_AnnualBudget"
strUpdateTbl(2) = "tbl_Allocations"


x = 0
  
For x = 0 To 2

    strActiveTable = strUpdateTbl(x)
    MsgBox strActiveTable
    
    strSQL_Update = "UPDATE " & strActiveTable
    strSQL_Update = strSQL_Update & " SET " & strActiveTable & ".[Department] = " & Chr(34) & [Forms]![frm_EditRecipients_ChangeRec]![cbo_Department] & Chr(34)
    strSQL_Update = strSQL_Update & " WHERE " & strActiveTable & ".PersonNum = " & [Forms]![frm_EditRecipients_ChangeRec]![txt_PersonNum]
    strSQL_Update = strSQL_Update & " AND " & strActiveTable & ".Program = " & Chr(34) & [Forms]![frm_EditRecipients_ChangeRec]![txt_Program] & Chr(34)

    MsgBox strSQL_Update

Next

Open in new window

0
GRayLCommented:
It has been my experience that it is easier to create a saved query than to create a query in code.  Once you get the query running - even with call(s) to an open form -  execute the query from within VBA using:
 currentdb.execute "qudName"
0
dagesiCommented:
Oh, I'm not faulting you.  The last set of queries I wrote all used single quote marks as well...
I just can't recall if that was because they were easier to read/write or because they didn't run use the double-quotes.
0
Nick67Commented:
@GrayL

I generally do CRUD with recordsets--because I can set breakpoints at .update and SEE what I am changing before I do it.
When you do a select recordset from a saved parameter query, you have to do a querydef and feed in the parameters.
Which is ok, and sometimes cleaner to look at than building the SQL statement.

Does currentdb.execute "qudName"  not suffer from that 'feed in parameters' requirement?
Just curious :)
0
GeoffHarperCommented:
@UTSWPeds:

Did you try my code I originally posted?

Here it is again:

Option Explicit

Sub Main()

Dim strUpdateTbl(1 To 3) As String
Dim strSQL_Update As String
Dim intCounter As Integer
Dim strActiveTable As String

'Declare tables as variables
  strUpdateTbl(1) = "tbl_CommitmentForecast"
  strUpdateTbl(2) = "tbl_Commitment_AnnualBudget"
  strUpdateTbl(3) = "tbl_Allocations"


intCounter = 1
  
For intCounter = 1 To 3

  strActiveTable = strUpdateTbl(intCounter)
  Debug.Print strActiveTable

  strSQL_Update = "UPDATE " & _
    strActiveTable & " SET " & _
    strActiveTable & ".[Department] = '" & [Forms]![frm_EditRecipients_ChangeRec]![cbo_Department] & "'" & _
    " WHERE " & strActiveTable & ".PersonNum = " & _
     [Forms]![frm_EditRecipients_ChangeRec]![txt_PersonNum] & _
    " AND " & strActiveTable & ".Program = '" & [Forms]![frm_EditRecipients_ChangeRec]![txt_Program] & "'"

    Debug.Print strSQL_Update

Next


End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GRayLCommented:
Yes, but as the parameters come from a form which is open and populated, the query will run without interruption.
0
Nick67Commented:
@GRayL.

Cool.
On the recordset side
rs.openrecordset("qrySomeSavedQuery",dbopendynaset,dbseechanges)
will NOT work, even if the form with the parameters is open
0
UTSWPedsBudget ManagerAuthor Commented:
stuck in training class.  will reply back after 5 PM CST.  Thanks for the help.
0
GRayLCommented:
It appears the author wishes to run a set of Action queries which do not return a recordset.  
0
Nick67Commented:
Agreed.
That is never my plan of attack.
I go with
set rs = db.openrecordset("whatever")
do until rs.eof = true
    with rs
        .edit
        !WhateverField = WhateveritUpdatesTo
        .update
    end with
    rs.movenext
loop

I can then drop a breakpoint on .update, run a mouse over both the field and update and make sure it's good.
I am too paranoid to run an update query unless compelled by necessity.
They always seem to go sideways on me.

Mind you, I never wind up doing CRUD on more that a hundred records at a time.
Big recordsets =  looping is evil
That is the necessity that sometimes compells me :)
0
UTSWPedsBudget ManagerAuthor Commented:
Haven't been back to my desk to check this out, but one issue I noticed.

I want this code to execute as an event proceedure following the update of cbo_Department, as a somewhat novice programmer, not sure how to get this to fire after update, would the code above be saved as a module and then just use "CALL MAIN" as the code in the after update event?

Thanks,
0
Nick67Commented:
No, just copy and paste it into the AfterUpdate envent of your combo box.
Your combo box has [Event Procedure] for what it will do
click the ... at the right, after clicking/highlighting Event procedure to open the code window
 PropertyAnd this is the code code
0
UTSWPedsBudget ManagerAuthor Commented:
Thanks for all the help.  On the meta-quotes, I like the idea of doing somethign different.  I've used single quotes in the past as you outlined.  The 3 quotes, 4 quotes thing actually works correctly, even though it looks weird.  

Much appreciated.
0
Nick67Commented:
The mega quotes work.
I've just driven myself nuts one too many times trying to debug them :0
& _ works too, but when the string it returns is too big for a msgbox, that's tough debugging too.

I'm glad you got it working :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.