record source on subform

[Chegu Tom]

I have a main form NDEForm2 bound to a table with containing ProjectID
I have a subform called spoolweld that is linked to the main form on the
projectid fields. It lists the spools in that project
I have an unlinked subform called NDEWeld that should show detail records
for the spool selected in the spoolweld form.

In the SpoolWeld on Current event I put the following code. The strSQL
works fine but the attempt to change the record source of the NDEWeld form
cannot find the form. I have checked and rechecked spelling. I tried to
use the filter option to filter the records in the third form but I have had
trouble with that too

I am open to anyway to make the third form show details based on the current
record in the second form


Private Sub Form_Current()
Dim strSQL As String

strSQL = "SELECT NDEWelds.ProjectID, NDEWelds.Spool, NDEWelds.Test,
NDEWelds.WeldLetter, NDEWelds.Needed, NDEWelds.Done, NDEWelds.NDEperformed,
NDEWelds.NDEDate, NDEWelds.Weld FROM NDEWelds WHERE Projectid='" &
Me.ProjectID & "' AND Spool ='" & Me.Spool & "'"

Forms!NDEWeld.RecordSource = strSQL


End Sub

 

[Jeanette Cunningham]

Hi Chegu Tom,
Make a saved query for the record source for the form NDEWeld.
Include the fields from the sql you posted and be sure to include both
ProjectID and Spool.

On the button that opens this form, put code something like this untested
air code-->

Dim strCriteria As String

