Form List Problem

[Paul Cooke]

Hi Guys hopefully you will kind enough to point me in the right direction I am sure this is simple but I just can't seem to work it out!!

I have a test DB with 2 tables

Contacts:

(Primary Key) ContactID - AutoNumber
FirstName - Text
LastName - Text
OccID - Number

Occupation:

(Primary Key) OccID - AutoNumber
Occupation - Number

I have created a 1 to many relationship between the primary key OccId and OccID in the Contacts table.

When I create a Contacts form based on this and go into form view the Occupation control shows the primary key number - not the text?

Also the same thing happens if i create a listbox on a form??

I am sure I am missing somthing simple here but for the life of me cannot work out what it is

Any help or advice greatly appriciated.

Many thanks

 

[boblarson]

1. Did you use lookups directly at table level? If so, get rid of them (and see here for why they are "Evil."

2. Make sure you have a COMBO box for selecting the item. You may have to go to FORMAT > CHANGE TO > Combo box from your text box (in Access 2003 and prior it is on the Format Menu and in 2007 and above it is a right-click option on the text box).

3. Let us know if you have issues setting up the combo or if you haven't done so before (I won't go into it now just in case you do and I don't waste a bunch of time typing it all out).

 

[CBrighton]

I assume "Occupation - Number" should read "Occupation - Text", otherwise I'm not sure which text you are expecting in the occupation control to display.

I agree with Bob, combobox is the way forward. Set it to be bound to the field in the contacts table but the row source to be based on both fields in the occupation table.

Set it to 2 columns, make sure the bound column relates to the ID field, change the column widths so the ID column is hidden.

The result is a drop-down box which looks like it contains only the text but actually saves the ID.

 

[Paul Cooke]

Thanks Guys I will do all that now and post back to you

Paul

 

[Paul Cooke]

Can i just quickly ask... I am assumming my relationship is set ok?

 

[CBrighton]

Looks fine to me.

 

[Paul Cooke]

Ok so good news and bad news !!

I re created the tables as before...


Contacts:

(Primary Key) ContactID - AutoNumber
FirstName - Text
LastName - Text
OccID - Number

Occupation:

(Primary Key) OccID - AutoNumber
Occupation - Text (sorry that was a typo on my original post)

The relationship is set to (pKey) OccID (Occupation tbl) to OccId (Contacts tbl)

I have created a form and used a combo box as per your advice and all is working fine with that

but (there usually is one!!) when i create a new form and insert a Listbox I created a select query as the row source and include all the controls from the Contact tbl

SELECT Contacts.ContactID, Contacts.Firstname, Contacts.LastName, Contacts.OccID FROM Contacts;

I set the column count to 4 and widths to 0;3;3;3; ect

When I view the list in formview the names show but the occupation column is showing a number (I assuming this is the OccId primary key number.

I've notice that the control source of the list box is empty?

does this make sense?

Thank you again for your advice

Paul

 

[boblarson]

1. You would have to bind the list box to a field from your query if you want the list box to save data.

2. If you don't want OccID to show, you would put 0";3";3";0". If you want the description to show you would need to include that in your query:

SELECT Contacts.ContactID, Contacts.Firstname, Contacts.LastName, Contacts.OccID, Occupation.Occupation FROM Contacts INNER JOIN Occupation.OccID = Contacts.OccID

 

[Paul Cooke]

Thanks again Bob but I am now get a Syntax error in From clause?

any ideas?

Paul

 

[boblarson]

Sorry, missed something (see red part):

SELECT Contacts.ContactID, Contacts.Firstname, Contacts.LastName, Contacts.OccID, Occupation.Occupation FROM Contacts INNER JOIN Occupation ON Occupation.OccID = Contacts.OccID

 

[Paul Cooke]

ha ha - thats working perfect now

Many thanks Bob & Cbrighton for your help yet again

Im very grateful

Paul