Looping Through Combobox Values

[msk7777]

Hello everyone,

I have tried using a lot of different people's codes to complete this but I am not too familiar with looping.

I have a form where once the user puts the data parameters in the combobox is populated with a list of dental office names. After the user selects the dental office name they then click a button which outputs a query (that uses the date and office name parameters) to excel.

Currently the user is clicking each office name and outputting a "report" manually.

I now have been tasked with making it so that when the button is clicked it will automatically change the combobox value and output the query results to excel, repeating for every name in the combobox.

This is the code I need to change:

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

If anyone could help me achieve my goal I would be greatly appreciative!

Thanks in advance!

 

[Ranman256]

For I=0 to cboBox.listcount -1
   sCode = cboBox.itemdata(I)
   CboBox = sCode
   Docmd.output ... CboBox
Next

 

[msk7777]

Thank you for replying. I tried your code but got an "Object Required" error. It indicates the first line of the code as the error.
I think I am missing something. I am a bit confused as to where exactly I should refer to combo box name within the code you provided. If you could elaborate a bit it might help. Thanks again!

For I=0 to cboBox.listcount -1
   sCode = cboBox.itemdata(I)
   CboBox = sCode
   Docmd.output ... CboBox
Next

 

[msk7777]

Ok I replaced "cboBox" for all places with actual combo box name and it started running. The problem now is that I see its outputting and changing the query name when exported to excel. The problem is that the combo box itself is a parameter for the query that's being output and the query is empty. Since I don't actually see the combo box value change so I am guessing the query isn't seeing it either?

Sub SubmitButton_Click()
For i = 0 To DentalPracticeNameCboBx.ListCount - 1
   scode = DentalPracticeNameCboBx.ItemData(i)
   CboBox = scode
   DoCmd.OutputTo acOutputReport, "ClaimsBatchOutputReport", acFormatXLS, _
        "[URL="file://\\cifs04\NetworkDevelopment\MS_Database\Reports\Claims"]\\cifs04\NetworkDevelopment\MS_Database\Reports\Claims[/URL] Batch Output\" & CboBox & "_Claims Output Report_" & Format(Date, "dd-mmm-yyyy") & ".xls", True
Next
End Sub

 

[Minty]

I would use a list box for this rather than a combo box.

A) it's easier to see what the results are
B) You can easily deselect or select items if the results aren't quite right.
C) you could remove each list box item as you looped through the reports so you had a visual idea of where you had got to in the process.

 

[msk7777]

Thanks for the input but that didn't help changing to list box. Unless I go in and select each name in the list the query doesn't grab the name from the list.

For example, If I select the first name in the list and click the button it works only on the first name. The rest of the exports have the correct file name but still displays the data of the first name in the list.

Also, If I select none of the names in the list and click the button it outputs all the correct file names but all the data within is blank.

So my guess is that I need code that actually selects each name in the box (one by one) or I need to completely approach this from another way.

 

[Minty]

Can you post the actual full code you are using for this?

 

[msk7777]

its the same code as above

Sub SubmitButton_Click()
For i = 0 To DentalPracticeNameCboBx.ListCount - 1
   scode = DentalPracticeNameCboBx.ItemData(i)
   CboBox = scode
   DoCmd.OutputTo acOutputReport, "ClaimsBatchOutputReport", acFormatXLS, _
        "[URL="file://cifs04/NetworkDevelopment/MS_Database/Reports/Claims"][COLOR=#0066cc]\\cifs04\NetworkDevelopment\MS_Database\Reports\Claims[/COLOR][/URL] Batch Output\" & CboBox & "_Claims Output Report_" & Format(Date, "dd-mmm-yyyy") & ".xls", True
Next
End Sub

Can you post the actual full code you are using for this?

 

[msk7777]

Just bumping on this to see if anyone can offer some assistance. Thanks!

 

[The_Doc_Man]

I think the approach stated in the first post is a clunky design that might be standing in your way. You are doing this by going around three left turns to make one right turn.

That combo box EITHER has an enumerated list of possible values OR it is fed by a row-source query. In the former case, you already boxed yourself in. But in the latter case (which makes more sense for the problem you described), you have a possible recordset already available and defined. If you can build a "real" query to match that .RowSource query, then you could open a recordset to it and step through the records just as you could have done a loop stepping through the rows in the combo box.

From your description, "changing the combo box" isn't important - it is finding the data associated with your selectable rows that is important.

Is there a sub-query involved here that isn't obvious from your description? I am not allowed to download samples at my business site due to stringent security settings, but then again, I didn't see anything that needed downloading anyway. Therefore, I don't see why pulling data from a combo box makes any more sense than making a query to match the combo box and doing a recordset enumeration instead. Less confusion, less data manipulation, ... sounds like a win-win to me.

 

[msk7777]

I think I am following what you are stating but in case I am not don't laugh. I am self taught so I am still learning a lot what many consider basic VBA lingo.

On this form there is Start and End Date parameter fields, that once filled, allow a query to be ran that fills the combo box we are discussing.

I did look into trying to find a way to utilize the query results instead of the combo box last week but wasn't able to find much help, though its highly likely I wasn't searching for the correct things.

I will start looking into recordset enumeration and see what I find.

I think the approach stated in the first post is a clunky design that might be standing in your way. You are doing this by going around three left turns to make one right turn.

That combo box EITHER has an enumerated list of possible values OR it is fed by a row-source query. In the former case, you already boxed yourself in. But in the latter case (which makes more sense for the problem you described), you have a possible recordset already available and defined. If you can build a "real" query to match that .RowSource query, then you could open a recordset to it and step through the records just as you could have done a loop stepping through the rows in the combo box.

From your description, "changing the combo box" isn't important - it is finding the data associated with your selectable rows that is important.

Is there a sub-query involved here that isn't obvious from your description? I am not allowed to download samples at my business site due to stringent security settings, but then again, I didn't see anything that needed downloading anyway. Therefore, I don't see why pulling data from a combo box makes any more sense than making a query to match the combo box and doing a recordset enumeration instead. Less confusion, less data manipulation, ... sounds like a win-win to me.

 

[The_Doc_Man]

When looking up queries, there is such a thing as a PARAMETER QUERY (that you can research as a topic) that allows you to enter your date fields to the query before you open it. That would be similar to what you describe, I think.

 

[msk7777]

Well I have these built out already. Ok, the current design is that the user opens this form. The user inputs data parameters which "query01" uses to pull office names entered into the table between those dates. The user then selects an office name from the combo box and hits a "Run" button that outputs "query02" to excel. Query02 uses both the date parameters and the combobox as query criteria.

Until now this has worked fine. When this process was first created the combo box was only filled with 5-10 office names. The users didn't mind selecting one office name and running the query, then selecting another office name and running the query for so few names.

Over time it is now getting close to 50 names and the users are screaming at me to make it so they can hit the button and all the individual office names are outputted to excel by clicking the button once.

So now my issue is trying to figure out how to get this done. I was hoping looping would work but all my attempts last week didn't do it. I think you are on the right track about using the "query01" results directly but I have no idea how to even approach that.

When looking up queries, there is such a thing as a PARAMETER QUERY (that you can research as a topic) that allows you to enter your date fields to the query before you open it. That would be similar to what you describe, I think.

 

[Minty]

