Form Combo Box Filtering

[aceoftrades]

Newbie question: I have a form frmSubcontractor (tblSubcontractor) with a combo box cboSpeciality which is populated by tblSpeciality (Speciality_ID, Speciality). I need the form to only show those records based on the combo box. Combo box is showing Specialties but not filtering the form. Thanks.

[ItsMe]

In the combobox's after update event you can reassign the recordsource of the form.

something like...
me.recordsource = "SELECT * FROM tblSubcontractor WHERE [PrimaryKey] = " & Me.cboSpeciality.Column(0)

[aceoftrades]

Ok...I tried this and must be doing something wrong:

error msg:
The record source 'SELECT' * FROM tblSubcontractor WHERE [PrimaryKey] = 1' specified on this form or report does not exist.

the =1 changes based on what I select in the combo box.

code:
Private Sub cboSpeciality_AfterUpdate ()
me.recordsource = "SELECT * FROM tblSubcontractor WHERE [PrimaryKey]= " & Me.cboSpeciality.Column(0)
EndSub

[ssanfu]

Just to be sure I understand, you have a form "frmSubcontractor". You want to be able to select a specific Specialty from a combo box and display only those subcontractors that have that specialty.

The form record source should have a field for "Speciality_ID" (I always have a query for the form record source).
In the form header, add the combo box.

For the combo box, set:
Column count: 2
Column Widths: 0;1
In the after update event of the combo box, enter the code:

      Me.Filter = "[SpecialityID] = " & Me.cboSpeciality
      Me.FilterOn = True

Change SpecialityID to your name for the field in the form for the specialty ID.


To see all specialties (remove filter), add a button in the header.
In the click event of the button, add the code:

   Me.Filter = ""
   Me.FilterOn = False

[ItsMe]

aceoftrades

You need to adjust the code I provided with the field you want to link to the combo. Change PrimaryKey to the name of the field that you want to match with your combo. Also, you need to select the correct column from your combo.

Me.cboSpeciality.Column(0) 'represents the first column
Me.cboSpeciality.Column(1) 'represents the second column
Me.cboSpeciality.Column(3) 'represents the third column

Since you are getting a number like 1 in your error message I am going to guess the first column is an Key value of integer or long integer type. This is fine, now select the correct field name from table tblSubcontractor to replace PrimaryKey

[aceoftrades]

Ok..I'm very confused..This is what I have:
tblSubcontractor - Subcontractor_ID, LastName, FirstName, Speciality (Data Type - Number)
tblSubcontractor_Speciality - Speciality_ID, Speciality (Data Type - Short Text)
qrySubcontractor - All fields from tblSubcontractor
frmSubcontractor - Default View - Single Form; Record Source - qrySubcontractor; cboSubcontractor - Row Source - SELECT [tblSubcontractor_Speciality].[Speciality_ID],[tblSubcontractor_Speciality].[Speciality] FROM [tblSubcontractor_Speciality] ORDER BY [Speciality]; / Bound Column - 1 / Column Count -2 / Column Width 0";2"
I have another combo box within the form for assigning Speciality to Subcontractor and this works perfect. The After Update is the code from the earlier reply (can't cut and paste here for some reason) I hope this explains things better...thanks for all the help.

[ItsMe]

What is it that you want to happen that is not happening?

[orange]

ace,

Can you post a jpg of your tables and relationships in zip format?

[aceoftrades]

http://www.mastercraft-kb.com/Relationships.html
Here is a link to my relationships...
Thanks for all the help but I've given up and decided to go a different route. I'm going to have the cboSpeciality combo box filter the cboSubcontractor combo box based on the selection. Think this will work better anyhow. Reading up on how to do this so any advise would be appreciated. Thanks again.

[ItsMe]

Just like post #2 gives an example for the form's recordsource you can use similar code to adjust the property of your comboboxes RowSource.

Me.cboSubcontractor.RowSource = strSQL

[aceoftrades]

worked great thanks..