CODEWITHSUNDEEP
ms-accessdatabase-designvba
Josell
Josell

Reputation: 1944

How to use Access' OpenRecordset to get a record using its numeric key field?

I have a problem in Access 2010 VBA trying to read a record from table to write it into TextBoxes.

My table is "Products" and its ProductID field is numeric. I used this method before, but it just works for text fields, not for numeric fields (ProductID is autonumber).

Private Sub GetProduct(ID As TextBox, Name As TextBox, Price As TextBox)
    If ID <> "" Then
        Set db = CurrentDb
        Set rs = db.OpenRecordset("Productos", dbOpenDynaset)

        'PROBLEM IS HERE
        rs.FindFirst "ProductID=" & "'" & ID & "'"

        If rs.NoMatch Then
            MsgBox "The producto doesn't exist."
            Price = ""
            Name = ""
        Else
            Name = rs!ProductName
            Price = rs!Price
        End If

        rs.Close
        Set rs = Nothing
        Set db = Nothing
    End If
End Sub

Please, help me. This is for a Final Proyect and I don't know other, but this method. Please help me.

Upvotes: 0

Views: 18889

Answers (2)

Rokhi
Rokhi

Reputation: 46

Good to see you figured it out. The problem is that ProductID is numeric and your code specifically tests for a text field

    rs.FindFirst "ProductID=" & "'" & ID & "'"

putting a single quote each side of the parameter makes Access parse the parameter as a string.

If ID is 123, this will read 

    rs.FindFirst "ProductID='123'"

and you get a type error

Upvotes: 2

Josell
Josell

Reputation: 1944

I found a simple solution!

    rs.FindFirst "ProductoID=" & ID

Upvotes: 0

Related Questions