representing a table in a form that uses foreign keys

[benoize]

HI all,

Just joined this group. I have a problem that I haven't been able to solve and can't seem to find on the net (probably I'm not googleing the right words).
I have a table (say tblA) that consists mostly of natural keys to other tables in which the actual data is stored (I think this is called normalization, but correct me if I'm wrong).
Now, I would like to see and add data in this table (via a form) not with the natural keys but with the original data. I would like to fill in new data in tblA using the original data (and not natural keys).
I established relationships with all the tables and I can create a form via a query that shows all the original data, but then I can't add data to the original table (tblA)...
I'm sure I could write VBA code that would solve this problem, but I was wondering whether there was an elegant solution to this problem...
ANy help would be greatly appreciated!

Regards,
Benoit

 

[YNWA]

Create a form using TableA as its Source. Then create subforms for Table B, Table C etc... Then these subforms should all link to the master form of TableA using Child/Master links in the forms properties.

eg.

Table A
ID is primary key
Name
DOB
Address

TableB
tableBID is PK
TableA_ID is FK
name
Sex
Age

The link on forms would be the PK in TableA and the FK in TableB.

May i ask what are all the keys you are storing in table A? Give us example field names and what you want them to store?

 

[benoize]

Hi YNWA,

Thanks for the info. I'll give you an example.

tblA has
ReportId_FK
ProductGroupId_FK
FileName
Month
value

tblReport:
ReportId_PK
ReportName

tblProductGroup
ProductGroupId_PK
ProductGroup

In the form I would like to see
ReportName, ProductGroup, month, file, value

instead of
ReportNameId_FK, ProductGroupId_FK , month, file, value

Also, when adding data I'd like to add the data restricted by the ReportName or ProductGroup. I don't want to have to fill in the FK's.

I hope this explains the situation a bit...
Thanks at any rate for the help!
Benoit

 

[YNWA]

So are tblReport and tblProductGroup lookup tables that feed drop down menus on your forms?

First off you need a ID field as a PK in tblA. Then relationship wise FK in tblA goes from FK to PK in tblReport and PK in tblProductGroup.

When you then have fields on the forms, you will use drop downs with 2 columns. Set widths of 0cm; 2cm. The bound column is 1, this will store the IDs in the tables but show the names on the forms.

You want to store the IDs in tables and not the names.

If I am on your form, what are you trying to achieve? Do you want to select a report to be stored against tblA or do you want to enter report details that relate to tblA?