Transcript – Combo Alpha List

Video Transcript

Video 1 Transcript – (Start)
You can double click the combo, or press a command button to call up the Combo Box Alpha List Form. The form automatically sorts your list of data in to alphabetic/numeric groups. These are accessed by the toggle buttons. You can press “G” and you will only see the records beginning with “G” listed. Select a record and your combo-box is automatically filled with the selected record. You can also select by numbers. If the first character is a 6 then it lists records beginning with a 6. However it doesn’t end there! You also have a “Custom Search Function”. Let’s say you remembered a Customer name that sounded like “Dew?” but couldn’t find it in the Combobox. You could type in D,E,W in the “Combo Box Alpha List Form” and you would then find the customer “Mason Dewey”.Notice there is only one customer listed beginning with “i” two customers beginning with “e” and none beginning with “j” so the “J” is greyed out. When you click on “J” it doesn’t return any results. The same for “y” and “z” there are no customer’s beginning with “y” and “z” they are also greyed out.

Notice too, that when you close the form without making a selection, then nothing is added to the combo box, if you’ve already made a selection in the combo, just opening the “Alpha Listing form” will not affect the selection, your original selection isn’t changed.

Notice that the buttons are for the normal 26 characters of the alphabet. Accented characters, that is characters other than the normal 26 are not shown. If you have got records in your list that start with an accented character like an “ä” then they don’t show up in the list. You can use the “Custom Search Function” to find entries with accented characters. Just place the accented character in the search terms box and the search will return entries with accented characters.

Well that’s it that’s the combo-box alpha form. See the next set of videos to see how easy it is to add this to your MS Access Database!
Video 1 Transcript – (End)Video 2 Transcript – (Start) 
In this video I will demonstrate how the Combo Alpha List can be used. The following example demonstrates a customer placing an order. You answer the phone, take an order in a rush. You wrote down “Jess Crunchy bars – 10” and “Dew” for the customer name. You forgot to note the customers number, and now you’re not sure who the customers was! Open up the database and type in D and “Drachenblut Delikatessen” is shown followed by “Du monde entier” you know its not either of those…

Next, double click, open the “Combo List Alpha form” select “Custom” type in D,E,W and then Select “Anywhere in the Text” with this option >>> *a* (with a star each side) and “Maison Dewey” is returned. You realise this was the customer you were thinking of. Now you type in “j” “e” and up pops the entry “jeffs Country Crisp” you realise that you mistakenly wrote Jess instead of Jeffs, but you can’t see a “Jeffs Crunchy bar” listed?

