using combo boxes and option groups to pass criteria to a report

Hello everybody !

I have a table with student's data with columns: name, gender, nationality, city.
I've created form with combo boxes for nationality and city and option group (male/female).
I'd like to be able to pass all choosen criterias from this form to a report (of course I've got query with these fields).
How should it be done ? (as for now I can do it only with one combo-box).

Thanx for any help.

Martinezzz

Hi Martinezzz

The best recommendation I can offer is to use a QBF (query by form) method. This involves making a form with all the controls you want your user to select by and placing them in unbound controls much like you have already done. In your form add a command button and give it a suitable name, like cmdSearch.

Open your code window from design view of your form (Ctl G) and at the top add Option Explicit directly below the Option Compare Database (Declarations area). Below this statement make a Function code similar to below:

Private Function BuildSQLString (strSQL As String) As Boolean
Dim strSelect As String, strFrom As String, strWhere As String

strSelect = "*"
strFrom = "NameOfTable" 'This is the name of the table that is in your query

If IsNull(comboboxNationality) Then 'Insert the name of your combobox between the ()
strWhere = strWhere
Else
strWhere = strWhere & " And Nationality = '" & comboboxNationality & "'"
End If

If IsNull(comboboxCity) Then
strWhere = strWhere
Else
strWhere = strWhere & " And City = '" & comboboxCity & "'"
End If

Do the same for each additional comboboxes on your form.

Your option group will not pass a text value of (male/female), so assuming you have the value of 1 for the male and 2 for the female use something like this:

Select Case OptionGroupMaleFemale 'Use the name of your option group
Case 1
strWhere = strWhere & " And Gender = ""Male"""
Case 2
strWhere = strWhere & " And Gender = ""Female"""
End Select

strSQL = "SELECT " & strSelect
strSQL = strSQL & "FROM " & strFrom
If strWHERE <> "" Then strSQL = strSQL & " WHERE " & Mid$(strWHERE, 6)
BuildSQLString = True

End Function
*Make note of where there are spaces before and after the quote marks
This function will return either True or False and will pass the SQL statement to your query if True.

In the OnClick event of the command button add the following similar code

Private Sub cmdSearch_Click()
On Error GoTo Err_Handler

Dim strRptName As String

strRptName = "NameOfYourReport"

If Not BuildSQLString(strSQL) Then
MsgBox "There was a problem building the search string"
Exit Sub
End If
CurrentDb.QueryDefs("NameOfYourQuery").SQL = strSQL 'Use the name of the query not the table
RefreshDatabaseWindow
DoCmd.OpenReport strRptName,acViewPreview
Exit_Handler:
Exit Sub
Err_Handler:
Msgbox Err.Description & Err.Number
Resume Exit_Handler
End Sub

Set your report's record source to this query.

If you are using more than one table in your query, you will have to modify the above Function code to include the additional tables to something like this:

strSelect = "Table1.*,Table2.*,*"
strFrom = "Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID"

This should get you started, if you have any problems post back with details and I will help you.

lwells

Microsoft has an example of this feature that I used at:

http://support.microsoft.com/kb/q208529/

It explains everything, and also has a link to the sample database "RptSmp00.mdb" along with other examples on how use reports.