loop with multiple arrays (1 Viewer)

dj59

Registered User.
Local time
Today, 03:26
Joined
Jul 27, 2012
Messages
70
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.

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
 

dj59

Registered User.
Local time
Today, 03:26
Joined
Jul 27, 2012
Messages
70
To be more clear I think this might help.
I want to be able to:
~open a main database and stay connected to it.

~open the first database in myConnect2Array.
~insert into all the necessary places in my queries (now it has [enter cty])
with the first myCtyArray.
~export results to a location.
~close first database in myConnect2Array.

Then do all the above steps for the second items in my Arrays.

I admit this is above my head, but I know there must be a way to do this.

Thank you for any help or direction on this.
 

smig

Registered User.
Local time
Today, 10:26
Joined
Nov 25, 2009
Messages
2,209
you need to open your table/query as RecordSource
You need to move both through your array (For... Next) and through the RecordSource (.MoveFirst, .MoveNext)
Don't forget you need to .Edit your RS before any record change and to .Update to save the record change.
You refer to fields in the RS as rs.Fields("NameOfField")
 

dj59

Registered User.
Local time
Today, 03:26
Joined
Jul 27, 2012
Messages
70
This works well to get my recordset.
I'm searching for the way to loop through my array and insert into my replaced value rather than the hard coded value.

Any advice is appreciated. thanks.

Code:
'you need to open your query as RecordSource
Set qdf = CurrentDb.QueryDefs("qry_55")
With qdf
    qdfOLD = .SQL
    .SQL = Replace(.SQL, "c.cnty_cd ='01'", "c.cnty_cd ='11'")
      'Code to do stuff with SQL-string/query
     'DoCmd.OpenQuery "qry_55", acViewNormal, acEdit
     Debug.Print .SQL
    .SQL = qdfOLD ' Reset SQL to old setting
End With
Set qdf = Nothing
 
Last edited:

dj59

Registered User.
Local time
Today, 03:26
Joined
Jul 27, 2012
Messages
70
Re: loop with multiple arrays -UPDATED-

I have hit a wall in how to loop through an array.
As it turns out I only need ONE array, but I'm so stuck I don't see how to do this. My code is below. highlighted are the places I want to insert array item one, then array item two, etc.

I've looked at so many examples and yet, I'm not seeing it. Thanks for any help.

Code:
Private Sub CmdRunIt_Click()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim prm As DAO.Parameter
    Dim rst As DAO.Recordset
    Dim strConnect_sw As String
    Dim strConnect_ss As String
    Dim myCtyArray As Variant
 
    strORAid_sw = "xxxx"
    strORApw_sw = "xxxx"
    strORAid_ss = "xxxx"
    strORApw_ss = "xxxx"
 
    myCtyArray = Array("01", "02", "11", "B2")
 
    'connect to southwest
    strConnect_southwest = "ODBC;DSN=SW;SERVER=SW;UID=" & strORAid_sw & ";PWD=" & strORApw_sw
    Set db = DBEngine.Workspaces(0).OpenDatabase("", False, False, strConnect_sw)
    'connect to ss db
    strConnect_ss = "ODBC;DSN=SS" & [COLOR=red]myCtyArray(0)[/COLOR] & ";SERVER=SS" & [COLOR=red]myCtyArray(0)[/COLOR] & ";UID= " & strORAid_ss & ";PWD=" & strORApw_ss
    Set db = DBEngine.Workspaces(0).OpenDatabase("", False, False, strConnect_ssis)
 
    Call ShowConnectInfo(strConnect_ss)
 
    Set db = DBEngine.Workspaces(0).OpenDatabase("", False, False, strConnect_ssis)
 
    'open your query as RecordSource
    Set qdf = CurrentDb.QueryDefs("qry_55")
        With qdf
            qdfOLD = .SQL
            .SQL = Replace(.SQL, "c.cty_cd ='01'", "c.cnty_cd =" & [COLOR=red]myCtyArray(0))[/COLOR]
            .SQL = Replace(.SQL, "a.sw_name_source_cd =  '01'", "a.sw_name_source_cd =" & [COLOR=red]myCtyArray(0))[/COLOR]
            .SQL = Replace(.SQL, "a2.sw_name_source_cd <> '01'", "a2.sw_name_source_cd <>" & [COLOR=red]myCtyArray(0))[/COLOR]
    'open query
    'DoCmd.OpenQuery "qry_55", acViewNormal, acEdit
    'export report
    'DoCmd.OutputTo acOutputReport, "Rpt_55", acFormatPDF, "H:\Test\test55.pdf", False
    'DoCmd.OutputTo acOutputReport, "Rpt_241", acFormatPDF, "H:\Test\test241.pdf", False
'You need to move both through your array (For... Next) and through the RecordSource (.MoveFirst, .MoveNext)
 Dim i As Variant
 For Each i In mCtyArray
         Debug.Print i
 Next
'Don 't forget you need to .Edit your RS before any record change and to .Update to save the record change.
'You refer to fields in the RS as rs.Fields("NameOfField")
    Debug.Print .SQL
    .SQL = qdfOLD ' Reset SQL to old setting
        End With
    Set qdf = Nothing
    db.Close
 
        MsgBox "done"
End Sub
 

smig

Registered User.
Local time
Today, 10:26
Joined
Nov 25, 2009
Messages
2,209
I did not read you code

Loop in array:

For i = LBound(yourArray()) to UBound(yourArray())
For j = 0 to NoOfVarsInArray - 0
X = yourArray(i, j)
Next j
Next i


If you only have one var in your array remove j
 

dj59

Registered User.
Local time
Today, 03:26
Joined
Jul 27, 2012
Messages
70
I am now getting script out of range.
I see I might need to redim, but so far I'm not seeing how that works.
Any help is appreciated.
 

Users who are viewing this thread

Top Bottom