continuous form combo box filter

[rvmams]

Hi,

I've got a combo box problem...

I have two combo boxes on a continuous form. What I would like to do is to filter the value of my second combo box based on the selected value of my first combo box.
I know how to do this when it's a single form, but for a continuos form.... I've got no clue, as I am pretty much a beginner in access.

I've tried to apply the way for single form to the continuous form, but this does not give me what I need. When I select a value for a record in combo box 1 ALL values for combo box 2 for ALL records are then filtered, while I just need the current record. How can I get this done?

Hopefully the above is clear enough.... can anyone help?

Thnx

RvM

 

[rvmams]

Can anyone help please??????????????????



Thnx,

RvM

 

[WildHare]

Single or continuous form should make no difference.

Combo box 1 choice is used as the CRITERIA for the query that produces the value list for the SECOND combo.

Then the two guy chosen are used to build the filter string.

Me.Filter = "itemGuy1 = " & me.Combo1 & " and itemGuy2 = " & me.combo2.
me.filterOn = True

Just remember to add appropriate quoting around these guys if they're text values.

Me.Filter = "ItemGuy1 = '[/red]" & me.combo1 & "'[/red] and itemGuy2 = '[/red]" & me.Combo2 & "'[/red]"

Should work just fine.

Jim

Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[rvmams]

Jim,

Sorry, but I am still confused... I am very much a beginner in Access could you explain a bit more?

Thnx again

RvM

 

[WildHare]

Ok, well then let's get back to basics.

I have two combo boxes on a continuous form.

What is the purpose of these comboeaux? Are they BOUND to fields on the record, or do you want them for filter/search capabilities, and they are thus UNBOUND?

Combo boxs of the UNBOUND variety generally use QUERIES as their recordsource. I have an example of a situation where the 2nd combo box on a form is limited to the records selected by a 1st combo box up on my website.

So, to make a secondary box limit itself to the higher-level criteria selected in the first combo, the query for the second box should refer to the FIRST combo in it's CRITERIA cell, something like

=Screen.ActiveForm!Combo1

Then, your GOTFOCUS event for the 2nd combo issues a REQUERY command, to refresh the contents of this 2nd combo box.

Your AFTER UPDATE event of the 2nd box sets the filter string that you want to apply.

If, for example, your first combo box is a list of DIVISIONs and your second is the DEPARTMENTS within that division, the filter string would look like this:

Me.Filter = "Division = '" & me!Combo1 & "' and DEPARTMENT = '" & Me!combo2 & "'"
Me.filterOn = True
Me.REQUERY

I have a couple of different example databases up on my site that illustrate these techniques.

Let us know if you are still having trouble.

Jim





Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[rvmams]

Thanks, Jim for all your help!

But........I am still struggling

My form is a subform within a subform and is bound to a table. The form is part of a time tracking tool.

The continouos form is where the users fill in their hours. To do this they must specify in box 1 (called CompanyID) the company their charging their hours to and in box 2 (called ProjectID) the project they have been working on. The form has a bunch of other text boxes but they are not important in this case.

Here is the SQL that I have currently for Box 2 (ProjectID)

SELECT tblcompaniesprojects.companyname, tblcompaniesprojects.Projectname, tblprojects.Projectname
FROM tblprojects INNER JOIN tblcompaniesprojects ON tblprojects.ProjectID = tblcompaniesprojects.Projectname
GROUP BY tblcompaniesprojects.companyname, tblcompaniesprojects.Projectname, tblprojects.Projectname
HAVING (((tblcompaniesprojects.companyname)=[Forms]![frmtimecard]![frmtimecarddatesubform].[Form]![frmtimecarddatehoursubform].[Form]![companyid]))
ORDER BY tblprojects.Projectname;

In the gotfocus event of projectid:
me.projectid.requery

In the afterupdate event of projectid:

Me.Filter = "companyid = '" & Me!companyid & "' and projectid = '" & Me!ProjectID & "'"
Me.FilterOn = True
Me.Requery

The above leads to the following... lets say I have a timecard with 2 different companies on it and 3 different projects... the moment I set focus to the projectID ONLY the records where the companyID matches the companyID of the current record are shown, the others go blank...

Have been struggling with this for way tooooooooo long... I don't know where I am going wrong...any help is welcome!

Thnx,

RvM

 

[WildHare]

There MAY be a slight flaw in your design. This kind of thing is very simple to do with the correct design, and querys used to join data for forms/subforms.

Company
CompanyID
CompanyName
CompanyDetails...



Projects:
ProjectID
ProjectName
ProjectDetails...
CompanyID


Employees:
EmpId
EmpFirst
EmpLast
EmpSSN
EmpBillRate



TimeCards
TCDate
TCEmpID
TCProjID
TCHoursReg
TCHoursOT...


If you build a query linking the TIMECARD table to your Employees and Projects table, keeping the Project-to-Company link viable, you should be able to create a datasheet type subform with drop downs that will be relationally correct.

If you are totally confused by now, let me know and I'll try to whip up a quick demo and get it to you somehow. This Employee/Project/Timecard example was in a class I started teaching in Relational Database Design in 1982...

Jim










Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[rvmams]

Nice quip about 1982.

Anyway, for all kind of reasons, I cannot adapt my database design at this point. Furthermore, I looked at it in detail, and I am pretty certain it is ok.

Thanks for your help, but if it is not possible to do this, so be it. It was only meant to make it easier for the users to selcet the correct project. Guess they'll have to be more carefull.

RvM

 

[WildHare]

It should still be possible - place an unbound combo giving the projects for a particular company on the form, base it on a query that uses the COMPANY on that same form as a criteria, and then on the combo's after_update event, set the bound column into the project id field.

I do this kind of thing alot, it DOES make it easier for the users to get their data in (and more imporantly, it makes it VERY HARD to get the wrong data in). I'm a big fan of combo box data entry when it's appropriate. Let me know if you still need a hand or whatever.

Jim

Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm