Query returns object numbers instead of values

Question

I have an Access database for my new gambling habit. About: Microsoft® Access® for Microsoft 365 MSO (16.0.13929.20206) 32-bit.

Each bet record in tblBets has fields for the bet receipt, date, bet amount, the contestants, etc.

I bet on tennis. I have a tblPlayers with all of the players and their home countries.

When I add a new record to tblBets, fldPlayers is populated with a drop-down list of all the players in tblPlayers. I choose "Roger Federer" or whomever I'm betting on, and also his opponent.

In Access, I have a form that allows me to query tblBets. I often query tblBets to find see which bets are still open (fldPaid is null).

Here is the problem:

That query works fine. I get a beautiful form populated with the bets that are still open, and the contestants in that bet.

BUT:

When I connect to Access from Excel, instead of getting the players' names, I get what is apparently the object number for that player. Instead of getting "Roger Federer" returned in the recordset from the query, I get something like "2397".

So I see Roger's name if I do it from the Form in Access, but I see a bunch of numbers if I do it from VBA in Excel.

What do I do?

The code obtains the records fine. But here it is if you want to see it. the query string comes from a cell in Excel, this was it: "Select * from tblBets where fldPaid is null".

Public Sub AskAccess()


    Dim strDBPath As String
    Dim strProvider As String

    'Creating objects of Connection and Recordset
    Dim connGambling As ADODB.Connection
    Dim rstQryResult As New Recordset
    Dim connString As String
    Dim strQuery As String
    Dim rngOrigin As Range
    Dim intField As Integer
    Dim intFields As Integer
    Dim intRow As Integer
    Dim intRows As Integer
    
    'This is the connection provider.
    strProvider = "Microsoft.ACE.OLEDB.12.0;"
    
    'Path to database located on my local computer, mirrored on OneDrive
    strDBPath = "C:\Users\Craig\OneDrive\My PC in the Cloud\Filing Cabinet\MS Office\Access\Gambling.accdb;Persist Security Info=False;"""
        
    'This is the connection string
    connString = "Provider=" & strProvider & "Data Source=" & strDBPath
    
    'Remember the initial cell selected in the active sheet
    Set rngOrigin = Application.Selection
    strQuery = rngOrigin.Value  'this contains the SELECT statement
    
    Set connGambling = New ADODB.Connection
    
    connGambling.Open ConnectionString:=connString
    
    rstQryResult.Open strQuery, connGambling
    
    'I had a devil of a time determining the RecordCount, so I counted them myself.
    rstQryResult.MoveFirst
    intRows = 0
    Do While Not rstQryResult.EOF
    
        intRows = intRows + 1
        rstQryResult.MoveNext
    
    Loop
    
    If intRows > 0 Then
    
        intFields = rstQryResult.Fields.Count
    
        rstQryResult.MoveFirst
        
        For intRow = 1 To intRows
        
            For intField = 1 To intFields
            
                rngOrigin(intRow + 1, intField).Value = rstQryResult(intField - 1).Value
                
            Next intField
            
            rstQryResult.MoveNext
        
        Next intRow
    
    End If
    
    'closing the connections
    rstQryResult.Close
    connGambling.Close

End Sub

Answer 1

as J7 points out the table actually holds the key value, even though it displays the alpha to you... this is the way Access defaults the set up of a lookup field type or a combobox....trying to be helpful.

depending where you are in your design phase you can alter the lookup and delete out the key field and be sure it binds on the name alpha... however if it is too late to change table structure there - you can add a new field for txtPlayerName and put a little code that puts the text value into this new field when a selection is made...