strCriteria = "[Projectid]=""" & Me.ProjectID & """ AND [Spool] =""" &
Me.Spool & """"

DoCmd.OpenForm "NDEWeld", WhereCondition:=strCriteria

The above should open NDEWeld to the record selected.

Note: I have used 3 double quotes around projectid and spool, there is one
double quote before projectid and one after the 3 double quotes after
Me.Spool.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Chegu Tom]

Thanks

Thanks for your solution. I had already done that. I was looking for a way
to change the content of an open subform on the main form without opening
and closing a popup form. This works close enough.

My problem was not in the building of rhe criteria string but in

Forms!NDEWeld.RecordSource = strSQL

Not recognizing the form. Do you see what is wrong with this? the form
this is called from and the called form are two subforms on a main form

Hi Chegu Tom,
Make a saved query for the record source for the form NDEWeld.
Include the fields from the sql you posted and be sure to include both
ProjectID and Spool.

On the button that opens this form, put code something like this untested
air code-->

Dim strCriteria As String

strCriteria = "[Projectid]=""" & Me.ProjectID & """ AND [Spool] =""" &
Me.Spool & """"

DoCmd.OpenForm "NDEWeld", WhereCondition:=strCriteria

The above should open NDEWeld to the record selected.

Note: I have used 3 double quotes around projectid and spool, there is one
double quote before projectid and one after the 3 double quotes after
Me.Spool.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I have a main form NDEForm2 bound to a table with containing ProjectID
I have a subform called spoolweld that is linked to the main form on the
projectid fields. It lists the spools in that project
I have an unlinked subform called NDEWeld that should show detail records
for the spool selected in the spoolweld form.

In the SpoolWeld on Current event I put the following code. The strSQL
works fine but the attempt to change the record source of the NDEWeld
form cannot find the form. I have checked and rechecked spelling. I
tried to use the filter option to filter the records in the third form
but I have had trouble with that too

I am open to anyway to make the third form show details based on the
current record in the second form


Private Sub Form_Current()
Dim strSQL As String

strSQL = "SELECT NDEWelds.ProjectID, NDEWelds.Spool, NDEWelds.Test,
NDEWelds.WeldLetter, NDEWelds.Needed, NDEWelds.Done,
NDEWelds.NDEperformed, NDEWelds.NDEDate, NDEWelds.Weld FROM NDEWelds
WHERE Projectid='" & Me.ProjectID & "' AND Spool ='" & Me.Spool & "'"

Forms!NDEWeld.RecordSource = strSQL


End Sub

 

[Jeanette Cunningham]

Now we're getting somewhere.

Use -->
Me.Parent.NameOfSubformControl.Form.RecordSource = strSQL

The above is how you do it when running code from one subform to change
something on another subform on the same form.
The reason is that
Forms!NameOfForm .RecordSource
can only be used for separate forms that are open as a separate form.
Access does not treat subforms as separate forms when they are on a parent
form.

Replace NameOfSubformControl with the actual name of the subform control.
The subform control may have the same name as the subform inside it, or it
might be different.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Thanks

Thanks for your solution. I had already done that. I was looking for a
way to change the content of an open subform on the main form without
opening and closing a popup form. This works close enough.

My problem was not in the building of rhe criteria string but in

Forms!NDEWeld.RecordSource = strSQL

Not recognizing the form. Do you see what is wrong with this? the form
this is called from and the called form are two subforms on a main form

Hi Chegu Tom,
Make a saved query for the record source for the form NDEWeld.
Include the fields from the sql you posted and be sure to include both
ProjectID and Spool.

On the button that opens this form, put code something like this untested
air code-->

Dim strCriteria As String

strCriteria = "[Projectid]=""" & Me.ProjectID & """ AND [Spool] =""" &
Me.Spool & """"

DoCmd.OpenForm "NDEWeld", WhereCondition:=strCriteria

The above should open NDEWeld to the record selected.

Note: I have used 3 double quotes around projectid and spool, there is
one double quote before projectid and one after the 3 double quotes after
Me.Spool.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I have a main form NDEForm2 bound to a table with containing ProjectID
I have a subform called spoolweld that is linked to the main form on the
projectid fields. It lists the spools in that project
I have an unlinked subform called NDEWeld that should show detail
records for the spool selected in the spoolweld form.

In the SpoolWeld on Current event I put the following code. The strSQL
works fine but the attempt to change the record source of the NDEWeld
form cannot find the form. I have checked and rechecked spelling. I
tried to use the filter option to filter the records in the third form
but I have had trouble with that too

I am open to anyway to make the third form show details based on the
current record in the second form


Private Sub Form_Current()
Dim strSQL As String

strSQL = "SELECT NDEWelds.ProjectID, NDEWelds.Spool, NDEWelds.Test,
NDEWelds.WeldLetter, NDEWelds.Needed, NDEWelds.Done,
NDEWelds.NDEperformed, NDEWelds.NDEDate, NDEWelds.Weld FROM NDEWelds
WHERE Projectid='" & Me.ProjectID & "' AND Spool ='" & Me.Spool & "'"

Forms!NDEWeld.RecordSource = strSQL


End Sub

 

[Chegu Tom]

Thank you for the solution and the explanation

"access does not treat subforms as separate forms......" Explains my
frustrations

Tom

Now we're getting somewhere.

Use -->
Me.Parent.NameOfSubformControl.Form.RecordSource = strSQL

The above is how you do it when running code from one subform to change
something on another subform on the same form.
The reason is that
Forms!NameOfForm .RecordSource
can only be used for separate forms that are open as a separate form.
Access does not treat subforms as separate forms when they are on a parent
form.

Replace NameOfSubformControl with the actual name of the subform control.
The subform control may have the same name as the subform inside it, or it
might be different.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Thanks

Thanks for your solution. I had already done that. I was looking for a
way to change the content of an open subform on the main form without
opening and closing a popup form. This works close enough.

My problem was not in the building of rhe criteria string but in

Forms!NDEWeld.RecordSource = strSQL

Not recognizing the form. Do you see what is wrong with this? the form
this is called from and the called form are two subforms on a main form

Hi Chegu Tom,
Make a saved query for the record source for the form NDEWeld.
Include the fields from the sql you posted and be sure to include both
ProjectID and Spool.

On the button that opens this form, put code something like this
untested air code-->

Dim strCriteria As String

strCriteria = "[Projectid]=""" & Me.ProjectID & """ AND [Spool] =""" &
Me.Spool & """"

DoCmd.OpenForm "NDEWeld", WhereCondition:=strCriteria

The above should open NDEWeld to the record selected.

Note: I have used 3 double quotes around projectid and spool, there is
one double quote before projectid and one after the 3 double quotes
after Me.Spool.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


I have a main form NDEForm2 bound to a table with containing ProjectID
I have a subform called spoolweld that is linked to the main form on
the projectid fields. It lists the spools in that project
I have an unlinked subform called NDEWeld that should show detail
records for the spool selected in the spoolweld form.

In the SpoolWeld on Current event I put the following code. The strSQL
works fine but the attempt to change the record source of the NDEWeld
form cannot find the form. I have checked and rechecked spelling. I
tried to use the filter option to filter the records in the third form
but I have had trouble with that too

I am open to anyway to make the third form show details based on the
current record in the second form


Private Sub Form_Current()
Dim strSQL As String

strSQL = "SELECT NDEWelds.ProjectID, NDEWelds.Spool, NDEWelds.Test,
NDEWelds.WeldLetter, NDEWelds.Needed, NDEWelds.Done,
NDEWelds.NDEperformed, NDEWelds.NDEDate, NDEWelds.Weld FROM NDEWelds
WHERE Projectid='" & Me.ProjectID & "' AND Spool ='" & Me.Spool & "'"

Forms!NDEWeld.RecordSource = strSQL


End Sub