Filtering a Combo box on a form

[jilppe]

I am having trouble setting up a filter on my form. I have two tables that are linked through a text field (contact_id). This field is a combination of two other text fields (first_name and last_name).

When information is entered into my forms it is entered based on the two firled first_name and last_name. (which are already entered). It would be nice if second field on the form (first_name) was filtered by the selection made in last_name.

Currently I have createda form (Form2) and the two combo boxes are cboLName and cboFName. The name of the table that containts this information in tblContact_ID and the three text fields in the table are Contact_ID, first_name, and last_name.

cboLName has last_name as its rowsource
cboFName has first_name and last_name as its rowsource

cboLName has this AfterUpdate events proceedure:

Private Sub cboLName_AfterUpdate()
Dim lName As String

lName = "SELECT [tblContact_ID].[first_name], [tblContact_ID].[last_name] " & _
"FROM tblContact_ID " & _
"WHERE [last_name] = " & Me.cboLName.Value
Me.cboFName.RowSource = fName
Me.cboFName.Requery
End Sub

However this does not work, if I select the last name on the form then I get a select statement error. Could anyone please help me.


Thanks
Jackie

[MMcCarthy]

Try this:

Dim str as String
 
str = "SELECT [first_name], [last_name] FROM tblContact_ID" & _
" WHERE [last_name] = '" & Me.cboLName.Value & "'"
 
Me.cboFName.RowSourceType = "Table/Query"
Me.cboFName.RowSource = str
Me.cboFName.ColumnCount = 2
Me.cboFName.Requery
End Sub
 

You need to put single quotation marks on either side of Me.cboLName.Value because it's a string

I am having trouble setting up a filter on my form. I have two tables that are linked through a text field (contact_id). This field is a combination of two other text fields (first_name and last_name).

When information is entered into my forms it is entered based on the two firled first_name and last_name. (which are already entered). It would be nice if second field on the form (first_name) was filtered by the selection made in last_name.

Currently I have createda form (Form2) and the two combo boxes are cboLName and cboFName. The name of the table that containts this information in tblContact_ID and the three text fields in the table are Contact_ID, first_name, and last_name.

cboLName has last_name as its rowsource
cboFName has first_name and last_name as its rowsource

cboLName has this AfterUpdate events proceedure:

Private Sub cboLName_AfterUpdate()
Dim lName As String

lName = "SELECT [tblContact_ID].[first_name], [tblContact_ID].[last_name] " & _
"FROM tblContact_ID " & _
"WHERE [last_name] = " & Me.cboLName.Value
Me.cboFName.RowSource = fName
Me.cboFName.Requery
End Sub

However this does not work, if I select the last name on the form then I get a select statement error. Could anyone please help me.


Thanks
Jackie

[jilppe]

Thank you very much I was so frustrated with that. It works perfectly

[jilppe]

If I want to filter another field based on both last_name and first_name (the field is incident_date) can I just add an AND clause to the WHERE statement and include both conditions?

[MMcCarthy]

str = "SELECT [incident_date] FROM tblContact_ID" & _
" WHERE [last_name] = '" & Me.cboLName.Value & "' AND [first_name]='" & Me.cboFName.Value & "'"

If I want to filter another field based on both last_name and first_name (the field is incident_date) can I just add an AND clause to the WHERE statement and include both conditions?

[jilppe]

For some reason it is not working. I have modified the text to match the tables

Private Sub cboFName_AfterUpdate()
Dim str As String
str = "SELECT [incident_date] FROM tblAccident_Illness" & _
" WHERE [last_name] = '" & Me.cboLName.Value & "' AND [first_name]='" & Me.cboFName.Value & "'"

Me.cboIDate.RowSourceType = "Table/Query"
Me.cboIDate.RowSource = str
Me.cboIDate.ColumnCount = 2
Me.cboIDate.Requery
End Sub

I am getting an error on all the .RowSource statements.

Ultimately what I am trying to do is to create a form of the three items where each box filters off the next and then have a command button that will open a report that will use the criteria selected in the three boxes.

[MMcCarthy]

str = "SELECT [incident_date] FROM tblAccident_Illness" & _
" WHERE [last_name] = '" & Me.cboLName.Value & "' AND [first_name]='" & Me.cboFName.Value & "'"

Are last_name and first_name fields in the tblAccident_Illness table, if not what is the relationship between the two tables.

For some reason it is not working. I have modified the text to match the tables

Private Sub cboFName_AfterUpdate()
Dim str As String
str = "SELECT [incident_date] FROM tblAccident_Illness" & _
" WHERE [last_name] = '" & Me.cboLName.Value & "' AND [first_name]='" & Me.cboFName.Value & "'"

Me.cboIDate.RowSourceType = "Table/Query"
Me.cboIDate.RowSource = str
Me.cboIDate.ColumnCount = 2
Me.cboIDate.Requery
End Sub

I am getting an error on all the .RowSource statements.

Ultimately what I am trying to do is to create a form of the three items where each box filters off the next and then have a command button that will open a report that will use the criteria selected in the three boxes.

[jilppe]

last_name and first_name are in tblAccident_Illness.

Also how do I get the information on the form to be the record selected for a report that I already have? The report is based on a query called

Accident_report

The fields last_name first_name and incident_date are also available in the query. Should the selection form use these fields and then I could put a command button to run or print the report. Will I need to specify that I want the selected record to be the only one in the report of will that be the default if I use the query that runs the report to generate the form?


Also I have a report that uses a crosstab query called bodycrosstab. This cross tab counts the number of incidents for each body part for each month. The report and query are working fine but I need a way that each time the report is run it asks which year should be used (and then filters the data in the report for that year. This would also need to be made availabile in the header of the report so if I selected 2005 then the header of the report would be Body Parts 2005.

Thanks for your help. After these two things I will be done.

[jilppe]

I was able to solve the problem with the report and the form acting as a filter. The only question I still have is the one below

Also I have a report that uses a crosstab query called bodycrosstab. This cross tab counts the number of incidents for each body part for each month. The report and query are working fine but I need a way that each time the report is run it asks which year should be used (and then filters the data in the report for that year. This would also need to be made availabile in the header of the report so if I selected 2005 then the header of the report would be Body Parts 2005.

[MMcCarthy]

It depends where you are opening the report from. I would suggest that if you are using a command button on a form you add a combobox (cboYear) above it with a list of Years (You could just design a lookup table or set a query to get the year field from the query grouped on year field).

Ask the user to select a year before running the report and in the On Click event of the button (cmdPrint) put the following code.

Private Sub cmdPrint_OnClick()
Dim stDocName As String
Dim stLinkCriteria As String
 
If IsNull(Me.cboYear) Then
Msgbox "You must select a year", vbOkOnly
Exit Sub
Else
stDocName = "Name of Crosstab Query Report"
stLinkCriteria = "[Year]=" & Me.cboYear
DoCmd.OpenReport stDocName, , , stLinkCriteria
End Sub
 

Put a textbox control on the Report next to the Header Label and set the source to

=[Forms]![FormName]![cboYear]

I was able to solve the problem with the report and the form acting as a filter. The only question I still have is the one below

Also I have a report that uses a crosstab query called bodycrosstab. This cross tab counts the number of incidents for each body part for each month. The report and query are working fine but I need a way that each time the report is run it asks which year should be used (and then filters the data in the report for that year. This would also need to be made availabile in the header of the report so if I selected 2005 then the header of the report would be Body Parts 2005.