Results 1 to 2 of 2
  1. #1
    Join Date
    May 2013
    Posts
    5

    Unanswered: Check Syntax Of SQL Select Statement In Access VBA Code, Access 2007

    I have added a text box on my EmpDate form to display the Employee Name. The field is called EmpName. I have a relationship set up between the EmpDate table and the Employees table connected by the EmpID field. The EmpName is stored in the Employees table.

    When I do the data entry with the EmpDate form the name displays fine.

    When I use the Search Button I do not get the results I want.

    See Attachment for Screen Captures.

    Here is my code:

    Form_EmpDate.RecordSource = "Select * from EmpDate where [EmpDate].DateWorked = " & _
    Format(Me!txtSearchDateWorked, "\#mm\/dd\/yyyy\#") & _
    " And [EmpDate].EmpID = " & Me!txtSearchEmpID


    I tried the following code:

    Form_EmpDate.RecordSource = "Select * from EmpDate, Employees where [EmpDate].DateWorked = " & _
    Format(Me!txtSearchDateWorked, "\#mm\/dd\/yyyy\#") & _
    " And [EmpDate].EmpID = " & Me!txtSearchEmpID

    And this code almost works (the Employee Name displays) but now the EmpID doesn’t display correctly and the record count at the bottom gives an error.

    I have attached the database and screen captures.

    I know this must be fairly easy to do so if someone could take a look I would appreciate it.
    Attached Files Attached Files

  2. #2
    Join Date
    May 2013
    Posts
    5
    Thanks for all the help and advice on here. Because of it it made me think a little harder and I sovled it. I remember being told that it is the way I am joining the tables.
    Then I read the reason you get the error #Name? in a field is because the field specified in the control's ControlSource property may havebeen removed from the underlying table or record source.
    Since it works fine when I do data entry, and only gives me this error when I use this same form for the search, I realized that when the search is performed it is loosing the join to this table.
    SO I copied the SELECT statement for the forms RECORD SOURCE property and examined it and saw the way I should be joining the tables in my search code. The result was the following:

    Form_EmpDate.RecordSource = "SELECT EmpDate.*, Employees.EmpName FROM Employees INNER JOIN EmpDate ON Employees.EmpID=EmpDate.EmpID where [EmpDate].DateWorked = " & _
    Format(Me!txtSearchDateWorked, "\#mm\/dd\/yyyy\#") & _
    " And [EmpDate].EmpID = " & Me!txtSearchEmpID

    The form now works in both the data entry and search modes.

    Again thanks for all the input.

Tags for this Thread

Posting Permissions

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