Combo Boxes & Filtering Value

[notanexpert]

I'm struggling to connect 2 Combo Boxes and then limiting choices. Basically
I have 2 tables:

tblClass (2 fields)
AcctClassID (primary)
Acct Class

tblAccount (4 fields)
AccountID (primary)
Account Class
Account Name
AccountNo

On the form I have a Combo Box which lists all the records in the Acct Class
field in a drop down. The next Combo Box should bring up all the records
from tblAccount that match the Acct class record chosen in the first Combo
Box. I've tried various things to do this with no success.

The other problem I'm having is that I want to columns/fields to display in
the 2nd Combo Box and can only get the first column to display.

Please help as I am on a time limit to get this to work.

 

[Larry Linson]

Alas, newsgroups whose answers all come from volunteers are not a good place
to expect, or ask for "help on a time limit"; we help when, and if, we can.
That said, I've given and received correct answers in a matter of just a few
minutes using this and other newsgroups. That is, far more rapidly than I
have sometimes waited just to tell my tale of woe to a telephone support
rep, for whose time I was being charged.

It would certainly be of some help if you described some of the various
things you've done to no avail... that might really be helpful.

First, I have an example database "QueryByForm with Three Combo Boxes" at
http://accdevel.tripod.com/dwnindx.htm. It may give you some direction on
how to cascade combo boxes.

What is the common field in the two tables you list? "Acct Class" and
"Account Class"? And, are you certain those are identical? I would tend to
use the Primary Key of the other Table as a Foreign Key, rather than a text
field (with, likely, more opportunity for error).

What you have to do is to use a Query (or SQL) for the Row Source on the
second Combo Box, which refers back to the value selected in the first Combo
Box. My suspicion is that your first Combo Box selects the AccountClassID,
but you are trying to relate the two on the visible (but not selected)
Column containing "Acct Class".

If you had described what you have tried in some detail, I might not have
had to speculate or suspect what you did.

Larry Linson
Microsoft Office Access MVP

I'm struggling to connect 2 Combo Boxes and then limiting choices.
Basically
I have 2 tables:

tblClass (2 fields)
AcctClassID (primary)
Acct Class

tblAccount (4 fields)
AccountID (primary)
Account Class
Account Name
AccountNo

On the form I have a Combo Box which lists all the records in the Acct
Class
field in a drop down. The next Combo Box should bring up all the records
from tblAccount that match the Acct class record chosen in the first Combo
Box. I've tried various things to do this with no success.

The other problem I'm having is that I want to columns/fields to display
in
the 2nd Combo Box and can only get the first column to display.

Please help as I am on a time limit to get this to work.



__________ Information from ESET Smart Security, version of virus
signature database 4035 (20090425) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4035 (20090425) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[notanexpert]

Mr. Linson: thank you for you prompt reply. I didn't list everything I
tried because I had changed so many different little things so many times I
wasn't even sure what I should elaborate on.

I checked out your demo database and it really helped. I'm just using the
one table now because all the info is in it and changed my After Update
events. I can now choose the Acct Class record in the first Combo Box and am
not receiving any errors but when you go to the second Combo Box on the form,
it's blank. I've been thru everything multiple times but can't figure out or
see what the problem is. The following are my query and event procedures for
the first Combo Box:

Query:
SELECT tblAccount.[Acct Class]
FROM tblAccount
GROUP BY tblAccount.[Acct Class]
ORDER BY tblAccount.[Acct Class];

Event:
Private Sub AcctClass1_AfterUpdate()

Me!cboAcctName1.Value = Null
Me!cboAcctName1.Requery
Me!cboAcctNo = Null

End Sub

I added a third Combo Box because I couldn't get two fields to show in the
2nd Combo Box on my form. Am I correct in that since no data shows up in the
2nd Combo Box, there's something wrong with either the first Combo Box's
event or the 2nd Combo Box's query?

2nd Combo Box query:
SELECT DISTINCT tblAccount.[Account Name]
FROM tblAccount
WHERE (((tblAccount.[Acct Class])=Forms!frmInvoice!AcctClass1))
ORDER BY tblAccount.[Account Name];

I'd appreciate any further help you can give. It would almost be better to
list all the choices than none at all!

Jackie

 

[Larry Linson]

When I carefully examined your posts, I wondered whether the Form and
Controls are, indeed, working as you describe, or if you made a typo.

Rather than go through a Q&A, I did something I rarely do... I created an
example.

Here is what I used:

tblAccount
AccountID (Autonumber PK)
AccountClass (Text)
AccountName (Text)
AccountNo (Text)

tblClass (note: this was defined for compatibility with what you'd done, but
not used)
AcctClassID (Autonumber PK)
AcctClass (Text)

A form, frmInvoice, with two Combo Boxes

AcctClass1
Note: 1 Column
RowSource: SELECT AccountClass FROM tblAccount GROUP BY AccountClass
ORDER BY AccountClass;
Event Procedure:

Private Sub AcctClass1_AfterUpdate()
Me.AccountName1 = Null
Me.AccountName1.Requery
End Sub

AccountName1
Note: 2 Columns
Row Source:
SELECT DISTINCT AccountName, AccountNo
FROM tblAccount
WHERE AccountClass=Forms!frmInvoice.AcctClass1
ORDER BY AccountName ASC;

Because Combo Boxes are so designed, the second column shows in the
drop-down list, but not in the box itself after a selection is made. If you
wish to show the Account Number after the dropdown is closed, you can
reference it as AccountName1.Column(1). Something like the following:

Private Sub AccountName1_AfterUpdate()
Me.txtAccountNo = AccountName1.Column(1)
End Sub

If you do, you should remember to set it to null, along with the second
ComboBox, in the AfterUpdate event of the first ComboBox.

I hope this helps... it all works for me. Post back here if you have
questions. I won't be online much for the rest of the weekend, but I'll try
to respond as soon as I can, if I notice an additional question (but maybe
some good-hearted participant here will see it first and answer if they
can).

You will note that I took advantage of whatever coding shortcuts I could...
did not use the default .Value property, did not bracket names nor include a
table reference when those were not needed... I think it makes it easier to
read, takes less typing, and doesn't cause a problem.

Larry Linson
Microsoft Office Access MVP




__________ Information from ESET Smart Security, version of virus signature database 4035 (20090425) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[notanexpert]

Mr. Linson,

Thank you for your help. I did get the combo boxes to work and have moved
on to some other, smaller issues that I hope to get resolved. This is a
great forum as there is no one at my workplace who has any access knowledge.

Thanks again,
Jackie