Filtering forms

[Hello1]

Hello,

I have a main form which contains few buttons which lead to other forms. The first button leads to another form which contains also few buttons to other forms, the first one leads to employees form, the other one to payoffs and so on.

I want to add a combo box to the main form which will contain company names and based on the company I select I want the other forms to show only the employees which work for that particular company, payoffs of that company and so on, except some forms which have same data for all companies.



Can you point me to some tutorials if this is even possible to make, and is this enuff information I gave or do I need to write it in more details?

Thanks

[June7]

So what do you mean by 'leads to' - are you running DoCmd.OpenForm?

Can use the WHERE CONDITION argument of DoCmd.OpenForm command to open form to a filtered recordset.

[davegri]

If you're using VBA, do this: For the forms that you want to restrict to employees of a particular company, the button would call the form with docmd.openform "frmTarget",,,,,,Openargs. The openargs would contain the companyId. When the target form opens, its On_Open event checks the openargs, and if present, filters the form for only that company. If openargs not present, then no filter and all data present.

[Hello1]

The following example opens the frmMainEmployees form in Form view and displays only records that apply to the department chosen in the cboDept combo box. The displayed records can be edited, and new records can be added.Sample code provided by: Bill Jelen, MrExcel.com

VBCopy
Private Sub cmdFilter_Click()
    DoCmd.OpenForm "frmMainEmployees", , , "DepartmentID=" & cboDept.Value EndSub

Guess I could use this? Just how do I select the main form which contains the combobox? VBA is still foreign for me

VBCopy
Private Sub cmdFilter_Click()
    DoCmd.OpenForm "frmEmployees", , , "CompanyID=" & "AllForms!frmMain" Me. cmb.cboCompanyID.Value EndSub

Any close? lol
And thanks for the help, would search forever

[June7]

The combobox is on frmMainEmployees? This code is behind one of the other forms?

DoCmd.OpenForm "frmEmployees", , , "CompanyID=" & Forms!frmMainEmployees.cboCompanyID

[davegri]

You haven't told us the name of your combobox, the name of the forms, the name of the button, the name of the field to filter on, or other details that makes specific advice possible.
It might be best if you submit your DB for the relevant code to be added.

[Hello1]

Its 2 times behind. frmMain -> frmOtherThings -> frmEmployees

The combobox contains CompanyID (PK) and Name from the table Company which is linked to employees table to the CompanyID (FK)

EDIT: Sorry I saw the last post after posting this one. I will try to write it more detailed now.

[Hello1]

Ok, in the main form "frmMain" I have a combobox "cboCompanyID". It contains the "CompanyID" and "CompanyName" which is set to column count: 2 and bound colum: 1 so it shows the company name on the drop down list. Now, when I select a company from that list, I want the code to show me only the records of employees which work for that company.
The employees are in the "frmEmployees" and to access that form I go from the "frmMain" to a form called "frmOtherStuff" and then on that form I click on the Employees button "cmdEmployees" and enter the "frmEmployees". In that form I have some employees info including the (FK) CompanyID.
Hope I was a bit more clear this time

[davegri]

OK. The complication is that there is another form between the frmMain and frmEmployees, being frmOtherStuff. The company combobox is on frmMain. Then a button on frmMain loads frmOtherstuff, then a button on frmOtherStuff loads frmEmployees which needs to see the cboCompanyID combobox on frmMain.
So the button on frmOtherStuff needs:
DoCmd.OpenForm "frmEmployees", , , "CompanyID=" & Forms!frmMain.cboCompanyID
pretty much as June7 said.

[Hello1]

Now I get "enter parameter value" and under it the CompanyID. I have to add that I added the companyID in the frmEmployees additionally as I did the combobox on the main form. In the combobox row source: SELECT Company.CompanyID, Company.Name FROM Company;
The companyID in the frmEmployees I added ith the option "Add existing fields"
Do you happen to have a link for a small database example? :/

[davegri]

CompanyID must be in the recordsource for frmEmployees. If not present, then your table and relationship structure is wrong. This is getting complicated. Need your DB posted.

[Hello1]

Its in another language, I made a small similar one. I get the same error, hopefully it can help. Thanks again

[davegri]

Didn't realize companyid was text. So the text field has to be delimited:

DoCmd.OpenForm "frmEmployees", , , "CompanyID='" & Forms!frmMain.cboCompanyID & "'"

[Hello1]

Now it works, thanks!
Maybe the moderator could change the thread title to a more fitting one, might be helpful to others.

[June7]

If you can still edit your original post, you should be able to change the title. Click 'Edit Post' then 'Go Advanced' below the Quick Reply edit window.