J
JethroUK©
Before you run a mile, because if you've ever used one, i know just the
words "Combo Box" is enough to send shudders down your spine. They seem
potential useful on the surface but usually involve additional tables, look
ups, relationships, displaying many columns (hiding some), binding it to the
right field - using said form in a report, the report displays the wrong
field from the combo - if you want to add anything to a combo it's a proper
pain and wo betide you if you decide to change the table layout, or you've
forgotton one of the options so you have to add it to the lookup table -
bah! - the combo wizard does a lot of work for you but it's still enough to
put you off using combos altogether and simply type everything directly into
the table via a plain ol textbox - but then you'll end up with so many
typing errors you'll never find what you're after - i cant beleive how many
'professional' dabases i've used that are littered with typing
inconsistancies they're practically useless
i've played with databases for few years on/off and during twiddling i found
a great use for combos, such that my forms include more combos than
textboxes and i hope you find it useful.
irespective of the format (text, date, etc), more than 50% of your fields
will need to contain repetative data - a good example is a 'Town' field -
whilst they may vary over 1000 records, chances are there a less than a
dozen towns in your database - but you're unlikely to know what towns you'll
need when you're setting up the database - the trick is really to get the
combo to list all items that have already been typed into that same field
(no seperate lookup to worry about so no value to type in and no building
relationships) - it's bit tricky the first time (the wizard wont do it) but
real easy after some practise
1/ Add your 'Town' field to the table in regular way /regular field types
(dont use the lookup wizard it wont look at itself)
2/ Include you 'Town' field on the form as regular textbox (these will
already be bound read/write to the correct field)
3/ Right-Click the textbox and choose 'change to combo'
4/ Double-Click the combo and choose Data>Row Source
5/ Click the 3 dots to the right of the Row Source (this starts SQL Query
builder)
6/ Add your table and ONLY the 'town' field - you can sort it if you like
and even add criteria (e.g if it was appointment dates you could use >NOW so
it doesn't list old dates, just future ones)
7/ Close the query builder and choose 'Yes' - You will end up with sql
statment as your row source (e.g. SELECT MYTABLE.[Town] FROM MYTABLE ORD....
didda didda didda)
8/ After the word SELECT add the word DISTINCT (e.g. SELECT DISTINCT
MYTABLE.[Town] FROM MYTABLE didda didda didda) - This will make sure the
combo only list 'different' (Distinct) towns used, instead of listing every
single record in the whole database
That's It - Well almost* - Try It
The combo now lists all towns that have already been used (none yet if it's
new database) - If the town you want is already in the list just choose it -
if not, just type it in - the benefits are clear:
You dont need to create a seperate lookup table
You dont need to create a relationship (and even worse, maintain it while
developing the rest of the database)
You dont need to input the values (even if you know what they are)
No complex routine for adding new values to the list
* Here's the 'Almost' bit
have you noticed yet - when you type a new town in and move to another
record, the new town hasn't been added to the combo list? (unless you close
the form and reopen it but that's no good is it)
that's because the combo list needs refreshing each time you add a new
town - you cant refresh the combo list until the record has been updated
(becomes part of the database for it to find) - the record wont get updated
unless you save it or move to a different record
solution - refresh the combo list every time you move records - add one line
of VB code:
1/ open the Code window (Alt+F11)
2/ Open the form module (Double click it)
3/ Add this line:
------------------------------------------------
Private Sub Form_Current()
Town.Requery
'where 'Town' is the name of your combo
'this Form current event is fired every time the record changes
'Town.Requery refreshes the combo list
End Sub
------------------------------------------------
4/ close the VB window
5/ Enjoy!
Note to the Purists - i know i know - i'm storing lookup values as text
which use up a lot of room and are slower to catalogue, and the user can
still add illegal values - i wouldn't recommend it for databases over
100,000 records - but hey - it makes database lot less complicated and end
user friendliness is a peach
words "Combo Box" is enough to send shudders down your spine. They seem
potential useful on the surface but usually involve additional tables, look
ups, relationships, displaying many columns (hiding some), binding it to the
right field - using said form in a report, the report displays the wrong
field from the combo - if you want to add anything to a combo it's a proper
pain and wo betide you if you decide to change the table layout, or you've
forgotton one of the options so you have to add it to the lookup table -
bah! - the combo wizard does a lot of work for you but it's still enough to
put you off using combos altogether and simply type everything directly into
the table via a plain ol textbox - but then you'll end up with so many
typing errors you'll never find what you're after - i cant beleive how many
'professional' dabases i've used that are littered with typing
inconsistancies they're practically useless
i've played with databases for few years on/off and during twiddling i found
a great use for combos, such that my forms include more combos than
textboxes and i hope you find it useful.
irespective of the format (text, date, etc), more than 50% of your fields
will need to contain repetative data - a good example is a 'Town' field -
whilst they may vary over 1000 records, chances are there a less than a
dozen towns in your database - but you're unlikely to know what towns you'll
need when you're setting up the database - the trick is really to get the
combo to list all items that have already been typed into that same field
(no seperate lookup to worry about so no value to type in and no building
relationships) - it's bit tricky the first time (the wizard wont do it) but
real easy after some practise
1/ Add your 'Town' field to the table in regular way /regular field types
(dont use the lookup wizard it wont look at itself)
2/ Include you 'Town' field on the form as regular textbox (these will
already be bound read/write to the correct field)
3/ Right-Click the textbox and choose 'change to combo'
4/ Double-Click the combo and choose Data>Row Source
5/ Click the 3 dots to the right of the Row Source (this starts SQL Query
builder)
6/ Add your table and ONLY the 'town' field - you can sort it if you like
and even add criteria (e.g if it was appointment dates you could use >NOW so
it doesn't list old dates, just future ones)
7/ Close the query builder and choose 'Yes' - You will end up with sql
statment as your row source (e.g. SELECT MYTABLE.[Town] FROM MYTABLE ORD....
didda didda didda)
8/ After the word SELECT add the word DISTINCT (e.g. SELECT DISTINCT
MYTABLE.[Town] FROM MYTABLE didda didda didda) - This will make sure the
combo only list 'different' (Distinct) towns used, instead of listing every
single record in the whole database
That's It - Well almost* - Try It
The combo now lists all towns that have already been used (none yet if it's
new database) - If the town you want is already in the list just choose it -
if not, just type it in - the benefits are clear:
You dont need to create a seperate lookup table
You dont need to create a relationship (and even worse, maintain it while
developing the rest of the database)
You dont need to input the values (even if you know what they are)
No complex routine for adding new values to the list
* Here's the 'Almost' bit
have you noticed yet - when you type a new town in and move to another
record, the new town hasn't been added to the combo list? (unless you close
the form and reopen it but that's no good is it)
that's because the combo list needs refreshing each time you add a new
town - you cant refresh the combo list until the record has been updated
(becomes part of the database for it to find) - the record wont get updated
unless you save it or move to a different record
solution - refresh the combo list every time you move records - add one line
of VB code:
1/ open the Code window (Alt+F11)
2/ Open the form module (Double click it)
3/ Add this line:
------------------------------------------------
Private Sub Form_Current()
Town.Requery
'where 'Town' is the name of your combo
'this Form current event is fired every time the record changes
'Town.Requery refreshes the combo list
End Sub
------------------------------------------------
4/ close the VB window
5/ Enjoy!
Note to the Purists - i know i know - i'm storing lookup values as text
which use up a lot of room and are slower to catalogue, and the user can
still add illegal values - i wouldn't recommend it for databases over
100,000 records - but hey - it makes database lot less complicated and end
user friendliness is a peach
