Loop through a list and match to combo box on form 1

[sterlecki]

I have a form. Specific records are accessed by selecting from a combobox. The combobox has two columns API and WellName, API is the bound column, the row source is a query.

I would like to write some code that would allow the user to take a list of API numbers from a temptable and loop through this list matching each item in the table to the item in the form's combo box. Once matched then automatically actuate a series of command buttons on the form that prints a report, exports to a file.

I'm thinking along the lines of this but am not sure of all code.

For each item in temptable.API
Find item in combobox
if match then
activate cmdbutton 1
activate cmdbutton 2
end if
next item in temptable.API

How do I reference the appropriate column in the combo box and programtically "click" the command buttons?

 

[greely]

To reference a specific column in a combobox you use:

Me!YourComboBoxName.Column(0)

Where the 0 is the first column, 1 is the second col, etc.

The best way to programmatically 'Click' a command button is to not click it at all. What you do is set up a procedure like this:

===================
Private Sub MakeItHappen()

'insert code for what should happen if command button is
'clicked

End Sub
=======================

Then on the command button click event put:

==================
Private Sub cmdButton1_Click()

MakeItHappen

End Sub
==================

When cmdButton1 is clicked, the MakeItHappen procedure will run. Additionally, anywhere else in your code that you put MakeItHappen, the MakeItHappen code will run. For example, if you put MakeItHappen in the onchange event of a combobox, MakeItHappen would run.



Ron

-Isn't ist great when it works the first time?

 

[TheAceMan1]

How are ya sterlecki . . . . .

In the code module for the form, copy/paste the following routine ([blue]you![/blue] substitute proper names in [purple]purple[/purple]):

[blue]Public Sub DoButtons()
   Dim db As DAO.Database, rst As DAO.Recordset
   Dim CBx As ComboBox, x As Integer
   
   Set db = CurrentDb
   Set CBx = Me![purple][b]ComboboxName[/b][/purple]
   Set rst = db.OpenRecordset("[purple][b]TempTableName[/b][/purple]", dbOpenDynaset)
   
   If Not rst.BOF Then
      Do While Not rst.EOF
         For x = 0 To CBx.ListCount - 1
            If rst!API = CBx.Column(1, x) Then
               Call [purple][b]Button1Name[/b][/purple]_Click
               Call [purple][b]Button2Name[/b][/purple]_Click
               Exit For
            End If
         Next
         
         rst.MoveNext
      Loop
   End If

End Sub[/blue]

Call the sub anyway you like.

Note:The code requires [purple]Microsoft DAO 3.6 Object Library[/purple] to run. To [blue]check/install[/blue] the library, in any code window click [blue]Tools[/blue] - [blue]References...[/blue] In the listing find the library and [blue]make sure its checked.[/blue] Then using the up arrow, [purple]push it up as high in priority as it will go[/purple]. Click OK.

[purple]Thats it . . . give it a whirl and let me know . . .[/purple]

See Ya! . . . . . .

 

[sterlecki]

Many thanks to greely and TheAceMan1

Aceman you hit it right on the money what I was looking to do a star for you.

 

[sterlecki]

TheAceMan1 A little more help if you please? The behaviour of the code below is as follows:

In the table tempAPI_List there are 3 records
The results are that after finding the first match I get 3 printouts of that match.

The desired results would be to have 1 printout each of 3 different matches.

Private Sub cmdPrintAPIList_Click()


Dim db As DAO.Database, rst As DAO.Recordset
Dim CBx As ComboBox, x As Integer
Forms![frmmstrHeader].SetFocus
Set db = CurrentDb
Set CBx = Forms![frmmstrHeader]![cboChooseWell]
Set rst = db.OpenRecordset("tempAPI_List", dbOpenDynaset)


If Not rst.BOF Then
Do While Not rst.EOF
For x = 0 To CBx.ListCount - 1
If rst!API = CBx.Column(1, x) Then
Call cmdPrint_Click
Exit For
End If
Next

rst.MoveNext
Loop
End If

