Form creation w/autonumbers

[Guest]

Hi All-

A little background first. My tables:

ENGINEER
EngID (PK/Autonumber)
EngName (text)
EngDept (text)

CERTIFICATION
CertID (PK/Autonumber)
CertName (text)
CertVend (text)

ENGINEERCERTIFICATION
EngCertID (PK/Autonumber)
EngID (Number- long integer)
CertID (Number- long integer)
ExpDate (Date/Time)

What I'm looking to do is to create a form. I need to be able to select the
name of the engineer from a drop down menu, then select a certificatoin from
a drop down menu. I will then input the expiration date manually. The
probelm is, however, that in the EngineerCertification table, it wants me to
input numeric values (since it thinks I want EngID and CertID, when in
reality, I want to select the names of both of those).

My newbie DB instincts tell me that I need to keep the EngID and CertID to
keep the relationships in the table, but I have no idea how to proceed. Any
help would be appreciated, as the boss is getting antsy.

Thanks!

 

[Allen Browne]

Can you use a combo box to enter the EngID and CertID?

For example, you could set the RowSource of the EngID combo to this query:
SELECT EngID, EngName
FROM Engineer
ORDER BY EngName
and then set these properties as well:
Bound Column 1
Column Count 2
Column Widths 0
Since the bound column is zero-width, Access displays the EngName. The user
can enter by EngName, even though the value of the combo is EngID (i.e.
that's the bound column.)

 

[Guest]

Hi Nick -- You're right in that you should store the numeric ID for the
relationships but display the text in the drop-down. In design view, right
click on the combo box and select properties.

On the Data tab, in the Row Source field check to make sure you are
selecting the ID and the Name. If you need to add a field, click the elipse
(...) and select the field from the table . Also, the Bound field should be
set to 1 and and the Control Source field to your ID field.

On the Format tab, the Column Count should be 2. Change the Column Widths
to 0"; 2.5", where the latter can be adjusted to the desired size.

This is can also be accomplished by using the Control Wizard.

 

[Guest]

Brilliant! This seems to be working. Thanks a million!

 

[Guest]

OK the steps above/below work, however, when I select a certification from
the drop down, I get an invalid data type because Access is expecting a
numerical value, not a textual input. How would I work around this?

Much obliged!