Lookup Combo on form

[MichaelWaimauku]

I can add a combo box to the header to lookup an item on the main form, however, how do I get all records to be returned for a specific item if there are a number of them?

E.g. 25 records of which 3 have the item code NLA2222011. Currently, the lookup will just show all 25 records in a list, of which, 3 have the NLA2222011 code. I want all 3 to be returned so I can look at all 3 and not just the one I happen to select.

 

[Isskint]

adjust the VBA used to 'Find' an item on your form, to a filter, EG

Me.Filter = "(([FieldName]) = '" & Me.ComboboxName & "')"
Me.FilterOn = True

 

[MichaelWaimauku]

Is this adjusted on the combo box in the header? Currently has a macro - searchforrecords which is standard.

How do I get the combo box to work as a filter for all rather than just each instance?

 

[Isskint]

Change the AfterUpdate event from MacroName to EventProcedure and write the code i posted previously.

 

[MichaelWaimauku]

That's what I did but thought it must've been wrong. When I select the item, nothing is returned even thought there are records. Code below

Me.Filter = "(([JDEItemNO]) = '" & Me.Combo57 & "')"
Me.FilterOn = True

 

[Isskint]

Is JDEItemNO a numerical value or text? you are setting the filter up as a text value. If it is numeric then use Me.Filter = "(([JDEItemNO]) = " & Me.Combo57 & ")"

 

[MichaelWaimauku]

The field is definitely text, holds both text & number. Not sure what's going on? It's filtering but only returning a blank form with #### in the primary field.

 

[MichaelWaimauku]

Not sure if this is causing a problem, when using the wizard to initially set up the combo box, I use Find record on form based on the value I selected in my combo box. This has a key column hidden which is numeric. The field selected is alph/numeric - test field. However, the hidden column is numerical. Does this change anything??

I've attached the db if you have time to look. The combo is JDE Item No. on the form searchnonconformity.

 

[Isskint]

If the hidden column is the bound column, then this is the problem. The 'value' of the combobox is the item in the bound column on the selected row. So you either need to set the filter = to the equivalent numeric field on the form OR use the column() property to return the text value. EG if the second column holds the text value you want to use then Me.Filter = "(([FieldName]) = '" & Me.ComboboxName.Columns(1) & "')"

 

[MichaelWaimauku]

Purfect, now, what happens if there's another combo box that is filtering on another field and potentially, a person could use the first filter then decide to use the other filter for a different result. I tried to use a standard combo box but it wouldn't override the first search. Is there something that can be put in vba that clears previous filter?

 

[Isskint]

You would need to EITHER:-

Clear the first filter :-

Me.FilterOn = False
Me.Filter = ""
...Run with code for other combox

OR:-

Add the second filter to the first so BOTH criterias are used:-

Me.FilterOn = False
Me.Filter = "(" & Me.Filter & ") AND (" & secondfilter & ")"
Me.FilterOn = True
...Run with code for other combox

 

[MichaelWaimauku]

I applied the same code but tweeked it to number format (only one column). Now either combo can be used one after the other without issue:

Private Sub Combo40_AfterUpdate()
Me.Filter = "(([NonConformID]) = " & Me.Combo40 & ")"
Me.FilterOn = True
End Sub
Private Sub Combo40_Change()
Me.Combo57.Requery
End Sub

 

[MichaelWaimauku]

Now, while the form is open, the last filter applied remains and the user can't get back to all records displayed.

Could I create a button "Clear Filters" that has some vba and resets them both to blank, regardless of whether they were used or not? Then all records can show.

 

[Isskint]

As previously mentioned...

You would need to EITHER:-

Clear the first filter :-

Me.FilterOn = False
Me.Filter = ""
...Run with code for other combox

.....

 

[MichaelWaimauku]

Created a button and put the Me.FilterOn = false code in and works. Now is there a way of refreshing the combo box fields back to a blank state rather than showing last records searched on at the same time?

 

[Isskint]

Me.ComboboxName.SetFocus
Me.ComboboxName.ListIndex=-1
Me.NextControl.SetFocus

 

[MichaelWaimauku]

Does this code go following Me.FilterOn = False
Me.Filter = "" for the clear filter button or is it added after each of the combo filters?

I tried adding it on the button but get error on last line:
Private Sub Command65_Click()
Me.FilterOn = False
Me.Filter = ""
Me.Combo40.SetFocus
Me.Combo40.ListIndex = -1
Me.Combo57.Column(1).SetFocus
Me.Combo57.Column(1).ListIndex = -1
Me.[NonConformID].SetFocus
End Sub

All this ends up doing is clearing the filter & setting focus on first record on the form. The two combo box' still retain the last selected record.

Tried using the Me.Combo57.column(1) but get a runtime error 424 object required???

 

[MichaelWaimauku]

I managed to sort it with the following: Needed to set combo value = null

Private Sub Command65_Click()
Me.FilterOn = False
Me.Filter = ""
Me.Combo40.SetFocus
Me.Combo40.Value = Null
Me.Combo57.SetFocus
Me.Combo57.Value = Null
Me.[NonConformID].SetFocus
End Sub