Combobox Search (1 Viewer)

mike60smart · Jan 12, 2020

Hi Everyone

I have Cascading Combobox's set up so that when I select a Supplier using Combo 1 the 2nd Combo displays a list of associated SKU Items.

Question is whether or not it is then possible to be able to search within the displayed SKU List produced?

Any help appreciated

MajP · Jan 12, 2020

Question is whether or not it is then possible to be able to search within the displayed SKU List produced?

Can you clarify this a little more? Not sure what you mean.

mike60smart · Jan 12, 2020

Hi MajP

The 2nd Combobbox displays a list of SKU Items which has a scroll bar to allow the user to scroll through the list.

Is it possible to use a number of keystrokes typed in order to find a SKU Item?

MajP · Jan 12, 2020

There is an autoexpand property that normally does this
https://docs.microsoft.com/en-us/office/vba/api/access.combobox.autoexpand

Do you want a filter as you type? Where if you type "A" only rows with "A" are shown, then if you type "AC" you get only rows with "AC".

mike60smart · Jan 12, 2020

Hi MajP

Yes that would be great. How would I achieve this?

isladogs · Jan 12, 2020

Alternatively, if the list of SKU items is very long, you may wish to consider further cascading combos. For example, see this Postcode Builder example which uses 5 cascading combos to handle 2.6 mullion postcodes

MajP · Jan 12, 2020

Here is my family of Find As You Type classes. These classes turn combos and listboxes into FAYT with a single line of code. For the cascading it takes a couple more lines of code. See the combo demo form. The last combo is a cascading FAYT.

Uncle Gizmo · Jan 12, 2020

As an alternative, I have created a pop-up form which extract the row source of the Combobox, uses that to identify how many record you have beginning with A, B, C, D etc. Highlights the buttons to show you which are available. Let's say the "P" button is one of the enabled buttons. Click on the "P" button and the combo-box (on the pop-up form) will list items beginning with "P". The Popup Form also incorporates the ability to Custom Search.

Demo YouTube Here:- Combo-box Alpha List

More Info on Nifty Access HERE:- Combo-box Alpha List

If you would like a free copy, let me know and I will explain how you can get it for free...

moke123 · Jan 12, 2020

MajP said:
Here is my family of Find As You Type classes. These classes turn combos and listboxes into FAYT with a single line of code. For the cascading it takes a couple more lines of code. See the combo demo form. The last combo is a cascading FAYT.

Thanks MajP for the classes. I've been trying to use them more and it's difficult to find example db's to disect. (one of my preferred learning methods)

You just changed my plans for the afternoon.

isladogs · Jan 12, 2020

Just to give you even more choice, see this link for Allen Bowne's approach to combos with thousands of records http://allenbrowne.com/ser-32.html

mike60smart · Jan 12, 2020

Hi MajP

I tried to create the Module included in your MajP FAYT V10.accdb but it errors on these lines:-

MajP · Jan 12, 2020

You do not recreate the module. You import it. Then initialize as in the demo form. That error is because you placed it in a standard module and not a Class module as per the instructions. But a lot easier to just import the modules and not copy paste. Read the instructions in the class module for use. But you will have to look at the demo for the requery when you filter the list from the first combo.

mike60smart · Jan 12, 2020

Hi MajP

I am trying to use your strSQL as a reference with the following but it highlights everything in RED indicating my Syntax is wrong somewhere.

Can you spot my error(s)??

Code:

30      strSQL = "SELECT [tblMaterialsTable].[MaterialID], [tblMaterialsTable].[ItemSKU], [tblMaterialsTable].[ItemDespcription], _
35      " &[tblMaterialsTable].[ItemUnitPrice], [tblMaterialsTable].[MarkupTypeID] FROM [tblMaterialsTable WHERE [SupplierID] = '" & Nz(Me.cboSupplier, "") & "' Order BY [tblMaterialsTable].[ItemSKU]"

mike60smart · Jan 12, 2020

Hi MajP

On the Form that contains the Combobox cboMaterial I have all of the following Code:-

Code:

Public faytMaterial As New FindAsYouTypeCombo
Public faytMaterialNoInt As New FindAsYouTypeCombo
Public faytMaterialForward As New FindAsYouTypeCombo
Public faytForwardNoHandles As New FindAsYouTypeCombo
'Cascade combo
Public faytMaterial As New FindAsYouTypeCombo


Private Sub Form_Load()
  faytMaterial.InitalizeFilterCombo Me.cboMaterial, "ItemSKU", AnywhereInString, True
  faytMaterialNoInt.InitalizeFilterCombo Me.cboMaterial, "ItemSKU", AnywhereInString, , False
  faytMaterialForward.InitalizeFilterCombo Me.cboMaterial, "ItemSKU", FromBeginning, True
  faytForwardNoHandles.InitalizeFilterCombo Me.cboMaterial, "ItemSKU", FromBeginning, False
  'Cascade Combo
  faytCascade.InitalizeFilterCombo Me.cboMaterial, "ItemSKU"
