Full Version: Looping through each combo box value
UtterAccess Forums > Microsoft® Access > Access Forms
georgeedwards
I need to loop through each combo box value automatically, performing a specified procedure after each selection.
tried this...
For intIndex = 0 To ctl.ListCount - 1
' procedure goes in here...
Next
However it does not work.
Is that the right syntax?
THere is my entire procedure:
CODE
Private Sub cmdFinaliseAll_Click()
Dim ctl As Control
Dim strDoc As String ' Report Name
Dim intIndex As Integer ' Index for combo box items
Dim varItem As Variant ' Value of the Bound Column
Dim stDocName As String
Dim Msg As String
' DAO DECLARATIONS
Dim db As DAO.Database, rstInvoice As DAO.Recordset
Dim rst As DAO.Recordset
Dim varNextInv As Variant
Set db = CurrentDb
Set rstInvoice = db.OpenRecordset("CustomerInvoices", dbOpenDynaset)
Set rst = db.OpenRecordset("SELECT Max(CInt([CustomerInvoiceNumber])) AS MaxNo " & _
"FROM CustomerInvoices")
Set ctl = Me.cboCustomer
' Loop through the combo box values...
    For intIndex = 0 To ctl.ListCount - 1
' ... and do the following
Msg = "Do you wish to finalise" & "'" & cboCustomer.Value & "'?"
Msg = Msg & vbCrLf & vbCrLf & "You should only click yes once the report has been printed off."
stDocName = "qappinvoicedcustomers"
If MsgBox(Msg, vbYesNo + vbQuestion, "Customer Invoicing") = vbYes Then
' Mark consignments as invoiced for the customer
    
DoCmd.OpenQuery stDocName
' ========================================================
' DAO Procedure to add Invoice record to appropriate table
' Created: 01/08/2007
' Revised: 01/08/2007
' George Edwards
' ========================================================
' If no previous invoices
If rst.EOF Then
    varNextInv = 1
Else
    If IsNull(rst!MaxNo) Then
    varNextInv = 1
Else
    varNextInv = rst!MaxNo + 1
End If
End If
rst.Close
Set rst = Nothing
' Create the invoice record
rstInvoice.AddNew
rstInvoice!CustomerInvoiceNumber = varNextInv
rstInvoice!InvoiceDate = Date
rstInvoice!StartPeriod = Forms![Customer Invoicing]!txtFromDate
rstInvoice!EndPeriod = Forms![Customer Invoicing]!txtToDate
rstInvoice!CustomerAccount = Forms![Customer Invoicing]!cboCustomer
' Save the row
rstInvoice.Update
' ... and close rst
rstInvoice.Close
' Null it
Set rstInvoice = Nothing
Else
    Exit Sub
End If
Next
End Sub
Doug Steele
The Value property always refers to the currently selected row. You need to refer to cboCustomer.ItemDate(intIndex)
georgeedwards
I think I'm with you. Is there no way to physically have code select the next combo box value, run a procedure, select the next combo box value, run a procedure.........until at the bottom of the combo box options?
Doug Steele
As I said, you use the ItemData property of the combo box, not the Value property. Try the following. It'll loop through the combo box, then display a message box showing you each value in the combo box. (Try it on a small combo box, otherwise it might be too big for a message box!)
!--c1-->
CODE
Dim lngLoop As Long
Dim strMessage As String
  
  For lngLoop = 0 To Me.MyComboBox.ListCount - 1
    strMessage = strMessage & "Row " & lngLoop " & ": " & Me.MyComboBox.ItemData(lngLoop) & vbCrLf
  Next lngLoop
  
  MsgBox strMessage

Now, that returns the value from the bound column of the combo box. If you wanted, say, the value from the 3rd column of each row, you'd use:
CODE
Dim lngLoop As Long
Dim strMessage As String
  
  For lngLoop = 0 To Me.MyComboBox.ListCount - 1
    strMessage = strMessage & "Row " & lngLoop " & ": " & Me.MyComboBox.Column(2, lngLoop) & vbCrLf
  Next lngLoop
  
  MsgBox strMessage

(Remember: the Column collection starts counting at 0, so the 3rd column is referred to as Column 2)
georgeedwards
Thanks Doug. I tried that code, and all it does it display the very last item in the combo box.. a Customer starting with "X".
The reason I asked what I asked is because I have a query that requires a selection to be present in the cboCustomer combo box in order to append information.
Omay be getting lost, but when the value is referenced as a "co-ordinate" from the initial value; I don't think what I want is achievable...
Thanks so far.
Doug Steele
You sure you typed the code in correctly? If you accidentally mistyped
trMessage = strMessage & "Row " & lngLoop " & ": " & Me.MyComboBox.Column(2, lngLoop) & vbCrLf
as
strMessage = "Row " & lngLoop " & ": " & Me.MyComboBox.Column(2, lngLoop) & vbCrLf
then I could see it only giving you the last entry, but it really should should you everything.
georgeedwards
Thanks.
How do I pass this value, through the usage of the "Me.CboCustomer.Column(0, lngLoop)" code, to the record set in the above procedure, and the Append query?
Thanks Doug !
Doug Steele
It's not really clear to me what you're trying to do with the value. I don't know why you're opening qappinvoicedcustomers, for instance.
However, to add details for each customer to rstInvoice, change
rstInvoice!CustomerAccount = Forms![Customer Invoicing]!cboCustomer
to
rstInvoice!CustomerAccount = Forms![Customer Invoicing]!cboCustomer.Column(0, lngLoop)
However, you close rstInvoice inside the loop, and never reopen it, so that's only going to work once.
Maybe you should explain in words what it is you're trying to do, rather than me addressing specific questions dealing with your code.
georgeedwards
Ok well the point of this procedure is the following...
ser comes along and selects a Customer to invoice. There are about 60 customers in the system at the moment, so "batch" producing invoices for every customer all at once is vital. I have addressed the issue of using a "Force New Page" with the report to include all Customers on one giant report. This works fine.
The procedure you see above is using DAO to insert a few pieces of information into a "tblCustomerInvoices" table - an Invoice Number, Date of the Invoice, Date Range of the Invoice, and the Customer...essentially enough information to recreate the invoice again in the future.
The update query (i know I gave it a qapp title, my mistake) works its way through the entity containing all the Consignments in the system (like Transactions), and marks the relevant ones matching the criteria selected on the dialog box as Invoiced and gives them an invoice number that is pre determined on the dialog box.
Hope that enlightens you,
George sad.gif
georgeedwards
Hmm, brain wave. Perhaps I could use the "loop" function you provided to store the current customer in a hidden text box, and point the query and DAO code to that text box?
Just an idea.
Doug Steele
Based on that description, the code should do what you're asking for.
hat's happening when you run the (amended) code? (For that matter, to what have you amended the code?)
georgeedwards
Doug
With your help, I created the following code, and it works fine!
I have one final request though...thoughout this entire procedure, if a Customer has no consignments to invoice, a "phantom" Invoice Number is generated in its place. Do you have any suggestions for a bail code to ensure that if a customer has no consignments to invoice, not to create a phantom invoice?
Thanks Doug. I'm a little bit lost right now!
CODE
Private Sub cmdFinaliseAll_Click()
Dim lngLoop As Long
Dim strMessage As String
Dim stDocName As String
Dim Msg As String
' DAO DECLARATIONS
Dim db As DAO.Database, rstInvoice As DAO.Recordset
Dim rst As DAO.Recordset
Dim varNextInv As Variant
Set db = CurrentDb
Set rstInvoice = db.OpenRecordset("CustomerInvoices", dbOpenDynaset)
Set rst = db.OpenRecordset("SELECT Max(CInt([CustomerInvoiceNumber])) AS MaxNo " & _
"FROM CustomerInvoices")
Msg = "Do you wish to mark the customers as invoiced?"
Msg = Msg & vbCrLf & vbCrLf & "You should only click yes once the report has been printed off."
If MsgBox(Msg, vbYesNo + vbQuestion, "Customer Invoicing") = vbYes Then
    For lngLoop = 0 To Me.cboCustomer.ListCount - 1
    ' Copy the next Account Code to the text box for usage
    Me.txtCustomer = Me.cboCustomer.Column(0, lngLoop)
    ' PROCEDURE GOES HERE!
    GoTo MainBit
ContinueLoop:
    Next lngLoop
GoTo EndProcedure
    
MainBit:
stDocName = "qappinvoicedcustomersALL"
' Mark consignments as invoiced for the customer
    
