Jamie Poole
asked on
Link Subform and form with VBA
I would like to change the link criteria between a form and its subform based on a value in the subform. Is this possible. If the value is tbl_TechTime there is only one link field if it is tbl_History there are two link fields.
Thank you
Jamie
Thank you
Jamie
On Load - of subform
If Me.TableInformation.Value = "tbl_TechTime" Then
Me.sfrm_Image_Count.LinkChildField = "ProjectID"
Me.chldPricing.LinkMasterField = "ProjectKey"
sfrm_Image_Count is the subform
chldPricing is the form.
ASKER
Mike,
re:> I would like to change the link criteria between a form and its subform
Q-1: Do you want duplicate something similar to parent/child relationship (link)?
Yes
re:> tbl_TechTime
Q-2: Is table in the record source of the form or subform?
No it is a field in the table which is the record source for the form
re:> tbl_History
Q-3: Is table in the record sour ce of the form or subform?
No it is a field in the table which is the record source for the form
Q-4: What is the record source of your form and subform?
for the form - tbl_Pricing_All for the subform tbl_MonthlyEstimates
Q-5: Do you have parent/child joint presently? What fields?
They are joined on Child - ProjectID Master - ProjectKey
I would like to add Child - Status Master - ServiceName if the TableInformation field is tbl_History
Thank you
re:> I would like to change the link criteria between a form and its subform
Q-1: Do you want duplicate something similar to parent/child relationship (link)?
Yes
re:> tbl_TechTime
Q-2: Is table in the record source of the form or subform?
No it is a field in the table which is the record source for the form
re:> tbl_History
Q-3: Is table in the record sour ce of the form or subform?
No it is a field in the table which is the record source for the form
Q-4: What is the record source of your form and subform?
for the form - tbl_Pricing_All for the subform tbl_MonthlyEstimates
Q-5: Do you have parent/child joint presently? What fields?
They are joined on Child - ProjectID Master - ProjectKey
I would like to add Child - Status Master - ServiceName if the TableInformation field is tbl_History
Thank you
In the design view of the form, enter its recordsource property (the first option on the property sheet). Click on [...] to the right to invoke recordsource query. Add the desired fields if not already there. Now in the design view of the recordsource quesry, select View/SQL to copy and paste its content here for my use.
Do the same for the subform.
Mike
Do the same for the subform.
Mike
ASKER
Master form - SELECT tbl_Pricing_All.ProjectKey , tbl_Pricing_All.ServiceNam e
FROM tbl_Pricing_All;
Subform - SELECT tbl_Monthly_Estimate.Proje ctID, tbl_Monthly_Estimate.Statu s
FROM tbl_Monthly_Estimate;
FROM tbl_Pricing_All;
Subform - SELECT tbl_Monthly_Estimate.Proje
FROM tbl_Monthly_Estimate;
Master form -
SELECT ProjectKey, ServiceName FROM tbl_Pricing_All;
Subform -
SELECT ProjectID, Status FROM tbl_Monthly_Estimate;
Tables tbl_Pricing_All and tbl_Monthly_Estimate are used above. What are the other tables (tbl_TechTime and tbl_History) you are writing about?
How do they relate to your form and subform?
Mike
SELECT ProjectKey, ServiceName FROM tbl_Pricing_All;
Subform -
SELECT ProjectID, Status FROM tbl_Monthly_Estimate;
Tables tbl_Pricing_All and tbl_Monthly_Estimate are used above. What are the other tables (tbl_TechTime and tbl_History) you are writing about?
How do they relate to your form and subform?
Mike
My bad. I am reading your original question again.
Master form -
SELECT ProjectKey, ServiceName FROM tbl_Pricing_All;
^-- Parent
Subform -
SELECT ProjectID, Status FROM tbl_Monthly_Estimate;
^--- Child
On Load - of subform
' change Parent/Child on the fly. Your question: Is this possible?
' v-- Is "TableInformation" a text box. Is bound to [ServiceName]?
If Me.TableInformation.Value = "tbl_TechTime" Then
Me.sfrm_Image_Count.LinkCh ildField = "ProjectID"
' the following line needs to chsnge:
'Me.chldPricing.LinkMaster Field = "ProjectKey"
Me.sfrm_Image_Count.LinkMa sterField = "ProjectKey"
Else
' What if it is not Me.TableInformation.Value = "tbl_TechTime"
End if
1. Question: Is "TableInformation" a text box. Is bound to [ServiceName]?
2. Parent child links are both properties of the dubform container. so use:
'Me.chldPricing.LinkMaster Field = "ProjectKey"
Me.sfrm_Image_Count.LinkMa sterField = "ProjectKey"
3. Question: What if Me.TableInformation.Value <> "tbl_TechTime". If it will not
change the links why you need to have this code in the first place?
4. You need to step trough the code to see if it is producing some error or it
works okay. I will test it also myself. Certain property may not be reset on the fly
We have to see if this one does.
5. If it didn't work, there is a work around we will discuss.
Mike
SELECT ProjectKey, ServiceName FROM tbl_Pricing_All;
^-- Parent
Subform -
SELECT ProjectID, Status FROM tbl_Monthly_Estimate;
^--- Child
On Load - of subform
' change Parent/Child on the fly. Your question: Is this possible?
' v-- Is "TableInformation" a text box. Is bound to [ServiceName]?
If Me.TableInformation.Value = "tbl_TechTime" Then
Me.sfrm_Image_Count.LinkCh
' the following line needs to chsnge:
'Me.chldPricing.LinkMaster
Me.sfrm_Image_Count.LinkMa
Else
' What if it is not Me.TableInformation.Value = "tbl_TechTime"
End if
1. Question: Is "TableInformation" a text box. Is bound to [ServiceName]?
2. Parent child links are both properties of the dubform container. so use:
'Me.chldPricing.LinkMaster
Me.sfrm_Image_Count.LinkMa
3. Question: What if Me.TableInformation.Value <> "tbl_TechTime". If it will not
change the links why you need to have this code in the first place?
4. You need to step trough the code to see if it is producing some error or it
works okay. I will test it also myself. Certain property may not be reset on the fly
We have to see if this one does.
5. If it didn't work, there is a work around we will discuss.
Mike
re:> 1 and 3
I am waiting for your response.
re:> 4
from help file: "The properties can only be set in Design view or during the Open event of a form or report."
So, it should work okay after you change as descriobe under item 2 above.
Let me know what you come up with.
Mike
ASKER
eghtebas
If the Me.TableInformation.Value <> tbl_Techtime then I need the link between the form and subform to be Child - ProjectID;Status
Master - ProjectKey;ServiceName
The subform is based on a table and that table is populated by tbl_History and tbl_TechTime,
If the Me.TableInformation.Value <> tbl_Techtime then I need the link between the form and subform to be Child - ProjectID;Status
Master - ProjectKey;ServiceName
The subform is based on a table and that table is populated by tbl_History and tbl_TechTime,
To test a point, use:
If 1= 1 Then
Me.sfrm_Image_Count.LinkCh ildField = "ProjectID"
Me.sfrm_Image_Count.LinkMa sterField = "ProjectKey"
Else
' What if it is not Me.TableInformation.Value = "tbl_TechTime"
End if
and
If 1<>1 Then
Me.sfrm_Image_Count.LinkCh ildField = "ProjectID"
Me.sfrm_Image_Count.LinkMa sterField = "ProjectKey"
Else
' What if it is not Me.TableInformation.Value = "tbl_TechTime"
End if
to see if reseting of the parent/child would work.
The reason for this test is the moment you are trying to set the link, maybe the value of TableInformation.Value could not be read.
Mike
If 1= 1 Then
Me.sfrm_Image_Count.LinkCh
Me.sfrm_Image_Count.LinkMa
Else
' What if it is not Me.TableInformation.Value = "tbl_TechTime"
End if
and
If 1<>1 Then
Me.sfrm_Image_Count.LinkCh
Me.sfrm_Image_Count.LinkMa
Else
' What if it is not Me.TableInformation.Value = "tbl_TechTime"
End if
to see if reseting of the parent/child would work.
The reason for this test is the moment you are trying to set the link, maybe the value of TableInformation.Value could not be read.
Mike
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
eghtebas
The reason I am concerned about weather the tbl_MonthlyEstimated was populated by tbl_TechTime or tbl_History because one has a matching servicename/status fields and one does not. I want to test if the project information comes from tbl_History I want the ProjectID to link ProjectKey and Status to link ServiceName. If the information came from tbl_TechTime I only want to link ProjectID to ProjectKey.
The reason I am concerned about weather the tbl_MonthlyEstimated was populated by tbl_TechTime or tbl_History because one has a matching servicename/status fields and one does not. I want to test if the project information comes from tbl_History I want the ProjectID to link ProjectKey and Status to link ServiceName. If the information came from tbl_TechTime I only want to link ProjectID to ProjectKey.
ASKER
eghtebas
Here is the code I came up with, but I now get a runtime error 2465 when I run it.
Private Sub sfrm_Image_Count_Enter()
Dim rs As Recordset
Dim strSQL As String
strSQL = "SELECT * FROM tbl_Monthly_Estimate WHERE [ProjectID] = '" & Forms!frm_projectdata.Proj ectKey & "' "
Set rs = CurrentDb.OpenRecordset(my SQL)
If rs!TableInformation = "tbl_TechTime" Then
Me.sfrm_Image_Count.LinkCh ildFields = "ProjectID"
Me.sfrm_Image_Count.LinkMa sterFields = "ProjectKey"
Else
Me.sfrm_Image_Count.LinkCh ildFields = "ProjectID, Status"
Me.sfrm_Image_Count.LinkMa sterFields = "ProjectKey, ServiceName"
End If
End Sub
Here is the code I came up with, but I now get a runtime error 2465 when I run it.
Private Sub sfrm_Image_Count_Enter()
Dim rs As Recordset
Dim strSQL As String
strSQL = "SELECT * FROM tbl_Monthly_Estimate WHERE [ProjectID] = '" & Forms!frm_projectdata.Proj
Set rs = CurrentDb.OpenRecordset(my
If rs!TableInformation = "tbl_TechTime" Then
Me.sfrm_Image_Count.LinkCh
Me.sfrm_Image_Count.LinkMa
Else
Me.sfrm_Image_Count.LinkCh
Me.sfrm_Image_Count.LinkMa
End If
End Sub
re:> I would like to change the link criteria between a form and its subform
Q-1: Do you want duplicate something similar to parent/child relationship (link)?
re:> tbl_TechTime
Q-2: Is table in the record sour ce of the form or subform?
re:> tbl_History
Q-3: Is table in the record sour ce of the form or subform?
Q-4: What is the record source of your form and subform?
Q-5: Do you have parent/child joint presently? What fields?
Mike