How to select a row from a pop-up form for record import?

[HSXWillH]

I am trying to import Excel files that contains names and stock id's. The format is something akin to this:

Stock Description -- Players
1991 Topps #145 -- Frank Thomas/Sammy Sosa/Cal Ripken

My 3 tables are

• tblStockHeader with Stock_ID (auto#), Stock_Description
• tblPlayerHeader with Player_ID (auto#), Player_Name, Player_DOB
• tblStockPlayer with SPlayer_ID (auto#), Stock_ID (long), Player_ID (long)

My goal is to create lines in tables that will normaize the data involving the players as more than 1 can show up in the Excel file for the same Stock_ID. I've gotten that far with no problems so I'm more challenged by there might 2 players named the same thing (think 1960's Frank Thomas and 1990's Frank Thomas) and my Excel files won't know which is which.

So what I'm trying to do is when I import, VBA will dcount the # of Player_Names that match and if it's 0, it'll add the player to tblPlayerHeader and then add the player_id to tblStockPlayer, if it's 1, it'll just add the player_id to tblStockPlayer and if it's 2 or more, it'll open a form of potential matches for me to select which one to then add to the tblStockPlayer.

I truly don't know what direction to go in order to get the form of options to popup and then select/carry back to my original vba process the selected data.

Here is what I have. Please note that GetCSWord(strAString, I) is the broken-out Player_Name from the multiple potentials.

 
For I = 1 To intCnt
 
searchcount = DCount("[Player]", "TestPlayerHeader", "[Player_Name] =""" & GetCSWord(strAString, I) & """")
 
    Select Case searchcount
        Case 0
            MsgBox "Need a new record"
        Case 1
            MsgBox "Only Record"
        Case Is > 1
            DoCmd.OpenForm "TestPlayerHeader", acFormDS, , "[Player_Name] =""" & GetCSWord(strAString, I) & """"
    End Select
 
    Next

[HSXWillH]

Repost: I've gone in a different direction and have some sample data that I'm not able to get DLOOKUP to work properly with.

My table, TestPlayerHeader has the fields of Player_ID (auto random #), Player (text) and DOB (date). Player and DOB are keys to limit unique records.

I have put some sample information in.

#### --- Edison --- 1/1/2000
#### --- Jones --- 1/1/2000
#### --- Jones --- 1/1/2001

My code to pull and find the Player_ID of the player + his DOB is as follows. I keep getting an Run-Time Error #6 -- Overflow error but there is not more than 1 record being pulled based on the conditions.

DoCmd.OpenForm "TestPlayerHeader", acFormDS, , "[Player] =""" & GetCSWord(strAString, I) & """"
searchdob = InputBox("Enter a birth date.", "Birthdate Entry")
searchid = DLookup("[Player_ID]", "TestPlayerHeader", "[Player] ='" & GetCSWord(strAString, I) & "' AND [DOB] =#" & searchdob & "#")
MsgBox searchid

What coding am I doing wrong here? I want the unique Player_ID and there should be only an unique one based on the information I've entered.