Creating form with one lock table and one edit table

[pacific0786]

I want to create a form using a table that I created but the foreign key is from the primary key of a locked table. Is this possible to do? If so how? When I create a form it does not allow me to make edits. I don't want to change the information from the locked table but I need the primary key to relate the extra information needed to be entered in the separate table.

The table locked is tblChild, and the primary key is ChildID, the form is frmChildDataEntry, and the other table that I created is tblFamily. I want the name of the child, and DOB to display but not for edit, and then I want to be able to add the Legal guardian, Foster Parents, Mother, Father...etc....Is this possible?

[zmbd]

First, don't lock any of your tables. This just adds a lot of complexity that you don't need.

Second you would benifit from reading:> Database Normalization and Table Structures.

What I would look at would be a parent-form/subform construction; however, you've not given us much information about your database design.
You would set the parent form as a non-editable and let the subform handle your data entry for the related records.

[pacific0786]

Thanks zmbd for responding....

The Access database that I am linking to is another company database that I am unable to unlock....This database stores most of the data but there are some data that I would like to add for our in-house reports. This is where I created additional tables. The locked tables are tblChild1, tblFamily1, tblEnrollment1, tblEligibility1, tblService1. Then the tables that I added were tblFamilyadd, tblEnrolladd, thbEligadd, tblObjectives, tblServiceadd...each linking the ChildID from the tblChild1 table, and from the tblFamily1 table. Your suggestion to set a parent form that is non-editable sounds like a great idea...and I will try it today. Any other suggestions are greatly appreciated. I am not sure what type of detail on the database design you want to know...please let me know what you would like to know about the design...bare with me I am so new at explaining this.....thanks so much....

[zmbd]

pacific0786
More like you need to be patient with me (^.-)

Sucks when you don't have control over the whole database.

What you have is basically a split database:Front-End / Back-End (FE/BE)

So, one really does not need to add tables for the ones in the other database... let's name yours as "BE_Company" for back-end-company.

Most likely the easiest is to link to these tables, which I hope you have already mastered.

You can then use these linked tables to provide the information you need.

I'm not really clear about what tables you have in the back end; however, this is what I think you have:
[BE_Company]![tblchild]![...relatedfields...]
[BE_Company]![tblFamily]![...relatedfields...]
[BE_Company]![tblEnrollment]![...relatedfields...][BE_Company]![tblEligibility]![...relatedfields...][BE_Company]![tblService]![...relatedfields...]

I hope that for each of these tables there is a primary key for example:
[BE_Company]![tblchild]![ChildID]
[BE_Company]![tblFamily]![FamilyID]
[BE_Company]![tblEnrollment]![EnrollmentID]
[BE_Company]![tblEligibility]![EligibilityID]
[BE_Company]![tblService]![ServiceID]

These would be the fields I would be interested in handling in my Front-End data base. We can write a query or open a recordset in VBA as needed to pull the Human-readable text.
(in case you're not familiar with the primary key:
Create or modify a primary key - the first section here covers it fairly well. I have gotten into the habit of using the autonumber as my primary key for variety of reasons one of which involved the fact that what was supposed to be a "fixed in stone" system of sample ID's turned out to be a mutable at the whim of the customer ID - however I digress)

Once again with a lot of hope, [BE_Company] is a related database and there is some relationships between each of the tables listed.

OK, from here...
It appears that there is not a table with the names of the parents, guardians, etc... and this is what you would like?


++I warn you now... this is off the top of my head... I don't guarentee that this will work++
Then in your front end I would have:
(please note:
PK = primary key
FK = foriegn key
table names are in quotes
[ ] is a field name
1:M means a table relationship 1 to Many

"tbl_role"
[role_pk] autonumber
[role_description] text(20)

"tbl_parentsguardians"
[parentsguardians_pk] autonumber
[parentsguardians_fname] text(20)
[parentsguardians_Lname] text(40)
[parentsguardians_fk_BECompanyFamily] numeric long 1:M

"tbl_childrelationship"
[childrelationship_pk] autonumber
[childrelationship_fk_BECompanyChild] numeric long 1:M
[childrelationship_fk_parentsguardians] numeric long 1:M
[childrelationship_fk_role] numeric long 1:M
[childrelationship_primary] boolean

I am making some assumptions here that I am expecting both
[BE_Company]![tblchild]![ChildID] and
[BE_Company]![tblFamily]![FamilyID] to be the data type Numeric and Long.
That the [BE_Company]![tblFamily]![...relatedfields...] has some information that relates to the adult.


"tbl_childrelationship" will have a record for each adult related to the child.

So, for the first query, base this on
[BE_Company]![tblchild]![...relatedfields...]

You will use this query to build the parent form, showing only those fields that you want the user to see. I would set the enabled property to false and lock.

Build your second query on "tbl_childrelationship"
Now notice that these are all numeric. When you build your subform, the related fields between the parent and the subform will be [BE_Company]![tblchild]![ChildID] to [childrelationship_fk_BECompanyChild].
The controls you setup will be comboboxes with the control source set to the subform's recordset, and the row source set to the "tbl_parentsguardians" and "tbl_role"

You will need a means to handle the parents and roles not already in the tables.

Please understand we try our best to help; however, there are certain basics that you will have to have some understanding of in order for us to be the most help with your project. This is an intermediate to advanced project so you will have to have a lot of patience with yourself and us. :)
The following will help with the jargon used here:
> Posting Guidelines
> How to ask good questions
> FAQ
> Before Posting (VBA or SQL) Code