Getting input from a user to DoCmd.openreport 1

[Genengineer]

I tried to set up a form where the user could select Country and Event from combo boxes, and then pass the results to a DoCmd.Openreport.

I tried all sorts of combinations of formatting and kept getting data mismatches or the openreport would not recognize the input, so I gave up and just used the “input” function - that is get access to ask for input for variables it did not recognize as follows.

This works well, but it requires me to put a list of countries on the form so the user types the correct ones in, rather than using the combobox with a “select distinctrow” to show the valid values.

How do I get the combo box entries to transfer to the openreport function? Both Country and Event have text attributes.

Private Sub Runquery_Click()
On Error GoTo Err_Runquery_Click

Dim WClause
Const Rview = "acViewPreview"
Const SView = "SelectRelations"
Const RName = "RegAgreeCountryEvent"
     WClause = "[SelectRelations].[Country] =  [Enter Country Here]" & _
     " AND [SelectRelations].[Event] = [Enter Event Number Here] "
    
    DoCmd.OpenReport _
        Reportname:=RName, _
        View:=acViewPreview, _
        Filtername:=SView, _
        WhereCondition:=WClause
    

' "[Type] = '" & [ProductType] & "'"

Exit_Runquery_Click:
    Exit Sub

Err_Runquery_Click:
    MsgBox Err.Description
    Resume Exit_Runquery_Click
    
End Sub

Many thanks for any help I can get.

Ray Shillito

 

[beetee]

I think all you need to do is add something like:

dim WClause as string
WClause = "[SelectRelations].[Country] = " & Me.CountryCombo & " AND [SelectRelations].[Event] = [Enter Event Number Here] "

 

[Genengineer]

Thanks for replying. I tried this and got a "data type mismatch in criteria selection"

This happens whether I dim Wclause as a string or without the data type.

The combo boxes do select from different tables, but the data types are all text and of the same length - unless the combo boxes are showing the program the key field column (which is long integer) instead of the data that actually shows in the form - might that explain it??
If I don't include the key in the combo box it does not work.

the rowsource for the CountryCombo box is
SELECT DISTINCTROW [tblCountry].[CountryID], [tblCountry].[Country] FROM [tblCountry] ORDER BY [Country];
and the column count is 2 and the bound column is 1. the first column has zero width.

Private Sub Runquery_Click()
On Error GoTo Err_Runquery_Click

Dim WClause As String
Const Rview = "acViewPreview"
Const SView = "SelectRelations"
Const RName = "RegAgreeCountryEvent"
     WClause = "[SelectRelations].[Country] =  " _
     & Me.CountryCombo & _
     " AND [SelectRelations].[Event] = " & Me.EventCombo
        
    DoCmd.OpenReport _
        Reportname:=RName, _
        View:=acViewPreview, _
        Filtername:=SView, _
        WhereCondition:=WClause
    
Exit_Runquery_Click:
    Exit Sub

Err_Runquery_Click:
    MsgBox Err.Description
    Resume Exit_Runquery_Click
    
End Sub

Thanks,

Ray

 

[beetee]

My mistake, I forgot to include quotes:

dim WClause as string
WClause = "[SelectRelations].[Country] = '" & Me.CountryCombo & "' AND [SelectRelations].[Event] = [Enter Event Number Here] "

 

[Genengineer]

I wrote the thing as...

WClause = "[SelectRelations].[Country] = '" & Me.CountryCombo & _
"' AND [SelectRelations].[Event] = '" & Me.EventCombo & "'"

And it does not fail, but it returns zero records - i will have to figure that out myself!

It was correct bracketing and " and ' that drove me crazy in the first place!

Thanks,

Ray

 

[Genengineer]

OK - I found out what was causing the missing records.

The Combo Box had the Bound column set to 1 when it should have been set to 2 (the column with the data!).

So now it works really well. I can use this all over the place.

I am posting the ful code here for anyone else to use. the two Combo boxes are called Countrycombo and Eventcombo

Private Sub Runquery_Click()
On Error GoTo Err_Runquery_Click

' set up constants and variables
Dim WClause As String
Const Rview = "acViewPreview"
Const SView = "SelectRelations"  ' this is a query
' I am using a query to combine results 
' from two linked tables
Const RName = "RegAgreeCountryEvent"  ' this is a report
     
' get the parameters from the combo boxes

     WClause = "[SelectRelations].[Country] = '" & Me.CountryCombo & _
     "' AND [SelectRelations].[Event] = '" & Me.EventCombo & "'"
    
' Open the report in preview mode

    DoCmd.OpenReport _
        Reportname:=RName, _
        View:=acViewPreview, _
        Filtername:=SView, _
        WhereCondition:=WClause
    
Exit_Runquery_Click:
    Exit Sub

Err_Runquery_Click:
    MsgBox Err.Description
    Resume Exit_Runquery_Click
    
End Sub

I need to add error checking for empty fields etc. and reports with no records, and the docmd.maximize in the form_load() seems to be delinquent, so I may be back!

many thanks!

Ray

 

[beetee]

This site is great, one learns many things; I'd never used the 'where' clause in report opening before. Thanks for the code!

Oh, FYI, there's a no data event for reports you can use for the no records case.