Missing something here

[Wull]

I thought I had managed to get my head round the basics of Access but something, and probably not the only thing, has me stumped.

I have stripped it right back to basics so that I am looking at a part of the data in its simplest form. Two tables, Customer and City_County.

Customer:
(PK) Customer_ID AutoNumber
Customer_First_Name Text
Customer_Surname Text
Customer_Email Text
Customer_House_Number Text
Customer_Street Text
Customer_Street_2 Text
Customer_Postcode Text
City_County_ID Number

City_County:
(PK) City_County_ID AutoNumber
City Text
County Text


In both tables (PK) signify the Primary Key, it's not a part of the name.

In relationship I have both tables, clicked and dragged City_County_ID from City_County over City_County_ID in the Customer table. Ticked Enforce Referential Integrity and clicked connect.

Is this all that is required to make the foreign key?

Then using the form wizard I create a form, add all fields from the Customer table with the exception of City_County_ID and I add City and County from the City_County table.

When the wizard is done I get the error 'The wizard is unable to open your form in Form view or Datasheet view, possibly because another user has a source table open in exclusive mode. Your form will be opened in Design view.' Despite this error, it does open automatically in Form view. When I try to type anything into the fields that are taken from the Customer table I get a beep and no info is entered, the only fields that will let me enter anything are the ones taken from the City_County_ID table.

Any help would be appreciated.

 

[Wull]

Is it because I haven't included the foreign key that this is happening?

When I include the field City_County_ID in the Customer table during the creation of the form it seems to function ok with no error messages from the wizard.

At the moment the only solution I can see is to include it but then hide it within the form so that the end user is not bombarded with info he or she does not require. Is this the right approach?

 

[Rabbie]

I thought I had managed to get my head round the basics of Access but something, and probably not the only thing, has me stumped.

I have stripped it right back to basics so that I am looking at a part of the data in its simplest form. Two tables, Customer and City_County.

Customer:
(PK) Customer_ID AutoNumber
Customer_First_Name Text
Customer_Surname Text
Customer_Email Text
Customer_House_Number Text
Customer_Street Text
Customer_Street_2 Text
Customer_Postcode Text
City_County_ID Number

City_County:
(PK) City_County_ID AutoNumber
City Text
County Text


In both tables (PK) signify the Primary Key, it's not a part of the name.

In relationship I have both tables, clicked and dragged City_County_ID from City_County over City_County_ID in the Customer table. Ticked Enforce Referential Integrity and clicked connect.

Is this all that is required to make the foreign key?

YES

Then using the form wizard I create a form, add all fields from the Customer table with the exception of City_County_ID and I add City and County from the City_County table.

When the wizard is done I get the error 'The wizard is unable to open your form in Form view or Datasheet view, possibly because another user has a source table open in exclusive mode. Your form will be opened in Design view.' Despite this error, it does open automatically in Form view. When I try to type anything into the fields that are taken from the Customer table I get a beep and no info is entered, the only fields that will let me enter anything are the ones taken from the City_County_ID table.

Any help would be appreciated.

You really should base your form on a query. You can do this easily using the graphical interface to select the required fields from both tables.

Remeber that you have 1:many relationship between City_County and Customer

 

[Wull]

Ahhh, seems I was looking at that back to front. I thought that for customer details entry the form would be first then any further queries run from the form. Looks like I need to rethink my understanding of Access. Thanks for the pointer.