Filtering Form Data based on a Combo Box

[RobTX]

Hi,

I'm trying to filter data that is viewed on a form based on the selection made in a combo box. I'm very new at VB programming and am struggling with this. I know that I need to enter the code in the afterupdate command, but I'm having a hard time getting it to work. I appreciate any suggestions.

Thank you?

 

[jgc31]

Rob

I would suggest that you would need to create a new form that was based on a recordset created using the input from the combo box. There are a number of ways of doing this check round this site and others for examples which could then be put into the afterupdate event for the combo box. I would presume that you would populate that combobox with a list of values from a field in the relavent table

The example below is one possible approach ( adapted from a recent project) assume combo box is called Cmbtext. and related field text in tbltext

Dim db As DAO.Database, rcd As DAO.Recordset
Dim Text As String
Dim Statement As String
Dim statement2 As String
Dim Number As Integer

If Not IsNull(Me!CmbText) Then

Text = Me!CmbText

Statement = "WHERE (((tblText.Text)=""" & Text & """" &"));"
Else

Exit Sub

End If


statement2 = "SELECT * FROM tbltext " & Statement

Set db = CurrentDb
Set rcd = db.OpenRecordset(statement2)

If rcd.RecordCount = 0 Then

MsgBox "No members match selection, please try again with different criteria"
Exit Sub
End If


DoCmd.OpenForm "FrmViewDetailsRelatedToText", , , "[Text]=""" & Text & """"


DoCmd.Close acForm, Me.Name

End Sub

 

[Rich]

Use the combo box Wizard, it will create the code for you.
Just select find a record on my form from the list of available options