ComboBox multiple selection

[tonycl69]

Hi all, is it possible to use a combo box to search in multiple fields and display a record?
I am using the combo box wizard to search for records in a table on a form and display that record, but the table has multiple columns that may have info that needs to be displayed, for example:

field 1=registration, field 2=special code, field 3= plant registration.



When I receive a job card for a service etc the workshop sometimes use the plant registration or the registration and sometimes the special code, so I need to be able to type in the combo box any one of the three and display that record.

Please feel free to ask any questions if you do not understand.

[ranman256]

You need code to look at all 3 combos to create the filter.

sub combo1_afterupdate()
  FilterItems
end sub

sub combo2_afterupdate()
  FilterItems
end sub

sub combo3_afterupdate()
  FilterItems
end sub

sub FilterItems()
dim sWhere as string

if not IsNull(combo1) then    sWhere =sWhere & " and [field1]='" & combo1 & "'"
if not IsNull(combo2) then    sWhere =sWhere & " and [field2]='" & combo2 & "'"
if not IsNull(combo3) then    sWhere =sWhere & " and [field3]='" & combo3 & "'"
  
if sWhere ="" then
   me.filterOn = false
else
   'remove 1st 'and '
   sWhere = mid(sWhere,5)  
   me.filter = sWhere
   me.filterOn = true
endif

end sub

[tonycl69]

Hi Ranman256, thanks for you reply, however I am only using one combo with all three fields, should I be creating three combos? Sorry but a little confused here.

[CJ_London]

try

sWhere ="[field1]='" & combo1 & "' OR [field2]='" & combo1 & "' OR [field3]='" & combo1 & "'"

[tonycl69]

Thanks but it is still only looking up the first column, I want the search criteria to look in all three columns and return the record.

[ranman256]

Why would the same data be in 3 columns?

[CJ_London]

Thanks but it is still only looking up the first column

The OR means it will look in all 3 columns - what is your actual code you are using

[tonycl69]

Private Sub Combo53_AfterUpdate()
DoCmd.SearchForRecord , "", acFirst, "[Unitid] = " & Str(Nz(Screen.ActiveControl, 0))
sWhere = "[unit]='" & Combo53 & "' OR [murphycode]='" & Combo53 & "' OR [plant registration]='" & Combo53 & "'"
End Sub

[CJ_London]

now totally confused - your searchforrecord will only find the first record on unitid and you have just assigned what we advised to a string called swhere and is not used in your searchforrecord and you have ignored Ranmans additional code.

Further your searchforrecord is searching on a field called unitid and your swhere is referencing a field called unit. I'm assuming this is a typo

Private Sub Combo53_AfterUpdate()
 Dim sWhere as string

     sWhere = "[unit]='" & Combo53 & "' OR [murphycode]='" & Combo53 & "' OR [plant registration]='" & Combo53 & "'"
     DoCmd.SearchForRecord , "", acFirst, sWhere
     
  End Sub

[tonycl69]

Hi and thanks for that, I realise your confusion but I was not using three combo boxes just one, as you can probably tell I am very new to coding.