Please help me understand this.

[pbaldy]

I see it as one-to-many because each title in the title table can appear many times in the employee table (for instance, you could have more than one supervisor). Notice that one-to-many is in the opposite direction as the one between employees and the junction table. Same with status; more than one employee could have any given status.

You've changed the subform so that the certificate is simply a textbox now. It was a combo, which is what I would have. I'd have a 2 column combo, first column is the ID field but hidden, second column is the name.

 

[voidcranium]

I see it as one-to-many because each title in the title table can appear many times in the employee table (for instance, you could have more than one supervisor). Notice that one-to-many is in the opposite direction as the one between employees and the junction table. Same with status; more than one employee could have any given status.

You've changed the subform so that the certificate is simply a textbox now. It was a combo, which is what I would have. I'd have a 2 column combo, first column is the ID field but hidden, second column is the name.

I can change it to a combo box but the only selection i get is the ID number not the actual text.
The subform on my form is linked to the EmployeeCertificate junction table.

It seems like I am taking 3 steps back for every step forward on this one.

It really should not be this complicated should it?

 

[pbaldy]

No, it shouldn't be. You need to change the SQL in the row source property to include both columns. You need to make sure the column count, width, and bound column properties are all set appropriately.

 

[voidcranium]

No, it shouldn't be. You need to change the SQL in the row source property to include both columns. You need to make sure the column count, width, and bound column properties are all set appropriately.

Thanks for you help pbaldy I really appreciate it.

2 things I have done with your suggestion.
ONE:
Control Source: CertificationID
Row Source Type:Table/Query
Row Source:
SELECT [CertificationTable].[CertificationID], [CertificationTable].[CertificationName] FROM CertificationTable;
Bound Column: 2

FORMAT
Column Count: 2

When I open the form the Sub Form CertificationID field now has a dropdown box and shows 2 columns; the ID and the name of each certificate.
When I select one I get the following message.

“The Value you entered isn’t valid for this filed.
For example, you may have entered text in a numeric field or a number that is larger than the FieldSize setting permits.”

I don’t think is the FieldSize.
Now the text/numeric field could be a problem but wouldn’t that mean changing my tables?

TWO:
Changed the query to only show a single column

Control Source: CertificationID
Row Source Type:Table/Query
Row Source:
SELECT [CertificationTable].[CertificationName] FROM CertificationTable;
Bound Column: 2

FORMAT
Column Count: 1

This time the CertificationID column only shows the certificates but when I select one nothing happens. No data fills the field; it's just blank but I get no message at all.

Could it be that my original tables are set up wrong?

 

[pbaldy]

For your first, the bound column should be 1 (the ID column), not 2 (the name column). Also, I would set the column widths to 0";1" so that the ID column was hidden from the user (or whatever width is appropriate for the second column).

For the second (which I wouldn't do anyway), the problem is you have the bound column as 2, but there is only 1 column in the combo, so...

 

[voidcranium]

For your first, the bound column should be 1 (the ID column), not 2 (the name column). Also, I would set the column widths to 0";1" so that the ID column was hidden from the user (or whatever width is appropriate for the second column).
...

Ok, I did that and now I have a drop down box with nothing in it.

 

[pbaldy]

Here's your most recent sample back.

 

[voidcranium]

Here's your most recent sample back.

Thanks for you help and patience pbaldy.
I opened your fixed copy up next to mine and I found the only difference was the column count; mine was 1 and yours was 2.

I did notice that the Rowsource SQL statement ended with "WITH OWNERACCESS OPTION;"
Is that because you opened and saved it?

Again thanks for all those who helped me.