Combo box does not work as I expected

[poppet]

Hi,
I have made the following pet database and form but perhaps I don't understand how the combo box works.

table - owners
primary key - owner id
ownername

table - pets
primary key - pet id
petname
foreign key - owner id

and created a relationship 1 to many.

What I would like to do on the form is when I create a pet I want to select the owner from a combo box and behind the scenes insert the owner id in the foreign key field. I know how to do this now but it doesn't look very nice.

For it to work I need both the foreign key field and the combo box to be visible on the form. Why do I need the foreign key to be visible? The user of the form is not interested in the foreign key. Can I hide it in some way?

When I do not have the foreign key on the pet creation form I cannot create a pet it says the link to the parent table is missing.


Not sure the above is clear but I hope you can help.
David.

[Bob Fitz]

Assuming that your form is bound to the "Pets" table, your combo box should be bound to the "foreign key - owner id" field. The combo box should use the "owners" table as its Rowsource. It should have two columns: first column "primary key - owner id" which should be hidden by setting its Column Width to 0. second column "ownername" with its Column Width property set to something greater than 0.
This will allow you to select an actual name in the combo box but will save the "owner id" field to the record in the pets table.

[poppet]

I don't know how to bind a form or a field.
I have access 2007.
What I am doing is selecting the pets table and then creating a form using create/form design
I put petsname field on the form.
Then I select the combobox.
I say that I want to select the values from a table.
I say that table is: owners
I select owner id & ownername from the available fields
I don't hide the key column. I set it to zero length.
I say that I want to store the owner id.
I try to store it in the foreign key field but that field is not available.

David.

[Bob Fitz]

Can you post a copy of the db

[poppet]

ok here it is.

form pet creation 1 appears to work but I don't want the owner id there.
form pet creation 2 does not allow me to update the foreign key.

thanks.
David.

[Bob Fitz]

form pet creation 1 appears to work but I don't want the owner id there.

I don't understand this comment.
Why not.
Where would you like it.

[poppet]

Perhaps I don't understand, but this is what I want.

I don't want the user to see the owner id on the form. I just want them to select the owners name from the combo box and that's it. (and the owner id is still updated correctly).

[Bob Fitz]

I don't want the user to see the owner id on the form. I just want them to select the owners name from the combo box and that's it. (and the owner id is still updated correctly).

But that is exactly how pet creation 1 form works.

[Bob Fitz]

Sorry, I see what you mean now. So just delete the text box from the form

[poppet]

Thank you.
It does appear to be working now and I can't get it not to work!

I was doing a couple of things wrong I think. I wasn't dragging the owner id on to the pet form and then deleting it later. Seems like a roundabout way of doing things. Couldn't the combobox just give you access to all pet fields rather than just the ones on the form?

Anyway. I am very happy now. (for the time being)
d.

[Bob Fitz]

Couldn't the combobox just give you access to all pet fields rather than just the ones on the form?

Sorry, but once again I don't understand. The combo box doesn't list any fields from the pet table fields. It lists fields from the owners table.

[poppet]

Sorry, my fault.
Can't the combo box wizard enable one to select any pet field at the point when it asks:
"Microsoft access can store the selected value from your combo box in your database"
At this point it is requiring a pet table field.

If you haven't drag and dropped 'ownerid' onto the form previously then ownerid will not be selectable at this point.

That's all I was saying. It's a rhetorical question I suppose. It's just how access works I guess.
d.