End Sub

It's soooo! close

 

[TheAceMan1]

[blue]The results are that after finding the first match I get [purple]3 printouts of that match.[/purple][/blue]

This is indicitive of more than three records in [blue]tempAPI_List[/blue]. Are you sure there's only 3 records in the table? . . . . (actually look at the table)

There may be a problem with the [blue]Column Number[/blue]. If [blue]API[/blue] is the first column in the combobox:

[blue][purple]Change:[/purple]
    CBx.Column(1, x)
[purple]To:[/purple]
    CBx.Column([purple][b]0[/b][/purple], x)[/blue]

If the wrong column was used, I'd expect nothing to print.

The code can only execute one print per record if an API match is found in the combobox.

If your still having problems post the code in the print button!

See Ya! . . . . . .

 

[sterlecki]

This is the code in my module:
Private Sub cmdPrintAPIList_Click()


Dim db As DAO.Database, rst As DAO.Recordset
Dim CBx As ComboBox, x As Integer
Forms![frmmstrHeader].SetFocus
Set db = CurrentDb
Set CBx = Forms![frmmstrHeader]![cboChooseWell]
Set rst = db.OpenRecordset("tempAPI_List", dbOpenDynaset)


If Not rst.BOF Then
Do While Not rst.EOF
For x = 0 To CBx.ListCount - 1
If rst!API = CBx.Column(0, x) Then
Call cmdPrint_Click
Exit For
End If
Next

rst.MoveNext
Loop
End If

End Sub

You will note that I did have to change the CBx.column(0,x)

The following is the print command code

Private Sub cmdPrint_Click()
On Error GoTo Err_cmdPrint_Click

Dim stDocName As String
Dim intval As Integer
stDocName = "rptHeader"

DoCmd.OpenReport stDocName, acViewPreview
intval = MsgBox("Do You Want to Print This Report", vbOKCancel, "Print Form?")
If intval = vbOK Then
DoCmd.PrintOut
DoCmd.Close
Else
DoCmd.Close
Exit Sub
End If

Exit_cmdPrint_Click:
Exit Sub

Err_cmdPrint_Click:
MsgBox Err.Description
Resume Exit_cmdPrint_Click

End Sub

It asks for confirmation with each print.

This is the list of values in tempAPI_List
API
43047318690000
43047328190000
45047359500000

In fact I noticed that it's not matching the value but just asking if I want to print 3 times. Like it's referring to x as the number of records in the tempAPI_List. I tested this by adding a 4th record to that table and it asked me 4 times. It seems that it is not looking for a match in the combobox but rather just looking at the first record and iterating the print command for as many records as there is in the tempAPI_List.

Hope this helps

 

[TheAceMan1]

sterlecki . . . . .

[purple]You've shown the code is working properly.[/purple] Only thing is [blue]the code doesn't tell the Report what API to print! . . . just executes the print button![/blue]

It appears that your [blue]report is normally getting the API from the current record in the form.[/blue] Is this so?

If not, [purple]how is your report determining what API to print?[/purple]

See Ya! . . . . . .

 

[sterlecki]

Nice catch AceMan!
The query behind the report has it's criteria set to [Forms]![frmmstrHeader]![API] in the API field of the query. However the code we're running never really changes/refreshes the record on the form.

The form frmmstrHeader gets it's data from the combobox which uses this code to find the record set.

Private Sub cboChooseWell_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[API] = '" & Me![cboChooseWell] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

So if we can relate the API match to the openReport command as a criteria or argument then it should work right?

I'm guessing this goes in the where condition portion of the DoCmd.OpenReport stDocName, acviewPreview, rst!API = [Forms]![frmmstrHeader]![API]

The stuff I wrote above doesn't work by the way I get the message object required but I think I'm on the right track.

 

[TheAceMan1]

sterlecki . . . . .

Before I give corrective code, is API [blue]in the table[/blue] string or numeric?

See Ya! . . . . . .

 

[sterlecki]

API is string in all tables

 

[TheAceMan1]

