VBA Code top open a report from a combo box selection

[bearcub]

I'm knew to Access VBA and was wondering if it were possible to open a report based upon a combo box selection. I did little investigating and saw some code snippets but I'm not sure what the entire code would look like.

Also, would this code be usable if I had the DB in Sharepoint?

Thank you for your help,

Michael

 

[welshgasman]

DoCmd.Openreport

https://msdn.microsoft.com/en-us/vba/access-vba/articles/docmd-openreport-method-access

Which event you would put it in is up to you. I'd probably go with a button next to the combo, so select the correct entry then clcik the button, in which case the code would go in the click event for the button.?

HTH

 

[bearcub]

That does help but wow would Access know which item in the list is being selected? Wouldn't they have to be some sort of list index used (like you would do in Excel)?

Michael

 

[welshgasman]

Well yes, it would probably be in the where clause of the command?

WHERE TableID = cboID

Here is some code from one of my forms.
I add the ID of 0 for ALL as that number cannot exist as an autonumber.
If they select other than ALL then I have a valid SubmiterID

HTH

Private Sub cmdOpen_Click()
' Open report for all or a certain submitter
If Me.cboSubmitter = 0 Then
    DoCmd.OpenReport strReport, acViewPreview
Else
    DoCmd.OpenReport strReport, acViewPreview, , "SubmitterID = " & Me.cboSubmitter
End If
End Sub

 

[bearcub]

Thank you, I think I can work with this.

This is assuming that you have the items in a table as opposed to a field list - correct?

Michael

 

[welshgasman]

Yes, I have the items in a table. They are the IDs of the records that I use for the combo.
It could be worked with a field list. At the end of the day you want to compare what is in the report to something that is in the combo.
In my situation I can produce the report for all submitters, or an individual submitter.

If you had a list box, you would loop through it getting the ID of selected index and issue the command for each in turn, or perhaps combine them with an IN clause.?

 

[Micron]

That does help but wow would Access know which item in the list is being selected?

No button required, but it might hold one advantage.
The combo's AfterUpdate event would fire if a combo selection was made. Whatever is the bound column in that combo would be the column value that is passed to the query or code to open the form. It matters not which column is visible, it only matters which is bound. So if column 1 is bound but set to 0" length (thus you don't see it) and "Smith" is in column 2 but visible, if the user has selected Smith the bound column value (perhaps the ID of 1) gets passed.

The advantage of the button is that if the user re-selects the same value from the combo list, the event fires again. The only way I know to trap this is to have a hidden textbox on the form to contain the selected value. If they're the same, don't run the code. There is an OldValue property for a bound combo, but if you stay on the record, it remains the same so it's not useful.