Combo box questions

[MikeB]

I was experimenting this afternoon and have accumulated a horde of
questions. Please forgive me for this rather lengthy post.

I'm trying to design a new data entry form for my Chess Club. First I
want to select from the available tournaments, then from the available
matches within that tournament, and then from the games within that
match. I envisage a form, a sub-form and a sub-sub-form. My previous
form was a 3-level nested subform similar to the above, but I had to
scroll through the various tournaments, then the matches and then the
games.

This time I thought I'd try and use a form with drop-down combo-boxes.

I started with a form not bound to a query or table (unbound form?)
then I added a Combo box with a query on the Tournaments table. This
works (more or less) in that it is blank when the form is opened,
whereas I want to perhaps put some text in there to say "Select
Tournament." Also (and I don't know if this is some quirk or not, but
if I click on a vlaue in the drop-down, it doesn't close and display
that value. Is this due to an error? Perhaps something I did further
on caused this, because my recollection is that combo boxes do
normally close again.

So to make a user prompt appear in the combo box, I thought I'd set
the combo box initially with a value and a default value, then when
the user clicks in it, I'd use the "onEnter" event to change the
properties of the combo box to bind it to a query.

The problem is that I find that the help is very very vague on exactly
how to specify the various properties.

Also, when I create the combo-box and set a default value, I could not
consistently make it appear in the combo box.

Let's assume that either I get my "magic" to work, or I abandon it and
simply live with a blank combo box on entry to the form, do I set the
combo box to "remember the value" for the item selected? Is that how I
use that value in the query for the Subform?

Thanks.

I really, really need a pointer to a better tutorial or help on using
combo boxes. Or (yet another) book on Access. Thanks to all.

Which reminds me, I have an access 2007 step-by-step book somewhere,
but I don't think it goes this deep into combo boxes and how to set
their properties from VBA event procedures.

 

[Wayne-I-M]

Hi Mike

To take the simplest idea (your application will be more than this)

You have 3 tables
tbltournaments =
tournamentID
tournamentStartDate
tournamentLocation
etc

tblmatches =
MatchID
MatcheGrade
etc

tbleGames =
GameID
GameTime
etc

Oh - I don't think that you will have anything like this - just an example.

You need to "relate" these tables like this

tbltournaments =
tournamentID
tournamentStartDate
tournamentLocation

tblmatches =
MatchID
"tournamentID"
MatcheGrade

tbleGames =
GameID
"MatchID"
GameTime

This is a One to Many relationship
This means that One tournament can have Many Matches and Each Match can have
many Games

Please don't be tempted to take the easy option and use lookup fields in
your table - there are many reasons not to - search this forum for details


Once you have this up and running you should create a form based on
tbltournaments - make sure you have the tournamentID some where on the form
(it can be visible = No if you want)


Add a combox to show the
tournamentID - this is the bound column
tournamentStartDate
tournamentLocation


Add a combox
Base it on the tblmatches
The ID is the bound column

Add another based on tblGames

In design view open the the matches combo properties box and select the
source row and then the build option (...)
A query will apprear - In the column that holds the tournamentID set the
criteria (use the build option) to point at the 1st combo on your form (based
on tbltournaments)

Now when you select a tournament in the 1st combo only the related matches
will apprear in the 2nd combo.

Do the same for the 3rd combo and there you have it

Last - you need to requery the next combo using the AfterUpdate event

Another method shown here
http://support.microsoft.com/kb/289670/en-us

 

[MikeB]

Hi Mike

To take the simplest idea (your application will be more than this)

....

Oh hey, thanks for responding. There are a few things in there that
I'll look into, but I noticed that you assumed I have no relations
between the various tables. That's incorrect. I do in fact have many
relations specified.

My "first generation" of the form was a series of nested subforms, but
I'm figuring that if I'm simply creating a form to obtain the single
variable of the Tournament (or the Match) for the next subform to use,
it may be easier to have an unbound form/control to do that and then
use that form.control in the parameter query for the next level form.