Looping through with Multiple Recordsets

[It_thug]

Okay, I am unable to find an answer for this, so if it's already been answered, I apologize. Any help being nudged in the right direction here would be very appreciated.

What I am trying to do is run a series of SQL Queries looped based on a Variable.

The Variable in question is VN, and it's pulled from a Function. Below is the code that I'm using. This all works great when I am not looping. But I now need it to run through the code. The SQL Code is working here, I just need to figure out how to loop it, and I'm stumped because all examples that I've been able to find are only using 1 recordset, and I'm using 4. (That final RecordSet Step 4 is a saved query based on a Crosstab Query, which I couldn't figure out how to do through VBA. )

I have all the functions that I use. So this is the complete code so far. (I'm very new at this, so it's possible I'm doing something in an inefficient way).

      Public Function GetGST() As String

        Dim db As Database
        Dim Lrs As DAO.Recordset
        Dim LSQL As String
        Dim LGST As String

        'Open connection to current Access database
        Set db = CurrentDb()

        'Create SQL statement to retrieve value from GST table
        LSQL = "select VAR_ID from SVNames"

        Set Lrs = db.OpenRecordset(LSQL)

        'Retrieve value if data is found
        If Lrs.EOF = False Then
            LGST = Lrs("Var_ID")
        Else
            LGST = "Not found"
        End If

        Lrs.Close
        Set Lrs = Nothing

        GetGST = LGST

    End Function

Public Function GetDateRange()
TempVars("S_Date").Value = InputBox("Enter Starting Year:")
TempVars("E_Date").Value = InputBox("Enter Ending Year:")
End Function
Public Function CreateSVTable()

Dim myConnection As ADODB.Connection
Set myConnection = CurrentProject.Connection
Dim myRecordset As New ADODB.Recordset
Dim StrSQL As String
myRecordset.ActiveConnection = myConnection
Dim Step1 As String
Dim Step2 As String
Dim Step3 As String
Dim Step4 As String
Dim VN As String
Dim SDate As Integer
Dim EDate As Integer
Dim D_Count As Integer

VN = GetGST()
SDate = TempVars("S_Date").Value
EDate = TempVars("E_Date").Value
D_Count = (EDate - SDate) + 1
Debug.Print SDate
Debug.Print EDate
Debug.Print D_Count

Step1 = "SELECT [Temp].SCH_NUM INTO [1V Base Schools] " & _
        "FROM (SELECT DISTINCT dbo_REPD.SCH_NUM as SCH_NUM, dbo_REPD.VAR_VALUE as VAR_VALUE, dbo_REPD.VAR_ID & ' ' & dbo_REPD.RPT_YR AS [Year/Variable]  " & _
        "FROM dbo_REPD " & _
        "WHERE (((VAR_VALUE)<>'' And (VAR_VALUE)<>'0') AND ((VAR_ID)= " & VN & ") AND ((RPT_YR) Between  '" & SDate & "' And  '" & EDate & "'  )) " & _
        "ORDER BY SCH_NUM)  AS [Temp] " & _
        "GROUP BY [Temp].SCH_NUM " & _
        "HAVING (((Count(*))=" & D_Count & "));"

Step2 = "SELECT DISTINCT dbo_REPD.SCH_NUM AS [School Number], dbo_REPD.VAR_VALUE AS [Variable Value], [VAR_ID] & ' ' & [dbo_REPD]![RPT_YR] AS [Year/Variable], dbo_SchoolStatus.RGN_CODE INTO [1V Data Table] " & _
        "FROM (dbo_REPD INNER JOIN dbo_SchoolStatus ON dbo_REPD.SCH_NUM = dbo_SchoolStatus.SCH_NUM) INNER JOIN [1V Base Schools] ON dbo_REPD.SCH_NUM = [1V Base Schools].SCH_NUM " & _
        "WHERE (((dbo_REPD.VAR_VALUE)<>'' And (dbo_REPD.VAR_VALUE)<>'0') AND ((dbo_REPD.VAR_ID)=" & VN & ") AND ((dbo_REPD.RPT_YR) Between  '" & SDate & "' And  '" & EDate & "') AND ((dbo_SchoolStatus.RPT_YR)='2010'));"

Step3 = "Alter TABLE [1V Data Table] " & _
        "Alter COLUMN [Variable Value] NUMBER;"

Step4 = "Select * INTO [" & VN & "] " & _
        "From [1V-5 Data for Export];"

DoCmd.DeleteObject acTable, "" & VN & ""
DoCmd.DeleteObject acTable, "1v Base Schools"
DoCmd.DeleteObject acTable, "1v Data Table"

myRecordset.Open Step1
myRecordset.Open Step2
myRecordset.Open Step3
myRecordset.Open Step4


    
End Function

 

[vbaInet]

If you're using Access 2007, in the help files it gives an explanation on how to use multiple recordsets if that helps. Just type Recordset Object. Have a look at that.

Could you also wrap your code in code tags.

 

[It_thug]

If you're using Access 2007, in the help files it gives an explanation on how to use multiple recordsets if that helps. Just type Recordset Object. Have a look at that.

Could you also wrap your code in code tags.

I'll look up the Recordset Object, but I'm not sure what you mean by code tags.

Thank you for answering.

 

[vbaInet]

I'll look up the Recordset Object...

It just shows an example on using multiple recordsets.Worth checking out.

..., but I'm not sure what you mean by code tags.

You put the word 'code' enclosed in square brackets at the just before the first line of your code and '/code' (enclosed in square brackets) at the end.

[ c o d e ]
Public Function GetGST() As String
....

End Function
[ / c o d e ]

Without the spaces and notice the backslash in the closing code tag. It just makes your code readable.

 

[It_thug]

[ c o d e ]
Public Function GetGST() As String
....

End Function
[ / c o d e ]

Without the spaces and notice the backslash in the closing code tag. It just makes your code readable.

I will def do that next time. Thank you.

 

[It_thug]

VBAInet - I appreciate the help, but I've been unable to find anything in the Help files that is showing examples of multiple recordsets. Sorry. Doing a search for Recordset Object gives a lot of examples, but none using multiple(that I can find).

 

[vbaInet]

VBAInet - I appreciate the help, but I've been unable to find anything in the Help files that is showing examples of multiple recordsets. Sorry. Doing a search for Recordset Object gives a lot of examples, but none using multiple(that I can find).

I'm talking about the Help Files of the VBA editor, not the Access application Help. Try it in there.

What are you trying to do with your code? When you say running multiple queries based on a variable (VN) what do you mean?

 

[It_thug]

VBAINet - duh Thanks.

Well, the first function in the list is GetGST() which is where the Variable VN comes from (naming convention there is off).

Then, in the CreateSVTable() Function is a series of 4 queries that need to run to do what I need. (Could be bulky because I am still new at this).

In the SQL Queries, there is a reference to the VN Variable that is pulled from GetGST().

I need to loop through all 4 Queries for each Record in the DB that GetGST() pulls from.

I may be completely off base on how I've been accomplishing this so far. What I have posted up there works great, for the first variable, and I am unsure how to get it to loop to the EOF

 

[vbaInet]

I'm just leaving the office now, so maybe someone can chip in.

Why don't you combine both functions together or have it done it that way so that one function can be reused for other things?

Your GST function seems to only run once. Are you trying to loop the other function in here:

 [B]If Lrs.EOF = False Then
            LGST = Lrs("Var_ID")
        Else [/B]

 

[It_thug]

I'm just leaving the office now, so maybe someone can chip in.

Why don't you combine both functions together or have it done it that way so that one function can be reused for other things?

Your GST function seems to only run once. Are you trying to loop the other function in here:

 [B]If Lrs.EOF = False Then
            LGST = Lrs("Var_ID")
        Else [/B]

That code was taken from somewhere else, while I was trying to figure out how to pull the data from a table. I Could absolutely combine those two functions together if need be. That is the table that the Variable VN comes from, and would be the information I need for the loop of the other 4 queries.

 

[It_thug]

I'm just leaving the office now, so maybe someone can chip in.

Why don't you combine both functions together or have it done it that way so that one function can be reused for other things?

Your GST function seems to only run once. Are you trying to loop the other function in here:

 [B]If Lrs.EOF = False Then
            LGST = Lrs("Var_ID")
        Else [/B]

I guess that's really part of my problem. I'm not really sure where I should be looping anything, or if I should take those two functions and run them through another module...

I'm really jumping past my knowledge on this... so I very much appreciate anyone's help on what should be done here.

 

[It_thug]

I'm just leaving the office now, so maybe someone can chip in.

Why don't you combine both functions together or have it done it that way so that one function can be reused for other things?

Your GST function seems to only run once. Are you trying to loop the other function in here:

 [B]If Lrs.EOF = False Then
            LGST = Lrs("Var_ID")
        Else [/B]

Thank you very very much.

With a little tweaking, I brought the GetGST function into the main function, and was able to get it to loop through the recordsets that way. Works perfectly.

Again, thank you very very much. Just the nudge in the right direction was all I needed.

 

[vbaInet]

Thank you very very much.

With a little tweaking, I brought the GetGST function into the main function, and was able to get it to loop through the recordsets that way. Works perfectly.

Again, thank you very very much. Just the nudge in the right direction was all I needed.

See how much you can achieve which just a little help.

Glad you got that working.

 

[It_thug]

See how much you can achieve which just a little help.

Glad you got that working.

Oh.. def... although sometimes it's nice to get it handed to you on a silver plate, most of the time I prefer just being pushed in the right direction. I'll remember it better this way.

Appreciate your help immensely.

 

[vbaInet]

Oh.. def... although sometimes it's nice to get it handed to you on a silver plate, most of the time I prefer just being pushed in the right direction. I'll remember it better this way.

Most definitely. Exactly what I try to encourage, like you said, you would remember how to apply it for next time use.

You're welcome.