Solved Using the same subforms on different forms utilising a dynamic query (1 Viewer)

Sam Summers

Registered User.
Local time
Today, 21:52
Joined
Sep 17, 2001
Messages
939
Hi,

I have 6 subforms that i want to use on multiple forms.

So to save creating a great many additional subforms and associated queries i am trying to make the subform queries dynamic.

At the moment i have this in the query criteria:

[Parent]![LocationRef]

But it isnt working?

The subforms work on their own with these queries but on the form they just show empty fields.

I can't link them them because the main form is using a cross tab query.

Anyone have any ideas?

Many thanks in advance
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:52
Joined
May 7, 2009
Messages
19,169
you can't use Parent in query.
use the form [Forms]![FormName]![LoationRef]
 

Sam Summers

Registered User.
Local time
Today, 21:52
Joined
Sep 17, 2001
Messages
939
you can't use Parent in query.
use the form [Forms]![FormName]![LoationRef]
Ok but that wont work because the Main forms name will be different each time.
Is there a way to make the queries dynamic?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:52
Joined
May 21, 2018
Messages
8,463
You could build the recordsource of the subform in the mainform's on current event.

dim strSql as string
strSql = "Select * from someQuery where LocationRef_ForeignKey = " & me.locationRef 'if numeric
'if string "Select * from someQuery where LocationRef_ForeignKey = '" & me.locationRef & "'"
me.SubformControlName.Form.recordsource = strSql
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 21:52
Joined
Jul 9, 2003
Messages
16,245
In this video:-

Subform - Nifty Access - Intro​


I demonstrate Stephen Covey's Time Management Matrix. I demonstrate how you can use one form in four separate subform/subreport controls. The VBA code sets the source object of the subform/subreport control which in this case is always the same form "sfrmMatrix" I then set the record source of the form using a function which provides a different record source depending on which set of data you want to display. More information on my website here:-

Time Management Matrix​


Sub-Forms​

 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:52
Joined
May 7, 2009
Messages
19,169
you can use technique in post #4
or you can create as many query as it may take.
 

Auntiejack56

Registered User.
Local time
Tomorrow, 08:52
Joined
Aug 7, 2017
Messages
175
You note that you can't link the subforms because the main form is a cross-tab.
I'm not 100% sure what you mean by that, but you can master/child link a subform to any main form. The link uses the value of a control on the main form - it doesn't matter what the data is (the main form doesn't even have to be bound).
It's quite ok to have an invisible control as the master link field on the main form, and populate it in code when the form opens, or indeed at any time. The linked subform will behave quite docile-ly and predictably whenever the contents of the control changes.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:52
Joined
May 21, 2018
Messages
8,463
It's quite ok to have an invisible control as the master link field on the main form, and populate it in code when the form opens, or indeed at any time. The linked subform will behave quite docile-ly and predictably whenever the contents of the control changes.
This would also be a good solution. Here is more detail
On the main form build a hidden textbox
"txtLink"
on the main form's current event
me.txtLink = value in whatever field you want to link to

in the subform control
Master Link Fields: [txtLink]
Child Link Fields: [LocationRef]
 

Sam Summers

Registered User.
Local time
Today, 21:52
Joined
Sep 17, 2001
Messages
939
You could build the recordsource of the subform in the mainform's on current event.

dim strSql as string
strSql = "Select * from someQuery where LocationRef_ForeignKey = " & me.locationRef 'if numeric
'if string "Select * from someQuery where LocationRef_ForeignKey = '" & me.locationRef & "'"
me.SubformControlName.Form.recordsource = strSql

This worked!! I ended up with this and it works:

Code:
Dim strSql As String
strSql = "Select * from Position1Qry where LocationRef = " & Me.LocationRef 'if numeric
'if string "Select * from someQuery where LocationRef_ForeignKey = '" & me.locationRef & "'"
Me.Position1QrySubform.Form.RecordSource = strSql
strSql = "Select * from Position2Qry where LocationRef = " & Me.LocationRef
Me.Position2QrySubform.Form.RecordSource = strSql
strSql = "Select * from Position3Qry where LocationRef = " & Me.LocationRef
Me.Position3QrySubform.Form.RecordSource = strSql
strSql = "Select * from Position4Qry where LocationRef = " & Me.LocationRef
Me.Position4QrySubform.Form.RecordSource = strSql
strSql = "Select * from Position5Qry where LocationRef = " & Me.LocationRef
Me.Position5QrySubform.Form.RecordSource = strSql
strSql = "Select * from Position6Qry where LocationRef = " & Me.LocationRef
Me.Position6QrySubform.Form.RecordSource = strSql

Thank you SO much
 

Sam Summers

Registered User.
Local time
Today, 21:52
Joined
Sep 17, 2001
Messages
939
I will also look at both your last two options as well - Thank you guys!
 

Users who are viewing this thread

Top Bottom