OnOpen form event Filter a Combo Box in Subform

[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.

 

[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

 

[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.