SOLVED -Copy SubForm Primary Key to another SubForms Foreign Key
Designing a database for a Foodbank that computer illiterate people(volunteers) will/can correctly use.
Simplifying this has become involved. It has to generate the proper data for it to be useful.
Following basic rules of design I have 4 tables. 1 MainForm and 3 SubForms
MainForm – Client_info
- ClientID
- Name Fields, Gender, Birthdate
SubForm – Address
- AddressID, ClientID
- Address Fields, Including some list boxes to help keep the data consistent
SubForm – HouseHold
- HouseHoldID, ClientID
- 4 age groups fields
SubForm – DatesServed
- ServingNumberID, ClientID, HouseHoldID
- DateServed, ReturningClient CheckBox
I know how to copy a MainForm Primary Key to a SubForm Foreign Key.
Is there an easy, no Macro technique to copy a SubForm Primary Key to another SubForm Foreign Key?
Copy the HouseHold Primary Key, HouseHoldID, into the DatesServed Foreign Key, HouseHoldID
I have been trying to write a Macro for this but it has been a few years since I written any serious code.(4000+ lines)
And the information I have read, Access2Base & The Base Handbook, have helped some but both seem to be…not correct or consistent?
The HouseHoldID field must remain in the DatesServed Table. A Clients HouseHold data/numbers may change, in which case a new HouseHoldID for the Client will be generated.(Another Hurdle Approaching)
The old HouseHold ID is still needed data.
The DatesServed, DateServed date must reference the the correct HouseHoldID which contains the data/numbers at the time of the Clients visit.
*I think that is explained correctly? *
R
| Attachment | Size |
|---|---|
| Foodbank.png | 13.32 KB |
| Foodbank.odb | 16.14 KB |
- Used for entering new Clients and current Clients that exist on several hundred index cards
- The HouseHoldID in the DatesServed Table is not needed since this is only a reference for their last visit…one visit a month only
LookUp_Birthdate_Form
- Eventually will be used most of the time after the index cards are all entered
- Uses the Clients Birthdate and Gender to display a results table from which the Client is selected
- This lists a History table and other Client information
When I Move the subform accordingly and update the Master Slave Properties I lose some of the data results but the HouseHoldID transfers over.
| Attachment | Size |
|---|---|
| ClientInformationForm.jpg | 195.18 KB |
| BirthdateLookUpForm.jpg | 331.54 KB |
| BirthdateLookUpFormEdit.jpg | 340.37 KB |
I noticed some records missing in the table but not sure when those were deleted.
Thanks for the help. I may not had gone back to this otherwise for some time.
Now I just have to get the new HouseHold Record to occur,with a new HouseHoldID, when a change is made in the age fields.
UPDATE: 
I found the cause for the missing data. The Hierarchy of the SubForms was the cause. That’s another issue resolved. Only 1, 2, 3…left. 
But how do I turn off the grid lines/dots? 
Did it again to demonstrate the issue for a reply and it was working correctly. KEY WORD – was
Saved the form, closed it, saved the database, closed, reopen, loaded form and well…was. Let me finish what I was initially going to update to your reply.