Using a form for select query criteria (1 Viewer)

[diversoln]

I have a form with a combo box that I'm using to limit data shown on another form that's data source is a select query.

Once I've selected the filter criteria on the 1st form, how do I get the query to run so that the results are displayed on the 2nd form?

This seems like Access 101 to me but I've been struggling with it for quite some time. When debugging this, if I run the query without filling in the first form, the query prompts me for the missing parameter. When I type it in, the query runs perfectly and the 2nd form displays with the correct data shown. When I use the first form to enter the criteria, the query produces a null result (the form and query are void of data).

Here's the code on the 1st form's button used to open the 2nd form:

Private Sub Open_History_Click()
On Error GoTo Err_Open_History_Click

Dim stDocName As String
stDocName = "Antibody_Lot_History"
DoCmd.OpenForm stDocName


Exit_Open_History_Click:
Exit Sub

Here's the query SQL.

SELECT Antibody_Lot_History.Antibody_Name, Antibody_Lot_History.Lot_Number, Antibody_Lot_History.Order_Date, Antibody_Lot_History.Received_Date, Antibody_Lot_History.Open_Date, Antibody_Lot_History.Exhaust_Date, Manufacturer.Manufacturer
FROM Antibody_Lot_History INNER JOIN Manufacturer ON Antibody_Lot_History.Antibody_Name = Manufacturer.Antibody_Name
WHERE (((Antibody_Lot_History.Antibody_Name)=[Forms]![Antibody_Pop-up]![Antibody_Select]));


Thanks for your help. PS - I searched the forum knowing this must be discussed but my search criteria turned up so many unrelated hits that it was impossible to wade thru them. (I tried)

 

[Uncle Gizmo]

For the purposes of debugging , what happens if you replace :

[Forms]![Antibody_Pop-up]![Antibody_Select]

with a known value ?

 

[diversoln]

Uncle Gizmo -

When I replace

[Forms]![Antibody_Pop-up]![Antibody_Select]

with a value, the query and 2nd form work correctly. I believe my problem lies in programming the 1st form that uses a combo box to select the query criteria. After the combo box is used to select the criteria, I have a button to open the 2nd form. Is there something I need to add to the Event Code to ensure that the criteria is used to run the query before the 2nd form opens ?


Please help ......

 

[Uncle Gizmo]

Is there something I need to add to the Event Code to ensure that the criteria is used to run the query before the 2nd form opens?
Please help ......

No, it should work fine. However you may have your combo box setup incorrectly. The combo box does not necessarily return the value shown. What values do you have in your:
bound column
column widths
Column count

 

[diversoln]

The following properties are set up for the combo box:

Bound Column = 1
Column Widths = 0";2.448"
Column Count = 2

I'm not sure what the values should be ..... Is this the source of the problem ?

 

[Uncle Gizmo]

I was thinking that the query behind your combo box maybe returning the wrong value for the query behind the form you are opening. Unfortunately the values you have provided for these:


Bound Column = 1
Column Widths = 0";2.448"
Column Count = 2

Look correct, so I’m doubtful that this is the problem. To double check, look at the query behind your combo box, there should be two columns, and the value that the query in your popup form uses should be the value in the left most column. The value in the column to the right of the left most column will be the value that displays in your combo box.

So this means the value you see in the combo box “SHOULD NOT” be the value that goes to the popup form, the value that goes to the popup form is the value to their left of it which would be its index number. For example if you had a customer “ Mr. Smith” and his account number was 01234, and you were looking up a customer “ Mr. Smith” The combo box would show “ Mr. Smith “ but the actual readable value of the combo box would be Mr. Smith’s account number, In this case 01234.

 

[diversoln]

If the value being stored is "01234" rather than "Mr. Smith" then the criteria being used in the query is "01234" ? I want the value used for the criteria to be "Mr. Smith" so how do I do this. Intuitively, if I select "Mr. Smith" from the combo box, I would think "Mr. Smith" would become the value .....


How do I get the stored value to be "Mr. Smith"?

 

[Uncle Gizmo]

>>>i want the value used for the criteria to be "Mr. Smith"<<<<

I'm not sure why you want to use the criteria Mr.Smith as opposed to using the reference to Mr. Smith but if that's what you want then:

Remove the first column from your combo box query in the query designer grid


And Set up for the combo box:

Bound Column = 1
Column Widths = 2.448"
Column Count = 1