Relationship Issues
- Thread starter OlBill
- Start date
mike60smart
Registered User.
- Local time
- Today, 13:56
- Joined
- Aug 6, 2017
- Messages
- 2,164
Have you not looked at the suggested solution I uploaded?
If not then I take it you are happy enough to just rumble along getting nowhere.
Luck with your project.
Yes, I have looked at it. I was working on what I just posted when you sent that one. Mine looks very similar to yours, but some of the relationships on yours were off. Not linked to the corresponding fields. Understandable, given that I named them. I am burned out right now, I will look at it again in the morning. I don't see what's different, but it's no doubt me. I will look at it again in the morning. I do appreciate your help.
mike60smart
Registered User.
- Local time
- Today, 13:56
- Joined
- Aug 6, 2017
- Messages
- 2,164
The difference is the Input Form that opens at startup
This allows you to Select an Asset on the Main Form
Enter details of a Type of Test (Lead or Asbestos) (Ignore the Command Button for Add New test as this needs to be deleted)
Whichever Type of Test you are entering details about then the Subform contained within The Tab Control
allows you to enter the Test Results.
Attachments
GPGeorge
George Hepworth
- Local time
- Today, 05:56
- Joined
- Nov 25, 2004
- Messages
- 2,905
That relationship diagram looks good to me.
You'll implement it as a Main Form/Sub Form design. Each form, whether main form or sub form, will be bound to one table, or to a query based on that one table.
I didn't open the form, I was focused on the tables and relationships. I will look at it today.
I can delete the Add New Test Type Command Button on the frmSampleSubform?
Last edited:
Thanks GPGeorge. I'll be back when I get the forms figured out. I'm sure Mike's example will help me.
Mike, I don't see how to add a new Asset?
I have built my main form frmInput. My Sample Sub, frmSampleSub, and my two results subs-frmLeadResultssub and frmAsbestosResultssub.
My frmLeadResultssub is picking up the AssetIDPK in the LeadSampleIDFK fine. So it is associated with the Asset. What the result is not associated with the Sample. In my relationship it shows SampleIDPK one to LeadSampleIDFK many. I went in and chose the masters and orphans available to get what I have now.
Can I not link my results to the sample?
My frmLeadResultssub is picking up the AssetIDPK in the LeadSampleIDFK fine. So it is associated with the Asset. What the result is not associated with the Sample. In my relationship it shows SampleIDPK one to LeadSampleIDFK many. I went in and chose the masters and orphans available to get what I have now.
Can I not link my results to the sample?
mike60smart
Registered User.
- Local time
- Today, 13:56
- Joined
- Aug 6, 2017
- Messages
- 2,164
Hi Bill
1. In your table tblSample, you need to change SampleFor, which is currently a Text field, to SampleForID Long Integer
This is because you are going to use a Combobox to select the Person it is For.
2. You need to create a table named tblFor to list the people
3. Your Main Form "frmInput" you need to change the Record Source from a Select statement and just base this form on "tblAsset."
4. Your Subform "frmLeadResultsSub" is not linked Master to Child correctly
The results are based on the Sample and not the Asset.
Currently the Link Master is set as "AssetIDPK"
Currently the Link Child is set as "LeadSampleIDFK"
Change the Link Master to the following:-
Forms!frmInput!frmSampleSub.Form!SampleIDFK
Your Input Form should look like the attached:-
Attachments
Many thanks Mike. I'll try that later today or tomorrow. I'm bowed up today as we say here. I appreciate it.
I think I followed your instructions to the letter, but I'm not getting your results. I did make the SampleFor table and put the cboBox and that is working.
My leadSampleIDFK is not picking up the SampleIDPK. Was I supposed to change the child as well?
My leadSampleIDFK is not picking up the SampleIDPK. Was I supposed to change the child as well?
mike60smart
Registered User.
- Local time
- Today, 13:56
- Joined
- Aug 6, 2017
- Messages
- 2,164
Ok, now it's picking it up, but it's assigning all the LeadSampleIDFK to the same SampleIDPK on that asset. If you look at yours, it has 2 tests: SampleIDPK 2 and 3. Both Lead tests. When I put in the results in the other sub, the LeadSampleIDFK is 2 for both of them. See what I mean?
The LeadResultIDPK6 is not is not Sample showing SampleIDPK is the LeadSampleIDFK box.
Last edited:
No, now it's not working. Ok, now it's picking it up, but it's assigning all the LeadSampleIDFK to the same SampleIDPK on that asset. If you look at yours, it has 2 tests: SampleIDPK 2 and 3. Both Lead tests. When I put in the results in the other sub, the LeadSampleIDFK is 2 for both of them. See what I mean?
mike60smart
Registered User.
- Local time
- Today, 13:56
- Joined
- Aug 6, 2017
- Messages
- 2,164
Hi Bill
See the attached - The Subform for the Sample Results is now linked in a different way using the Unbound Control
named "txtSampleIDPK" set between both Subforms highlighted in Yellow.
=[Forms]![frmInput]![frmSampleSub].[Form]![SampleIDPK]
See the attached - The Subform for the Sample Results is now linked in a different way using the Unbound Control
named "txtSampleIDPK" set between both Subforms highlighted in Yellow.
=[Forms]![frmInput]![frmSampleSub].[Form]![SampleIDPK]
I see it, but I don't understand it. Where do I put the results for SampleIDPK3 on yours? Are you telling me to put the formula in the unbound control somewhere on mine?
Similar threads
