Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reference well nested ctl as query criteria (XP)

    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:<pre>[Forms].[frm_TicketView].[fk_StatusCodeID]</pre>

    ...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. <img src=/S/dragon.gif border=0 alt=dragon width=17 height=15> 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:<pre>[Forms].[frm_Main].[tabMain].[Pages].[pgOverview].[Subforms].[frm_TicketView]. _
    [fk_StatusCodeID]</pre>

    <pre>[Forms].[frm_Main].[tabMain].[pgOverview].[frm_TicketView].[fk_StatusCodeID]</pre>

    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Reference well nested ctl as query criteria (XP)

    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 <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  3. #3
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reference well nested ctl as query criteria (XP)

    Thanks for the quick reply, Pat, but still no dice.
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  4. #4
    Star Lounger
    Join Date
    Aug 2001
    Location
    Seattle, Washington, USA
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reference well nested ctl as query criteria (XP)

    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

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Reference well nested ctl as query criteria (XP)

    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 <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Reference well nested ctl as query criteria (XP)

    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. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29> 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. <img src=/S/confused.gif border=0 alt=confused width=15 height=20> 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?
    Charlotte

  7. #7
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reference well nested ctl as query criteria (XP)

    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. <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23> <pre>MsgBox Forms!frm_Main!frm_TicketView!fk_StatusCodeID.Valu e</pre>

    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?
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Reference well nested ctl as query criteria (XP)

    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.
    Wendell

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Reference well nested ctl as query criteria (XP)

    Shane,

    <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15> 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. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> I suspect this truly is the result of the changes in the query engine in A2k and later.
    Charlotte

  10. #10
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reference well nested ctl as query criteria (XP)

    Well isn't that the darndest thing?! For completeness' sake, the ultimate criteria I ended up with is:<pre>CLng([Forms]![frm_Main]![frm_TicketViewer]![fk_StatusCodeID])</pre>

    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! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Reference well nested ctl as query criteria (XP)

    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! <img src=/S/nuts.gif border=0 alt=nuts width=15 height=15>
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •