Autofill Combo Boxes

[Frank]

All

Quite new to Access 2000.

I would like to create a form with 3 fields:
Bird Species
Scientific Name
Species ID number

each in its own combo box.

Each 'Bird Species' has a related 'Scientific Name' and 'Species I
Number'.

I want to enter data into any one of the combo boxes and have th
related data autofill in the other 2.

Or could I have the other 2 combos scrolling through their data as
scroll through the 'Bird Species'?


Can you help?

Thanks in advance.


Fran

 

[Steve Schapel]

Frank,

Are you aware that you can have a combobox display more than one column
in the drop-down list? What is your purpose in wanting the three
comboboxes? Is it so you can have the list in three different sort
orders? If you have a combobox, it presupposes that you already have a
table which lists the data in these three fields, which can serve as the
RowSource of the combobox. Is this form then bound to another table?
And you are trying to add new records to the other table? Or are you
trying to locate an existing record in the existing table? Ok, I'll
stop asking questions now. Maybe if you could post back with some more
information about what you are trying to achieve, it will make more
sense, and someone will be able to advise.

 

[Frank]

Steve

Thanks for your reply.

What I'm trying to do is compile a database of bird sightings whic
will then be sent in to a local ornithological society for inclusion i
their county (England) records.

I am aware that more than one field can be in a combo but when the
import my sightings into their database (access based), the 'bir
species', 'scientific name' and 'species ID number' are imported int
their own fields which, requires my database to contain each of thes
in their own field to facilitate the upload.

As each 'bird species' has a related 'scientific name' and 'ID number
and, as I need to add new records (sightings) regularly, I would lik
to type in or choose the species name from a drop down list and hav
the other fields (scientific and ID) fill in automatically in order t
save a lot of typing.

I have each of three fields in their own table and each table has
primary key (is this needed?).
I also have a table containing all three fields along with a primar
key.

Whether I enter the info into a form or directly into a table makes n
difference to me unless you have a reason why one is preferred to th
other.

As for rowsources, joining tables, relationships etc., please treat m
like a complete newbie and explain in simple terms, as my knowledge o
access jargon is limited to the basics and my basics are probabl
well short of yours

Hope this makes more sense.

Thanks and regards

Fran

 

[Steve Schapel]

Frank,

Very good. Thanks for the clear explanation of your needs.

You will possibly be pleased to know that the way you are trying to do
things is unnecessarily complicated.

Ok, so you have a table with 4 fields:
Bird Species
Scientific Name
Species ID
Primary Key

Fair enough. However, you can delete the primary key field from this
table. If I understand you correctly, it is redundant. Since the
purpose of a primary key field is to uniquely identify each record in
the table, any of the three other fields can serve as the primary key
for the table. I would delete the primary key field you currently have,
and set the Species ID to be the primary key in the table.

And now you need to record your Sightings. It is incorrect to think
that you need the Bird Species, Scientific Name, and Species ID fields
in this table. Absolutely. This relates to the whole point of using a
database programme like Access! You only need a Species ID field, so
that the sightings can be related to the Birds table. So I *imagine*
your Sightings table might have this structure...
SightingID (autonumber)
SightingDateAndTime
Location
Species ID
NumberOfBirds
BehaviourNotes
whatever

Any other tables are surplus to requirements, and should be deleted from
your database.

Ok, now your Sightings data gets recorded in a form which is based on
the Sightings table. And the Species ID field gets represented on this
form by a combobox. And the Row Source of the combobox is the Birds
table. Which of the 3 Bird fields is displayed on the form depends on
which one you relate to personally as the way you identify the bird, but
it is the Species ID which is stored. I can give more specific
information about setting this up if need be. If you want more than one
of the Bird fields displayed on the form, this is also easy to set up,
but you would not enter the data in each one each time!

Then, when it comes time to send your data to the ornithological
society, you do so via a Query, not via a Table. You know why? Because
some of the data is in one table and some is in another, and these two
tables are in a one-to-many relationship with each other. You would
make a query and add both of your tables to the query design. The
tables will be joined on the Species ID field from both. Then you place
all three fields from the Birds table into the query design grid, and
also all of the fields from the Sightings table which you wish to have
included in the data sent to the society. And then, you export the data
from the query into whatever format you will be using to send to the
society.

 

[Frank]

Steve

Thanks for your help.

Have set up the database as you suggested and it works a treat...an
I've learned in the process.

Regards

Fran

 

[Frank]

Steve

Another question!

In the database I have a list of 'Locations' each with an associate
'Grid Ref' contained in a 'Location' table.

On the 'Sightings form' I have the 'Location' as a combo box and th
'Grid Ref' as a text box (I know the Grid Ref box is not really neede
on the form but you will understand when you read on)....all works wel
when running the 'Sightings Query'.

However, there are times when I need to use the same 'Location' fro
the combo but with a more accurate grid reference than the on
associated with it in the 'Location' table.

When I tried putting the accurate grid ref into the form, the grid ref
in both the location table and all previous records at that locatio
changed to the new typed in value.

So is it possible for the database to allow me to enter a more accurat
Grid Ref when needed but to default to the associated Grid Ref if
don't type one in?


Also just noticed another problem...Adding new records via th
sightings form...sightings table updates with new sightings but th
'sightings query' doesn't update at the same time.

P.S. After looking at this, the Query doesn't update if I use
'Location' other than those in the combo box, i.e. when I type in a ne
'location'.
So I need the flexiblilty to add new 'locations' and 'grid refs' whic
are not contained in the combo. Would I need to add these new location
and grid refs to the locations table for this to work or is ther
another way?
Here is the sightings query SQL:

SELECT Sightings.Date, Birds.Species, Birds.[Scientific Name]
Birds.[Cawos ID], Sightings.Number, [Site Locations].Location, [Sit
Locations].[Grid Ref], [Site Locations].[Site ID], Sightings.Comments
FROM [Site Locations] INNER JOIN (Birds INNER JOIN Sightings O
Birds.Species = Sightings.Species) ON [Site Locations].Location
Sightings.Location;

Hope you understand.

Just one other thing... how do I get the comboboxes to scroll dow
using the wheel on the mouse rather than the scroll bar?


Thanks

Fran

 

[Steve Schapel]

Frank,

As you have discovered, if you want to identify the grid ref of a
sighting by reference to the locations as listed in the Locations table,
then each defined Location can only have one associated Grid Ref. As
you can see from the query, the Grid Ref in the query, and hence on the
form, is in the Site Locations table, associated with the specific
Location. If you edit this on the form, you are in effect editing the
Grid Ref in the Site Locations table. In these sorts of circumstances,
it is probably usual practice for the Grid Ref textbox on the form to
have its Locked property set to Yes to prevent accidental tampering with
lookup reference data.

How to handle it will depend to some extent on the real-life data
recording practices. One way is for each distinct grid reference to be
included in the Site Locations table as a separate record. This would
mean one of these scenarios:
1. Persisting with the current concept of the Location field being the
key identifier, and therefore having to enter a distinct name for the
Location of each Grid Ref you use.
2. Using the Grid Ref instead as the key field, so you could have more
than one grid reference with the same Location name. The implication of
this is that the Sightings table will have a Grid Ref field instead of a
Location field, and this would be the basis of the relationship with the
Site Locations table.
3. Add another field, Autonumber data type, let's call it LocationID,
to the Site Locations table, and this is the primary key field. This
gives you total freedom to enter whatever Location names and grid
references you like, which will be available to you via the query for
your reporting and data export. And the Sightings table will record the
LocationID via the combobox.

On the basis of what I know so far about your project, I would rank
these 3, 2, 1. Of course, this implies a change in the data structure
which would require an adjustment to, or re-entry of, any existing data.

The other option, of having a Grid Ref as well as a Locatio field in the
Sightings table, would not be a good idea in my opinion.

As regards scrolling in the combobox, I'm sorry I don't myself have a
mouse with a wheel, so I am not familiar enough to give an answer, but
as far as I know it is not possible to do what you ask. Are you aware
that if the Auto Expand property of the combobox is set to Yes, this
means you can move to that section of the combobox list alphabetically
by typing the first, and then subsequent, letters of the item you want.?
And if the list of items is long, it may help to do this code on the On
Load event of the form...
Dim lngTemp As Long
lngTemp = Me.YourComboboxName.ListCount

By the way, as an aside, the word Date has a special meaning in Access
(it is called a 'reserved word') and as such it is not a good idea to
use it as the name of a field or control.

--
Steve Schapel, Microsoft Access MVP

Steve

Another question!

In the database I have a list of 'Locations' each with an associated
'Grid Ref' contained in a 'Location' table.

On the 'Sightings form' I have the 'Location' as a combo box and the
'Grid Ref' as a text box (I know the Grid Ref box is not really needed
on the form but you will understand when you read on)....all works well
when running the 'Sightings Query'.

However, there are times when I need to use the same 'Location' from
the combo but with a more accurate grid reference than the one
associated with it in the 'Location' table.

When I tried putting the accurate grid ref into the form, the grid refs
in both the location table and all previous records at that location
changed to the new typed in value.

So is it possible for the database to allow me to enter a more accurate
Grid Ref when needed but to default to the associated Grid Ref if I
don't type one in?


Also just noticed another problem...Adding new records via the
sightings form...sightings table updates with new sightings but the
'sightings query' doesn't update at the same time.

P.S. After looking at this, the Query doesn't update if I use a
'Location' other than those in the combo box, i.e. when I type in a new
'location'.
So I need the flexiblilty to add new 'locations' and 'grid refs' which
are not contained in the combo. Would I need to add these new locations
and grid refs to the locations table for this to work or is there
another way?
Here is the sightings query SQL:

SELECT Sightings.Date, Birds.Species, Birds.[Scientific Name],
Birds.[Cawos ID], Sightings.Number, [Site Locations].Location, [Site
Locations].[Grid Ref], [Site Locations].[Site ID], Sightings.Comments
FROM [Site Locations] INNER JOIN (Birds INNER JOIN Sightings ON
Birds.Species = Sightings.Species) ON [Site Locations].Location =
Sightings.Location;

Hope you understand.

Just one other thing... how do I get the comboboxes to scroll down
using the wheel on the mouse rather than the scroll bar?


Thanks

Frank