Combo Box as filter

[SueBK]

I have a form (StartForm01) with a combo box (ComboBox01), which lists dozens of projects. I want to choose a project, and have another form (frmMasterProject) open with that project showing.

I have managed to get the frmMasterProject to open, but not to filter to the select project.

I am a very beginner working on a very large database (and trying not to break anything), and I'm using Access 2003.

 

[boblarson]

What have you used so far to open the second form?

Normally you pass a where clause to it like:

docmd.OpenForm "frmMasterProject",acNormal,,"[YourFieldNameInfrmMasterProject]=" & Me.ComboBox01

But it Yourfield is a text field instead of number you would need:

docmd.OpenForm "frmMasterProject",acNormal,,"[YourFieldNameInfrmMasterProject]=" & Chr(34) & Me.ComboBox01 & Chr(34)

 

[boblarson]

Oh, and by the way...

 

[SueBK]

Thank you for the welcome (I think) I'm not sure about being chucked in the deep end of databasing, but if my head doesn't explode, all will be well I'm sure.

I'm not having a lot of luck with this code. I have just realised that there are other issues, which may be having an effect. (The hassle of coming into a project part way through.)

My combo box shows "Project #" but actually points to "ProjectID" (ProjectID being an autonumber field; Project # being a recognisable string of words). So, I was trying to use the text coding you supplied unnessarily. One issue removed

My current code is
DoCmd.OpenForm "frmProjectMaster", , , "[ProjectIDinfrmProjectMaster] = " & Me.Combo121

And I'm getting an error message "Run Time Error 3075: Syntax error (missing operator) in query expression '[ProjectIDinfrmProjectMaster]='

Really appreciate your help

 

[SueBK]

Realised that when you wrote "YourFieldNameInfrmMasterProject" I'm supposed to just include the field name NOT the "field name in form name" DUH. HOWEVER, even though I have fixed this the code still isn't working. Code is now:

DoCmd.OpenForm "frmProjectMaster", , , "[ProjectID] = " & Me.Combo121

I'm getting the same error message. <sigh>

 

[rapsr59]

Hi!

What is the row source for the combo box Combo121?

If your combo box has more than one column, which is the design of many combo boxes, then there is a possibility that by referencing it as “Me.Combo121” you may not be picking up the column value that is needed for the OpenForm Method’s WhereCondition argument.

Example:

Say your combo box Row Source is…
SELECT EmployeName, EmployeeID FROM Employees;

… and the settings are…

Column Count: 2
Column Widths: 2”;0”
Bound Column: 1

… then the following OpenForm command would not open the employee form at the required employee because the WhereCondition arument is referencing the EmployeeName, which is not the primary key, in the combo box.

DoCmd.OpenForm "frmEmployees", , , "[EmployeeID = “ & Me.Combo121

By changing the OpenForm Method to:

DoCmd.OpenForm " frmEmployees ", , , "[EmployeeID = “ & Me.Combo121.Column(1)

Or Changing…

The Bound Column to…

Bound Column: 2

Then it will open the form at the required employee.

This MIGHT be why you are having problems in opening your form.


Best of luck


Richard

 

[WIS]

Realised that when you wrote "YourFieldNameInfrmMasterProject" I'm supposed to just include the field name NOT the "field name in form name" DUH. HOWEVER, even though I have fixed this the code still isn't working. Code is now:

DoCmd.OpenForm "frmProjectMaster", , , "[ProjectID] = " & Me.Combo121

I'm getting the same error message. <sigh>

Should it be:

Me.ComboBox01 instead of Me.Combo121 - in the 1st post you called the cbo ComboBox01. Have you changed the name to Combo121?