OK sterlecki . . . . .

Rock & Roll:
[ol][li]In the [blue]Declarations Section[/blue] of a module in the modules window, copy/paste the following:

[blue]Public hldAPI As String[/blue]

[/li]
[li]In the [blue]same module[/blue], copy/paste the following function:

[blue]Public Function idAPI()
   idAPI = hldAPI
End Function[/blue]

[/li]
[li]Replace the [blue]criteria for API[/blue] in the reports query with the following:

[blue]idAPI()[/blue]

[/li]
[li]In the [blue]Close[/blue] event of the report, copy/paste the following:

[blue]hldAPI = ""[/blue]

[/li]
[li]Replace the code in [blue]Private Sub cboChooseWell_AfterUpdate()[/blue] with the following:

[blue]    Dim rs As DAO.Recordset
    Dim Msg As String, Style As Integer, Title As String
   
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[API] = '" & Me!cboChooseWell & "'"
    
    If rs.NoMatch Then
      Msg = "API:" & Me!cboChooseWell & " Not Found!"
      Style = vbInformation + vbOKOnly
      Title = "Record Not Found Notice! . . ."
      MsgBox Msg, Style, Title
   Else
      Me.Bookmark = rs.Bookmark
      hldAPI = Me!API
   End If
   
   Set rst = Nothing[/blue]

[/li]
[li]Replace the code in [blue]Private Sub cmdPrint_Click()[/blue] with the following:

[blue]   Dim Msg As String, Style As Integer, Title As String, DL As String
   
   DL = vbNewLine & vbNewLine
   
   If hldAPI = "" Then
      Msg = "Can't Print . . . hldAPI Not Set!"
      Style = vbCritical + vbOKOnly
      Title = "No hldAPI Error! . . ."
      MsgBox Msg, Style, Title
   Else
      DoCmd.OpenReport "rptHeader", acViewPreview
      DoEvents
      
      Msg = "Print This Report with API: " & hldAPI & " ?" & DL & _
            "Click 'Yes' to print." & DL & _
            "Click 'No' to abort . . ."
      Style = vbQuestion + vbYesNo
      Title = "Print Report?"
      
      If MsgBox(Msg, Style, Title) = vbYes Then
         DoCmd.PrintOut
      End If
      
      DoCmd.Close
   End If[/blue]

[/li]
[li]Finally, replace the code in [blue]Private Sub cmdPrintAPIList_Click()[/blue] with the following:

[blue]   Dim db As DAO.Database, rst As DAO.Recordset
   Dim CBx As ComboBox, x As Integer
   
   Forms![frmmstrHeader].SetFocus
   Set db = CurrentDb
   Set CBx = Forms![frmmstrHeader]![cboChooseWell]
   Set rst = db.OpenRecordset("tempAPI_List", dbOpenDynaset)
   
   If Not rst.BOF Then
      Do While Not rst.EOF
         For x = 0 To CBx.ListCount - 1
            If rst!API = CBx.Column(0, x) Then
               [purple][b]hldAPI = rst!API[/b][/purple]
               Call cmdPrint_Click
               Exit For
            End If
         Next
         
         rst.MoveNext
      Loop
   End If[/blue]

[/li][/ol]
[blue]Thats it! . . . give it a whirl & let me know . . .[/blue]

See Ya! . . . . . .

 

[sterlecki]

OK AceMan here's what happens. Following instructions and double checking everything seems to be in place as intended.

If I click the print button on my form to print just the record displayed I get the following message:

Msg = "Can't Print . . . hldAPI Not Set!" as you have set up in your code. So I have lost the capability of printing the single displayed record.

If I try to print multiple records from the tempAPI_List I get no error messages but no prints either (ie no response)
So I'm not sure what it's doing.

Ideally I would like my original form to print the displayed record but if the user chooses to populate the tempAPI_List and call up the form "Print a List of Wells" then it would print the records in that list.

I have changed the cmdPrint_Click to be a Public Sub so the code is available to both print buttons.

Thanks again for your help