Relationship Issues

[OlBill]

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.

 

[OlBill]

What am I missing Mike? What's different from yours?

 

[mike60smart]

What am I missing Mike? What's different from yours?

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.

 

[GPGeorge]

What am I missing Mike? What's different from yours?

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.

 

[OlBill]

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.

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?

 

[OlBill]

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.

Thanks GPGeorge. I'll be back when I get the forms figured out. I'm sure Mike's example will help me.

 

[OlBill]

Hi Bill

The attached is one way of doing this.

Mike, I don't see how to add a new Asset?

 

[OlBill]

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?

 

[mike60smart]

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?

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:-

 

[OlBill]

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:-

Many thanks Mike. I'll try that later today or tomorrow. I'm bowed up today as we say here. I appreciate it.

 

[OlBill]

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?

 

[mike60smart]

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?

Hi Bill

See attached

 

[OlBill]

Hi Bill

See attached

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.

 

[OlBill]

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?

 

[OlBill]

If my LeadSampleIDFK and my AsbSampleIDFK matched my SampleIDPK, I'd be there I think. I'm getting all 2s.

 

[mike60smart]

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]

 

[OlBill]

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]

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?

 

[OlBill]

Ah, you changed the master on the frmLeadResultsSub to the control in the yellow text box?

 

[OlBill]

I think I see what you did. So now to see the results from the different tests, I switch the record in the frmSampleSub. I get it.

Since I'm doing it this way, can I change frmLeadResulsSub and frmAsbestosResultsSub from continuous to single form without tearing it up? It's only going to give me the result for that one Sample IDPK.

I did this and it worked fine. Thank you. Am I normalized now?

 

[mike60smart]

Maybe this will be easier to understand