Results 1 to 11 of 11
  1. #1
    sdyk76 is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    13

    Question How to distinguish between two field names that match in an Recordset

    I have an open recordset based on a SQL string that combines (among other things) tables with information about employees and customers. Both tables have a FirstName field. How do I distinguish between the two fields in VBA?

    When I try this:

    rs!tblEmployees.FirstName

    I get an error saying that the item is not included in the collection

    but I can't just do:

    rs!FirstName

    because how would the program distinguish between the two matching fields names? As a work around I guess I could call the specific column number (e.g. rs!Field(5) )...

    Any other suggestions?

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    name the recordset accordingly...

    rsEmps
    rsCust

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not sure how you give one recordset 2 different variable names, or even why - you'd be no further ahead as they'd have the same fields anyway? However, I've never encountered this situation and would be surprised that you could create this without raising an error in the recordset. As an experiment, I'd loop over the recordset fields and debug.Print each field name to see what you have (unless you're already sure of that). It may be that Access automatically gave one of the dupes an alias. Or On that note, I'd also experiment with giving one of the fields an alias in the sql and see if the recordset uses the alias you give it or sticks with the field name.

    I'd also venture that you cannot refer to the table when referencing the recordset as the table name is not part of the recordset fields collection. If you referred to a stored query instead, perhaps you could include the table name or the alias that Access would automatically give it as it would any normal query.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    sdyk76 is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    13
    You are saying to create two recordsets right? Is there not a way to reference a specific field within a single recordset if it contains two fields with matching names?

  5. #5
    sdyk76 is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    13
    Ok I'll print out the field names and see what it comes up with.

  6. #6
    sdyk76 is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    13
    So the field names are tblEmployees.FirstName, tblEmployees.LastName, etc, etc which was how I was referencing it. I think I will just reference the fields numerically for now..

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Hmm, didn't expect that; so if the table name is in each reference and the second instance has a different table name, then a reference like
    rs.Field("tblEmployees.FirstName") might work too.
    Hope your solution works for you! I know it's valid - until you end up modifying the sql and moving the field!
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    In the query you could differentiate by something like
    Select tblEmployees.FirstName as empFirstName, tblCustomer.FirstName as cusFirstName, etc

  9. #9
    sdyk76 is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    13
    Quote Originally Posted by Micron View Post
    Hmm, didn't expect that; so if the table name is in each reference and the second instance has a different table name, then a reference like
    rs.Field("tblEmployees.FirstName") might work too.
    Hope your solution works for you! I know it's valid - until you end up modifying the sql and moving the field!
    That did it! Thank you!

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You're welcome. For the record, an alias in a query or the sql was suggested in post 3.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    The dot is what causes the problem, as with field names with spaces and illegal characters etc you need to use square brackets

    rs![tblEmployees.FirstName]

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 56
    Last Post: 08-08-2020, 03:30 AM
  2. Replies: 7
    Last Post: 03-21-2018, 04:58 AM
  3. Replies: 9
    Last Post: 07-03-2017, 11:24 PM
  4. Replies: 2
    Last Post: 07-29-2015, 07:50 AM
  5. Replies: 4
    Last Post: 12-09-2013, 01:27 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums