Form selection based on previous

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all.

Please excuse silly question. Am proper newb at this and am slowly teaching
myself when I get the chance.

Well, have two questions. Am putting together a small database for my wife
who works as a hearing aid audiologist. It is so she can keep track of her
appointments and any orders that arrise.

The forst snag i have is that she has many different manufacturers of
hearing aids. My plan is to have a combo box where she can choose the
manufacturer and then the contents of the next box will change to reflect the
choices of model from that manufacturer. Its probably very easy to do, but
cant figure it oout.

The second is regarding a query i want to run. Whe needs a weekly breakdown
of all her appointment s split into different catergories. Not sure of the
conditions to place to get this query to run correctly.

Again sorry if the questions are really simple

Many thanks in advance
 
What you are wanting to do with the combo boxes is known as Cascading Combo
boxes. It isn't that hard. The trick is that the second combo needs to be
filtered on the value in the first combo. For example purposes, I will
assume you have a table that has Manufacturers and their models in it. The
row source for the first combo should be something like:
SELECT DISTINCT Manufacturer FROM tblMakers

The row source for the second combo:

SELECT ModelID From tblMakers WHERE Manufacturer = '" & Me.Combo1 & "'"

Now, to make it all work, you need to requery Combo2 in the After Update
event of Combo1

Me.Comb2.Requery

As to the Query, maybe you should have an idea of what you want to do, then
ask the question with more info so someone can give advice. I would suggest
posting in the queries group.
 
Klatuu said:
What you are wanting to do with the combo boxes is known as Cascading Combo
boxes. It isn't that hard. The trick is that the second combo needs to be
filtered on the value in the first combo. For example purposes, I will
assume you have a table that has Manufacturers and their models in it. The
row source for the first combo should be something like:
SELECT DISTINCT Manufacturer FROM tblMakers

The row source for the second combo:

SELECT ModelID From tblMakers WHERE Manufacturer = '" & Me.Combo1 & "'"

Now, to make it all work, you need to requery Combo2 in the After Update
event of Combo1

Me.Comb2.Requery

As to the Query, maybe you should have an idea of what you want to do, then
ask the question with more info so someone can give advice. I would suggest
posting in the queries group.
Click to expand...
Klatuu

Thanks for the reply.

This is close to what I need but have set up my tables as follows

tblManufacturer - contains list of each manufacturer
tbmodelxxx - all models made by xxx (where xxx is a manufacturer)

have 5 model tables, 1 for each individual manufacturer.

I just need to be able to alter what appears in the second combo box
dependandt upon what has been chosen in the first.

Will find out what Mrs S needs form the queries and then post that in the
query forum. Thanks
 
You could determine which model table to use based on the manufacturer
selected, but I would not do that. What I would do is correct a serious
design flaw in the database.

It is incorrect to have a different model table for each manufacturer. It
will cause all kinds of problems in forms, reports, and queries. Before you
get too far along, combine all your model tables into one. All you need to
do is add a field to carry the manufacturer's primary key value. This will
relate the two tables.

Believe me, you are thinking this is going to be a lot of work, but the
effort you put into this correction will pay back great dividents over time.
 
Klatuu said:
You could determine which model table to use based on the manufacturer
selected, but I would not do that. What I would do is correct a serious
design flaw in the database.

It is incorrect to have a different model table for each manufacturer. It
will cause all kinds of problems in forms, reports, and queries. Before you
get too far along, combine all your model tables into one. All you need to
do is add a field to carry the manufacturer's primary key value. This will
relate the two tables.

Believe me, you are thinking this is going to be a lot of work, but the
effort you put into this correction will pay back great dividents over time.


Klatuu
Click to expand...

Whew, that was quick.

Sounds like a good idea that. So i should end up with just two tables then?
One for the manufacturers and one for ALL of the models? (Good job I havent
entered any data yet :-) )

Would that row source argument then work for what I need? or will I have to
put a query into the row source for the model combo box.

Am getting myself well confused at the moment. May have decided to run a
marathon before I am out of a baby-grow
 
You always need a row source for a combo. The only difference here is that
you would fiter the row source for the model combo on the value selected in
the manufacturer combo:

Me.cboModels.RowSouce = "SELECT Model FROM tblModels WHERE Manufacturer = '"
& Me.cboManufacturer & "'"

