Solved

Link Subform and form with VBA

Posted on 2008-10-21
13
1,356 Views
Last Modified: 2013-11-28
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


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.

Open in new window

0
Comment
Question by:life42
  • 8
  • 5
13 Comments
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 22768442
Yes, it is possible.

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
0
 

Author Comment

by:life42
ID: 22768506
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
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 22768710
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
0
 

Author Comment

by:life42
ID: 22768746
Master form - SELECT tbl_Pricing_All.ProjectKey, tbl_Pricing_All.ServiceName
FROM tbl_Pricing_All;
Subform - SELECT tbl_Monthly_Estimate.ProjectID, tbl_Monthly_Estimate.Status
FROM tbl_Monthly_Estimate;
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 22768952
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
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 22768969
My bad. I am reading your original question again.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 22769192
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.LinkChildField = "ProjectID"

   ' the following line needs to chsnge:
   'Me.chldPricing.LinkMasterField = "ProjectKey"
   Me.sfrm_Image_Count.LinkMasterField = "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.LinkMasterField = "ProjectKey"
       Me.sfrm_Image_Count.LinkMasterField = "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
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 22769322

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

0
 

Author Comment

by:life42
ID: 22769335
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,

0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 22769388
To test a point, use:

If 1= 1 Then
   Me.sfrm_Image_Count.LinkChildField = "ProjectID"
   Me.sfrm_Image_Count.LinkMasterField = "ProjectKey"
Else

' What if it is not Me.TableInformation.Value = "tbl_TechTime"
End if

and

If 1<>1 Then
   Me.sfrm_Image_Count.LinkChildField = "ProjectID"
   Me.sfrm_Image_Count.LinkMasterField = "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
 
0
 
LVL 33

Accepted Solution

by:
Mike Eghtebas earned 500 total points
ID: 22769476
re:> 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

Does this mean, in this case, you want a compisite link like
 
     ProjectID  ------->  ProjectKey    and
     Status  ---------->  ServiceName

?
--------------------
re:> The subform is based on a table and that table is populated by tbl_History and tbl_TechTime,

You gave me the recorsource of your subform as:

Subform -
SELECT ProjectID, Status FROM tbl_Monthly_Estimate;
                ^--- Child

tbl_Monthly_Estimate is populated by tbl_History and tbl_TechTime.

You are saying it is populated before hand. Why should we concerned about  tbl_History and tbl_TechTime while we are executing this code.

If you feel we are disconnected (not seeing eachother point), please delete this question and post a new one to get more expert attentiopn and a quick response.

Mike
0
 

Author Comment

by:life42
ID: 22769636
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.
0
 

Author Comment

by:life42
ID: 22770144
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.ProjectKey & "' "
Set rs = CurrentDb.OpenRecordset(mySQL)

If rs!TableInformation = "tbl_TechTime" Then
Me.sfrm_Image_Count.LinkChildFields = "ProjectID"
Me.sfrm_Image_Count.LinkMasterFields = "ProjectKey"
Else
Me.sfrm_Image_Count.LinkChildFields = "ProjectID, Status"
Me.sfrm_Image_Count.LinkMasterFields = "ProjectKey, ServiceName"
End If

End Sub
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now