auto fill in fields if left blank

[Guest]

Hi I'm getting confused here. I have a form where people will fill in a
species name, but I would like them to have the choice of filling in either
the common or scientific name, and then having access fill in the other one
automatically. I have another table that lists the scientific and common
names. I have already set up combo boxes so that people don't have to worry
about spelling mistakes. So they are currently selecting the name from a
list.

Thanks for your help.

 

[Steve Schapel]

Stephanie,

In the table that lists all the species, is there also another ID field
or some such, or is it just the 2 fields for the common and scientific
names? The form that you are referring to is bound to another table,
right? In that other table, do you mean you also have two separate
fields for the common or scientific names? If so, my recommendation
would be to change this. You should only have one field in the second
table for the species. There are a couple of approaches that can be
taken to this. Ine is to use an ID number for the species. Another is
to choose either the scientific or common name as the key field that
will identify the species in the other table, and the other one can be
looked up as required. In both scenarios, your combobox functionality
can be made to work well. Let's say you go with the ID idea... there
may in fact be an actual identification numbering system for species, in
which case use that, otherwise an Autonumber field in the Species table
will serve the purpose. So the fileds in the Species table are ID,
ScientificName, CommonName. And then just have the one field in the
second table, let's call it SpeciesID. Ok, both comboboxes on the form
are bound to the SpeciesID field, i.e. the Control Source property for
both will say SpeciesID. They will both use the Species table as the
Row Source (or you might want to make a query so they are sorted, for
example 'SELECT * FROM Species ORDER BY CommonName'). The difference
between them will be which column from the Species table Row Source is
visible, which you control via the settings of the Column Count and
Column Widths properties of the comboboxes. So one will probably have
like this:
Bound Column: 1
Column Count: 2
Column Widths: 0;3
.... and the other like this...
Bound Column: 1
Column Count: 3
Column Widths: 0;0;3

Ok, now you can select the scientific name from the ScientificID
combobox, and the common name will automatically be displayed in the
other combobox, or vice versa, but in both cases the SpeciesID is what
is being stored in the table. Make sense?

 

[Guest]

Thanks Steve, Sooo if I have this right, the the table people are entering
into will only show a record of the Species_ID and then you would do queries
to pull out the species names from that? That sounds fine to me, but I
already have a table populated with the data so how do I get it to fill in
the new species ID column (like you said I already have that in the species
table and it auto numbers the species) Im talking about adding the ID numbers
to the other table (not the species table).

Hopefully I haven't confused you!

 

[Guest]

Hi Steve, One more problem, I've done all the stuff you told me but when I
try to add new records I get an error message saying that my action isn't
valid, and that maybe I've entered text into a numeric field or my number is
too long for the field. This happens when I try to select one of the species
from the combo box.

 

[Steve Schapel]

Stephanie,

Yes, you have understood the concept perfectly.

First of all, make sure you have a backup copy of your database. Make a
query including both the tables, and join on an existing species name
field that is in both tables. Add the new SpeciesID field from the main
data table into the query design grid. Make it an update query (select
Update from the Query menu), and in the Update To row of the grid, enter
the equivalent of [Species].[ID]. Run the query (click the toolbar
button with the red [!] icon). After this you can remove the species
name field(s) from the main table.

 

[Steve Schapel]

Stephanie,

Can you please let us know what are the following properties of the
combobox?...

Control Source
Row Source
Bound Column
Column Count
Column Widths

Also, whatever is the Control Source, what is the Data Type of this
field in the table design?

Also, whatever is the Row Source, what are the fields and what is their
order in the design of the table?

Also, what is the exact wording of the error message?

Thanks.

 

[Guest]

Hi steve, the error message is this: The value you entered isn't valid for
this field. for example you may have entered text into a numeric field or a
number that is larger than the field size setting permits.

Control Source - Species_ID
Row Source - SELECT [Species List].[Common Name] FROM [Species List] ORDER
BY [Species List].[Common Name];
Bound Column - 1
Column Count - 2, 3 (for the 2 separate boxes)
Column Widths - 20

control source data type is number.

the row source table goes Species_ID, common name, scientific name.... then
several other columns with data about the species.

 

[Steve Schapel]

Stephanie,

Thanks. Please check the answers you gave me, some do not make sense.

Change the Row Source of the combobox to:
SELECT [Species_ID], [Common Name] FROM [Species List] ORDER BY
[Species List].[Common Name];

Column Count should be 2
Column Widths should be 0;x (where x = whatever number you need in order
to show the full text of the species name).

The other combobox should be set up with Row Source like this...
SELECT [Species_ID], [Scientific Name] FROM [Species List] ORDER BY
[Species List].[Scientific Name];
Other properties the same.

 

[Guest]

Thanks so much Steve!! It works perfectly now! I'm not too good with access
yet, just kind of got thrown into it and this forum has been so helpful.

Stephanie,

Thanks. Please check the answers you gave me, some do not make sense.

Change the Row Source of the combobox to:
SELECT [Species_ID], [Common Name] FROM [Species List] ORDER BY
[Species List].[Common Name];

Column Count should be 2
Column Widths should be 0;x (where x = whatever number you need in order
to show the full text of the species name).

The other combobox should be set up with Row Source like this...
SELECT [Species_ID], [Scientific Name] FROM [Species List] ORDER BY
[Species List].[Scientific Name];
Other properties the same.

--
Steve Schapel, Microsoft Access MVP

Hi steve, the error message is this: The value you entered isn't valid for
this field. for example you may have entered text into a numeric field or a
number that is larger than the field size setting permits.

Control Source - Species_ID
Row Source - SELECT [Species List].[Common Name] FROM [Species List] ORDER
BY [Species List].[Common Name];
Bound Column - 1
Column Count - 2, 3 (for the 2 separate boxes)
Column Widths - 20

control source data type is number.

the row source table goes Species_ID, common name, scientific name.... then
several other columns with data about the species.

 

[Steve Schapel]

Great! Thanks for letting us know, Stephanie. Best wishes with the
rest of the project. Access is fantastic once you get the hang of it.