You can open a recordset by using something like below (It's not tested but you should get the idea) - this uses a directly written query (sSql) but you can used a saved query name instead. There are advantages to writing the SQL string on the fly, that may not be obvious but its definitely worth learning how to do this.

Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim sSQL as String
Dim iRecs as integer

sSQl = "SELECT fields  "
sSQl = sSQl & "FROM YourTable "
sSQl = sSQl & "WHERE your criteria go in here"

Debug.Print sSql   'Comment thi out once you have your sql working
Set db = CurrentDb
Set rs = db.OpenRecordset(sSQl, dbOpenDynaset, dbSeeChanges)

iRecs = rs.recordcount
If Not rs.EOF Then ...

You now have a recordset open and a count of records. You can then step through each record to perform your export.

 

[msk7777]

Thanks Minty...I will play with this a bit and get back to you!

You can open a recordset by using something like below (It's not tested but you should get the idea) - this uses a directly written query (sSql) but you can used a saved query name instead. There are advantages to writing the SQL string on the fly, that may not be obvious but its definitely worth learning how to do this.

Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim sSQL as String
Dim iRecs as integer

sSQl = "SELECT fields  "
sSQl = sSQl & "FROM YourTable "
sSQl = sSQl & "WHERE your criteria go in here"

Debug.Print sSql   'Comment thi out once you have your sql working
Set db = CurrentDb
Set rs = db.OpenRecordset(sSQl, dbOpenDynaset, dbSeeChanges)

iRecs = rs.recordcount
If Not rs.EOF Then ...

You now have a recordset open and a count of records. You can then step through each record to perform your export.

 

[msk7777]

Minty - if I could get your assistance that would be great. I am getting an error at this line:

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

So far this is what I have:

Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim sSQL As String
Dim iRecs As Integer
 sSQL = "SELECT Claims.DentalPracticeName, Claims.NonSaudiMbr  "
sSQL = sSQL & "FROM InvoiceOutputDentalPracticeNameQuery "
sSQL = sSQL & "WHERE (((Claims.DataEntryDate)>=[Forms]![InvoiceOutputParameterDateForm]![StartDateTxtBx] And (Claims.DataEntryDate)<=[Forms]![InvoiceOutputParameterDateForm]![EndDateTxtBx]))"
 DoCmd.OutputTo acOutputReport, "ClaimsBatchOutputReport", acFormatXLS, _
        "[URL="file://\\cifs04\NetworkDevelopment\MS_Database\Reports\Claims"]\\cifs04\NetworkDevelopment\MS_Database\Reports\Claims[/URL] Batch Output\" & CboBox & "_Claims Output Report_" & Format(Date, "dd-mmm-yyyy") & ".xls", True
Set db = CurrentDb
Set rs = db.OpenRecordset(sSQL, dbOpenDynaset, dbSeeChanges)
 iRecs = rs.RecordCount
If Not rs.EOF Then
Exit Sub
End If

You can open a recordset by using something like below (It's not tested but you should get the idea) - this uses a directly written query (sSql) but you can used a saved query name instead. There are advantages to writing the SQL string on the fly, that may not be obvious but its definitely worth learning how to do this.

Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim sSQL as String
Dim iRecs as integer

sSQl = "SELECT fields  "
sSQl = sSQl & "FROM YourTable "
sSQl = sSQl & "WHERE your criteria go in here"

Debug.Print sSql   'Comment thi out once you have your sql working
Set db = CurrentDb
Set rs = db.OpenRecordset(sSQl, dbOpenDynaset, dbSeeChanges)

iRecs = rs.recordcount
If Not rs.EOF Then ...

You now have a recordset open and a count of records. You can then step through each record to perform your export.

 

[MarkK]

I am getting an error at this line:

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

What error are you getting?

 

[msk7777]

Its a "Too few parameters. Expected 4" error. I think it's because the output query is missing parameters now. The output query uses the combo box value as a parameter.

Is there maybe a way to place the current value of the sSQL into the combo box before the output runs?

What error are you getting?

 

[MarkK]

Your FROM clause cites the table InvoiceOutputDentalPracticeNameQuery, but all of the fields in the SQL cite the non-existent table Claims.

Consider re-adding Minty's line . . .

Debug.Print sSql   [COLOR="Green"]'Comment thi out once you have your sql working[/COLOR]

. . . and writing your SQL to the immediate pane, and testing it first. Copy the printed SQL from the immediate pane into the SQL view of a new query and test it. Look at what Minty's comment says. Very good advice there.

Hope this helps,

 

[msk7777]

Thanks for the assist Mark. I'm afraid what I was hoping would be simple has ventured into an area that is way above my head.

I hate wasting everyone's time so I will take what has been suggested and just study up on it.

Thanks for everyone's input and time!

Your FROM clause cites the table InvoiceOutputDentalPracticeNameQuery, but all of the fields in the SQL cite the non-existent table Claims.

Consider re-adding Minty's line . . .

Debug.Print sSql   [COLOR=green]'Comment thi out once you have your sql working[/COLOR]

. . . and writing your SQL to the immediate pane, and testing it first. Copy the printed SQL from the immediate pane into the SQL view of a new query and test it. Look at what Minty's comment says. Very good advice there.

Hope this helps,