Reference well nested ctl as query criteria (XP)

Topic

New Reply

I have a form named frm_TicketViewer, with a text box named fk_StatusCodeID. In a query, I’m able to successfully set the WHERE clause to:

[Forms].[frm_TicketView].[fk_StatusCodeID]

…and all is well.

Now I’ve dropped frm_TicketViewer on a page in a tab control in another form, and need to change the WHERE clause accordingly. Here be dragons. The new form is named frm_Main, the tab control is named tabMain, and the page on which the original form was dropped is named pgOverview. So, I’ve tried a thousand and one permutations and am now turning to the knowledgable folks here. Here are some examples of what doesn’t work. Please ignore the continuation character in the first example:

[Forms].[frm_Main].[tabMain].[Pages].[pgOverview].[Subforms].[frm_TicketView]. _
[fk_StatusCodeID]

[Forms].[frm_Main].[tabMain].[pgOverview].[frm_TicketView].[fk_StatusCodeID]

Reply | Quote

Replies

I believe that all you have to do is to change your original:

[Forms].[frm_TicketView].[fk_StatusCodeID]

to:

[Forms].[frm_Main].[fk_StatusCodeID]

I believe that it does not matter if it’s on a tab control or page, but that it’s on a form.

Pat

Reply | Quote

Thanks for the quick reply, Pat, but still no dice.

Reply | Quote

Shane,

I think you need to use a bang operator ! after [Forms], at a minimum. The form with the well-nested control is open, I presume.

After that, it depends on which option you’re choosing.

HTH,

Tom

Reply | Quote

Sorry about that (Hagradd from Harry Potter).

I didn’t notice it was XP you were using. I will have to leave this one to someone who knows.

Is that dropped form frm_TicketViewer a subform of frm_Main?

Do you just address it like [Forms].[frm_Main].[frm_TicketView].[fk_StatusCodeID] ?

Pat

Reply | Quote

Interesting! I just tried the equivalent of this …

[Forms]![frm_Main]![frm_TicketView]![fk_StatusCodeID]

in a calculated column both in Access XP and A2k and it didn’t return anything in either one, even though there is data in the field I was referencing and I had clicked into the subform control to be sure I knew where the focus was. I’m not sure whether it’s an artifact of the changes to the query engine in A2k or what. I wasn’t using a tab control, and you don’t have to reference them anyhow, but the behavior is quite plain to see. What makes it even more interesting is that it doesn’t work even if you slavishly follow the MSKB instructions and include the subform.Form reference like this:

[Forms]![frm_Main]![frm_TicketView].Form![fk_StatusCodeID]

And before anyone asks, no my controls were not named the same as the underlying field. The MSKB says you can do this, and you certainly could in earlier versions. This isn’t even very deep nesting. I wonder what’s going on here?

Reply | Quote

Thanks, all, for the replies. I’m glad I’m not alone is not understanding what underlies this issue. To make it more confusing yet, I placed the following in the OnOpen event of frm_Main, and it works properly by displaying the value in the text box fk_StatusCodeID. But, the same syntax, with or without referencing the Value property, in a query as criteria doesn’t. Hmmmmm.

MsgBox Forms!frm_Main!frm_TicketView!fk_StatusCodeID.Value

I’m not far from just fetching the value in the field and changing the QueryDef behind the scenes, but for a number of reasons, would really rather not. Any more thoughts?

Reply | Quote

This is kind of weird – I got bit by a very similar problem yesterday in 2000. In my case I was trying to simply open a recordset based on a query which had same kind of criteria being taken from a form. The query would work like a champ if it simply opened it, but in VBA when I tried to open the DAO recordset it would say it was expecting 1 parameter and got none. (On reflection, I think I’ve seen this problem once or twice before.) The solution in my case was to simply set a parameter for the querydef, and then open the recordset and it worked fine, so it only added one line of code. In your case, you might try applying a filter instead of having a criteria in the query. Unfortunately, that would probably require as much or more code as setting a parameter in the querydef. Hope this helps a bit.

Reply | Quote

Shane,

If I use a function in the query to cast the value as a particular datatype or use Eval(), then I get a value in the query. That doesn’t make any sense to me, but it seems to work. Give it a try. The field I was testing for was a date, so I used the CDate function in the query on the control value from the subform and that seems to work. I suspect this truly is the result of the changes in the query engine in A2k and later.

Reply | Quote

Well isn’t that the darndest thing?! For completeness’ sake, the ultimate criteria I ended up with is:

CLng([Forms]![frm_Main]![frm_TicketViewer]![fk_StatusCodeID])

That is, ignore the fact that the subform is on a tab control, and cast the value in the referenced control as the necessary data type using the appropriate conversion function.

Thanks, all, for your help and suggestions. Cheers, Charlotte!

Reply | Quote

Crazy, isn’t it? Even though the value in the referenced control is the right data type, you still have to use some kind of function–Eval() or Val(), if nothing else–to make sure it’s still the right datatype when the query gets it!

Reply | Quote