Row Source Query in Navigation Subform

[abcrf]

Hi all,

I have a combo box the row source of which refers to the value of another control.


It works completely fine in the form itself, written as

SELECT distinct TBL_PROTOCOL.[PROTOCOL NUMBER] FROM TBL_PROTOCOL WHERE (([STUDY NAME]=Forms.FRM_PROTOCOL.SetStudy)) ORDER BY TBL_PROTOCOL.[PROTOCOL NUMBER];

but when the form is accessed via the navigation subform that users will use to access it, it stops working, and instead prompts me for a value of Forms.FRM_PROTOCOL.SetStudy.


I've tried changing the where statement to

[STUDY NAME]=Forms!NAV_DATABASE.NavigationControl0.FRM_PROTOCOL.Form.SetStudy)

per what I've read on the web, but the problem persists


Can anyone tell me how I need to tweak the syntax to allow this to work in the overall navigation structure?

Thanks!!

[NeoPa]

If it's in SQL then using dots (.) is wrong as they won't be recognised. I don't know where the data is relative to your form but assuming you have that right, then changings dots to bangs (!) should fix it.

[abcrf]

Unfortunately, it doesn't. And thinking back, there have been many times in the past I've used dots in the row source without problems.

[NeoPa]

I'm afraid you're right. Even trying to duplicate that in VBA fails, but in a query it resolves [Forms].[FormName] as if it were [Forms]![FormName].

"Well, I'll be!" as they say.

[NeoPa]

Looking at your code, it seems that your reference within the form is quite different from your posted WHERE code. Can you explain what the structure is you're working with. I find it particularly curious that the reference within the form has FRM_Protocol as a form found in the Forms collection, whereas the other reference appears to treat it as a sub-form control.

An explanation of the types of each of the objects involved would help to make the question clearer.

[abcrf]

Yeah, it was written as such because FRM_PROTOCOL is a form being accessed within a navigation subform.

What I ended up doing was pulling the value of the first combobox directly in VBA, and setting the rowsource of the second with that value:

Me.SetNumber.RowSource = "SELECT distinct TBL_PROTOCOL.[PROTOCOL NUMBER] FROM TBL_PROTOCOL WHERE (([STUDY NAME]=""" & Me.SetStudy.Value & """)) ORDER BY TBL_PROTOCOL.[PROTOCOL NUMBER]"

Works fine. That's what I get for trying to use the Microsoft interface...

[NeoPa]

abcrf:
That's what I get for trying to use the Microsoft interface...

I suspect there is a way to use the Microsoft interface, which would be a valid answer to the original question, but I'm afraid I don't have experience with such controls, nor a database to play with which can sometimes be enough.

Anyway, well done for finding a way around the problem.