Pass a variable from a form to a select query

[gjwpromo1@weissfin.com]

Hi,
I need some help. I have a form (frmSelectManager) which uses a combo box to select a number. After selecting the number, you click a command button and it runs Private Sub cbuOK_Click() where the number selected is stored as a variable vMID (dim vMID as integer).
I then I want to open a query using the variable vMID as the criteria for a field named ManagerID (note: the form remains open during this operation). I have tried [forms]![frmSelectManager]![vMID] in the criteria box, but it does not work. Can someone tell me what I am doing wrong?

If it helps the sql is:
SELECT tblPerformanceData.ManagerID, tblPerformanceData.DataTypeCode,
FROM tblPerformanceData
WHERE ((([tblPerformanceData].[managerid])=[forms]![frmSelectManager]![vmid]));

Have I messed up with the brackets or exclamation point?




Thanks.
Gene

[davegri]

Remove that comma after tblPerformanceData.DataTypeCode in the first line.

[gjwpromo1@weissfin.com]

Sorry for the delay in responding. Thank you for checking that out. The comma is just an editing error when I copied text for this post. I did confirm that it still did not work.
Gene

[Welshgasman]

Use the combobox. Vmid is not part of the form.

[Gicu]

Gene,
You cannot use a local form variable as a parameter in a query. You can directly reference the control itself as suggested by Welshgasman in the previous post or you can use a tempvar or a custom VBA function that uses a global variable (so basically you declare vMID as Public in a standard module and have a function that returns its value):

Option Explicit
Public vMID as Long
Public Function fnMid() as Long
fnMid=vMID 'the value of vMID is set in the click event of the button just as you have it now.
End Function

The SQL becomes:
SELECT tblPerformanceData.ManagerID, tblPerformanceData.DataTypeCode,
FROM tblPerformanceData
WHERE [tblPerformanceData].[managerid]=fnMID();

Cheers,

[gjwpromo1@weissfin.com]

Again, apologies for late response. Thank you both. Both solutions work. I appreciate the vba code that Gicu wrote out which makes it easy to insert it into my code, and Welshgasman's comment about using the combobox clarified my thinking about what a query could or couldn't use as criteria. I consider this thread solved and will try to figure out how to mark it as such.
Gene