Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2007

    Unanswered: Populate a variable with the result of a query

    I am fairly new to VBA coding in Access. So please forgive if this is a dumb question..

    Is it possible to populate a variable with the result of a query?

  2. #2
    Join Date
    May 2005
    Nevada, USA
    Provided Answers: 6
    If the query returns a single value, you could use DLookup(). Alternatively, you can open a recordset on the query and navigate the recordset to populate the variable. More info about what you're trying to do will probably yield a more specific response.

  3. #3
    Join Date
    Jun 2007
    Thank you so much for your advice on DLookup() it worked perfectly:

    PID = DLookup("[PID]", "Users", [AID] = AID)
    The DB is going to be used as a work assignment/tracking tool. Using a form that I have created 1 group of folks puts in a request for work to be done by another group. Each person that will be putting in work requests has a distinct id code (v116478 for example) that is used in several systems company wide. So by using User Level Security, I can force the users to log in using their code.

    As they fill out the form and submit the request I can use CurrentUser() to determine who they are without having to ask either with a inputbox or having them make a selection on the form (idiot proofing). When they click the Submit button on the form, the data from all of the Combo Boxes, List Boxes, and Text Boxes is written to a table called Notifications.

    In the notifications table there is a field for AuthorID, and a separate field for PartnerID. The AuthorID is set by looking at who the current user is. Each author is assigned to a specific Author Partner who is responsible for completing their work. So to establish the Author/Partner relationships I have a table called Users. In the Users table I have 2 fields AuthorID, and PartnerID.

    Now, thanks to your proposed solution, I can use the value of CurrentUser() and do a DLookup and find the correct Partner for the Author...

    Thanks again for the help!

Posting Permissions

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