How to filter using combo box

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • farahani
    New Member
    • Aug 2016
    • 2

    #1

    How to filter using combo box

    Can anyone help me to fix my filtering using combo box in the frmBookingFilte r. The selection of combo box must come from tblRoom. Need to filter all the information in tblBooking based on tblRoom.Room.

    i have 2 table
    1) tblRoom (Room ID, Room, Building, Level, Ext)
    2) tblBooking (Booking ID, BookingDate, Room, StartTime, EndTime, UserName, UserExt, Purpose, Chairman)

    In the tblRoom, the number of room has been fixed. No addition of room in future (8 rooms). Thus, i would like to filter the records in tblBooking using the Room information.

    I have created 2 forms, frmBookingEntry and frmBooking Filter.

    p/s: i would like to attch my database, but i don't know how.

    Thanks in advance
  • Tyler1394
    New Member
    • Jul 2016
    • 18

    #2
    On your form, create the combo box. Use the combo box to grab information from your tblRoom.

    If you only have the 2 tables created individually, you will need to link them together through another table with a relationship.

    Once they're linked, you can look up the value from one table, and it will display a linked value in the second table.


    your combo box = cbotblRoom '(combo box tblRoom)


    Code:
    Private Sub cbotblRoom_AfterUpdate()
    Dim myroom As String
    myroom = " Select * from NameOfConnectingTable where ([FieldNameOftblRoomInConnectingTable] = " & cbotblRoom & ")"
    Me.NameofConnectingTable_subform.Form.RecordSource = myroom
    Me.NameOfConnectingTable_subform.Form.Requery
    End Sub
    This goes into your table, and under your combo box you're creating a variable called myroom.
    Youre combo box pulls values from the linked table of tblRoom, and then selects that value from your 'connecting table' and displays the result.

    Hope this helps!

    Comment

    • farahani
      New Member
      • Aug 2016
      • 2

      #3
      i've tried, not functioning.

      i have 2 table
      1) tblRoom (RoomID, Room) PK:RoomID
      2) tblBooking (Booking ID, BookingDate, RoomID, StartTime, EndTime, UserName, UserExt, Purpose, Chairman)PK:Boo kingID FK:RoomID

      Using One-to-many relationship between two table RoomID-RoomID

      Current Queries:

      1) qryBookingList
      Code:
      SELECT tblBooking.BookingID, tblBooking.BookingDate, tblRoom.RoomID, tblRoom.Room, tblBooking.StartTime, tblBooking.EndTime, tblBooking.UserName, tblBooking.UserExt, tblBooking.Division, tblBooking.Section, tblBooking.Purpose, tblBooking.Chairman
      FROM tblRoom INNER JOIN tblBooking ON tblRoom.RoomID = tblBooking.RoomID;
      Currently, i created 4forms:
      1)frmBookingEnt ry - To add new data
      2)frmBookingLis t - To view ALL Booking for all Room. Using a query named as 'qryBookingList ' to show all data.
      3)frmBookingFil ter - As a single form, that contain a subform 'sbfrmBookingFi lter'. The combobox named as 'cbotblRoom' will be at this form.
      4)sbfrmBookingF ilter - a subform that will show filtered information when select the combobox

      Will u suggest a better way?
      Attached Files

      Comment

      • Tyler1394
        New Member
        • Jul 2016
        • 18

        #4
        Yeah okay,

        So when you make your combobox - grab the values you want to grab from tblroom and it sounds like you'll be grabbing the RoomID as an integer but you can also pass through the names and search it by name.

        Regardless,

        after you make the combo box, your row source under the data tab in properties should read something like

        Code:
        SELECT [Roomtbl][RoomID] 
        FROM Roomtbl;
        Under the event tab create an event procedure under the after update option

        Assuming the name of the combobox is cbosearchroom

        Code:
        Private Sub cbosearchroom_AfterUpdate()
        Dim searchvariable as string
        searchvariable = "Select * from tblbooking where ([RoomID] = " & cbosearchroom & " )"
        Me.frmBookingList.Form.RecordSource = searchvariable 
        Me.frmBookingList.Form.Requery
        ** the me.frmbookingli st may be the wrong form - but its me.__whatever form youre searching from thats linked to your name table__.Form.Re cordSource.

        Finally,
        if the roomID information you grab is only an integer the above code should work.

        If it is a string, you will need to put '' around "&cbosearchroom &"

        so it looks like

        "______ ([RoomID] = ' " & cbosearchroom & " ')"

        Hope that helps

        Comment

        Working...