OnOpen form event Filter a Combo Box in Subform (1 Viewer)

[wattsaj]

On clicking a button to open a form I want to filter a list in a combo Box located on the forms subform

The main form is called 'Frm_orders'
The sub form is called 'sub'
The combo box field is called 'CostCodeID'
and I want to filter column 0 of the combobox which is Tbl_InvoiceCostCodes.Type to show records with a type of 'Repairs'

 

[aleb]

In the properties of your combo box select the row source type - Table/Query
In the row source paste the following
SELECT Tbl_InvoiceCostCodes.CostCodeID, Tbl_InvoiceCostCodes.CosCodeType FROM Tbl_InvoiceCostCodes WHERE (((Tbl_InvoiceCostCodes.CostcodeType)="repairs"));

Column count = 1

This is it ... if I am not mistaken in the design of your tables.

 

[wattsaj]

Thanks, however I wnat to use the same form and subform again but apply a different filter if a different button is selected on the menu form, so I dont want to put the code in the Row source of the combo box as it can change. I believe I therefore need the code on the event procedure to open thte form?

 

[SueKorrel]

I realise this is a very old query, but it is exactly my issue.
I have a form "frmChecklists" that is opened by command buttons that apply the relevant query - qryGroup 1, qryGroup 2, qryGroup 3 etc. On my form I have a combo box based on ContactID which goes to the record that is selected.
How do I set a query or filter against my combo box when the form opens. For example: I click "Group 1" button, the form opens filtered by qryGroup1. I want the combo box to only shows contacts in Group 1. I click Group 2, the form is filtered by qryGroup2 and the combo box only shows Group 2 contacts. etc.

 

[theDBguy]

What is in the code behind your buttons? Maybe you can modify it to add the filter for the combo.

 

[SueKorrel]

What is in the code behind your buttons? Maybe you can modify it to add the filter for the combo.

I have an embedded macro on each button "Open Form, filtered by qryGroup1/2/3/etc." . I've only just returned to Access after about 15 years of avoiding it, so I have not waded back into VBA.

 

[Pat Hartman]

The Where clause of the combo can reference a hidden, unbound control:

Where SomeField = Forms!frmMain!txtHidden

Then the click event of the button can copy some value to the unbound control and requery the subform.
Me.txtHidden = "whatever"
Me.sfrm.Requery

If you have more than a couple options or you envision the list growing, use an unbound combo rather than buttons and reference the combo in the RowSource of the combo. That way you only need the requery command in the click event of the combo.

I don't recommend using macros at all. This is a very simple solution requiring only one or two lines of code and a where clause in the combo's RowSource. Easy enough to ease you into code.

 

[SueKorrel]

Thanks for your replies. At least I know I'm thinking in the right direction. It's been 15 years or more since I wrote any serious VBA, so I'm all but starting from scratch. My 'go-to' in the past has been to download template and pull them apart, but I'm not finding too many useful examples. If you could point in the direction of basic coding language and protocols that would be great. Then I can muddle through on my own without asking anyone to do the work for me.

 

[arnelgp]

do you need some reading materials (VBA perhaps)
Free Programming Books – GoalKicker.com

 

[Pat Hartman]

I'll decode what I wrote:

The Where clause of the combo can reference a hidden, unbound control:

Where SomeField = Forms!frmMain!txtHidden

The Combo has a RowSource property which can be a table or a query. To use a WHERE clause, you would need to create a query.
Controls on forms can be bound or unbound. Bound controls have a ControlSource that isconnected to a field in the Form/Report's RecordSource because they contain the name of one of the fields. Unbound controls either have no ControlSource or the ControlSource starts with "=" and is a "calculation". Since you are using the combo as a filter and are not saving the result, you would be using an unbound control - i.e. the ControlSource will be empty.

"SomeField" = the name of field you want to filter on.
"Forms!" = literal, "frmMain" = the name of your form, "!" = literal, "txtHidden" = the Name property of the hidden control that will hold the value selected by the combo.

Then the click event of the button can copy some value to the unbound control and requery the subform.
Me.txtHidden = "whatever"
Me.sfrm.Requery

Controls and Forms have Events. These are hooks where you can place code that will be executed when the Event happens. Your button control has a Click event. When you press the button, the Click event runs and that will cause your code to run.
"Me." = Literal - references the form's object model so that Access knows where the variable is defined. "Me." gives you intellisense so you can see what properties and Methods are available. " = " = the relational operator used to set a value. "'whatever'" = some text value enclosed in quotes.

We can go into the preferred option (using a combo rather than a bunch of buttons to provide a value) another time.

 

[SueKorrel]

So! After much phaffing about and swearing (mainly because I was opening the wrong form while testing - DUH!) I've got things working how I want.
On my 'switchboard', the user chooses an employment status from a combobox. A button saves their choice to openargs and opens the checklist form.
On the checklist form, onload uses openargs to assign a query to the form and to a combobox, which lists the contacts by name.
Thanks for your input. Looking forward to handing this thing over to an actual programmer so I can just focus on the quality of the data and not the database.