End Sub

Private Sub Form_Open(Cancel As Integer)
   faytMaterial.InitalizeFilterCombo Me.cboMaterial, "ItemSKU", AnywhereInString, True, False
 End Sub


Private Sub cboMaterial_Enter()

10        On Error GoTo cboMaterial_Enter_Error
      Dim strSQL As String
20      If Me.Dirty Then Me.Dirty = False
30      strSQL = "SELECT [tblMaterialsTable].[MaterialID], [tblMaterialsTable].[ItemSKU], [tblMaterialsTable].[ItemDespcription],[tblMaterialsTable].[ItemUnitPrice], [tblMaterialsTable].[MarkupTypeID] FROM [tblMaterialsTable WHERE [SupplierID] = '" & Nz(Me.cboSupplier, "") & "' Order BY [tblMaterialsTable].[ItemSKU]"
40      Me.cboMaterial.RowSource = strSQL
50      Me.cboMaterial.Requery
        'Important cascade code
60      faytMaterial.RowSource = strSQL

70      Me.ItemSKU = Me.cboMaterial.Column(2)
80      Me.ItemDescription = Me.cboMaterial.Column(3)
90      Me.cboMarkupType = Me.txtMU
100     Me.ItemCost = Me.txtCost

110   Me.cboMaterial.Requery
          
120       On Error GoTo 0
130       Exit Sub

cboMaterial_Enter_Error:

140       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cboMaterial_Enter, line " & Erl & "."

End Sub

When I try to select the Combobox cboMaterial I get the following error:-

Hit Debug and it highlights this line of code:-

60 faytMaterial.RowSource = strSQL

Where am I wrong??

MajP · Jan 12, 2020

Sorry for the confusion. That demo form has many FAYT for each of the combos in the demo. Your code should probably be something simpler like this assuming you are trying to make cboMaterial a FAYT.

Code:

Public faytMaterial As New FindAsYouTypeCombo

Private Sub Form_Load()
  faytMaterial.InitalizeFilterCombo Me.cboMaterial, "ItemSKU", AnywhereInString, True
End Sub

However, that does not explain the error. Can you post a trimmed down version of your db. The only thing I could think based on that error message is if you did not fully close the form before attempting this. Then the onload would not have fired and the variable would not be set. That is a strange error.

mike60smart · Jan 12, 2020

Hi MajP

Many Many thanks I have now got this module working.

Many thanks for the example database.

mike60smart · Jan 14, 2020

Hi MajP

I am having problems with trying to do this.

I have attached a stripped down version of the Database.

If you would be so kind to take a look and apply a fix it would be greatly appreciated.

I need to be able to search ItemDescription using the cboMaterial Combobox

With thanks
Mike

MajP · Jan 14, 2020

You have a lot going on. Even if this was not a FAYT, there are issues with doing cascading combos on continuous forms. Imagine in record one you select "dog" you move to record two and filter out the selection that contains "dog". Now record one will appear to go blank because the combobox rowsource no longer has dog as a selection. So you have to do some tricks. I do this by putting a textbox directly over the combbox. Then you select from the combo but the textbox displays the data. In a datasheet view you can use some other tricks.

Your table structure is wrong. In your table purchase order details, you are repeating all kinds of data that is stored in other tables. If you just store materialID then all of that information will come from a query. If it is in the material table Including supplierID it does not belong in this table.

mike60smart · Jan 15, 2020

Hi MajP

Many thanks will study and apply to my db

Many thanks

MajP · Jan 16, 2020

I am not sure if this is the best User Interface, maybe a better option. When adding a new item how are they searching? Do they know which supplier they are buying from and then they pick the sku? That is how you have it set. I could envision a pop up form with a very searchable, filterable listbox.

The reason I ask is that you added a FAYT on a cascade, but the amount of SKUs returned for a give supplier is small. I do not get the utility.

Combobox Search (1 Viewer)

Registered User.

You've got your good things, and you've got mine.

Registered User.

You've got your good things, and you've got mine.

Registered User.

MVP / VIP

You've got your good things, and you've got mine.

Attachments

Nifty Access Guy

AWF VIP

MVP / VIP

Registered User.

Attachments

You've got your good things, and you've got mine.

Registered User.

Registered User.

Attachments

You've got your good things, and you've got mine.

Registered User.

Registered User.

Attachments

You've got your good things, and you've got mine.

Attachments

Registered User.

You've got your good things, and you've got mine.

Similar threads

Users who are viewing this thread