DoCmd.OpenQuery stDocName
' ========================================================
' DAO Procedure to add Invoice record to appropriate table
' Created: 01/08/2007
' Revised: 01/08/2007
' George Edwards
' ========================================================
' If no previous invoices
Set rst = db.OpenRecordset("SELECT Max(CInt([CustomerInvoiceNumber])) AS MaxNo " & _
"FROM CustomerInvoices")
If rst.EOF Then
    varNextInv = 1
Else
    If IsNull(rst!MaxNo) Then
    varNextInv = 1
Else
    varNextInv = rst!MaxNo + 1
End If
End If
rst.Close
Set rst = Nothing
' Copy the invoice number
Me.txtProposedInvoiceNumber = varNextInv
' Create the invoice record
rstInvoice.AddNew
rstInvoice!CustomerInvoiceNumber = varNextInv
rstInvoice!InvoiceDate = Date
rstInvoice!StartPeriod = Forms![Customer Invoicing]!txtFromDate
rstInvoice!EndPeriod = Forms![Customer Invoicing]!txtToDate
rstInvoice!CustomerAccount = Forms![Customer Invoicing]!txtCustomer
' Save the row
rstInvoice.Update
' ... and close rst
GoTo ContinueLoop
End If
EndProcedure:
rstInvoice.Close
Set rstInvoice = Nothing
End Sub
Doug Steele
I believe your problem is caused by the fact that you're using the combo box, which contains every customer, to dirve this, rather than strictly a list of those customers who actually had consignments.
ather than using the combo box, I'd recommend creating a recordset that returns 1 row for each relevant customer. Unfortunately, since I don't know your tables, I can't give you the actual SQL to use to create that recordset, but presumably it would be something like:
SELECT DISTINCT CustomerAccount
FROM SomeTable
WHERE SomeDateField BETWEEN Forms![Customer Invoicing]!txtFromDate AND Forms![Customer Invoicing]!txtToDate
Of course, if you're using that to create a recordset, you either need to resolve the parameters, or else use a SQL string that injects the values appropriate:
SET MyRecordset = CurrentDb.OpenRecordset("SELECT DISTINCT CustomerAccount " & _
"FROM SomeTable" & _
"WHERE SomeDateField BETWEEN " & Format(Me!txtFromDate, "\#yyyy\-mm\-dd\#") & _
" AND " & Format(Forms![Customer Invoicing]!txtToDate, "\#yyyy\-mm\-dd\#"))
georgeedwards
I think I see what you're saying. Well could I make it so that the cboCustomers combo box is populated ONLY from the customers that have consignments assigned to them?
For example, could I create a WHERE statement in the rowsource of the combo box that says something like - WHERE DCount ( "[ConsignmentNumber]" , tblConsignments)? Do you see what I mean?
I'm sure it's "doable", and it will work...
Thanks!
Doug Steele
Yes, limiting what's displayed in the combo box is a valid approach as well.
However, I'd recommend not using DCount in a query if you don't have to: it'll be inefficient.
Use a variation of the SQL I suggested before as the RowSource for the combo box.
Alternatively, you could put something like
CODE
  If DCount("*", "tblConsignment", "(ConsignmentDate BETWEEN " & _
    Format(Forms![Customer Invoicing]!txtFromDate, "\#yyyy\-mm\-dd\#") & _
    " AND " & Format(Forms![Customer Invoicing]!txtToDate, "\#yyyy\-mm\-dd\#") & ") " & _
    " AND CustomerAccount = " & Forms![Customer Invoicing]!txtCustomer) > 0 Then
[color="green"]' Run the rest of the code to get the invoice number and store it[/color]
  Else
[color="green"]' The customer doesn't have any consignments: skip to the next one[/color]
  End If
georgeedwards
Sounds like it could work out well, thanks Doug.
I'll give it a shot and keep you posted.
Cheers! I owe you a pint or seventeen!
georgeedwards
Doug, I used the code you suggested but all I get is a "you cancelled the operation" error, code 2001...any recommendations?
georgeedwards
Doug,
sorted it and it works 100% ok! I simply used a query (qryCountCustCons) in place of the SQL you suggested, and then ran a DCount on this query. All works fine!
Many thanks!!! o! uarulez2.gif thanks.gif
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.