Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Feb 2001
    Location
    Trenton, New Jersey, USA
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Define a variable using a SQL statement (Access 97)

    This should be simple but... We need to use one of three IDs to look up a name in the corresponding table of three different tables (Individuals, Entities, Families). We think an easy way to do this would be to use Select Case and have each Case be for each type. The code to run in each case would pull the correct name from the correct table use the corresponding ID. We want to use SQL statement in each case to define the name for a text box on a form. We are having no luck finding how to use a SQL statement to define a variable. Maybe you can not do this. We would appreciate any help.

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

    Re: Define a variable using a SQL statement (Access 97)

    Sorry, it must be Friday. Could you try to explain your problem again? I don't understand. What do you mean by "SQL statement to define a variable"? And "define the name for a text box" is confusing. You can only change the name of a text box in design view, so you probably mean something else.

  3. #3
    Lounger
    Join Date
    Feb 2001
    Location
    Trenton, New Jersey, USA
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Define a variable using a SQL statement (Access 97)

    We would like to write code to select a payorname from a table referenced by a payorid. There are three separate tables with payorIDs. The first three letters of the payorid define which table the payorid can be found in.

    This is what we like to happen:

    Select Case left(me![Payorid],3)
    Case "ENT"
    'The following state defines the value we would like to retrieve from the table TblEntity
    'SELECT TblEntity.EName FROM tblEntity WHERE tblEntity![EntID] = me![PayorID]
    'We would like the control me![PayorName} to assume the value retrieved
    Case "IND"
    'The following state defines the value we would like to retrieve from the table Tblindividual
    'SELECT TblIndividual.EName FROM tblIndividual WHERE tblndividual![INDD] = me![PayorID]
    'We would like the control me![PayorName} to assume the value retrieved
    Case "FAM"
    'The following state defines the value we would like to retrieve from the table TblFamily
    'SELECT TblFamily.EName FROM tblFamily WHERE tblFamily![FamD] = me![PayorID]
    'We would like the control me![PayorName} to assume the value retrieved
    case else
    msgBox "Proper ID not available"
    end select

    This project was started by another member of my team. It may not be possible to do something like this in MSAccess VBA but we thought we would ask anyway.

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

    Re: Define a variable using a SQL statement (Access 97)

    Actually, I believe a union query would do the trick as long as the tables have the same structure (and you don't need to update the record). Otherwise you will need to open a recordset on each table in sequence until you find the correct record - at that point you would know which table the payor is in. It may be too late for you to take a different approach, but we usually advise that things such as this be put in a single table so that you always have a unique ID. Actually, in your case it appears you do have the possibility of a unique ID based on the three letter prefix. Your code is actually more or less right - what you need to do is open a recordset using DAO. If you aren't familiar with it, there's a bit of a learning curve - but most Access books that have a reasonable section on VBA will give you examples to work from. One excellent reference is the Access 97 Developer's Handbook by Litwin, Getz, et al and published by Sybex. Hope this helps.
    Wendell

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

    Re: Define a variable using a SQL statement (Access 97)

    Since the PayorID uniquely identifies both the table and the individual, you can combine the information from the three tables in a union query. Union queries are written entirely in SQL. In this case, create one with SQL text

    SELECT EntID As PayorID, EName FROM TblEntity
    UNION
    SELECT IndD, EName FROM TblIndividual
    UNION
    SELECT FamD, EName FROM TblFamily

    (Shouldn't that be IndID and FamID?)

    Let's say you name this query, um, qryUnion (I'm in a creative mood tonight)

    I don't know whether the PayorID control on the form is a text box or combo box. A combo box with row source type Table/Query and row source qryUnion would be a user-friendly way to let the user select a PayorID.

    The control source of the PayorName text box can be

    =DLookup("PayorID","qryUnion","PayorID="&[PayorID])

  6. #6
    Lounger
    Join Date
    Feb 2001
    Location
    Trenton, New Jersey, USA
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Define a variable using a SQL statement (Access 97)

    Thanks

    The Union Query worked fine.

Posting Permissions

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