Looping Through Combobox Values (1 Viewer)

[MarkK]

OK, so to do the loop, I've amended your code a little. Most things are cosmetic but at the bottom you can see that we will print each name as we loop through each record in the recordset. This is our test of the loop, now that we have pretty good confidence in the SQL we've constructed . . .

Private Sub ExportClaimsBttn_Click()
    Dim rs As DAO.Recordset
    Dim sSQL As String
    Dim iRecs As Integer
    Dim dStart As Date
    Dim dEnd As Date
    Dim iSaudi As Integer
    
    iSaudi = [Forms]![InvoiceOutputParameterDateForm]![NonSaudiProviderCkBx]
    dStart = [Forms]![InvoiceOutputParameterDateForm]![StartDateTxtBx]
    dEnd = [Forms]![InvoiceOutputParameterDateForm]![EndDateTxtBx]
    
    sSQL = _
        "SELECT DentalPracticeName " & _
        "FROM Claims " & _
        "WHERE DataEntryDate >= #" & dStart & "# " & _
            "And DataEntryDate <= #" & dEnd & "# " & _
            "And NonSaudiMbr = " & iSaudi & " " & _
        "GROUP BY DentalPracticeName;"
    
    Set rs = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset, dbSeeChanges)
    With rs
        Do While Not .EOF          [COLOR="Green"]'start loop[/COLOR]
            Debug.Print .Fields(0) [COLOR="Green"]'print data from each record[/COLOR]
            .MoveNext              [COLOR="Green"]'move to the next record[/COLOR]
        Loop
        .Close
    End With

End Sub

So when we have confidence that is working, then we can do some more complex operation for each iteration of the loop.

 

[MarkK]

Also, does it make sense to you if we amend the code as follows, to farm out the recordset creation to a subcontractor? ...

Private Sub ExportClaimsBttn_Click()
[COLOR="Green"]'   This sub handles the button click, and controls the overall process
'   of exporting the claims[/COLOR]
    Dim rs As DAO.Recordset
    
    Set rs = [COLOR="Blue"]GetRecordset[/COLOR]
    With rs
        Do While Not .EOF
            Debug.Print .Fields(0)
            .MoveNext
        Loop
        .Close
    End With

End Sub

Private Function [COLOR="Blue"]GetRecordset[/COLOR]() As DAO.Recordset
[COLOR="Green"]'   This function returns the recordset we need, when we need it[/COLOR]
    Dim sSQL As String
    Dim dStart As Date
    Dim dEnd As Date
    Dim iSaudi As Integer
    
    iSaudi = [Forms]![InvoiceOutputParameterDateForm]![NonSaudiProviderCkBx]
    dStart = [Forms]![InvoiceOutputParameterDateForm]![StartDateTxtBx]
    dEnd = [Forms]![InvoiceOutputParameterDateForm]![EndDateTxtBx]
    
    sSQL = _
        "SELECT DentalPracticeName " & _
        "FROM Claims " & _
        "WHERE DataEntryDate >= #" & dStart & "# " & _
            "And DataEntryDate <= #" & dEnd & "# " & _
            "And NonSaudiMbr = " & iSaudi & " " & _
        "GROUP BY DentalPracticeName;"
        
    Set [COLOR="Blue"]GetRecordset[/COLOR] = CurrentDb.OpenRecordset(SQL)
End Function

See how this somewhat simplifies our Button_Click handler code? In that routine, we don't need to care about how the recordset is constructed anymore, and this is how we write modular code. Does that make sense?

 

[msk7777]

It does make sense and as soon as I have a second to test this I will do that for you. Give me a little bit and I will respond with what I found. Thanks again Mark!

 

[msk7777]

Mark when I run it I get an error :

The Microsoft Office Access Database engine cannot find the input table or query". Make sure it exists and that its name is spelled correctly.

Debugging points to:
Set GetRecordset = CurrentDb.OpenRecordset(SQL)

 

[MarkK]

Type "Option Explicit" as the second line of your code module, under Option Compare Database, on the next line, like . . .

Option Compare Database
Option Explicit

Then try to run the code again and you should get a compile error, which will show you what is wrong. I have SQL, which is an undeclared variable. It should be sSQL, but the compiler should catch this before we run the code.

To force the compiler to require all variables be declared, type Option Explicit at the top of modules that don't have it. To set this as a global default for all new modules (recommended) go to, in a code window, MainManu->Tools->Options->Editor tab->Code Settings section->Require Variable Declaration checkbox, and set the checkbox to True. This has the effect of putting Option Explicit at the top of every module.

 

[msk7777]

Ok that killed that error, now I get a compile error: Variable not defined.

Debugging highlights :
Private Function GetRecordset() As DAO.Recordset

 

