Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Location
    Seattle
    Posts
    10

    Unanswered: How to store a constant on a form for reference by a query?

    How can I store a constant value on a form for use by the query that provides the form's data source?

    Basically, I want my form to retrieve only records that are pertinent to the user who's using the form. I intend to roll out a front end to each user and I figured I'd just include an invisible box on the form that stores that user's UserID number, and then I'd reference that number in the query that returns the data source to the form itself.

    How do I do this? I put in an invisible text box and I can't seem to get the query to recognize its contents (the number "2," for example).

    What might I be doing wrong? Ain't no way this should be so difficult but man I'm ripping my hair out! Thanks!

  2. #2
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: How to store a constant on a form for reference by a query?

    Originally posted by gianni_sacco
    How can I store a constant value on a form for use by the query that provides the form's data source?

    Basically, I want my form to retrieve only records that are pertinent to the user who's using the form. I intend to roll out a front end to each user and I figured I'd just include an invisible box on the form that stores that user's UserID number, and then I'd reference that number in the query that returns the data source to the form itself.

    How do I do this? I put in an invisible text box and I can't seem to get the query to recognize its contents (the number "2," for example).

    What might I be doing wrong? Ain't no way this should be so difficult but man I'm ripping my hair out! Thanks!
    Go to your query design, and in the Criteria area for the field containing the UserID, type...
    [Forms].[frmName]![txtName]
    (change frmName to your form name... and change txtName to your invisible text box name)

    HTH

  3. #3
    Join Date
    Aug 2003
    Location
    Seattle
    Posts
    10

    Re: How to store a constant on a form for reference by a query?

    Originally posted by Trudi
    Go to your query design, and in the Criteria area for the field containing the UserID, type...
    [Forms].[frmName]![txtName]
    (change frmName to your form name... and change txtName to your invisible text box name)

    HTH
    Hi Trudi. Actually, that has been my approach, but for some reason the query is not recognizing the reference. I know the reference is correct, so I'm thinking I don't actually have the number stored in the textbox like I think I do.

    This is hopelessly basic, but how do I make sure that the contents of that textbox is actually forever and always the number "2" ? Lordie! Thanks!

  4. #4
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: How to store a constant on a form for reference by a query?

    Originally posted by gianni_sacco
    Hi Trudi. Actually, that has been my approach, but for some reason the query is not recognizing the reference. I know the reference is correct, so I'm thinking I don't actually have the number stored in the textbox like I think I do.

    This is hopelessly basic, but how do I make sure that the contents of that textbox is actually forever and always the number "2" ? Lordie! Thanks!
    Hmmm... The problem COULD be to do with the fact that text boxes contain text... rather than numeric data...

    I'd try doing a CInt() around the expression we made in the query... and see what happens...

    If you want this text box to ALWAYS contain 2... why not just make it unbound, and either put... =2 into the control source... or 2 in the default value... Both will do what you want... If the control is invisible, it's not going to be changed by anyone...

  5. #5
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: How to store a constant on a form for reference by a query?

    Originally posted by gianni_sacco
    Hi Trudi. Actually, that has been my approach, but for some reason the query is not recognizing the reference. I know the reference is correct, so I'm thinking I don't actually have the number stored in the textbox like I think I do.

    This is hopelessly basic, but how do I make sure that the contents of that textbox is actually forever and always the number "2" ? Lordie! Thanks!
    Another thing... You say that the query is not recognizing the reference... Does that mean you're getting some kind of error message???

    Also, remember that the Forms collection only contains forms that are currently OPEN... so if you're trying to build a query in design mode and the form is closed, it won't work... (Just thought I'd mention it... )

  6. #6
    Join Date
    Aug 2003
    Location
    Seattle
    Posts
    10

    Re: How to store a constant on a form for reference by a query?

    Originally posted by Trudi
    Another thing... You say that the query is not recognizing the reference... Does that mean you're getting some kind of error message???

    Also, remember that the Forms collection only contains forms that are currently OPEN... so if you're trying to build a query in design mode and the form is closed, it won't work... (Just thought I'd mention it... )
    It seems to have been, off all things, the inclusion of the equals sign that did the trick; I had "2" in the control source instead of "=2", which made all the difference in the world.

    The query now recognizes the contents of the textbox and in fact recognizes it as a number. Whew!

Posting Permissions

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