more than 2 combo boxes

[MdHaziq]

Hello,

I have 9 combo boxes which I like to let the user select to get what they want from my dept inventory database.

I am currently following a youtube tutorial I found in this website thread. It works for the two combo boxes but no more than 2.

Can anyone help me?

I have attached the MS Access zip file as the file is bigger than 500KB.

Thanks

Yours Sincerely,
Haziq

[ranman256]

this is the wrong method.
if the user is picking departments, instead of 9 boxes,
use a tPicked table. Put all depts in this table. Join the tPicked table to tData table. Only these will be retrieved.
This way you are not limited to 9.

use a list box for the user to dbl-click, this runs an append query to add it to the table.

[ssanfu]

You are missing a lot of tables and only 2 combo boxes are configured.


Attached is an early attempt of mine using multiple 6 cascading combo boxes.
Be aware I named the tables wrong - the names begin with a number.
I now know this is a BAD IDEA. (Note to self: I should take the time to fix the naming problems )

Look at the relationship window to see how the tables are related. Then click on the button "Cascading combo boxes". Try selecting the combo boxes.......

The other button is cascading list boxes. It is the same as the cascading combo boxes, but using list boxes. Sometimes it is easier to see what is going on using the list boxes...

[MdHaziq]

Is using combo boxes to narrow down search a bad idea?

Should it done via tPicked table?
BTW this is the first time I heard about tPicked table and got to study it.

Thanks

[ssanfu]

Is using combo boxes to narrow down search a bad idea?

No, not if they meet your requirements.

tPicked table?

"tPicked" is just the name ranman named the table he used....can be any name.

[MdHaziq]

Oh okay
Thanks

[MdHaziq]

What you send me is beautiful.

Thanks.

[MdHaziq]

I am having trouble with the relationships.

The results do not come out in my form.

I did not input real data as I am just testing it out.

What went wrong?

I have inserted an attachment.

[ssanfu]

Well.... lots..

OK - here goes:


I guess you missed this:

<snip>
Be aware I named the tables wrong - the names begin with a number.
I now know this is a BAD IDEA. (Note to self: I should take the time to fix the naming problems )
<snip>

It is ok when designing (on paper), but don't leave the table names with a number as the first character.

Having "ID" as the PK field name is bad programming practice. Imagine looking at queries with multiple tables and there are 4 "ID" fields...... what tables to the "ID" fields belong to???

Here are the table names and field names renamed:


Finally, table "Main" is linked to the other tables and RI is set:



The table "QTY" is not needed because you type in the number.

================================================== ======================
Now the form.
You have to set each combo box row source property and set the combo box formatting.

The first combo box (cboTerminal) row source is easy:

SELECT tblTerminal.TerminalID_PK, tblTerminal.TerminalDesc
FROM tblTerminal
ORDER BY tblTerminal.TerminalDesc;

(I added a sort order)

Set the properties:
Data tab
Bound column =1
Limit to List = Yes
Format tab
Column count = 2
Column widths = 0";1" (or however wide you want to combo box)


The next combo box (cboStore) has to be filtered based on the previous combo box:

SELECT tblStore.StoreID_PK, tblStore.StoreDesc
FROM tblStore
WHERE (((tblStore.TerminalID_FK)=[forms]![frmECD_Inventory].[cboTerminal]))
ORDER BY tblStore.StoreDesc;

Set the properties:
Data tab
Bound column =1
Limit to List = Yes
Format tab
Column count = 2
Column widths = 0";1" (or however wide you want to combo box)

The next combo box (cboRack) is filtered based on the previous combo box "cboStore":

SELECT tblRack.RackID_PK, tblRack.RackDesc
FROM tblRack
WHERE (((tblRack.StoreID_FK)=[forms]![frmECD_Inventory].[cboStore]))
ORDER BY tblRack.RackDesc;

Set the properties:
- Like above -


And the next combo box (cboRack) is filtered based on the previous combo box "cboRack":
-- By now you should be able to figure out the SQL for the row source and the formatting.


The next thing is to add the after event VBA and the got fucus code.
Look at the VBA code in my example dB. Start with the last combo box - it only has the got focus code. Each combo box has this code to cause the dropdown to display.
Then look at the previous com box - it also has the after update event.
Follow the code for each previous combo box. See what is happening??


At this point the combo boxes on the form are still unbound.
After you have all of the combo boxes row sources set, the formatting set and the VBA added, you can bind the combo boxes to the appropriate fields in the form. The form record source should be a query based on the "Main" table (or directly to the table).

[MdHaziq]

Thank you so much for explaining to me.

I appreciate it.

I will get coding as soon as I get back home.

[MdHaziq]

Steve,

I have done the part which you walked me through.

If possible could you walk me though the next portion?

Thanks

This is what I got so far

[ssanfu]

On the form, the text box "txtqty" should NOT have a control source of "=[tblmain]![Qty]" and should NOT have any code in the after update event.

The next thing is to add the after event VBA and the got focus code.
Look at the VBA code in my example dB. Start with the last combo box - it only has the got focus code. Each combo box has this code to cause the dropdown to display.
Then look at the previous com box - it also has the after update event.
Follow the code for each previous combo box. See what is happening??

First, these two lines should be the top two lines of EVERY nodule:

Option Compare Database
Option Explicit

-------------------------
"cboitem"

Starting at the bottom combo box "cboitem", add the code for the Got Focus event.

Private Sub cboitem_GotFocus()
    Me.cboitem.Dropdown
End Sub

Since you are changing the selection in "cboItem", you should/could also set the quantity to NULL in the After Update event:

Private Sub cboitem_AfterUpdate()
    Me.txtqty = Null
End Sub

-------------------------
"cboEquipment"
Moving up one combo box "cboEquipment". It should have the (or it is nice to have) code for the Got Focus event:

Private Sub cboequipment_GotFocus()
    Me.cboequipment.Dropdown
    Me.txtqty = Null
End Sub

Changing the Equipment selection, you should clear the "cboItem" combo box and delete the quantity. So there should be code in the after update event to set "cboitem" to NULL, set "txtQty to NULL, then requery "cboitem".

Private Sub cboequipment_AfterUpdate()
    Me.cboitem = Null
    Me.txtqty = Null
    Me.cboitem.Requery
End Sub

-------------------------
Do these steps for the rest of the combo boxes.
If you have trouble, look at the code in "Impact9A.accdb" above.


=============================================
When the combo boxes are functioning correctly, set the form record source to "tblMain" (I would use a query, but that is your choice),
Then bind the combo boxes to the correct fields.

Fill the tables (NOT tblMain) with real data.
Set the "Data Entry" property in form "frmECD_Inventory" to YES.

Start testing........

[MdHaziq]

Hello, Steve,
I gave it a shot but something must be wrong.
I have done the necessary above but I do not know how to set the record source.

I have not gone through your program. I will when I get home.

TIA

Haziq

[ssanfu]

Doesn't look like you looked at the code in the Impact example dB.

Each of the combo boxes GotFocus events should have only 1 line - the name of the combo box and the Dropdown command.
Examples:

Private Sub cboitem_GotFocus()
    Me.cboitem.Dropdown
End Sub

Private Sub cboequipment_GotFocus()
    Me.cboequipment.Dropdown
End Sub

Private Sub cbosystem_GotFocus()
    Me.cbosystem.Dropdown
End Sub

Starting at the bottom combo box for the cascaded combo boxes:

The Item combo box code looks good:

Private Sub cboitem_AfterUpdate()
    Me.txtqty = Null  "good
    Me.txtqty.SetFocus  '<<-- ADD this line - moves to the next control - txtqty
 End Sub

The next combo box is Equipment combo box - the code has problems:

Private Sub cboequipment_AfterUpdate()
    Me.txtqty = Null  '<<-- MOVED from the cboequipment_GotFocus event
    Me.cboitem = Null   'good
    Me.cboequipment = Null  '<< WHAT??? Really??? You just cleared the value you just selected in the Equipment combo box!!! DELETE this line
    Me.cboitem.Requery

    Me.cboitem.SetFocus  '<<-- ADD this line - moves to the next control - cboitem
End Sub

Next combo box is System - - the code has problems:

Private Sub cbosystem_AfterUpdate()
    Me.txtqty = Null  '<<-- MOVED from the cbosystem_GotFocus event
    Me.cboitem = Null        'good
    Me.cboequipment = Null   'good
'    Me.cbosystem = Null     '<< WHAT??? Really??? You just cleared the value you just selected in the System combo box!!! DELETE this line
    Me.cboitem.Requery
              'What about the requery for cboequipment???
    Me.cboequipment.Requery   '<<-- ADD this line

    Me.cboequipment.SetFocus  '<<-- ADD this line- moves to the next control - cboequipment
End Sub

See how this works?? Continue with the other combo boxes
cboRack
cboStore
cboTerminal

[MdHaziq]

Thx for debuging for me.
I will read your code first thing when I get to the office.