Open up the Combo Alpha List search facility type crunchy in the custom box and select option “anywhere ” in the text (that’s an *a* surround by stars and hey presto the missing item appears >>>”Jeffs Crunchy Bar<<<. >>>”Jeffs Crunchy Bar<<< has been incorrectly added to the database with a preceding quotation mark which stopped an ordinary search being able to find it. The Combo box Alpha List Form is an excellent utility you can easily add to your database and provide your user with extra functionality.
Video 2 Transcript – (End)

Video 3 Transcript – (Start)
Set-up the Combo Box Alpha List
This is a typical Orders Form where on the main form you selected customer and on the subform you select the product and quantity.

A Customer asked for vanilla syrup, you key in a “V” but “Vanilla” does not pop up? You haven’t got “Vanilla” listed? You recall selling vanilla before but are unsure what to do. This is an ideal problem that can be solved by the ComboAlphaList Lookup form. Let’s import it.

Click on external database, select “Access” and browse. Find the MS Access MDB database you have just downloaded (the current name is comboboxAlphalist_5a.mdb). Press “open” and then press OK. You will see a list of objects within the database. Select the forms tab and then select the form frmComboAlphaList. Click ok and then click close.

The frmComboAlphaList should appear in the “navigation pane”. You can double click on it to confirm that it has imported successfully.

You need to add some code so that it will work with your form. Add it to the customer combobox first. Open the form frmOrders in design view, select the combobox cboCustomerID select events, then go to the “on double click” event. Press the ellipsis and then select “code builder”. The visual basic code window will open showing a subroutine stub for the customer combobox.

Close the VBA window and open the form frmComboAlphaList in design view. Select the code behind the form. Move down to the bottom, to the sample code and copy it. Close the form.

Open the code window behind the Orders form, paste the code in between the subroutine stub for the combo box double click event. Remove the comments, copy the name portion of the command button from the code and replace the “Your Combo Name Here” part. Save it, debug, close the code window and the form. Open the form and double click on the combobox.

You might want to use a command button instead of the double click so to do that add a command button, double click on the command button select the events tab. Select the onclick event and press the ellipsis select code Builder. Copy the code from the combobox double click event save, deBug, close the code window, close the form. Reopen the form and click the command button.

We would like to have the same functionality for the products however note the products are displayed in datasheet view. Datasheet view does not support a command button. Close the orders form open the subform in design view, find the combobox, select properties, events, “double click” event. Paste the code in, it should still be present in the clipboard. Save, debug… and we have an error! We need to change the highlighted part to the new combobox name.

Copy the name from the subroutine, save, debug close… close… close…. Open the orders form double click on the combobox.
We were having trouble with “Vanilla” Open the “custom” tab, type “van”, “V”, “A”, “N”, select anywhere (*a*) in the middle and there we have 1, 2, 3, 4 vanilla’s to choose from. Let’s choose vanilla syrup! That’s it thank you.
Video 3 Transcript – (End)

 Video 4 – Combo Box Alpha Form Prerequisites – 1 – (Start)
Before you download this “Combo Alpha List Form” you need to make sure your database is compatible with it. The “Combo Alpha List Form” {“frmComboAlphaList”} must be provided with a “Row Source” in the form of an SQL statement. I will demonstrate the simple steps you need to take to set it up with a demo form containing a single Combobox. This form is named “frmSetupDemo” and titled “Combo Box Setup Demo”.

The form “frmSetupDemo” contains a Combo-box “cboCustomerID” with its “Row Source” set to Query1. “cboCustomerID” works fine, however when you double click “cboCustomerID” to open the “Combo Alpha List Form” {“frmComboAlphaList”} you get an error.  This is because “frmComboAlphaList” requires an SQL Statement.

Open “Query1” in design view. Select “SQL view” and you will note the query is in essence an SQL statement. Move the “From Clause” to the top line, make sure there’s a space in it.  Copy the text into your clipboard. Close “Query1”

Open the form “frmSetupDemo” in design view.  Open the property sheet for the Combo-box “cboCustomerID” by double clicking on it. Paste the copied SQL statement from the clipboard into the combobox rowsource.  Close the form and reopen it. Note:- It is listing the records correctly. Double click on it to call up the “Combo Alpha List Form”. Select g, select f, to show everything is working fine.

Change Combo-box “cboCustomerID” rowsource to the “Customers” table. Open “Combo Alpha List Form”. Note it is listing the records correctly. Double click on Combo-box “cboCustomerID” to open the “Combo Alpha List Form” {“frmComboAlphaList”}. Note that you get an error message which states that “Customers” is not a valid SQL statement and then the form “frmComboAlphaList” is automatically closed.

The Combo-box “cboCustomerID” requires an SQL statement as its row source. You need to replace the Table name “Customers” with an SQL statement. To do this:- Select the “CREATE” Tab, Select “Query Design” Select the “Tables” tab select the table “Customers” press add close the “Show Table” dialogue box. Select “customerID” and “CompanyName” drag them into the query grid. Click run – everything looks OK. Select view “SQL view” move the “FROM” clause up to the “SELECT” clause line, making sure you include a Space. Copy the SQL statement to the clipboard.

Open the form “frmSetupDemo” in design view. Open the property sheet for the Combo-box “cboCustomerID” by double clicking on it.
Paste in the new row source, close and save. Open the form, the list is returning OK. Double click and the combobox alpha list demo appears OK.

Combo Box Alpha Form Prerequisites – Video 4 – (End)