Set up ComboBox to select record from a forms recordset

[mmaule]

How can I set up a ComboBox to select a record from its form's recordset?

My form has selectable filter options.

When I use the form's Record Source as Row Source, all records are available instead or only the filtered recordset.

Selecting a record, not in the form's filtered recordset, fails to update the form.

[ranman256]

just make query of the forms query. and put it in the combo rowsource
usu distinct

[mmaule]

Thank you, ranman256

I have not been successful, making a query of the forms query. can you provide a good example?

[orange]

Can you describe what you are trying to do in plain English for more clarity?
Your form has a recordsource. What exactly is the purpose of the combobox?

[mmaule]

The form's Record Source is a table "StatusUnits" about 3200 records
Which includes fields Class, System & StatusName.

The form has ComboBoxs to select Class and System filter options.

Class - A, B, Both
System - 1, 2, 3, (All)

A ComboBoxs to select StatusName w/ default set to Form!StatusName and Row Source of Select ID, StatusName From StatusUnits;
When I select the dropdown for StatusName, all records are available instead or only the filtered recordset, i.e. StatusNames for only System 1 Class A (Class <> B).

I am having trouble setting the Select Status Name ComboBox's Row Source to the forms filtered recordset.

[orange]

For clarity, you have a form recordsource of 3200 records.
You have 2 comboboxes,
1 for StatusName and 1 for Class.

What do you expect the user to do?
Select a Class or Select a StatusName?
How are the tables related?Are there X statuses for a given Class?

'general approach
I would think you would have the rowsource for each combo to be the corresponding table.
Then, after the combos have been selected,
something like the following --untested--

Me.recordsource = "select * from table " _
      & " Where statusname = '" & me.cboStatus & "'  AND class = '" & me.cboClass &"' "  'use selected values
 Me.Requery  'requery to get/use the latest recordsource

But I may be misreading your intent.

[mmaule]

Thank you for your help.

The form's Record Source is a table "StatusUnits" about 3200 records
Which includes fields SU_ID, Class, System & StatusName.

The form has 3 ComboBoxs
2 ComboBoxs for filter options
1 ComboBox o select a record by StatusName.

Class - A, B, BOTH
If Me.SelectClass = "BOTH" ' I don't add the Class value to the filter' I want records available for all 3 classes .
If Me.SelectClass = "B" then Me.Filter = [Class] <> "A" ' I want both "B" and "BOTH" class records values available.
If Me.SelectClass = "A" then Me.Filter = [Class] <> "B" ' I want both "A" and "BOTH" class records values available.

System - 1, 2, 3, (All)
If Me.SelectSystem = "(All)" ' I don't add the System value to the filter, I want all Systems records available.
Else Me.Filter = [System] = "Me.SelectSystem.Value ' I only want records with the entered System value available.
etc.

The form record count varies depending on the filter setting.

It can take a long time to step to the desired Status.

The ComboBox to select StatusName has default value set to Form!SU_ID and Row Source of Select SU_ID, StatusName From StatusUnits;

When I select the dropdown for StatusName, all records are available instead of only the filtered recordset, i.e. StatusNames for only System 1 Class A (Class <> B).

So, Selecting a Status by StatusName may try to display one that is not in Me.Recordset (Form!Recordset), causing confusion and wasting time.

I want to select a StatusName record from the form's filtered recordset.

[orange]

You have confused me.
Normally you make selections from combo(s) and use those to filter the Form's recordsource.
Then requery the form to see the result of the filter.

But you seem to know what you want.

[mmaule]

How do I access the form's filtered record source for the ComboBox's row source?

[mmaule]

I worked it out.

After modifying the filter

Me.Requery
'
Set Me.SelectStatus.Recordset = Me.Recordset ' ---------------
'
Me.SelectStatus.Requery
'
Me.SelectStatus.Value = Me!SU_ID
'

[dwaterman]

This is easily accomplished using the Combo Box Wizard. When it asked "How do you want your combo box to get its values?" select "Find a record on my form based on the value I selected in my combo box."