Use Combo Box in aForm Based on Parameter Query 1

[JOEYB99]

I am using Access 2007 and have developed a parameter prompt query and then a split Form based on this query, named Form1.

Now I want to use a combo box instead for the user to select values instead of the parameter prompt. So I added a combo box to Form1, and it was assigned Combo26.

In Design View of Form1 I have modified the criteria in the Query builder to [Forms]![Form1]![Combo26]

but I'm not getting any results! What am I doing wrong?

 

[PHV]

What is the SQL code of the RecordSource of Form1 ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JOEYB99]

Hi PHV, how are you? Thansk for responding.

Here is the SQL code of the query...

SELECT tblLabourBillings.[Formatted Cost Code], tblLabourBillings.[Ticket Date (YYYY-MM-DD)], tblLabourBillings.[Employee Name], tblLabourBillings.[Employee Class], tblLabourBillings.[Employee Type], tblLabourBillings.[Regular Hours], tblLabourBillings.[Overtime Hours], tblLabourBillings.[Other Hours], tblLabourBillings.[Sequence Number], tblLabourBillings.[REG Rate], tblLabourBillings.[OVT Rate], tblLabourBillings.[OTH Rate], tblLabourBillings.UnionGroup, tblLabourBillings.[Cost Code Description]
FROM tblLabourBillings
WHERE (((tblLabourBillings.[Formatted Cost Code])=[Forms]![frmQuery1]![Combo26]));



Here is the SQL code for the Record Source of the form,

SELECT tblLabourBillings.[Formatted Cost Code], tblLabourBillings.[Ticket Date (YYYY-MM-DD)], tblLabourBillings.[Employee Name], tblLabourBillings.[Employee Class], tblLabourBillings.[Employee Type], tblLabourBillings.[Regular Hours], tblLabourBillings.[Overtime Hours], tblLabourBillings.[Other Hours], tblLabourBillings.[Sequence Number], tblLabourBillings.[REG Rate], tblLabourBillings.[OVT Rate], tblLabourBillings.[OTH Rate], tblLabourBillings.UnionGroup, tblLabourBillings.[Cost Code Description]
FROM tblLabourBillings
WHERE (((tblLabourBillings.[Formatted Cost Code])=[Forms]![frmQuery1]![Combo26]));

 

[PHV]

I'd use this WHERE clause:
WHERE tblLabourBillings.[Formatted Cost Code]=[Forms]![frmQuery1]![Combo26] OR [Forms]![frmQuery1]![Combo26] Is Null

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JOEYB99]

Thanks PHV, that got some results in my form output.

However, the Combo Box will allow selections of a cost code but the results do not change for the output of the form.

The desired result is to have the out put of the form - the datasheet view in the bottom half - show just the records corresponding to the cost code selection made in the combo box.

What else can I do here?

 

[PHV]

Requery the form in the AfterUpdate event procedure of the combo.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JOEYB99]

Thanks PHV, but can you please elaborate.

What exactly do I enter/input to the After Update property of the Combo Box?

When I click on that property it prompts for a macro, VBA code or a builder expression.

 

[JOEYB99]

Hi PHV, I have discovered something that has helped me out here but I still need some help.

Apparently there is a prompt in the Combo Box wizard that I did not understand and is relevant here. The first screen has three prompts and the third one is very applicable to my situation, "Find a record on my form based on the value I selected in my combo box." I checked this option and it appears to be working just fine now.

However, I still receive all the records from the table that my Form is reading as its record source. The combo box selection simply highlights those records I want but still shows all other records in my datasheet view.

My desired effect is to have the combox box selection show only those records pertaining to the selection in the datasheet view, and no others. When the user makes a new selection in the combo box then a new set of records shows in the datasheet view.

How can I get the combox box and Split Form to behave this way?

 

[PHV]

What is the actual code behind your combo ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JOEYB99]

The combo box does not have anything for the Control Source property. It is blank.

The Records Source property for the combo box has the following SQL code,

SELECT DISTINCT tblLabourBillings.[Formatted Cost Code]
FROM tblLabourBillings;


The AfterUpdate property has a macro with one line, called SearchForRecord with several arguments and the following WHERE condition,

="[Formatted Cost Code] = " & "'" & [Screen].[ActiveControl] & "'"

Do you want to know the arguments for this?

 

[PHV]

Sorry, I know nothing about macros in access.