Reference subform with SearchForRecord macro action

  • Thread starter Thread starter RL
  • Start date Start date
R

RL

Evening, I am struggling to reference a subform with a searchforrecord action.

I have a form: [f_ProspectsHomepage]
That form contains a subform: [f_ProspectsList]

There is a control in the f_ProspectsHomepage form: [ProspectSearch]

I want to run a searchforrecord macro which searches for the contents of the
[ProspectSearch] control in the [p_ID] field of the subform.

What should the following settings be?

Object type:
Object name:
Record:
Where Condition:

Any ideas?

Thanks in advance,
RL
 
Cheers Allen.

I'm still struggling with this I'm afraid! I don't need to reference a
control in the subform - I need to reference the subform itself. I'm pretty
sure that will require a variation of what you just sent, but I keep trying
various combinations, without much joy!

I thought the SearchForRecord action arguments would require the following:

Object type: Subform
Object name: Reference to the subform
Record: First
Where Condition: Subform field = Control on main form

I am comfortable with constructing the criteria, because I have used this
action successfully before, but the other way around (i.e. searching for a
subform control in a form).

I am however having problems with the object type - 'Subform' is not one of
the options. So, I have been trying to use form to then reference the subform
in the object name argument.

Can you shed any light on that? Is it possible, and if so what should the
argument be?

Many thanks,
RL

Allen Browne said:
You can refer to the control in your subform like this:
Forms![f_ProspectsHomepage]![f_ProspectsList].Form![ProspectSearch]

Explanation in:
Referring to Controls on a Subform
at:
http://allenbrowne.com/casu-04.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.
RL said:
Evening, I am struggling to reference a subform with a searchforrecord
action.

I have a form: [f_ProspectsHomepage]
That form contains a subform: [f_ProspectsList]

There is a control in the f_ProspectsHomepage form: [ProspectSearch]

I want to run a searchforrecord macro which searches for the contents of
the
[ProspectSearch] control in the [p_ID] field of the subform.

What should the following settings be?

Object type:
Object name:
Record:
Where Condition:

Any ideas?

Thanks in advance,
RL
Click to expand...
Click to expand...
 
I'm not sure that will be a productive way to approach this.

What I prefer to to is to use code to build a filter string. If there could
only be one mach (e.g. if you are searching on a primary key value), then
find that record; otherwise apply it as the filter for the form in the
subform control, so the user can step through the matching records to find
the one they want.

This example lets you put 2 controls on any form:
Find as you type - Filter forms with each keystroke
at:
http://allenbrowne.com/AppFindAsUType.html
The first is a combo where you select the field you wish to search. The
second is a text box where you type the value you want to find. You can copy
this onto any form (including a subform), and it works without you having to
write any code.

This example lets you put several search boxes on a form:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
The user fills in any values they want to find, and clicks a button. The
sample database shows an example of how to write write the code to build the
filter string. It then applies it to the form, using the line:
Me.Filter = strWhere
Me.FilterOn = True
To use that approach with a subform, you need to change those 2 lines to
read:
Me.Sub1.Form.Filter = strWhere
Me.Sub1.Form.FilterOn = True

I think you will find that this is a much more productive approach than
using the macro.

