Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2009
    Posts
    5

    Unanswered: Form in Access to find record (by last name) in Oracle...

    I'm trying to use a combobox in a form in Access to find a record by last name in Oracle. The combobox is already populated by lastname, firstname, EmployeeID. However...when I click on a last name in the combobox, I get an error. I have the same thing set up to find a record with a combobox by employee id and it works like a charm. Here's the code for the combobox....anybody see what I'm doing wrong?


    Private Sub cmbFindByLastName_Click()

    Dim rs As ADODB.Recordset
    Dim strSQL As String

    strSQL = "SELECT Employee_ID, First_Name, Last_Name, Email, Phone_Number, Hire_Date, Job_ID, Salary, Commission_Pct, Manager_ID, Department_ID FROM HR_Employees WHERE Last_Name=" & Me.cmbFindByLastName & ";"

    Set rs = New ADODB.Recordset
    rs.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

    Me.cmbFindByLastName = ""
    Me.txtEmployeeID = rs("Employee_ID")
    Me.txtFirstName = rs("First_Name")
    Me.txtLastName = rs("Last_Name")
    Me.txtEMail = rs("Email")
    Me.txtPhoneNumber = rs("Phone_Number")
    Me.txtHireDate = rs("Hire_Date")
    Me.txtJobID = rs("Job_ID")
    Me.txtSalary = rs("Salary")
    Me.txtCommission = rs("Commission_Pct")
    Me.txtManagerID = rs("Manager_ID")
    Me.txtDepartmentID = rs("Department_ID")


    End Sub

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Try

    strSQL = "SELECT Employee_ID, First_Name, Last_Name, Email, Phone_Number, Hire_Date, Job_ID, Salary, Commission_Pct, Manager_ID, Department_ID FROM HR_Employees WHERE Last_Name='" & Me.cmbFindByLastName & "';"
    Paul

  3. #3
    Join Date
    Dec 2009
    Posts
    5
    Ha, ha, ha....oh I don't believe it. Just two apostrophe's. Unbelievable. Thanks Paul.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    string/text/char columns when used in a where clause or values on an insert or update MUST be encapsulated with either a ' or ".. depends in the SQL engine.
    numeric columns should be left as is

    eg
    select my,column,list from mytable where mytextcolumn like "%Miller%"
    update mytable set userid="Sarah Miller" where userid="SarahMiller"
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •