Drop Down Parameter Not Returning a Value For a Query

Question

I have a database set up which includes a query and a form, which are causing me problems. In the form, I have a drop down menu which allows you to select from a list of ID values (e.g. 1, 2, 3, ...). Once you have selected a value, you then click a button and it runs the query with a parameter of the ID number from the drop down ([Forms]![KitInfoRetrievalForm]![DropDown]).

The problem here though, is that when I select something from the drop down menu and click the button to run the query, it gives a pop up box asking for a value to substitute in for [Forms]![KitInfoRetrievalForm]![DropDown]. This leads me to believe that either the drop down menu is a null value for some reason or my pathing to it is incorrect.

This was working at one point and then stopped after a series of weird error messages from something else entirely (in the same Access project). Any help you can give me would be much appreciated.

What you have listed [Forms]![KitInfoRetrievalForm]![DropDown] is the proper syntax for the query criteria. Are you sure the error messages are unrelated?? — random_answer_guy
Yea, they were on different forms and modules. Completely unrelated. For now I've just gone to a prior version of my DB where this still works and I don't see any differences (other than that it works). — Ulthran

Sergey S. Sergey S. · Accepted Answer · 2016-07-01T22:39:36

I would recommend to replace reference to dropdown in query by function. It should eliminate this problem and also this is a workaround for old bug in Access, which exists in all recent versions: if the form/subform is in datasheet mode and you applied filter (quick filter thru user interface or using VBA), it stops reading variables with references to controls or just parameters and uses last used value. I'm using this function for reading form/subform control value:

Public Function GetControlValue(strFormName As String, strControlName As String, Optional strSubFormControlName As Variant) As Variant
On Error Resume Next
If IsMissing(strSubFormControlName) Then
    GetControlValue = Forms(strFormName).Controls(strControlName).Value
Else
    GetControlValue = Forms(strFormName).Controls(strSubFormControlName).Form.Controls(strControlName).Value
End If
End Function

In your case replace [Forms]![KitInfoRetrievalForm]![DropDown] by

GetControlValue("KitInfoRetrievalForm","DropDown")

Drop Down Parameter Not Returning a Value For a Query

1 Answers