Thanks so much for your help ssanfu - it is greatly appreciated.
Will keep those things in mind for next time.
Thanks so much for your help ssanfu - it is greatly appreciated.
Will keep those things in mind for next time.
Hi ssanfu, its me again. Thank you again for creating a relationship between my category combo box and my search box.It is difficult to give a good answer when the object names and table relationships are unknown (for whatever reason).
Feels like I'm one of the 3 blind mice, touching the elephant's foot and trying to describe what the elephant looks like.
I'm not sure what you want as far as searching, so maybe this will be of help.
With 2 controls, there are 4 options
1) Both controls Null,
2) cboCatagory control filled in, text box control Null
3) text box control filled in, cboCatagory control Null
4) Both controls filled in.
The search code deals with all 4.
When the form opens, you can see all of the terms I entered.
Because of the wild card character, if you search for Male, Female is also returned.
I have another question to ask you. Would it be the same concept if I were to add in a list box which there would be a relationship to the combo box and then to the textbox?
So the priority would be tapered such that it would be List Box --> Combo Box --> Search Keyword --> then results pop up.
Additionally I would also like to have the listbox be on its own and/or have the search keyword option be related to the listbox if possible.
I am not sure you understand what is happening in the code.
You have a combo box (cbo) and a text box (tb). They have no relationship - they are completely independent of each other. The code looks at the two controls and determines if there are values entered into either/both if the controls.
The cbo is only compared with one field in the table; the tb is compared with the other fields listed.
If there is a value entered in the control, enter a 0, else enter a 1.
With 2 controls, there are 4 options.
cbo
tb
0 0 0 1 1 0 1 1
With 3 controls (lst = list box, cbo = combo box, tb = text box), there are 8 options to code for:
lst
cbo
tb
0 0 0 0 0 1 0 1 0 0 1 1 1 0 0 1 0 1 1 1 0 1 1 1
If the controls are independent of each other (a value in a control is NOT dependent on another control), the code has to account for each option.
If the cbo is dependent on a lst selection (dependent or cascading) the you are back to two controls/ 4 options.
After all of this, the answer to your question is "Yes, iit is/could be the same concept".
But, again, the controls are independent of each other. And you have to determine what fields each of the controls are looking at.
In this case, you would require all 3 controls to be filled for each search. Then you have one condition (plus the error message if 1 or more controls are left blank).
You lost me here.... explain what you want to happen in each case........ give examples..
vha7_Demo2 (2).zipQUOTE=ssanfu;406236]
With 3 controls (lst = list box, cbo = combo box, tb = text box), there are 8 options to code for:
lst
cbo
tb
1 1 1 1 1 1 1 1 1 1 1 1
If the controls are independent of each other (a value in a control is NOT dependent on another control), the code has to account for each option.
If the cbo is dependent on a lst selection (dependent or cascading) the you are back to two controls/ 4 options.
[/QUOTE]
Hi ssanfu, I understood your code when you explained it now - and the above is exactly what I am searching for. Do you mind helping me with the code for this - an example with an additional listbox and/or checkbox option? I have attached your demo database with the additional ad ons.
Notably, I want the listbox and checkbox to be "values" instead of a query and what not, similar to the concept of the combo drop box where it uses the LIKE operator to search the key term across all fields/columns.
EDIT1: Nvm, I somehow managed to get my code and search form to work!!
Last edited by vha7; 08-20-2018 at 01:40 PM.
Happy you got it to work. Would be interested in seeing the code.
BTW, you should get rid of ALL spaces in object names - fields, tables & forms.This will save you lots of grief.
Pretty much it worked out by accident. I asked for help for another code for a multi-select listbox to apply to the same table and fortunately, I was just trying to figure out how I could link everything like you said and then I noticed my count box for the number of results was tapered and smaller than usual and I realized it filtered my list box based on the category and search keyword I have chosen.
But heres the code:
Module code:Code:Private Sub List63_AfterUpdate() Dim strList As String Dim strSplit As Variant Dim strFilter As String Dim i As Integer strList = getLBX(Me.List63) 'iterate listbox and get string of values Me.fGetlbx = strList 'just to display on sample form strSplit = Split(strList, ",") 'split the list of values For i = 0 To UBound(strSplit) strFilter = strFilter & "[Reference Citation] like '*" & strSplit(i) & "*' Or " 'construct strFilter Next i If Len(strFilter) > 0 Then strFilter = Left(strFilter, Len(strFilter) - 3) 'trim off trailing "Or" End If Me.txtFilter = strFilter 'just to display on sample form 'Apply the filter If Me.List63.ItemsSelected.Count > 0 Then Me.subPublications2.Form.Filter = strFilter Me.subPublications2.Form.FilterOn = True Else Me.subPublications2.Form.FilterOn = False End If End Sub
Code:Option Compare Database Option Explicit Public Function getLBX(Lbx As ListBox, Optional intColumn As Variant = 0, Optional Seperator As String = ",", _ Optional Delim As Variant = Null) As String 'Iterates thru the multiselect listbox and constructs a string of the selected items 'Arguments: 'Lbx is Listbox Object ie.Me.MyListbox 'intColumn is the column # to be returned 'Seperator is the character seperating items in string returned 'Delim is optional delimiter to be return in string ie. #1/1/2001#,#12/25/2015# Dim strList As String Dim varSelected As Variant On Error GoTo getLBX_Error If Lbx.ItemsSelected.Count = 0 Then 'MsgBox "Nothing selected" Else For Each varSelected In Lbx.ItemsSelected strList = strList & Delim & Lbx.Column(intColumn, (varSelected)) & Delim & Seperator Next varSelected strList = Left$(strList, Len(strList) - 1) 'remove trailing comma End If getLBX = strList On Error GoTo 0 Exit Function getLBX_Error: MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure getLBX of Module modLBX" End Function Public Sub SelectLBX(Lbx As ListBox, strIN As String) 'Clears the multiselect listbox and then selects the values in junction table On Error GoTo SelectLBX_Error Call ClearList(Lbx) Dim varItm As Variant Dim i As Integer Dim y As Integer varItm = Split(strIN, ",") For y = 0 To UBound(varItm) For i = 0 To Lbx.ListCount - 1 If Lbx.ItemData(i) = varItm(y) Then Lbx.Selected(i) = True Exit For End If Next i Next y On Error GoTo 0 Exit Sub SelectLBX_Error: MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure SelectLBX of Module modLBX" End Sub Function ClearList(lst As ListBox) As Boolean On Error GoTo Err_ClearList 'Purpose: Unselect all items in the listbox. Dim varItem As Variant If lst.MultiSelect = 0 Then lst = Null Else For Each varItem In lst.ItemsSelected lst.Selected(varItem) = False Next End If ClearList = True Exit_ClearList: Exit Function Err_ClearList: Resume Exit_ClearList End Function Public Function SelectAll(lst As ListBox) As Boolean On Error GoTo Err_Handler 'Purpose: Select all items in the multi-select list box. Dim lngRow As Long If lst.MultiSelect Then For lngRow = 0 To lst.ListCount - 1 lst.Selected(lngRow) = True Next SelectAll = True End If Exit_Handler: Exit Function Err_Handler: Resume Exit_Handler End Function
For the routines "Function ClearList" and "Function SelectAll", I do it a little different.
I have 2 buttons: "Clear Selection" and "Reverse Selection".
The code for the buttons is
The code for the sub "DoList" isCode:Private Sub cmdReverse_Click() Call DoList("R", "lstEmp") End Sub Private Sub cmdClear_Click() Call DoList("C", "lstEmp") End Sub
If there are no selections in the list box and you click the Reverse button, All options are selected. If you then un-select one (or more) options and click the Reverse button, the select state gets reversed.Code:Sub DoList(psAction As String, psTLD As String) Dim theList As Control, n As Long Set theList = Me(psTLD) Select Case psAction Case "C" 'Unselect all items in the listbox. For n = 0 To theList.ListCount theList.Selected(n) = False Next Case "R" 'reverse select of items in the listbox. For n = 0 To theList.ListCount theList.Selected(n) = Not theList.Selected(n) Next End Select End Sub
Or you want all selected except one option: click the Clear button, select the one option you DO NOT want, then click the reverse button.
In any case, good luck with your project......