That will limit your list in the models table to only the models fo the
selected manufacturer.
 
Klatuu said:
You always need a row source for a combo. The only difference here is that
you would fiter the row source for the model combo on the value selected in
the manufacturer combo:

Me.cboModels.RowSouce = "SELECT Model FROM tblModels WHERE Manufacturer = '"
& Me.cboManufacturer & "'"

That will limit your list in the models table to only the models fo the
selected manufacturer.
Click to expand...
Klatuu

Sorry to be a pain the proverbial, but have put in what you have told me and
all i get in the second combo box is an empty field. Here is how i have it
all set up

tblManufacturer
1st field - MfrID (AutoNumber)
2nd field - MfrName (Text of Manufacturer)

tblModel
1st field - ModelID (AutoNumber)
2nd field - Model (Brand name given by Manufacturer)
3rd field - MfrName (Manufacturer of the model)

Cant figure out where i am going wrong on this

Can feel some classes coming on

Cheers
 
I need some class, my friends tell me I have none.

There is a bit of a problem in the model table. You should not be using the
Manufacturers name, but the ID (autonumber) field. Define it as Numeric Long
Integer.

tblModel
1st field - ModelID (AutoNumber)
2nd field - Model (Brand name given by Manufacturer)
3rd field - MfrID (Long Integer)

Now we get into some pretty stuff so users can see what they need and the
database can be efficient. I would suggest that both combos be two columns.
The bound column being the primary key of the table, and the other column
being the name. Make the column widht of the bound column 0 so the user will
not see it. But, we will actually use the hidden values to do our work.

tblModel
1st field - ModelID (AutoNumber)
2nd field - Model (Brand name given by Manufacturer)
3rd field - MfrID (Long Integer)

So for the Mfgr
"SELECT MfrID, MfrName FROM tblManufacturer;"

And for the Model
"SELECT ModelID, Model FROM tblModel WHERE mfrID = " & Me.cboMfgrs & ";"
 
Klatuu

Bet you thought i had disappeared. Sorry, but like the proverbial bad
penny, I just keep turning up.

I tried putting in those sql lines you gave, but just end up with blank combos

Heres what i got again


Manufacturer

MfrID - Autonumber
Mfr - the name of the manufacturer

Model

ModelID - Autonumber
Model - text
MfrID - I have populated this field by using a select box from the
Manufacturer table that puts in the MfrID and Mfr. MfrID is hiddden so only
the name can be seen.
Price - cost for each unit(will use this later on to populate another filed
depending upon which model is selected.

Am i going horribly wrong soomewhere? or am i being a dimmer than a 5W lamp
that is on half power. Feel like I am getting nowhere and am being a pest

Sorry for the bother again, but am at wits end with this as 1-it is beating
me and i dont like that and 2-Mrs S is getting on my ase to have this up and
running

Cheersin advance

Paul
 
Klatuu said:
Post what you have the the row source in both combos, please.
Click to expand...
Klatuu

Row sources as follows

For Manufacturer Combo - tbl.manufacturer
For Model - tbl.Model

Thanks
 
Sorry, Paul, I am still not getting enough info to really help.
My original reply showed how you use one combo to filter what is presented
in the second combo. I don't really understand the difficulty here.
Let me restate how it operates.

The usual way to set up your tables is, as you already have, a table for the
manufacturers. In most cases, there will be a code that identifies the
manufacturer to the computer. That code, however, is probably not something
a human can quickly understand. So, to make it easy for both, you would use
a 2 column combo. The bound column would be the code. It is usually Column
1; however, when addressing that column in VBA it is known as Column(0). The
second column - Column(1) - would be the name. If you don't want the user to
see the code column, set its column width to 0.

The second combo is based on the model table. It also needs a code and a
description and is set up the same as the previous combo. Then when the user
selects a manufacturer in the first combo, the After Update event fires.
That is where you requery the model combo so it will only show the models for
the selected manufacturer. That is done by setting up the query to filter on
the value in the manufacturer combo. It would look something like this:

SELECT ModelID, ModelDescription FROM tblModels WHERE mfgID = Me.cboMfgr;

The mfgID would be a field in the Model table. It will be the same value
for every model that belongs to that manufacturer and is the code in the
manufacturer table that identifies the manufacturer.
 
Back
Top