SQL query to pull the values for a record from table?-CodePudding

So im basically trying to use a customer ID to pull customer information like name, address, phone etc. from a table to be used to pre-fill certain textboxes in the form. I have it linked to a button press that searches another table called CustomerID to see if the phone number exists in the "phone" column and if so returns the cusID associated with it, i do this using Dlookup which seems to work. Then I want to use the cusID to find the corresponding customer information from the table CustomerInfo using the SQL query below but it just throws up a "Run-time elso rror '13' Type mismatch" on the SQL query line. I've tried a bunch of different formatting for it and cannot seem to get it to work, any one know what i'm doing wrong? Also is it even possible to parse the SQL query data into individual textboxes? Also code is below

Private Sub querycust_Click()
phonesearch = DLookup("cusID", "CustomerID", "phone='" & 2506176974# & "'")
If IsNull(phonesearch) Then
    MsgBox ("No Customer ID Exists For This Phone Number")
    
ElseIf Not IsNull(phonesearch) Then
    DoCmd.RunSQL "SELECT " * " FROM CustomerInfo WHERE [CustomerInfo]![CusID]=phonesearch"
    


End If


End Sub

CodePudding user response：

Try something like this:

Private Sub querycust_Click()

    Dim Records As DAO.Recordset
    Dim Sql As String

    Sql = "Select * From CustomerInfo Where phone = '2506176974'"
    Set Records = CurrentDb.OpenRecordset(Sql)
    If Records.RecordCount = 0 Then
        MsgBox "No Customer ID Exists For This Phone Number"
    Else
        Me!txtCustomerID = Records!cusID.Value
        ' More controls and values.
    End If
    Records.Close

End Sub

CodePudding user response：

You can do this:

Private Sub querycust_Click()
   cusID = DLookup("cusID", "CustomerID", "phone='" & 2506176974# & "'")
   If IsNull(cusID) Then
      MsgBox ("No Customer ID Exists For This Phone Number")    
   Else
      me.RecordSource = "SELECT * FROM CustomerInfo WHERE CustomerID = " & CusID    
   end if
end Sub

so above will load up the form and all the textboxes etc with the data from that one sql statement.