Looping Through Combobox Values (1 Viewer)

[MarkK]

Just for your consideration, I post because I feel like it, so it's impossible for you to waste my time. If I post, I deemed it worth my while.

So please don't let that stop you from getting a solution to your problem. If you have the stamina to ask a question and someone else has the stamina to answer, no harm no foul.

All the best,

 

[Minty]

You are not wasting anyone's time - you have asked your questions in a clear sensible way and been prepared to go away and actually try to find a solution. Too many times on here I see people expecting to get a fully written code snippet for a badly designed structure.

So - to your problem - Your SQL is pretty good for a first go, but has missed some gotcha's that you will learn to love as time go on. Specifically Dates.

The sSql you have will not work because it doesn't know how to interpret your forms!... references and even if it did it wouldn't understand that they were dates. The other issue is that SQL wants to treat all dates in mm/dd/yyyy format. If that is your local default layout you won't have any issues. If you use UK formatted dates you have to leap through some extra hoops.

so using the code you have, firstly get your dates before writing the SQL string.

Dim dStart as Date
Dim dEnd as Date

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

You also have to enclose date values in # marks. so your final sSql would be;

sSQL = "SELECT Claims.DentalPracticeName, Claims.NonSaudiMbr  "
sSQL = sSQL & "FROM InvoiceOutputDentalPracticeNameQuery "
sSQL = sSQL & "WHERE (((Claims.DataEntryDate)>= #" & dStart & "# And (Claims.DataEntryDate)<= #" & dEnd & "#));"

Debug.Print sSql

See how the string is built up to include the variables outside of the string.
Now put back in the debug and you'll be able to see in the immediate window exactly what query will be run, this will allow you to check your date formats.

 

[msk7777]

Thank you Minty (and Mark)! I will start working in your suggestions and get back to you soon!

You are not wasting anyone's time - you have asked your questions in a clear sensible way and been prepared to go away and actually try to find a solution. Too many times on here I see people expecting to get a fully written code snippet for a badly designed structure.

So - to your problem - Your SQL is pretty good for a first go, but has missed some gotcha's that you will learn to love as time go on. Specifically Dates.

The sSql you have will not work because it doesn't know how to interpret your forms!... references and even if it did it wouldn't understand that they were dates. The other issue is that SQL wants to treat all dates in mm/dd/yyyy format. If that is your local default layout you won't have any issues. If you use UK formatted dates you have to leap through some extra hoops.

so using the code you have, firstly get your dates before writing the SQL string.

Dim dStart as Date
Dim dEnd as Date

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

You also have to enclose date values in # marks. so your final sSql would be;

sSQL = "SELECT Claims.DentalPracticeName, Claims.NonSaudiMbr  "
sSQL = sSQL & "FROM InvoiceOutputDentalPracticeNameQuery "
sSQL = sSQL & "WHERE (((Claims.DataEntryDate)>= #" & dStart & "# And (Claims.DataEntryDate)<= #" & dEnd & "#));"

Debug.Print sSql

See how the string is built up to include the variables outside of the string.
Now put back in the debug and you'll be able to see in the immediate window exactly what query will be run, this will allow you to check your date formats.

 

[msk7777]

I got the same error code (Run-time error '3061': Too few parameters. Expected 5.)

Incase I failed to provide any data you might find useful I will try to be as detailed as possible.

This is the query "InvoiceOutputDentalPracticeNameQuery" which supplies the combobox data:

SELECT Claims.DentalPracticeName
FROM Claims
WHERE (((Claims.NonSaudiMbr)=Forms!InvoiceOutputParameterDateForm!NonSaudiProviderCkBx) And ((Claims.DataEntryDate)>=Forms!InvoiceOutputParameterDateForm!StartDateTxtBx And (Claims.DataEntryDate)<=Forms!InvoiceOutputParameterDateForm!EndDateTxtBx))
GROUP BY Claims.DentalPracticeName;

Here is the code I have now after revising with your suggestions:

Private Sub ExportClaimsBttn_Click()
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim sSQL As String
Dim iRecs As Integer
Dim dStart As Date
Dim dEnd As Date
dStart = [Forms]![InvoiceOutputParameterDateForm]![StartDateTxtBx]
dEnd = [Forms]![InvoiceOutputParameterDateForm]![EndDateTxtBx]
sSQL = "SELECT Claims.DentalPracticeName, Claims.NonSaudiMbr  "
sSQL = sSQL & "FROM InvoiceOutputDentalPracticeNameQuery "
sSQL = sSQL & "WHERE (((Claims.DataEntryDate)>= #" & dStart & "# And (Claims.DataEntryDate)<= #" & dEnd & "# And (Claims.NonSaudiMbr)=Forms!InvoiceOutputParameterDateForm!NonSaudiProviderCkBx));"
Debug.Print sSQL
Set db = CurrentDb
Set rs = db.OpenRecordset(sSQL, dbOpenDynaset, dbSeeChanges)
iRecs = rs.RecordCount
If Not rs.EOF Then
Exit Sub
End If
End Sub

