I need to loop through two arrays changing the value in one field from the query and I cannot see how this is done. My code is below with my problem area highlighted in red.
Instead of the qry55, I could insert the sql into this code, but that seems like the wrong path to take.
Thanks for any help on this.
I have changed the "real" array names, etc for privacy sake.
Instead of the qry55, I could insert the sql into this code, but that seems like the wrong path to take.
Thanks for any help on this.
I have changed the "real" array names, etc for privacy sake.
Code:
Private Sub CmdRunIt_Click()
Dim db As Database
Dim myCtyArray As Variant
Dim myConnect2Array As Variant
Dim strConnect As String
'Dim strConnect2 As String
'Dim ctyCd As String
Dim x As Integer
strORAid = "sw"
strORApw = "psw"
strORAid2 = "ss"
strORApw2 = "psw2"
'define myCtyArray
myCtyArray = Array("01", "02","11", "B2")
'define myConnect2Array
myConnect2Array = Array("DSN=SS01;SERVER=SS01;UID=" & strORAid2_ss & ";PWD=" & strORApw2_ss,
"DSN=SS02;SERVER=SS02;UID=" & strORAid2_ss & ";PWD=" & strORApw2_ss,
"DSN=SS11;SERVER=SS11;UID=" & strORAid2_ss & ";PWD=" & strORApw2_ss,
"DSN=SSB2;SERVER=SSB2;UID=" & strORAid2_ss & ";PWD=" & strORApw2_ss)
'Connect to sw and stay connected.
strConnect_sw = "DSN=SW;SERVER=SW;UID=" & strORAid_sw & ";PWD=" & strORApw_sw
Set db = DBEngine.Workspaces(0).OpenDatabase("", False, False, strConnect_sw)
[COLOR=red]'define start and end of array[/COLOR]
[COLOR=red] For x = LBound(myConnect2Array) To UBound(myConnect2Array) [/COLOR]
[COLOR=red] ' run query named qry55 with myCtyArray = 01 and myConnect2Array = [/COLOR]
[COLOR=red] ' "DSN=SS01;SERVER=SS01;UID=" & strORAid2_ss & ";PWD=" & strORApw2_ss[/COLOR]
[COLOR=red] ' ALSO, myCtyArray = 01 needs to be in the field ctyCd from the qry55.[/COLOR]
[COLOR=red] ' How do I insert each item from the myCtyArray into the field ctyCd in qry55?[/COLOR]
[COLOR=red] Next x ' Loop![/COLOR]
[COLOR=red] 'then run again with the second items from the 2 array lists. etc...[/COLOR]
'export report
DoCmd.OutputTo acOutputReport, "Rpt55", acFormatPDF, "C:\Test\test55.pdf", False
'DoCmd.OutputTo acOutputReport, "Rpt_65", acFormatPDF, "C:\Test\test65.pdf", False
DoCmd.OutputTo acOutputReport, "Rpt_241", acFormatPDF, "C:\Test\test241.pdf", False
db.Close
End Sub