Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Look up a record in a query based on a form (Win ME/Access 97)

    I thought this was gong to be straight forward, but it isn't.

    I want to run a series of queries, taking the criteria data from an open form. Example: I open the form frmCancelation, enter the customer number in an unbound control named CustNum, then run a macro which runs the queries. The queries have in the criteria field of CustNum the string Forms!frmCancelation!CustNum. The queries all return one record, which is blank, rather than all of that customer's records.

    The query has one field selected, the CustNum field, which is used for the criteria string, and then the *, like TPayments.*.

    Any ideas as to why this is not working?

  2. #2
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Look up a record in a query based on a form (Win ME/Access 97)

    As I was browsing through postings trying to get more insight into my problem, I found reference to the "Like" operator. With that addition, everything is fine. That was really dumb.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Look up a record in a query based on a form (Win ME/Access 97)

    Problem was already solved when I posted this

    How do you call the macro? If you use a command button on the form, the focus shifts from the text box to the command button, so the text box has been updated with the text entered by the user. The query should work as intended then.

    However, if you have an AutoKey that calls the macro while the focus is still on the text box, the text box hasn't been updated yet. In that case, the macro will use the "stored" value of the text box. When you open the form, this will be empty. To get around this, refer to the Text property of the text box in the criteria: Forms!frmCancelation!CustNum!Text. The Text property contains the text currently displayed in the control.

  4. #4
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Look up a record in a query based on a form (Win ME/Access 97)

    Excellent insight into this type of procedure. Thank you. As they say, you learn something new everyday.

Posting Permissions

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