Returning the wrong record value

[littleclayjar]

I have an Access table with the fields "Name" and "IDNum". I need to write a vba module that returns the corresponding "IDNum" when i input the "Name" into the InputBox. So far I have the code below, but it only returns the first "IDNum" record in the table for whatever "Name" I type in. What am I missing in the code below? Thanks!

Sub SupplierInfo()
Dim TableName As String
TableName = "tblSuppliers"

Dim SupplierDB As Database, SupplierTbl As Recordset
Set SupplierDB = DBEngine.Workspaces(0).Databases(0)
Set SupplierTbl = SupplierDB.OpenRecordset(TableName, dbOpenSnapshot)
Dim Ask As String
Dim Msg As String
Dim ID As String


Msg = "What is the name of the supplier?"
Ask = InputBox(Msg, "SupplierName")
ID = SupplierTbl!SupplierID

MsgBox "The Supplier ID is " & ID


End Sub

 

[TheAceMan1]

How are ya littleclayjar . . .

[blue] . . . but it only returns the first "IDNum" record in the table for whatever "Name" I type in.[/blue]

Try the code below. An SQL statement is used instead to return one or all ID's with the name. Also [blue]you![/blue] substitute proper names/values in [purple]purple[/purple]:

[blue]   Dim db As DAO.Database, rst As DAO.Recordset, SQL As String
   Dim Msg As String, Search As String
   
   Search = InputBox("What is the name of the supplier?", "SupplierName")
   
   If Search = "" Then
      MsgBox "No User Reply!"
   Else
      Set db = CurrentDb
      SQL = "SELECT [purple][b]SupplierID[/b][/purple], [purple][b]SupplierName[/b][/purple] " & _
            "FROM tblSuppliers " & _
            "WHERE ([[purple][b]SupplierName[/b][/purple]] = '" & Search & "') " & _
            "ORDER BY [[purple][b]SupplierName[/b][/purple]];"
      Set rst = db.OpenRecordset(SQL, dbOpenSnapshot)
      
      If rst.BOF Then
         MsgBox "Name not found!"
      Else
         Do
            Debug.Print rst![purple][b]SupplierID[/b][/purple]
            rst.MoveNext
         Loop Until rst.EOF
      End If
   End If[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[TheAceMan1]

BTW littleclayjar: Welcome to Tek-Tips

To get great answers be sure to have a serious look at FAQ219-2884 or FAQ181-2886

See Ya! . . . . . .

 

[littleclayjar]

Thank you for the help and welcome!

I didn't even think of using a SQL statement at all so this was helpful. I ended up trying out a FindFirst method but I'm always open to seeing other methods. Learn more that way. =)