Debugging is highlighting the line:

Set rs = db.OpenRecordset(sSQL, dbOpenDynaset, dbSeeChanges)

Since I am in the U.S. I didn't even think about the date format. However, this is the only database I have that covers our U.K. business and the date fields on the form are dd/mm/yyyy. So in reference to your message I guess the date format is causing the parameter error?

 

[Minty]

No not the dates (not yet)

The problem is your third criteria - (Claims.NonSaudiMbr)=Forms!InvoiceOutputParameterDateForm!NonSaudiProviderCkBx));

The SQL interpreter cannot reference forms as they aren't in it's object reference.
So again you need to get the form value into the code outside of the string.
For readability of code later I normally do this outside of the sSql string creation into a variable, exactly the same as we did earlier with the dates - We also need to know if this is text or a number to format the string correctly.

Dim iSaudi as Integer or
Dim sSaudi as String.

Then your string will be completed by either
And (Claims.NonSaudiMbr)= " & iSaudi & " ));" 'Number or True false field
And (Claims.NonSaudiMbr)= '" & sSaudi & "' ));" 'Text field

Note the extra single quote in red to denote that it's a text string

 

[Frothingslosh]

This might be a good time to look into the use of parameter queries rather than creating a SQL statement at runtime.

 

[msk7777]

I got the same error, shouldn't I also create a line like

iSaudi = [Forms]![InvoiceOutputParameterDateForm]![NonSaudiProviderCkBx]

Also, the isaudi is a checkbox so I used it as an integer.

Ok now as I read your message again I think you were saying we will need to add something like above after I tell you the info.

No not the dates (not yet)

The problem is your third criteria - (Claims.NonSaudiMbr)=Forms!InvoiceOutputParameterDateForm!NonSaudiProviderCkBx));

The SQL interpreter cannot reference forms as they aren't in it's object reference.
So again you need to get the form value into the code outside of the string.
For readability of code later I normally do this outside of the sSql string creation into a variable, exactly the same as we did earlier with the dates - We also need to know if this is text or a number to format the string correctly.

Dim iSaudi as Integer or
Dim sSaudi as String.

Then your string will be completed by either
And (Claims.NonSaudiMbr)= " & iSaudi & " ));" 'Number or True false field
And (Claims.NonSaudiMbr)= '" & sSaudi & "' ));" 'Text field

Note the extra single quote in red to denote that it's a text string

 

[Minty]

I got the same error, shouldn't I also create a line like

iSaudi = [Forms]![InvoiceOutputParameterDateForm]![NonSaudiProviderCkBx]

Also, the isaudi is a checkbox so I used it as an integer.

Ok now as I read your message again I think you were saying we will need to add something like above after I tell you the info.

Yes - that is correct if you add that code you should be getting somewhere.
You should see your actual query string in the immediate window in the VB editor, you should examine that to spot any obvious typo's etc.

Frothingslosh has made a very valid point - but from a learning perspective this is a useful technique, and I don't want to add another level of confusion at this point.

 

[msk7777]

Ok I am still getting the error. I will be honest, this is the first time I have ever looked at the "immediate" screen.

Here is the latest version of my code:

Private Sub ExportClaimsBttn_Click()
Dim rs As DAO.Recordset
Dim db As DAO.Database
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 Claims.DentalPracticeName, Claims.NonSaudiMbr "
sSQL = sSQL & "FROM InvoiceOutputDentalPracticeNameQuery "
sSQL = sSQL & "WHERE (((Claims.DataEntryDate)>= #" & dStart & "# And (Claims.DataEntryDate)<= #" & dEnd & "# And (Claims.NonSaudiMbr)= " & iSaudi & " ));"
 Debug.Print sSQL
 Set db = CurrentDb
Set rs = db.OpenRecordset(sSQL, dbOpenDynaset, dbSeeChanges)
 iRecs = rs.RecordCount
If Not rs.EOF Then
Exit Sub
End If
End Sub

And here is what is showing in the immediate screen:

SELECT Claims.DentalPracticeName, Claims.NonSaudiMbr  FROM InvoiceOutputDentalPracticeNameQuery WHERE (((Claims.DataEntryDate)>= #1/18/2016# And (Claims.DataEntryDate)<= #1/22/2016#));
SELECT Claims.DentalPracticeName, Claims.NonSaudiMbr  FROM InvoiceOutputDentalPracticeNameQuery WHERE (((Claims.DataEntryDate)>= #1/18/2016# And (Claims.DataEntryDate)<= #1/22/2016#));
SELECT Claims.DentalPracticeName, Claims.NonSaudiMbr  FROM InvoiceOutputDentalPracticeNameQuery WHERE (((DataEntryDate)>= #1/18/2016# And (DataEntryDate)<= #1/22/2016#));
SELECT DentalPracticeName, NonSaudiMbr  FROM InvoiceOutputDentalPracticeNameQuery WHERE (((DataEntryDate)>= #1/18/2016# And (DataEntryDate)<= #1/22/2016#));
SELECT Claims.DentalPracticeName, Claims.NonSaudiMbr  FROM InvoiceOutputDentalPracticeNameQuery WHERE (((Claims.DataEntryDate)>= #1/18/2016# And (Claims.DataEntryDate)<= #1/22/2016#));
SELECT Claims.DentalPracticeName, Claims.NonSaudiMbr  FROM InvoiceOutputDentalPracticeNameQuery WHERE (((Claims.DataEntryDate)>= #1/18/2016# And (Claims.DataEntryDate)<= #1/22/2016#)And (Claims.DataEntryDate)<=[Forms]![InvoiceOutputParameterDateForm]![EndDateTxtBx]));
SELECT Claims.DentalPracticeName, Claims.NonSaudiMbr  FROM InvoiceOutputDentalPracticeNameQuery WHERE (((Claims.DataEntryDate)>= #1/18/2016# And (Claims.DataEntryDate)<= #1/22/2016# And (Claims.DataEntryDate)<=[Forms]![InvoiceOutputParameterDateForm]![EndDateTxtBx]));
SELECT Claims.DentalPracticeName, Claims.NonSaudiMbr  FROM InvoiceOutputDentalPracticeNameQuery WHERE (((Claims.DataEntryDate)>= #1/18/2016# And (Claims.DataEntryDate)<= #1/22/2016# And (Claims.NonSaudiMbr)=Forms!InvoiceOutputParameterDateForm!NonSaudiProviderCkBx));
SELECT Claims.DentalPracticeNaSELECT Claims.DentalPracticeName, Claims.NonSaudiMbr  FROM InvoiceOutputDentalPracticeNameQuery WHERE (((Claims.DataEntryDate)>= #1/18/2016# And (Claims.DataEntryDate)<= #1/22/2016# And (Claims.NonSaudiMbr)=Forms!InvoiceOutputParameterDateForm!NonSaudiProviderCkBx));
SELECT Claims.DentalPracticeName, Claims.NonSaudiMbr  FROM InvoiceOutputDentalPracticeNameQuery WHERE (((Claims.DataEntryDate)>= #1/18/2016# And (Claims.DataEntryDate)<= #1/22/2016# And (Claims.NonSaudiMbr)= 0 ));
SELECT Claims.DentalPracticeName, Claims.NonSaudiMbr FROM InvoiceOutputDentalPracticeNameQuery WHERE (((Claims.DataEntryDate)>= #1/18/2016# And (Claims.DataEntryDate)<= #1/22/2016# And (Claims.NonSaudiMbr)= 0 ));
SELECT Claims.DentalPracticeName, Claims.NonSaudiMbr FROM InvoiceOutputDentalPracticeNameQuery WHERE (((Claims.DataEntryDate)>= #1/18/2016# And (Claims.DataEntryDate)<= #1/22/2016# And (Claims.NonSaudiMbr)= 0 ));
SELECT Claims.DentalPracticeName, Claims.NonSaudiMbr FROM InvoiceOutputDentalPracticeNameQuery WHERE (((Claims.DataEntryDate)>= #1/18/2016# And (Claims.DataEntryDate)<= #1/22/2016# And (Claims.NonSaudiMbr)= 0 ));
SELECT Claims.DentalPracticeName, Claims.NonSaudiMbr FROM InvoiceOutputDentalPracticeNameQuery WHERE (((Claims.DataEntryDate)>= #1/18/2016# And (Claims.DataEntryDate)<= #1/22/2016# And (Claims.NonSaudiMbr)= 0 ));
SELECT Claims.DentalPracticeName, Claims.NonSaudiMbr FROM InvoiceOutputDentalPracticeNameQuery WHERE (((Claims.DataEntryDate)>= #1/18/2016# And (Claims.DataEntryDate)<= #1/22/2016# And (Claims.NonSaudiMbr)= 0 ));
SELECT Claims.DentalPracticeName, Claims.NonSaudiMbr SELECT Claims.DentalPracticeName, Claims.NonSaudiMbr FROM InvoiceOutputDentalPracticeNameQuery WHERE (((Claims.DataEntryDate)>= #1/18/2016# And (Claims.DataEntryDate)<= #1/22/2016# And (Claims.NonSaudiMbr)= 0 ));
FROM ISELECT Claims.DentalPracticeName, Claims.NonSaudiMbr FROM InvoiceOutputDentalPracticeNameQuery WHERE (((Claims.DataEntryDate)>= #1/18/2016# And (Claims.DataEntryDate)<= #1/22/2016# And (Claims.NonSaudiMbr)= 0 ));

Revised this, realized that I missed parts to copy.

I am not sure what I am looking at here on the immediate screen and why the codes are repeating and some are different.

Yes - that is correct if you add that code you should be getting somewhere.
You should see your actual query string in the immediate window in the VB editor, you should examine that to spot any obvious typo's etc.

Frothingslosh has made a very valid point - but from a learning perspective this is a useful technique, and I don't want to add another level of confusion at this point.

 

[Minty]

Okay - you appear to have an extra bracket around the criteria, I would remove the lot of them personally and just put one open bracket at the beginning and one at the end.

WHERE (Claims.DataEntryDate >= #1/18/2016# And Claims.DataEntryDate <= #1/22/2016# And Claims.NonSaudiMbr = 0) ;

The other results in the immediate window are previous attempts that you can simply delete.

 

[msk7777]

I feel like we are getting there but I still got the same error.

Private Sub ExportClaimsBttn_Click()
Dim rs As DAO.Recordset
Dim db As DAO.Database
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 Claims.DentalPracticeName, Claims.NonSaudiMbr "
sSQL = sSQL & "FROM InvoiceOutputDentalPracticeNameQuery "
sSQL = sSQL & "WHERE (Claims.DataEntryDate >= #" & dStart & "# And Claims.DataEntryDate <= #" & dEnd & "# And Claims.NonSaudiMbr = " & iSaudi & " );"
Debug.Print sSQL
Set db = CurrentDb
Set rs = db.OpenRecordset(sSQL, dbOpenDynaset, dbSeeChanges)
iRecs = rs.RecordCount
If Not rs.EOF Then
Exit Sub
End If
End Sub

Immediate screen:

SELECT Claims.DentalPracticeName, Claims.NonSaudiMbr FROM InvoiceOutputDentalPracticeNameQuery WHERE (Claims.DataEntryDate >= #1/18/2016# And Claims.DataEntryDate <= #1/22/2016# And Claims.NonSaudiMbr = 0 );

Okay - you appear to have an extra bracket around the criteria, I would remove the lot of them personally and just put one open bracket at the beginning and one at the end.

WHERE (Claims.DataEntryDate >= #1/18/2016# And Claims.DataEntryDate <= #1/22/2016# And Claims.NonSaudiMbr = 0) ;

The other results in the immediate window are previous attempts that you can simply delete.

 

[Minty]

Your query InvoiceOutputDentalPracticeNameQuery does that require parameters at all ?

 

[msk7777]

Yes sir. I did post it earlier for you.

With that in mind I decided to bypass the query and go straight to the table.

Private Sub ExportClaimsBttn_Click()
Dim rs As DAO.Recordset
Dim db As DAO.Database
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 Claims.DentalPracticeName, Claims.NonSaudiMbr "
sSQL = sSQL & "FROM Claims "
sSQL = sSQL & "WHERE (Claims.DataEntryDate >= #" & dStart & "# And Claims.DataEntryDate <= #" & dEnd & "# And Claims.NonSaudiMbr = " & iSaudi & " );"
 Debug.Print sSQL
 Set db = CurrentDb
Set rs = db.OpenRecordset(sSQL, dbOpenDynaset, dbSeeChanges)
 iRecs = rs.RecordCount
If Not rs.EOF Then
Exit Sub
End If
End Sub

A step closer! When I run this I no longer get an error. However, nothing happens. From the way I am wrapping my head around this. All the above code is doing is looking at the list that the created query comes up with.

What now needs to happen is that I need to apply the results of the created query to the output report.

If you remember, previously I was clicking the button and then the following would fire:

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

This report query consists of:

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]));

This query uses the same parameter fields from the form. So as I see the code right now the end result (which it to export the data to this report) isn't being done.

To backtrack a bit, the query you just helped me create within the code produces a list of dental offices that submitted claims within the date parameters. Previously, after entering the date parameters and selecting an office name from the combo box, the user would click the "Export Claim" button and it would export all the claims data for that specific office name to excel. Then the user would select a different office name from the combo box and click the button again to export that specific office's claims data. They would repeat this until all the office names had exported data.

It looks like we have only covered creating a way to loop through all the office names, now we have to get those office names into the parameters of the report query.

Your query InvoiceOutputDentalPracticeNameQuery does that require parameters at all ?

 

[Minty]

Apologies I was intent on getting your recordset correct - we haven't covered the looping bit yet. (always break tasks like this down into "simple" steps. Get the selected data right , then work with it.)

Unfortunately - I'm pretty busy today - but will revisit this shortly. What you need to do now is is make sure we have all the data you require, in the record set. You can quickly check this by copying and pasting the generated SQL into the querty design window and look at the results it pulls.

 

[msk7777]

I did as you asked and placed it into a query. After a few tweaks I got it working just as I needed it. It wasn't giving me unique values so I had to add a GROUP BY statement.

Private Sub ExportClaimsBttn_Click()
Dim rs As DAO.Recordset
Dim db As DAO.Database
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 Claims.DentalPracticeName "
sSQL = sSQL & "FROM Claims "
sSQL = sSQL & "WHERE (Claims.DataEntryDate >= #" & dStart & "# And Claims.DataEntryDate <= #" & dEnd & "# And Claims.NonSaudiMbr = " & iSaudi & " )"
sSQL = sSQL & "GROUP BY Claims.DentalPracticeName ;"
Debug.Print sSQL
Set db = CurrentDb
Set rs = db.OpenRecordset(sSQL, dbOpenDynaset, dbSeeChanges)
iRecs = rs.RecordCount
If Not rs.EOF Then
Exit Sub
End If
End Sub

Now it is working perfectly!

Apologies I was intent on getting your recordset correct - we haven't covered the looping bit yet. (always break tasks like this down into "simple" steps. Get the selected data right , then work with it.)

Unfortunately - I'm pretty busy today - but will revisit this shortly. What you need to do now is is make sure we have all the data you require, in the record set. You can quickly check this by copying and pasting the generated SQL into the querty design window and look at the results it pulls.

 

[msk7777]

Minty - was just bumping this incase you forgot about me. Thanks!

 

[MarkK]

What are you looking for? You post says, "Now it is working perfectly!" I've also read back a few posts and can't see what else you require.
Maybe you could re-state the current problem or direct our attention to the post where the problem is defined. Cheers,

 

[msk7777]

Minty was helping me in stages. If you reference his post:
Apologies I was intent on getting your recordset correct - we haven't covered the looping bit yet. (always break tasks like this down into "simple" steps. Get the selected data right , then work with it.)

Unfortunately - I'm pretty busy today - but will revisit this shortly. What you need to do now is is make sure we have all the data you require, in the record set. You can quickly check this by copying and pasting the generated SQL into the querty design window and look at the results it pulls.


My statement of "Now it is working perfectly" was in reference to that specific stage. Minty was going to help me with the final step. If he doesn't respond soon I will restate it all for sure.

Thanks for trying to step in Mark, its very much appreciated!

What are you looking for? You post says, "Now it is working perfectly!" I've also read back a few posts and can't see what else you require.
Maybe you could re-state the current problem or direct our attention to the post where the problem is defined. Cheers,

 

[MarkK]

Yes, I see your process, I just don't see the current problem, so I can't help.
Cheers,

 

[msk7777]

Ok I will try to catch you up sir. The code we just finished creates a query that pulls a list of dental office names that had claims entered into the data base between the date parameters entered on the form.

The next step would be to add on to that code so that for each dental office name in that list it would be the criteria in an output query that I originally used

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

This output query also uses the same parameters as the code Minty helped me with so that would have to be taken into account and also in the above output where I am naming the file I would need the dental office name being created from the query instead of using the form combo box as it used to (Forms!InvoiceOutputParameterDateForm!DentalPracticeNameTxtBx).

So basically, on the form, when the user enters the date parameters and click the "run report" button, the code Minty helped with gathers all the office names between those dates. 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.

Previously the users would have to select each office name and export the query report to excel, now that there are 50+ office names in the combo box we need to have it automatically print each one.

Hope that helps. And thanks again for any help you might offer!

Yes, I see your process, I just don't see the current problem, so I can't help.
Cheers,