Public variable in combo SQL

[JohnPapa]

Is it possible to introduce a Public variable (call it plngID) in the SQL code that is executed by a combo box.

SELECT tblOrthoPopup.lngID, tblOrthoPopup.lngGroup, tblOrthoPopup.txtDescription, tblOrthoPopup.blnVisible
FROM tblOrthoPopup
WHERE (((tblOrthoPopup.lngGroup)=2) AND ((tblOrthoPopup.blnVisible)=True))
ORDER BY tblOrthoPopup.txtDescription;

In the code above I would like to replace the "2" by public variable plngID.

Thanks,
John

 

[PeterF]

Make a public function to return te value of plngID and replace the 2 in your query by a call to your function.

Function GetplngID() as integer
   GetplngID = NZ(plngID, 2) [COLOR="Green"]'If not set choose a fixed value or allow null by removing the NZ[/COLOR]
End Function

In your query replace 2 by "GetplngID()"

 

[JohnPapa]

Thanks for your reply Peter,

If you have a look at the attachment you'll see what I mean. The attachment shows what gets executed when I click on the combo box. I want to be able to reuse this query for other values of lngGroup. I need to replace the "2" with a Public Variable.

John

 

[PeterF]

Did you try the sample code? "GetplngID()" should be without quotes in the critera.

How do you plan to update your variable plngID, if it's based on a form you can simply put a reference to the control on the form as a critera in the query.

 

[JohnPapa]

Here’s what I am trying to do. Imagine SEVERAL combo boxes on SEVERAL forms, actually 4-5 forms with about 10 combo boxes each. The contents of each combo box is user defined ie the user can enter the values he wants. I attach frmOrtho where 2 combo boxes are indicated (Classification and Extraction)

All combo box entries are included in tblOrthoPopup (see attachment). The entries that correspond to Classification have lngGroup = 2 and for Extraction lngGroup = 3

I would like to use the same combo box Row Source (attachment OrthoRowSource) to display the correct content based on the combo I click. When I click on a combo Classification, it must set the public variable = 2 and pass this value in the Row Source.
I put the setting of the public variable in the On Mouse Down event of the combo box.
Unfortunately it does not work all the time (sometimes I do not get any option dropped down and sometimes I get the options of a different combo.

I used the following based on your suggestion

Public Function funlngOrthoGroup() As Long
   funlngOrthoGroup = Nz(plngOrthoGroup, 2) 'If not set choose a fixed value or allow null by removing the NZ
End Function

Also I tried using a hidden textbox on an open form, with the same problems.

Any ideas?
John

 

[JohnPapa]

I must not be thinking very straight today. Since the Row Source of each combo is unique and specific to that combo, then I can hard code the lngGroup number in the combo Row Source.
I believe it is solved.
John