(BTW, I'm assuming that there is some reason why you can't just use the
LinkMasterFields/LinkChildFields properties of the subform control to
effectively filter it.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

RL said:
Cheers Allen.

I'm still struggling with this I'm afraid! I don't need to reference a
control in the subform - I need to reference the subform itself. I'm
pretty
sure that will require a variation of what you just sent, but I keep
trying
various combinations, without much joy!

I thought the SearchForRecord action arguments would require the
following:

Object type: Subform
Object name: Reference to the subform
Record: First
Where Condition: Subform field = Control on main form

I am comfortable with constructing the criteria, because I have used this
action successfully before, but the other way around (i.e. searching for a
subform control in a form).

I am however having problems with the object type - 'Subform' is not one
of
the options. So, I have been trying to use form to then reference the
subform
in the object name argument.

Can you shed any light on that? Is it possible, and if so what should the
argument be?

Many thanks,
RL

Allen Browne said:
You can refer to the control in your subform like this:
Forms![f_ProspectsHomepage]![f_ProspectsList].Form![ProspectSearch]

Explanation in:
Referring to Controls on a Subform
at:
http://allenbrowne.com/casu-04.html

RL said:
Evening, I am struggling to reference a subform with a searchforrecord
action.

I have a form: [f_ProspectsHomepage]
That form contains a subform: [f_ProspectsList]

There is a control in the f_ProspectsHomepage form: [ProspectSearch]

I want to run a searchforrecord macro which searches for the contents
of
the
[ProspectSearch] control in the [p_ID] field of the subform.

What should the following settings be?

Object type:
Object name:
Record:
Where Condition:
Click to expand...
Click to expand...
Click to expand...
 
Due to what I am using this functionality for, I don't think I can use either
of those suggestions. I'll try and explain what I am trying to achieve. I may
have overcomplicated something which there is a very simple method for. If
so, I appologise - I'm in the process of teaching myself Access by trial and
error, so am probably not using conventional methods!

I have 3 forms:

[f_ProspectHomepage]. This contains [f_ProspectList] as a subform.

[f_ProspectList]. This is a continuous form with a [q_ProspectList] record
source and 'Snapshot' recordset type. [q_ProspectList] is a query containing
a few fields from the [t_Prospects] table only.

[f_ProspectDetail]. This is a single form, containing all the fields from
the [t_Prospects] table.

If the user clicks on any prospect in the [f_ProspectList] subform, the
[f_ProspectDetail] form pops up for the corresponding record.

When the user closes the [f_ProspectDetail] pop up, [f_ProspectHomepage]
requeries so that any changes made are updated in the [f_ProspectList]
subform.

My problem is that when it requeries, the [f_ProspectList] subform returns
to the first record. I want it to return to the record they clicked on.

My attempt to achieve this was two extra actions in the OnClose event of the
[f_ProspectDetail] form.
1. SetValue action - set a hidden control on the [f_ProspectHomepage] to the
primary key of the record that was open in the [f_ProspectDetails] form.
2. SearchForRecord - go to the record in the [f_ProspectList] subform which
had that primary key.

The only bit I am struggling with, is the final step - finding the
appropriate record in the subform. SearchForRecord action does sound like it
would be the right one.

Any ideas?

RL

Allen Browne said:
I'm not sure that will be a productive way to approach this.

What I prefer to to is to use code to build a filter string. If there could
only be one mach (e.g. if you are searching on a primary key value), then
find that record; otherwise apply it as the filter for the form in the
subform control, so the user can step through the matching records to find
the one they want.

This example lets you put 2 controls on any form:
Find as you type - Filter forms with each keystroke
at:
http://allenbrowne.com/AppFindAsUType.html
The first is a combo where you select the field you wish to search. The
second is a text box where you type the value you want to find. You can copy
this onto any form (including a subform), and it works without you having to
write any code.

This example lets you put several search boxes on a form:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
The user fills in any values they want to find, and clicks a button. The
sample database shows an example of how to write write the code to build the
filter string. It then applies it to the form, using the line:
Me.Filter = strWhere
Me.FilterOn = True
To use that approach with a subform, you need to change those 2 lines to
read:
Me.Sub1.Form.Filter = strWhere
Me.Sub1.Form.FilterOn = True

I think you will find that this is a much more productive approach than
using the macro.

(BTW, I'm assuming that there is some reason why you can't just use the
LinkMasterFields/LinkChildFields properties of the subform control to
effectively filter it.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

RL said:
Cheers Allen.

I'm still struggling with this I'm afraid! I don't need to reference a
control in the subform - I need to reference the subform itself. I'm
pretty
sure that will require a variation of what you just sent, but I keep
trying
various combinations, without much joy!

I thought the SearchForRecord action arguments would require the
following:

Object type: Subform
Object name: Reference to the subform
Record: First
Where Condition: Subform field = Control on main form

I am comfortable with constructing the criteria, because I have used this
action successfully before, but the other way around (i.e. searching for a
subform control in a form).

I am however having problems with the object type - 'Subform' is not one
of
the options. So, I have been trying to use form to then reference the
subform
in the object name argument.

Can you shed any light on that? Is it possible, and if so what should the
argument be?

Many thanks,
RL

Allen Browne said:
You can refer to the control in your subform like this:
Forms![f_ProspectsHomepage]![f_ProspectsList].Form![ProspectSearch]

Explanation in:
Referring to Controls on a Subform
at:
http://allenbrowne.com/casu-04.html

Evening, I am struggling to reference a subform with a searchforrecord
action.

I have a form: [f_ProspectsHomepage]
That form contains a subform: [f_ProspectsList]

There is a control in the f_ProspectsHomepage form: [ProspectSearch]

I want to run a searchforrecord macro which searches for the contents
of
the
[ProspectSearch] control in the [p_ID] field of the subform.

What should the following settings be?

Object type:
Object name:
Record:
Where Condition:
Click to expand...
Click to expand...
Click to expand...
 
Here's some sample code that saves the value of the primary key (named
MyID), and finds it again after a requery:

Dim rs As DAO.Recordset
Dim strWhere As String
strWhere = "MyID = " & Me.MyID
Me.Requery
Set rs = Me.RecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Oops: it's gone"
Else
Me.Bookmark = rs.Bookmark
End If

You can use it on another form just by substituting a reference to the other
form instead of Me, e.g.:
Forms![Form1]![Sub1].Form

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

RL said:
Due to what I am using this functionality for, I don't think I can use
either
of those suggestions. I'll try and explain what I am trying to achieve. I
may
have overcomplicated something which there is a very simple method for. If
so, I appologise - I'm in the process of teaching myself Access by trial
and
error, so am probably not using conventional methods!

I have 3 forms:

[f_ProspectHomepage]. This contains [f_ProspectList] as a subform.

[f_ProspectList]. This is a continuous form with a [q_ProspectList] record
source and 'Snapshot' recordset type. [q_ProspectList] is a query
containing
a few fields from the [t_Prospects] table only.

[f_ProspectDetail]. This is a single form, containing all the fields from
the [t_Prospects] table.

If the user clicks on any prospect in the [f_ProspectList] subform, the
[f_ProspectDetail] form pops up for the corresponding record.

When the user closes the [f_ProspectDetail] pop up, [f_ProspectHomepage]
requeries so that any changes made are updated in the [f_ProspectList]
subform.

My problem is that when it requeries, the [f_ProspectList] subform returns
to the first record. I want it to return to the record they clicked on.

My attempt to achieve this was two extra actions in the OnClose event of
the
[f_ProspectDetail] form.
1. SetValue action - set a hidden control on the [f_ProspectHomepage] to
the
primary key of the record that was open in the [f_ProspectDetails] form.
2. SearchForRecord - go to the record in the [f_ProspectList] subform
which
had that primary key.

The only bit I am struggling with, is the final step - finding the
appropriate record in the subform. SearchForRecord action does sound like
it
would be the right one.

Any ideas?

RL

Allen Browne said:
I'm not sure that will be a productive way to approach this.

What I prefer to to is to use code to build a filter string. If there
could
only be one mach (e.g. if you are searching on a primary key value), then
find that record; otherwise apply it as the filter for the form in the
subform control, so the user can step through the matching records to
find
the one they want.

This example lets you put 2 controls on any form:
Find as you type - Filter forms with each keystroke
at:
http://allenbrowne.com/AppFindAsUType.html
The first is a combo where you select the field you wish to search. The
second is a text box where you type the value you want to find. You can
copy
this onto any form (including a subform), and it works without you having
to
write any code.

This example lets you put several search boxes on a form:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
The user fills in any values they want to find, and clicks a button. The
sample database shows an example of how to write write the code to build
the
filter string. It then applies it to the form, using the line:
Me.Filter = strWhere
Me.FilterOn = True
To use that approach with a subform, you need to change those 2 lines to
read:
Me.Sub1.Form.Filter = strWhere
Me.Sub1.Form.FilterOn = True

I think you will find that this is a much more productive approach than
using the macro.

(BTW, I'm assuming that there is some reason why you can't just use the
LinkMasterFields/LinkChildFields properties of the subform control to
effectively filter it.)

RL said:
Cheers Allen.

I'm still struggling with this I'm afraid! I don't need to reference a
control in the subform - I need to reference the subform itself. I'm
pretty
sure that will require a variation of what you just sent, but I keep
trying
various combinations, without much joy!

I thought the SearchForRecord action arguments would require the
following:

Object type: Subform
Object name: Reference to the subform
Record: First
Where Condition: Subform field = Control on main form

I am comfortable with constructing the criteria, because I have used
this
action successfully before, but the other way around (i.e. searching
for a
subform control in a form).

I am however having problems with the object type - 'Subform' is not
one
of
the options. So, I have been trying to use form to then reference the
subform
in the object name argument.

Can you shed any light on that? Is it possible, and if so what should
the
argument be?

Many thanks,
RL

:

You can refer to the control in your subform like this:

Forms![f_ProspectsHomepage]![f_ProspectsList].Form![ProspectSearch]

Explanation in:
Referring to Controls on a Subform
at:
http://allenbrowne.com/casu-04.html

Evening, I am struggling to reference a subform with a
searchforrecord
action.

I have a form: [f_ProspectsHomepage]
That form contains a subform: [f_ProspectsList]

There is a control in the f_ProspectsHomepage form: [ProspectSearch]

I want to run a searchforrecord macro which searches for the
contents
of
the
[ProspectSearch] control in the [p_ID] field of the subform.

What should the following settings be?

Object type:
Object name:
Record:
Where Condition:
Click to expand...
Click to expand...
Click to expand...
 
Back
Top