combobox question

[hrov]

I have a form that is designed to enter data. But at the same time this form looks up information, (such as looks up a athlete instead of creating a new one) and then finally inserts a new result time for the designated athlete and event etc.

It is working perfectly ok at the minute

However, I have this combo box (see atachment "dropdownbox").
I want this to be split into multiple comboboxes. E.g.the first would list the strokes, the second would show the distance, the third would show the gender etc. This combo box is based on this table (see attachment "table")

How do i go about doing this? It would be easy enough if i was entering a new event, but im not i am referring/looking up an existing event, then assigning an athlete and time. Thanks for any help

 

[JANR]

Search this site for cascading combobox there is alot of examples of diffrent methodes

JR

 

[hrov]

Search this site for cascading combobox there is alot of examples of diffrent methodes

JR

I thought cascading comboboxes meant that the 2nd combobox was dependant on the 1st combo box.

This isn't what I'm looking for.

My first combobox has:
stroke, distance, gender, relay (yes/no)

I just want to display this in multiple comboboxes. So the user selects the stroke, then the distance then the gender etc.

BUT I'M NOT entering new data here I am looking up the data from the attachment "tables"

 

[JANR]

If you gonna use your lookuptable as it is then I woulden't add all the fields in the first combo. Set each combo with it's own rowsource taken from the same table like:

1. combo

SELECT tblevent.Stroke FROM tblevent GROUP BY tblevent.Stroke;

2. combo

SELECT tblevent.distance FROM tblevent GROUP BY tblevent.distance ORDER BY tblevent.distance;

3. combo

SELECT tblevent.sex FROM tblevent GROUP BY tblevent.sex;

I'v called the table "tblevent" change it to the name of your table.

JR

 

[wiklendt]

ah, what i think you are doing is wanting to normalise your data - i.e., you want a table where you can dropdown each of the fields...

i usually do this with a related table. i.e.,

tblAthlete
AthleteID (PK)
AthleteName
AthleteDOB
AthleteGender

tblStroke
StrokeID
StrokeName

tblDistance
DistanceID
DIstanceMeters

tblEvent (which is a junction table for each event - i.e., a combination of distance and stroke.)
EventID (PK)
DistanceID (FK)
Stroke (FK)

Then:

tblResults (which is another junction table of sorts)
ResultID (PK)
EventID (FK)
EventDate
AthleteID (FK)
FinishTime

... does that sound more like what you mean? wherever you see "(FK)" that means "foreign key" - basically it means that it's a 'lookup' field (equates to dropdown or combo box in a form) in your table (make using the lookup wizard).

 

[wiklendt]

sorry, i just re-read you original post - you're saying you're not entering data, just looking it up - does that mean you want to filter your table based on the selection of the comboboxes?

in which case this is definitely a 'cascading combo' situation, except that one of your 'combos' would actually be a listbox... (a combobox is basically a listbox anyway, except a little more compact)

(edit: so your combo boxes can be made just like suggested above, and your listbox would need to have a query as its source, where each relevant field refers to the combos for its data.)

 

[hrov]

If you gonna use your lookuptable as it is then I woulden't add all the fields in the first combo. Set each combo with it's own rowsource taken from the same table like:

1. combo

SELECT tblevent.Stroke FROM tblevent GROUP BY tblevent.Stroke;

2. combo

SELECT tblevent.distance FROM tblevent GROUP BY tblevent.distance ORDER BY tblevent.distance;

3. combo

SELECT tblevent.sex FROM tblevent GROUP BY tblevent.sex;

I'v called the table "tblevent" change it to the name of your table.

JR

Thanks with a bit of tweaking i have got what i wanted.

But how would i go about changing the sex combo box to a text box. This text box just stating "m" or "f", e.g. not allowing the user to choose a gender.

(where would i put the code into a text box, as in for the combo box it was the row source)?

 

[JANR]

Since there is just 2 choises, why not use a Option Group instead? Easier for a user to click rather than type.

JR

 

[wiklendt]

yes, option box would be nice. easier for user, but not easier for novice DB designer... jump in with both feet, is what i say! no way to learn the complex things like just getting on with it

having said that, what are you doing different to you gender combo? if you managed to get combos for the other options, what's different about your gender?