Dual listboxes... start typing values and find matching record to move from left to right listbox

[skydivetom]

Experts:

I need some assistance with streamlining a form (i.e., listbox). Please find attached database which contains the following:

Objects:
a. Table "T01_Billets" -- contains 2001 records
b. Table "T11_WorkingGroups" -- contains 10 working groups
c. Junction table, 3 queries, 1 form

Current process:
1. User opens form "F01_Listboxes"
2. User selects any of the 10 working groups from combo (e.g., "Working Group A").
3. User selects values from left listbox "Available Billets" and adds one or multiple billets to "Assigned Billets" via the "right" (Add) button. This works great!

However, the challenge is that the billets table has 2000 records. Thus, if I want to add billet "2356 | Data Analyst I", I would have to scroll down half way in the listbox. Given that I sometimes need to add up to 20 billets/postions, the "scolling" and "finding" the billet can be quite cumbersome.

So, here's what I need some help with:
- While overall form process works well, I want to enhance the listbox so that I can start typing, e.g., "2356"... I then want any matching records to be on top of the listbox so that I can more easily add the billets.
- Is that doable? If so, what VBA needs to be added to achieve the filtering method?

Thanks,
EEH

[mike60smart]

Hi

Does each Working Group only have a specific number of Billets

If this is the case then you need to use Cascading Combobox's - Combobox 1 select Working Group and 2nd Combobox only lists those Billets associated with the Working Group selected.

[skydivetom]

Mike:

There is no restriction as to how many billets can be assigned. Naturally, common sense suggests we won't have 2000 people attending a working group (at least not in my work environment). From, a mechanical viewpoint though, I don't want to limit the list of billets that can be added to the 2nd listbox.

That said, if I select all 2000 billets from "Available", the 1st listbox will be empty and all billets have been moved over into right listbox (but only for that selected working group). Different number of billets will be assigned to "Working Group B"... I must maintain that process.

Simply want to be able to more readily locate a billet in the left listbox by start typing, e.g., 4 characters and then find the first match.

Makes sense?

[CJ_London]

Simply want to be able to more readily locate a billet in the left listbox by start typing, e.g., 4 characters and then find the first match.

you can't type in a listbox, why not use a combobox?

or use a textbox for the user to complete and after they have typed 4 chars code runs to populate the listbox - use the change event

code would be something like

if len(txtBillet.Text )>3 then lstBillets.rowsource="SELECT * FROM tblBillets WHERE Billet like '" & txtBillet.Text & "*'"

[skydivetom]

Ajax, I would have to see how that works... would I still be able the capability to drag/drop multiple values at the same time? What about all of the other VBA code? Are you willing to post example of what you have in mind?

[mike60smart]

Hi

You can make the Listbox Multiselect and then use code something like the attached in an On Click Event to move

Dim db As DAO.Database
Dim strSQL As String
Dim varItem As Variant

Set db = CurrentDb()

For Each varItem In Me.ListboxName.ItemsSelected
strSQL = "INSERT INTO TableName (ID, FieldName) VALUES (" & Me.ID & ", '" & Me.ListboxName.ItemData(varItem) & "')"
db.Execute strSQL, dbFailOnError
Next

[skydivetom]

Mike:

Current solution works based on clicking the "AddOne" & "Remove" one buttons. Those are the two functions I need to maintain. Clicking on listboxes themselves should not result in action (I believe).

If you can, pls review existing VBA which came w/ the posted database.

Thanks,
EEH

[mike60smart]

Hi

I would change the Multi Select property to Simple vice Extended.

This allows you to select more than 1 Billet at a time

[skydivetom]

It already does that in the originally posted db example.

The db works fine... only want to be able to quickly locate billet vs. Scrolling down

[davegri]

See if this helps. Typing in search textbox gives instant by the keystroke search result in listbox.
Listboxes quick Search davegri-v01.zip

[skydivetom]

Yes! That works great... however, I now lost the ability to select other working groups. The ability to switch among the various WGs is critical. Is there a way I can to both... select the WGs and also filter for the billets then assigned to the selected WG?

If so, it'll be the perfect solution.

[davegri]

Is there a way I can to both... select the WGs and also filter for the billets then assigned to the selected WG?

No. Not with your current structure. There is no relationship established that assigns a billet to a particular workgroup.

[skydivetom]

davegri -- no, that's not the case. In the originally posted db, user can select e.g., Working Group #A and then assign any number of billets. Then, via combo, Working Group # can be select and any number of billets can be assigned to WG B... same for any other working groups. This is a critical functionality that I cannot lose.

I was merely hoping to have your search feature built into the existing working solution so that users can find locate billets to be assigned to any working group.

Pls check out the orginally posted db and you'll notice the existing functionality.

Thanks,
EEH

[davegri]

Pls check out the orginally posted db and you'll notice the existing functionality.

I have the original db you posted in post#1. It has no such functionality.

[skydivetom]

davegri -- I just downloaded it again to double-check. Clicking on the combo allows users to select between Working Group A through Working Group I. Each can have any number of billets assigned... no constraints.

Pls see attached JPG ... again, I downloaded the zip file posted on 09:53 AM today (Saturday).