Query as ListBox Row Source problem

[rkl122]

I'm afraid this is a fundamental issue, but I'm rusty. I've got the following query as the row source for a listbox on a main form. cboShootName is a combobox on that same form.

SELECT DISTINCT  QrySbfShotList.CamerasFK, tblCameras.CameraNum 
FROM QrySbfShotList INNER JOIN tblCameras ON QrySbfShotList.CamerasFK = tblCameras.CamerasID 
WHERE (((QrySbfShotList.shootsFK)=[me]![cboShootName]))   
UNION 
SELECT null, "(ALL)"  FROM QrySbfShotList INNER JOIN tblCameras ON QrySbfShotList.CamerasFK = tblCameras.CamerasID 
WHERE (((QrySbfShotList.shootsFK)=[me]![cboShootName]));

When the form loads, it prompts for a value for me.cboShootName, even though I've given the list box a default value. More important, after the cboShootName is established, ie. in its after update event, if I put Me.lstCamera.requery (lstCamera is the list box in question), the system prompts me for the value of me!cboShootName. I know the cboShootName is in scope because I see its value with a msgbox diagnostic.

The first column of both the cboShootName and lstCamera refer to numeric fields. Both controls are unbound.

What am I missing? Is there a trick to referencing control values in a query?

Thank you, Ron

 

[JHB]

Try by replacing the [me]![cboShootName] with, (not tested).

Forms![YourFormName]![cboShootName]

Else post a stripped version of your database with some sample data, (zip it).

 

[rkl122]

That actually worked. Thank you!!

If you or anyone has a moment, I'd appreciate learning why it works. Are there rules about when you can and can't use the "me" reference when you want to access the value of an unbound control in a query? Does it depend on the context/purpose for which the query is used? A definitive link would be appreciated.

Thx again, Ron

Try by replacing the [me]![cboShootName] with, (not tested).

Forms![YourFormName]![cboShootName]

..........

 

[pr2-eugin]

rkl122, Me is the short hand representation of a current Form/Report Object that has the focus, preferably used inside an one such object. Trying to refer it outside them has no meaning..

If you need to refer a Form/Report object you need to use the right syntax.. Referencing Controls matrix can be available here.. http://access.mvps.org/access/forms/frm0031.htm

 

[rkl122]

Thx, I've used that link offten. But it refers to form-subform hierarchies. My issue occurs totally on the main form.

The query that caused my problem is the value of the row source property of a(n unbound) listbox on the same form that contains a(n unbound) combo box whose value the listbox is accessing. Does not the containing form therefore have focus? (The query in question does in fact make reference to a query which is the recordsource for a subform, but that should not matter. Should it?)

I suspect this issue has more to do with how the query engine interacts with VBA, but I've never found definitive rules about that.

Thanks for the response. -Ron

rkl122, Me is the short hand representation of a current Form/Report Object that has the focus, preferably used inside an one such object. Trying to refer it outside them has no meaning..

If you need to refer a Form/Report object you need to use the right syntax.. Referencing Controls matrix can be available here.. http://access.mvps.org/access/forms/frm0031.htm

 

[pr2-eugin]

Thx, I've used that link offten. But it refers to form-subform hierarchies. My issue occurs totally on the main form.

The matrix needs to be mapped properly to get the information. Although not required in your case.. Okay the Queries (available in the Object window) called the pre compiled Queries can refer to other objects only through the right syntax.. Forms!FormName!controlName..

Does not the containing form therefore have focus?

Yes, you are correct the Form has focus, but the Query has no idea what Me is.. As mentioned earlier.. Using Me outside a Form/Report module is meaningless..

The query in question does in fact make reference to a query which is the recordsource for a subform, but that should not matter. Should it?

Unless the RowSource of the ListBox is set dynamically through VBA you cannot use Me outside the Form object's Module..

but I've never found definitive rules about that.

Not a complicated rule at all, rather it is only one simple rule, The Keyword Me cannot be used outside a Form/Report object.

 

[rkl122]

....................

...........
Not a complicated rule at all, rather it is only one simple rule, The Keyword Me cannot be used outside a Form/Report object.

Ok thanks. I guess the concept of "outside" is hazy to me since I was using the query - or more precisely, stating the query - within the form object. But I think I get what you are saying. A query definition, even though defined within the form object, is still "outside," because the query engine engine (compiler?) doesn't understand shortcuts that are intended only for passage to the database (ACE? JET?) engine. Or something like that....? (I'm a hobbiest, not pro.) I will ruminate on it.

Thanks, Ron

 

[JHB]

If the list box and the [cboShootName] it is in the same form, then the below should also work, (take away the [Me]!).

SELECT DISTINCT QrySbfShotList.CamerasFK, tblCameras.CameraNum FROM QrySbfShotList INNER JOIN tblCameras ON QrySbfShotList.CamerasFK = tblCameras.CamerasID WHERE (((QrySbfShotList.shootsFK)=[cboShootName]))
UNION SELECT null, "(ALL)" FROM QrySbfShotList INNER JOIN tblCameras ON QrySbfShotList.CamerasFK = tblCameras.CamerasID WHERE (((QrySbfShotList.shootsFK)=[cboShootName]));

 

[rkl122]

I've been away. Yes, to confirm, that works too. Thank you.

If the list box and the [cboShootName] it is in the same form, then the below should also work, (take away the [Me]!).

SELECT DISTINCT QrySbfShotList.CamerasFK, tblCameras.CameraNum FROM QrySbfShotList INNER JOIN tblCameras ON QrySbfShotList.CamerasFK = tblCameras.CamerasID WHERE (((QrySbfShotList.shootsFK)=[cboShootName]))
UNION SELECT null, "(ALL)" FROM QrySbfShotList INNER JOIN tblCameras ON QrySbfShotList.CamerasFK = tblCameras.CamerasID WHERE (((QrySbfShotList.shootsFK)=[cboShootName]));