Excel userform and MS Access

[Pookiemeister]

I'm trying to populate a userform based on an employee number, but I am not at that point yet. Right now, I'm just trying to get their first name based on their number. Unfortunately, my code doesn't work. As soon as I start typing in their employee number, the following line of code appears in the "frmPersonnelEntry.txtFN" textbox:

SELECT [First Name] FROM Personnel WHERE [Employee #] =

but after the equal sign is the value in frmPersonnelEntry.txtEmpNum.Text.

So basically, what I am wanting, as the user types in the employee number, vba keeps looking into the database until the record is found.

Example:
Employee #: 12345
Employee First Name: John

ENTRY:
frmPersonnelEntry.txtEmpNum.Text = 1 'NOTHING FOUND
frmPersonnelEntry.txtFN.Text =

frmPersonnelEntry.txtEmpNum.Text = 12 '1 is already there NOTHING FOUND
frmPersonnelEntry.txtFN.Text =

frmPersonnelEntry.txtEmpNum.Text = 123 '12 is already there NOTHING FOUND
frmPersonnelEntry.txtFN.Text =

frmPersonnelEntry.txtEmpNum.Text =1234 '123 is already there NOTHING FOUND
frmPersonnelEntry.txtFN.Text =

frmPersonnelEntry.txtEmpNum.Text = 12345 '1234 is already there RECORD FOUND
frmPersonnelEntry.txtFN.Text = John

I hope my explanation of what I'm wanting to achieve makes sense. I'm familiar with using MS Access syntax within Excel VBA. Thank you.

Private Sub txtEmpNum_Change()
    Call FindRecordInAccess
End Sub

Sub FindRecordInAccess()

    'Path
    Dim strPath As String

    'Provider
    Dim strProv As String

    'Connection String
    Dim strConn As String

    'Connection
    Dim Conn As New Connection

    'RecordSet
    Dim reQry As New Recordset

    'SQL Query
    Dim strQry As String

    strPath = "C:\Users\123456\OneDrive\Documents\Personnel.accdb"
    strProv = "Microsoft.ACE.OLEDB.12.0;"
    strConn = "Provider=" & strProv & "Data Source=" & strPath

    'Connection Open
    Conn.Open strConn

    strQry = "SELECT [First Name] FROM Personnel WHERE [Employee #] =" & frmPersonnelEntry.txtEmpNum.Text
    Debug.Print strQry
   
    frmPersonnelEntry.txtFN = strQry

'More code later

End Sub

 

[RoryA]

You haven't done anything about actually executing the query. All your code does is to put the query text into the textbox. You need to set and use your recordset - something like:

set reQry = conn.execute strQry
frmPersonnelEntry.txtFN = reQry(0)