Results 1 to 3 of 3
  1. #1
    sukhjinder's Avatar
    sukhjinder is offline Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    43

    Search criteria of two combo boxes

    Following is my code i have used two combo boxes for the search in the form. The code works fine in the VBA but the problem arises as follows




    Code:
    SELECT tbl_item.itemID_PK, tbl_item.item, tbl_category.category, tbl_itemtype.type, tbl_perbelongsto.pname, tbl_rooms.roomname
    FROM  tbl_rooms INNER JOIN (tbl_perbelongsto INNER JOIN (tbl_itemtype INNER  JOIN (tbl_category INNER JOIN tbl_item ON tbl_category.[categoryID_PK] =  tbl_item.[categoryID_FK]) ON tbl_itemtype.[itemtypeID_PK] =  tbl_item.[itemtypeID_FK]) ON tbl_perbelongsto.[itembelongtoID_PK] =  tbl_item.[itembelongtoID_FK]) ON tbl_rooms.room_PK =  tbl_item.[roomID_FK]
    WHERE  (((tbl_itemtype.type)=[Forms]![frm_item_entry]![]) AND  ((tbl_perbelongsto.pname)=[Forms]![frm_item_entry]![cbobelongto]));
    in the combo boxes of the frm_item_entry it linked primary ID with 0cm width and shows items name only bound to column one
    the items are
    PK item
    1 A
    2 b
    3 c
    4 all item

    so i want to select "all item" in the combo box it does not work. I dim selectpk as integer and make the if statement if selectPK=4 then the string should display all the record as mentioned in combo box one and anly those record equal to feild in the second combo box.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you should have done it different,....
    if the combo box is empty, show all records.
    if the combo box has a value, filter ONLY that item.

    Code:
    If IsNull(cboBox) Then
      Me.FilterOn = False
    Else
      Me.Filter = "[panel]='" & cboBox & "'"
      Me.FilterOn = True
    End If

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    No VBA code is posted - only the SQL of a query. What is the code?

    "Type" is a reserved word in Access and shouldn't be used as an object name. Plus it is not very descriptive.... "type" of what???

    And the query has problems. You are missing the control name of the first field of the WHERE clause of the SQL statement:
    Code:
    WHERE  (((tbl_itemtype.type)=[Forms]![frm_item_entry]![]) AND  ((tbl_perbelongsto.pname)=[Forms]![frm_item_entry]![cbobelongto]));

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 09-22-2016, 08:42 AM
  2. Replies: 3
    Last Post: 01-04-2015, 06:09 PM
  3. Replies: 11
    Last Post: 08-06-2014, 09:47 AM
  4. Replies: 9
    Last Post: 06-04-2014, 10:45 AM
  5. Search using combo boxes
    By jakeao in forum Access
    Replies: 0
    Last Post: 05-18-2011, 12:17 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums