Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2015
    Posts
    20

    Unanswered: VBA SQL Code to filter text box

    I'm having a hard time getting my vba code to work right.

    I have a form which contains two text boxes. Building ID and Room ID. txtBuildingID and txtRoomID. The form is built based on a query. txtBuildingID is bound to tblBuildings.buildingFK and txtRoomID is bound to tblRooms.RoomFK.

    I am trying to filter these two text boxes based on a search form which searched for customer's last name. As customer last name is not located anywhere on the form, I am using a SELECT statement. My code is this:

    Code:
    Private Sub cmdSearch_Click()
    Dim sqlSearch As String
    sqlSearch = "SELECT tblFacilityMgr.BuildingFK, tblFacilityMgr.CustomerFK, tblCustomer.LastName, tblCustomer.FirstName FROM " _
               & " (tblCustomer INNER JOIN tblFacilityMgr ON" _
               & " tblCustomer.CustomerPK = tblFacilityMgr.CustomerFK)" _
               & " WHERE tblCustomer.LastName ='" & Me.cboSearchLastName & "'"
    
    Me.Filter = sqlSearch
    Me.FilterOn = True
    I keep getting runtime error 3075, invalid syntax. It doesn't like my select statement. I'm not sure if the select statement is wrong, or I'm applying the filter wrong. I do know that the filter needs to be applied to txtBuildingID. And thats not happening right now. I think it needs to be something like sqlSearch = me.txtBuildingID = SELECT.....

  2. #2
    Join Date
    Aug 2015
    Posts
    20
    I figured it out. I needed to set the recordsource to sqlSearch.

    Here is my code:

    Code:
    Private Sub cmdSearch_Click()Dim sqlSearch As String
    If Not IsNull(Me.cboSearchLastName) Then
    sqlSearch = "SELECT tblCustomer.LastName, tblRoomsPOC.CustomerFK, tblRoomsPOC.RoomsFK, tblRooms.BuildingFK FROM" _
            & " tblCustomer INNER JOIN (tblRooms INNER JOIN tblRoomsPOC ON tblRooms.RoomsPK = tblRoomsPOC.RoomsFK) ON" _
            & " tblCustomer.CustomerPK = tblRoomsPOC.CustomerFK" _
            & " WHERE LastName ='" & Me.cboSearchLastName & "'"
            End If
    Me.RecordSource = sqlSearch
    End Sub
    I have two text boxes, txtBuildingID and txtRoomID. txtBuildingID is bound to BuildingFK. txtRoomID is bound to RoomsFK. If I search for a last name, I see all the records for rooms owned by that last name. At the bottom of the form, I can scroll through the records. Record 1 of 10, as an example. It works great!

    But life is not without problems, it seems. I also need to filter by facility manager. I want to be able to filter both by RoomsPOC and by Facility Manager.

    I've tried using two search criteria using using AND between them. But that requires two separate SQL statements. Which doesn't allow me to bind txtRoomsID to RoomsFK because RoomsFK doesn't exist in the second SQL statement. How can I fix this?


    Code:
    Private Sub cmdSearch_Click()Dim sqlSearch As String
    If Not IsNull(Me.cboSearchLastName) Then
    sqlSearch = "SELECT tblCustomer.LastName, tblRoomsPOC.CustomerFK, tblRoomsPOC.RoomsFK, tblRooms.BuildingFK FROM" _
            & " tblCustomer INNER JOIN (tblRooms INNER JOIN tblRoomsPOC ON tblRooms.RoomsPK = tblRoomsPOC.RoomsFK) ON" _
            & " tblCustomer.CustomerPK = tblRoomsPOC.CustomerFK" _
            & " WHERE LastName ='" & Me.cboSearchLastName & "'AND"
            End If
    If Not IsNull(Me.cboSearchLastName) Then
    sqlSearch = "SELECT tblFacilityMgr.BuildingFK, tblFacilityMgr.CustomerFK, tblCustomer.LastName FROM" _
               & " (tblCustomer INNER JOIN tblFacilityMgr ON" _
               & " tblCustomer.CustomerPK = tblFacilityMgr.CustomerFK)" _
               & " WHERE LastName ='" & Me.cboSearchLastName & "'"
               End If
    Me.RecordSource = sqlSearch
    End Sub

Posting Permissions

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