[MarkK]

Are you sure? There is no variable definition occurring there.

I can easily cause that error at this line . . .

    Set rs = GetRecordset

... and at this line ...

    Set GetRecordset = CurrentDb.OpenRecordset(sSQL)

Other things you can try are . . .

Private Function GetRecordset() As Recordset

If all that fails, go to, in a code window, Main Menu->Tools->Options->General tab->Error Trapping section and set the "Break In Class Module" radio button, and run the code again. See if you get the same error at a different location.

 

[msk7777]

Yea you had :
Set GetRecordset = CurrentDb.OpenRecordset(SQL)

I changed it to

Set GetRecordset = CurrentDb.OpenRecordset(sSQL)

No errors & I see in the intermediate window all the office names.

 

[MarkK]

OK, so from your post #40 . . .

Then the output query would use the same date parameters and take the first office name and outputs the query report to excel, then loops through each office name doing the same.

This is where we are, right? We have this loop working. Now, what do we do in each iteration of the loop. Is there code for this? A query?

 

[msk7777]

It looks like we are headed in the right direction! Yes, we need to next pass each office name (and the date parameters) in the follow query:

SELECT ClaimsBatchMasterUNIONQuery.PolicyNumber AS [Policy Number], ClaimsBatchMasterUNIONQuery.DOB AS [Patient Date of Birth], ClaimsBatchMasterUNIONQuery.MemberLastName AS Surname, ClaimsBatchMasterUNIONQuery.MemberFirstName AS [First Name], "" AS Empty01, "" AS Empty02, IIf(IsNull([ClaimsBatchMasterUNIONQuery]![PracticeReferenceNumber]),[ClaimsBatchMasterUNIONQuery]![CIInvoiceNumber],[ClaimsBatchMasterUNIONQuery]![PracticeReferenceNumber] & "-" & [ClaimsBatchMasterUNIONQuery]![CIInvoiceNumber]) AS [Provider Invoice Number], ClaimsBatchMasterUNIONQuery.PractitionerName AS [Provider Name], "" AS Empty03, "GBR" AS [Country of Treatment Code], ClaimsBatchMasterUNIONQuery.NonDiscountedAmount AS [Original Invoice Amount], "GBP" AS [Invoice Currency Code], ClaimsBatchMasterUNIONQuery.DataEntryDate AS [Invoice Date], [ClaimsBatchMasterUNIONQuery]![NonDiscountedAmount]-[ClaimsBatchMasterUNIONQuery]![DiscountedAmount] AS Discount, 0 AS ZeroField, ClaimsBatchMasterUNIONQuery.[Treatement Date] AS [Treatment From Date], ClaimsBatchMasterUNIONQuery.[Treatement Date] AS [Treatment To Date], [ClaimsBatchMasterUNIONQuery]![DiscountedAmount] AS [Amount to Pay], "KSASTUD-D" AS [KSASTUD-D], "" AS Empty04, "" AS Empty05, "Z01.2" AS ZField, "Dental Examination" AS [Dental Examination], "" AS Empty06, [ClaimsBatchMasterUNIONQuery]![Procedure] AS [Child Exam], ClaimsBatchMasterUNIONQuery.DentalPracticeName
FROM ClaimsBatchMasterUNIONQuery
WHERE (((ClaimsBatchMasterUNIONQuery.DataEntryDate)>=[Forms]![InvoiceOutputParameterDateForm]![StartDateTxtBx] And (ClaimsBatchMasterUNIONQuery.DataEntryDate)<=[Forms]![InvoiceOutputParameterDateForm]![EndDateTxtBx]) AND ((ClaimsBatchMasterUNIONQuery.DentalPracticeName)=[Forms]![InvoiceOutputParameterDateForm]![DentalPracticeNameTxtBx]) AND ((ClaimsBatchMasterUNIONQuery.Procedure) Is Not Null) AND ((ClaimsBatchMasterUNIONQuery.NonSaudiMbr)=[Forms]![InvoiceOutputParameterDateForm]![NonSaudiProviderCkBx]));

The WHERE statement was previously pulling the parameters from the form, but since we are basically bypassing the form with the code I don't know how to get the parameters into the query.

Then we need to export the query to excel...previously I had been doing it by this statement:

DoCmd.OutputTo acOutputReport, "ClaimsBatchOutputReport", acFormatXLS, _         "[URL="file://10.74.8.144/NetworkDevelopment/MS_Database/Reports/Claims"][COLOR=#0066cc]\\10.74.8.144\NetworkDevelopment\MS_Database\Reports\Claims[/COLOR][/URL] Batch Output\" & Forms!InvoiceOutputParameterDateForm!DentalPracticeNameTxtBx & "_Claims Output Report_" & Format(Date, "dd-mmm-yyyy") & ".xls", True

Note that I also need to place the office name in the file name.

OK, so from your post #40 . . .

This is where we are, right? We have this loop working. Now, what do we do in each iteration of the loop. Is there code for this? A query?

 

[MarkK]

Hey question: What is the name of the form in which code is running here? I'm asking because I'd love to shorten this reference down, and it looks like we need to read data from this form in multiple places.

    iSaudi = [Forms]![InvoiceOutputParameterDateForm]![NonSaudiProviderCkBx]
    dStart = [Forms]![InvoiceOutputParameterDateForm]![StartDateTxtBx]
    dEnd = [Forms]![InvoiceOutputParameterDateForm]![EndDateTxtBx]

Is this InvoiceOutputParameterDateForm the same form we are currently working on?

 

[msk7777]

yes sir that is the same form.

Hey question: What is the name of the form in which code is running here? I'm asking because I'd love to shorten this reference down, and it looks like we need to read data from this form in multiple places.

    iSaudi = [Forms]![InvoiceOutputParameterDateForm]![NonSaudiProviderCkBx]
    dStart = [Forms]![InvoiceOutputParameterDateForm]![StartDateTxtBx]
    dEnd = [Forms]![InvoiceOutputParameterDateForm]![EndDateTxtBx]

Is this InvoiceOutputParameterDateForm the same form we are currently working on?

 

[MarkK]

So then we can shorten our code to . . .

Private Sub ExportClaimsBttn_Click()
'   This sub handles the button click, and controls the overall process
'   of exporting the claims
    Dim rs As DAO.Recordset
    
    Set rs = GetRstPracticeName
    With rs
        Do While Not .EOF
            Debug.Print .Fields(0)
            .MoveNext
        Loop
        .Close
    End With
End Sub

Private Function GetRstPracticeName() As DAO.Recordset
'   This function returns the recordset we need, when we need it
    Dim sSQL As String
    
    sSQL = _
        "SELECT DentalPracticeName " & _
        "FROM Claims " & _
        "WHERE DataEntryDate >= #" & Me.StartDateTxtBx & "# " & _
            "And DataEntryDate <= #" & Me.EndDateTxtBx & "# " & _
            "And NonSaudiMbr = " & Me.NonSaudiProviderCkBx & " " & _
        "GROUP BY DentalPracticeName;"
    
    Set GetRstPracticeName = CurrentDb.OpenRecordset(sSQL)
End Function

See how we just grab the values directly from the textbox names on the current form, rather than the verbose Forms!Formname!Controlname reference? Not much code left!!! And the loop still works, right?

But, as far as the that long SQL goes that we need to export, the only data it doesn't have in the WHERE clause is the practice name, right? So one fast and dirty solution is add a hidden textbox to your form, call it tbPracticeNameTEMP. Then, in each iteration of the loop, we will update that textbox. Then, set the report's SQL to reference that hidden textbox, so your where clause might look like . . .

    WHERE DataEntryDate >= [Forms]![InvoiceOutputParameterDateForm]![StartDateTxtBx]
    And DataEntryDate <= [Forms]![InvoiceOutputParameterDateForm]![EndDateTxtBx]
    AND DentalPracticeName = [Forms]![InvoiceOutputParameterDateForm]![COLOR="Red"]tbPracticeNameTEMP[/COLOR]
    AND [Procedure] Is Not Null
    AND NonSaudiMbr = [Forms]![InvoiceOutputParameterDateForm]![NonSaudiProviderCkBx];

so see how that just leverages all the fields on the form, except the tbPracticeNameTEMP, which is our new hidden textbox, and that value we change in every loop as follows . . .

    Set rs = GetRstPracticeName
    With rs
        Do While Not .EOF
            [COLOR="Red"]Me.tbPracticeNameTEMP = .Fields(0)[/COLOR]
            DoCmd.OutputTo acOutputReport, "ClaimsBatchOutputReport", acFormatXLS, _
                "\\10.74.8.144\NetworkDevelopment\MS_Database\Reports\Claims Batch Output\" & [COLOR="Red"].Fields(0)[/COLOR] & "_Claims Output Report_" & Format(Date, "dd-mmm-yyyy") & ".xls", True
            Debug.Print .Fields(0)
            .MoveNext
        Loop
        .Close
    End With

Do you get what's going on here? Yes, there are a few steps, but update the code, and update the one parameter in the where clause of the report, and you should be very close.
Keep me posted,

 

[msk7777]

THANK YOU!!!!!!!!!!!!!! This is now working just as I needed it too! I was thinking two weeks ago this would never get done but now it works!

Thanks you so much....and Minty too!

 

[MarkK]

Well done yourself, and Minty. All the best,