I have a toolbar that I want to assign to multiple forms that are viewed in
Datasheet view mode only. I have assigned a Function on the toolbar to run an
On Acxtion command. Each form has a different Recordsource (qryAll,
qryTotals, qryParts, etc). The following works well on one form only. As the
form and Recordsource change, I need to be able to recognize the current
Recordsouce so I don't have to create a separate toolbar for each form only
to change qryAll to qryTotals. Any help appreciated.

Function QTag()
Dim strWhere As String
Dim strMsg As String

With CodeContextObject
strWhere = "WHERE " & .Filter
End With

strMsg = "FILTERED records will be tagged."
If (MsgBox(strMsg, 273, "Warning") <> 1) Then
'Tag filtered records on form
DoCmd.CancelEvent
Exit Function
End If

DoCmd.SetWarnings False

'This is where I need to vary the recorsource below
DoCmd.RunSQL "UPDATE qryAll " & _
"SET qryAll.T = '-1'" & strWhere

DoCmd.Requery
DoCmd.SetWarnings True

End Fuction

On Sat, 30 Jan 2010 08:12:06 -0800, NEWER USER
<(E-Mail Removed)> wrote:

There is a better way to filter: rather than changing the
RecordSource: set the Filter and FilterOn property.

For readability change your MsgBox statement to something like:
If (MsgBox(strMsg, vbYesNo or vbQuestion, "Warning") = vbNo) Then

-Tom.
Microsoft Access MVP


>I have a toolbar that I want to assign to multiple forms that are viewed in
>Datasheet view mode only. I have assigned a Function on the toolbar to run an
>On Acxtion command. Each form has a different Recordsource (qryAll,
>qryTotals, qryParts, etc). The following works well on one form only. As the
>form and Recordsource change, I need to be able to recognize the current
>Recordsouce so I don't have to create a separate toolbar for each form only
>to change qryAll to qryTotals. Any help appreciated.
>
>Function QTag()
> Dim strWhere As String
> Dim strMsg As String
>
> With CodeContextObject
> strWhere = "WHERE " & .Filter
> End With
>
> strMsg = "FILTERED records will be tagged."
> If (MsgBox(strMsg, 273, "Warning") <> 1) Then
> 'Tag filtered records on form
> DoCmd.CancelEvent
> Exit Function
>End If
>
>DoCmd.SetWarnings False
>
>'This is where I need to vary the recorsource below
>DoCmd.RunSQL "UPDATE qryAll " & _
> "SET qryAll.T = '-1'" & strWhere
>
>DoCmd.Requery
>DoCmd.SetWarnings True
>
>End Fuction

I don't think we connected. Normally, I would place a command button on the
form to run the Update SQL based on the open form and any filters that may be
applied since opening the form. Howver, datasheet view doesn't allow for a
command button, so I chose to place a toolbar assigned to the open form and
run a Function (On Action property). If I were to place the toolbar on
another form with a differ recorsource, I would have to create a new toolbar
and function that references the current form. I want to have one toolbar on
several different forms and have the Function recognize the Current Form and
recordset. Can this be done?

"Tom van Stiphout" wrote:

> On Sat, 30 Jan 2010 08:12:06 -0800, NEWER USER
> <(E-Mail Removed)> wrote:
>
> There is a better way to filter: rather than changing the
> RecordSource: set the Filter and FilterOn property.
>
> For readability change your MsgBox statement to something like:
> If (MsgBox(strMsg, vbYesNo or vbQuestion, "Warning") = vbNo) Then
>
> -Tom.
> Microsoft Access MVP
>
>
> >I have a toolbar that I want to assign to multiple forms that are viewed in
> >Datasheet view mode only. I have assigned a Function on the toolbar to run an
> >On Acxtion command. Each form has a different Recordsource (qryAll,
> >qryTotals, qryParts, etc). The following works well on one form only. As the
> >form and Recordsource change, I need to be able to recognize the current
> >Recordsouce so I don't have to create a separate toolbar for each form only
> >to change qryAll to qryTotals. Any help appreciated.
> >
> >Function QTag()
> > Dim strWhere As String
> > Dim strMsg As String
> >
> > With CodeContextObject
> > strWhere = "WHERE " & .Filter
> > End With
> >
> > strMsg = "FILTERED records will be tagged."
> > If (MsgBox(strMsg, 273, "Warning") <> 1) Then
> > 'Tag filtered records on form
> > DoCmd.CancelEvent
> > Exit Function
> >End If
> >
> >DoCmd.SetWarnings False
> >
> >'This is where I need to vary the recorsource below
> >DoCmd.RunSQL "UPDATE qryAll " & _
> > "SET qryAll.T = '-1'" & strWhere
> >
> >DoCmd.Requery
> >DoCmd.SetWarnings True
> >
> >End Fuction
> .
>

On Sat, 30 Jan 2010 12:13:01 -0800, NEWER USER
<(E-Mail Removed)> wrote:

You're right, sorry about that. I thought you wanted to filter a form,
but you want to run an update query.

The current form can be referenced using Screen.ActiveForm. The
current RecordSource is Screen.ActiveForm.RecordSource.

-Tom.
Microsoft Access MVP




>I don't think we connected. Normally, I would place a command button on the
>form to run the Update SQL based on the open form and any filters that may be
>applied since opening the form. Howver, datasheet view doesn't allow for a
>command button, so I chose to place a toolbar assigned to the open form and
>run a Function (On Action property). If I were to place the toolbar on
>another form with a differ recorsource, I would have to create a new toolbar
>and function that references the current form. I want to have one toolbar on
>several different forms and have the Function recognize the Current Form and
>recordset. Can this be done?
>
>"Tom van Stiphout" wrote:
>
>> On Sat, 30 Jan 2010 08:12:06 -0800, NEWER USER
>> <(E-Mail Removed)> wrote:
>>
>> There is a better way to filter: rather than changing the
>> RecordSource: set the Filter and FilterOn property.
>>
>> For readability change your MsgBox statement to something like:
>> If (MsgBox(strMsg, vbYesNo or vbQuestion, "Warning") = vbNo) Then
>>
>> -Tom.
>> Microsoft Access MVP
>>
>>
>> >I have a toolbar that I want to assign to multiple forms that are viewed in
>> >Datasheet view mode only. I have assigned a Function on the toolbar to run an
>> >On Acxtion command. Each form has a different Recordsource (qryAll,
>> >qryTotals, qryParts, etc). The following works well on one form only. As the
>> >form and Recordsource change, I need to be able to recognize the current
>> >Recordsouce so I don't have to create a separate toolbar for each form only
>> >to change qryAll to qryTotals. Any help appreciated.
>> >
>> >Function QTag()
>> > Dim strWhere As String
>> > Dim strMsg As String
>> >
>> > With CodeContextObject
>> > strWhere = "WHERE " & .Filter
>> > End With
>> >
>> > strMsg = "FILTERED records will be tagged."
>> > If (MsgBox(strMsg, 273, "Warning") <> 1) Then
>> > 'Tag filtered records on form
>> > DoCmd.CancelEvent
>> > Exit Function
>> >End If
>> >
>> >DoCmd.SetWarnings False
>> >
>> >'This is where I need to vary the recorsource below
>> >DoCmd.RunSQL "UPDATE qryAll " & _
>> > "SET qryAll.T = '-1'" & strWhere
>> >
>> >DoCmd.Requery
>> >DoCmd.SetWarnings True
>> >
>> >End Fuction
>> .
>>

This is where I stumble. In my code below, what do I replace "qryAll" with?
As other forms open the Recordsource will be a different query. The
Recordsource will have to vary (qryTotals, qryParts, etc). Your help is
appreciated.

DoCmd.RunSQL "UPDATE qryAll " & _
"SET qryAll.T = '-1'" & strWhere


"Tom van Stiphout" wrote:

> On Sat, 30 Jan 2010 12:13:01 -0800, NEWER USER
> <(E-Mail Removed)> wrote:
>
> You're right, sorry about that. I thought you wanted to filter a form,
> but you want to run an update query.
>
> The current form can be referenced using Screen.ActiveForm. The
> current RecordSource is Screen.ActiveForm.RecordSource.
>
> -Tom.
> Microsoft Access MVP
>
>
>
>
> >I don't think we connected. Normally, I would place a command button on the
> >form to run the Update SQL based on the open form and any filters that may be
> >applied since opening the form. Howver, datasheet view doesn't allow for a
> >command button, so I chose to place a toolbar assigned to the open form and
> >run a Function (On Action property). If I were to place the toolbar on
> >another form with a differ recorsource, I would have to create a new toolbar
> >and function that references the current form. I want to have one toolbar on
> >several different forms and have the Function recognize the Current Form and
> >recordset. Can this be done?
> >
> >"Tom van Stiphout" wrote:
> >
> >> On Sat, 30 Jan 2010 08:12:06 -0800, NEWER USER
> >> <(E-Mail Removed)> wrote:
> >>
> >> There is a better way to filter: rather than changing the
> >> RecordSource: set the Filter and FilterOn property.
> >>
> >> For readability change your MsgBox statement to something like:
> >> If (MsgBox(strMsg, vbYesNo or vbQuestion, "Warning") = vbNo) Then
> >>
> >> -Tom.
> >> Microsoft Access MVP
> >>
> >>
> >> >I have a toolbar that I want to assign to multiple forms that are viewed in
> >> >Datasheet view mode only. I have assigned a Function on the toolbar to run an
> >> >On Acxtion command. Each form has a different Recordsource (qryAll,
> >> >qryTotals, qryParts, etc). The following works well on one form only. As the
> >> >form and Recordsource change, I need to be able to recognize the current
> >> >Recordsouce so I don't have to create a separate toolbar for each form only
> >> >to change qryAll to qryTotals. Any help appreciated.
> >> >
> >> >Function QTag()
> >> > Dim strWhere As String
> >> > Dim strMsg As String
> >> >
> >> > With CodeContextObject
> >> > strWhere = "WHERE " & .Filter
> >> > End With
> >> >
> >> > strMsg = "FILTERED records will be tagged."
> >> > If (MsgBox(strMsg, 273, "Warning") <> 1) Then
> >> > 'Tag filtered records on form
> >> > DoCmd.CancelEvent
> >> > Exit Function
> >> >End If
> >> >
> >> >DoCmd.SetWarnings False
> >> >
> >> >'This is where I need to vary the recorsource below
> >> >DoCmd.RunSQL "UPDATE qryAll " & _
> >> > "SET qryAll.T = '-1'" & strWhere
> >> >
> >> >DoCmd.Requery
> >> >DoCmd.SetWarnings True
> >> >
> >> >End Fuction
> >> .
> >>
> .
>

On Sat, 30 Jan 2010 19:33:01 -0800, NEWER USER
<(E-Mail Removed)> wrote:

You concatenate the correct sql statement. Something like:
dim sql as string
dim frm as form
set frm = screen.activeform
sql = "update " & frm.Recordsource & " set " & frm.recordsource & ".T
= '-1'" & strWhere
docmd.runsql sql

This would of course assume T is a field in all these queries.

-Tom.
Microsoft Access MVP



>This is where I stumble. In my code below, what do I replace "qryAll" with?
>As other forms open the Recordsource will be a different query. The
>Recordsource will have to vary (qryTotals, qryParts, etc). Your help is
>appreciated.
>
>DoCmd.RunSQL "UPDATE qryAll " & _
> "SET qryAll.T = '-1'" & strWhere
>
>
>"Tom van Stiphout" wrote:
>
>> On Sat, 30 Jan 2010 12:13:01 -0800, NEWER USER
>> <(E-Mail Removed)> wrote:
>>
>> You're right, sorry about that. I thought you wanted to filter a form,
>> but you want to run an update query.
>>
>> The current form can be referenced using Screen.ActiveForm. The
>> current RecordSource is Screen.ActiveForm.RecordSource.
>>
>> -Tom.
>> Microsoft Access MVP
>>
>>
>>
>>
>> >I don't think we connected. Normally, I would place a command button on the
>> >form to run the Update SQL based on the open form and any filters that may be
>> >applied since opening the form. Howver, datasheet view doesn't allow for a
>> >command button, so I chose to place a toolbar assigned to the open form and
>> >run a Function (On Action property). If I were to place the toolbar on
>> >another form with a differ recorsource, I would have to create a new toolbar
>> >and function that references the current form. I want to have one toolbar on
>> >several different forms and have the Function recognize the Current Form and
>> >recordset. Can this be done?
>> >
>> >"Tom van Stiphout" wrote:
>> >
>> >> On Sat, 30 Jan 2010 08:12:06 -0800, NEWER USER
>> >> <(E-Mail Removed)> wrote:
>> >>
>> >> There is a better way to filter: rather than changing the
>> >> RecordSource: set the Filter and FilterOn property.
>> >>
>> >> For readability change your MsgBox statement to something like:
>> >> If (MsgBox(strMsg, vbYesNo or vbQuestion, "Warning") = vbNo) Then
>> >>
>> >> -Tom.
>> >> Microsoft Access MVP
>> >>
>> >>
>> >> >I have a toolbar that I want to assign to multiple forms that are viewed in
>> >> >Datasheet view mode only. I have assigned a Function on the toolbar to run an
>> >> >On Acxtion command. Each form has a different Recordsource (qryAll,
>> >> >qryTotals, qryParts, etc). The following works well on one form only. As the
>> >> >form and Recordsource change, I need to be able to recognize the current
>> >> >Recordsouce so I don't have to create a separate toolbar for each form only
>> >> >to change qryAll to qryTotals. Any help appreciated.
>> >> >
>> >> >Function QTag()
>> >> > Dim strWhere As String
>> >> > Dim strMsg As String
>> >> >
>> >> > With CodeContextObject
>> >> > strWhere = "WHERE " & .Filter
>> >> > End With
>> >> >
>> >> > strMsg = "FILTERED records will be tagged."
>> >> > If (MsgBox(strMsg, 273, "Warning") <> 1) Then
>> >> > 'Tag filtered records on form
>> >> > DoCmd.CancelEvent
>> >> > Exit Function
>> >> >End If
>> >> >
>> >> >DoCmd.SetWarnings False
>> >> >
>> >> >'This is where I need to vary the recorsource below
>> >> >DoCmd.RunSQL "UPDATE qryAll " & _
>> >> > "SET qryAll.T = '-1'" & strWhere
>> >> >
>> >> >DoCmd.Requery
>> >> >DoCmd.SetWarnings True
>> >> >
>> >> >End Fuction
>> >> .
>> >>
>> .
>>

YOU NAILED IT! Thanks for all the effort and patience. TOP MVP in my book.
Thanks again.

"Tom van Stiphout" wrote:

> On Sat, 30 Jan 2010 19:33:01 -0800, NEWER USER
> <(E-Mail Removed)> wrote:
>
> You concatenate the correct sql statement. Something like:
> dim sql as string
> dim frm as form
> set frm = screen.activeform
> sql = "update " & frm.Recordsource & " set " & frm.recordsource & ".T
> = '-1'" & strWhere
> docmd.runsql sql
>
> This would of course assume T is a field in all these queries.
>
> -Tom.
> Microsoft Access MVP
>
>
>
> >This is where I stumble. In my code below, what do I replace "qryAll" with?
> >As other forms open the Recordsource will be a different query. The
> >Recordsource will have to vary (qryTotals, qryParts, etc). Your help is
> >appreciated.
> >
> >DoCmd.RunSQL "UPDATE qryAll " & _
> > "SET qryAll.T = '-1'" & strWhere
> >
> >
> >"Tom van Stiphout" wrote:
> >
> >> On Sat, 30 Jan 2010 12:13:01 -0800, NEWER USER
> >> <(E-Mail Removed)> wrote:
> >>
> >> You're right, sorry about that. I thought you wanted to filter a form,
> >> but you want to run an update query.
> >>
> >> The current form can be referenced using Screen.ActiveForm. The
> >> current RecordSource is Screen.ActiveForm.RecordSource.
> >>
> >> -Tom.
> >> Microsoft Access MVP
> >>
> >>
> >>
> >>
> >> >I don't think we connected. Normally, I would place a command button on the
> >> >form to run the Update SQL based on the open form and any filters that may be
> >> >applied since opening the form. Howver, datasheet view doesn't allow for a
> >> >command button, so I chose to place a toolbar assigned to the open form and
> >> >run a Function (On Action property). If I were to place the toolbar on
> >> >another form with a differ recorsource, I would have to create a new toolbar
> >> >and function that references the current form. I want to have one toolbar on
> >> >several different forms and have the Function recognize the Current Form and
> >> >recordset. Can this be done?
> >> >
> >> >"Tom van Stiphout" wrote:
> >> >
> >> >> On Sat, 30 Jan 2010 08:12:06 -0800, NEWER USER
> >> >> <(E-Mail Removed)> wrote:
> >> >>
> >> >> There is a better way to filter: rather than changing the
> >> >> RecordSource: set the Filter and FilterOn property.
> >> >>
> >> >> For readability change your MsgBox statement to something like:
> >> >> If (MsgBox(strMsg, vbYesNo or vbQuestion, "Warning") = vbNo) Then
> >> >>
> >> >> -Tom.
> >> >> Microsoft Access MVP
> >> >>
> >> >>
> >> >> >I have a toolbar that I want to assign to multiple forms that are viewed in
> >> >> >Datasheet view mode only. I have assigned a Function on the toolbar to run an
> >> >> >On Acxtion command. Each form has a different Recordsource (qryAll,
> >> >> >qryTotals, qryParts, etc). The following works well on one form only. As the
> >> >> >form and Recordsource change, I need to be able to recognize the current
> >> >> >Recordsouce so I don't have to create a separate toolbar for each form only
> >> >> >to change qryAll to qryTotals. Any help appreciated.
> >> >> >
> >> >> >Function QTag()
> >> >> > Dim strWhere As String
> >> >> > Dim strMsg As String
> >> >> >
> >> >> > With CodeContextObject
> >> >> > strWhere = "WHERE " & .Filter
> >> >> > End With
> >> >> >
> >> >> > strMsg = "FILTERED records will be tagged."
> >> >> > If (MsgBox(strMsg, 273, "Warning") <> 1) Then
> >> >> > 'Tag filtered records on form
> >> >> > DoCmd.CancelEvent
> >> >> > Exit Function
> >> >> >End If
> >> >> >
> >> >> >DoCmd.SetWarnings False
> >> >> >
> >> >> >'This is where I need to vary the recorsource below
> >> >> >DoCmd.RunSQL "UPDATE qryAll " & _
> >> >> > "SET qryAll.T = '-1'" & strWhere
> >> >> >
> >> >> >DoCmd.Requery
> >> >> >DoCmd.SetWarnings True
> >> >> >
> >> >> >End Fuction
> >> >> .
> >> >>
> >> .
> >>
> .
>

Because this worked so well, I want to go one step further. Suppose I have
a Tab Control with multiple subforms on a Main form. You taught me how to
reference the Active Main form (below) and I now want to reference the active
subform once I have clicked a page on the tab control. How might I do this
without a specific subform name?

My plan is to build one function and assign it to a command button on
several subforms which can perform updates on the same field as I did
earlier. I want to get away from using repetitive code only to change the
current subform name in the update sql. Can this be done?

dim frm as form
set frm = screen.activeform

"Tom van Stiphout" wrote:

> On Sat, 30 Jan 2010 19:33:01 -0800, NEWER USER
> <(E-Mail Removed)> wrote:
>
> You concatenate the correct sql statement. Something like:
> dim sql as string
> dim frm as form
> set frm = screen.activeform
> sql = "update " & frm.Recordsource & " set " & frm.recordsource & ".T
> = '-1'" & strWhere
> docmd.runsql sql
>
> This would of course assume T is a field in all these queries.
>
> -Tom.
> Microsoft Access MVP
>
>
>
> >This is where I stumble. In my code below, what do I replace "qryAll" with?
> >As other forms open the Recordsource will be a different query. The
> >Recordsource will have to vary (qryTotals, qryParts, etc). Your help is
> >appreciated.
> >
> >DoCmd.RunSQL "UPDATE qryAll " & _
> > "SET qryAll.T = '-1'" & strWhere
> >
> >
> >"Tom van Stiphout" wrote:
> >
> >> On Sat, 30 Jan 2010 12:13:01 -0800, NEWER USER
> >> <(E-Mail Removed)> wrote:
> >>
> >> You're right, sorry about that. I thought you wanted to filter a form,
> >> but you want to run an update query.
> >>
> >> The current form can be referenced using Screen.ActiveForm. The
> >> current RecordSource is Screen.ActiveForm.RecordSource.
> >>
> >> -Tom.
> >> Microsoft Access MVP
> >>
> >>
> >>
> >>
> >> >I don't think we connected. Normally, I would place a command button on the
> >> >form to run the Update SQL based on the open form and any filters that may be
> >> >applied since opening the form. Howver, datasheet view doesn't allow for a
> >> >command button, so I chose to place a toolbar assigned to the open form and
> >> >run a Function (On Action property). If I were to place the toolbar on
> >> >another form with a differ recorsource, I would have to create a new toolbar
> >> >and function that references the current form. I want to have one toolbar on
> >> >several different forms and have the Function recognize the Current Form and
> >> >recordset. Can this be done?
> >> >
> >> >"Tom van Stiphout" wrote:
> >> >
> >> >> On Sat, 30 Jan 2010 08:12:06 -0800, NEWER USER
> >> >> <(E-Mail Removed)> wrote:
> >> >>
> >> >> There is a better way to filter: rather than changing the
> >> >> RecordSource: set the Filter and FilterOn property.
> >> >>
> >> >> For readability change your MsgBox statement to something like:
> >> >> If (MsgBox(strMsg, vbYesNo or vbQuestion, "Warning") = vbNo) Then
> >> >>
> >> >> -Tom.
> >> >> Microsoft Access MVP
> >> >>
> >> >>
> >> >> >I have a toolbar that I want to assign to multiple forms that are viewed in
> >> >> >Datasheet view mode only. I have assigned a Function on the toolbar to run an
> >> >> >On Acxtion command. Each form has a different Recordsource (qryAll,
> >> >> >qryTotals, qryParts, etc). The following works well on one form only. As the
> >> >> >form and Recordsource change, I need to be able to recognize the current
> >> >> >Recordsouce so I don't have to create a separate toolbar for each form only
> >> >> >to change qryAll to qryTotals. Any help appreciated.
> >> >> >
> >> >> >Function QTag()
> >> >> > Dim strWhere As String
> >> >> > Dim strMsg As String
> >> >> >
> >> >> > With CodeContextObject
> >> >> > strWhere = "WHERE " & .Filter
> >> >> > End With
> >> >> >
> >> >> > strMsg = "FILTERED records will be tagged."
> >> >> > If (MsgBox(strMsg, 273, "Warning") <> 1) Then
> >> >> > 'Tag filtered records on form
> >> >> > DoCmd.CancelEvent
> >> >> > Exit Function
> >> >> >End If
> >> >> >
> >> >> >DoCmd.SetWarnings False
> >> >> >
> >> >> >'This is where I need to vary the recorsource below
> >> >> >DoCmd.RunSQL "UPDATE qryAll " & _
> >> >> > "SET qryAll.T = '-1'" & strWhere
> >> >> >
> >> >> >DoCmd.Requery
> >> >> >DoCmd.SetWarnings True
> >> >> >
> >> >> >End Fuction
> >> >> .
> >> >>
> >> .
> >>
> .
>

On Sun, 31 Jan 2010 13:05:01 -0800, NEWER USER
<(E-Mail Removed)> wrote:

Screen.ActiveControl will give you the active control. This may be in
a subform.
If it is in a subform then screen.ActiveControl.Parent.Name is not
equal to screen.ActiveControl.Name.

Can you take it from there?

-Tom.
Microsoft Access MVP



>Because this worked so well, I want to go one step further. Suppose I have
>a Tab Control with multiple subforms on a Main form. You taught me how to
>reference the Active Main form (below) and I now want to reference the active
>subform once I have clicked a page on the tab control. How might I do this
>without a specific subform name?
>
>My plan is to build one function and assign it to a command button on
>several subforms which can perform updates on the same field as I did
>earlier. I want to get away from using repetitive code only to change the
>current subform name in the update sql. Can this be done?
>
>dim frm as form
>set frm = screen.activeform
>
>"Tom van Stiphout" wrote:
>
>> On Sat, 30 Jan 2010 19:33:01 -0800, NEWER USER
>> <(E-Mail Removed)> wrote:
>>
>> You concatenate the correct sql statement. Something like:
>> dim sql as string
>> dim frm as form
>> set frm = screen.activeform
>> sql = "update " & frm.Recordsource & " set " & frm.recordsource & ".T
>> = '-1'" & strWhere
>> docmd.runsql sql
>>
>> This would of course assume T is a field in all these queries.
>>
>> -Tom.
>> Microsoft Access MVP
>>
>>
>>
>> >This is where I stumble. In my code below, what do I replace "qryAll" with?
>> >As other forms open the Recordsource will be a different query. The
>> >Recordsource will have to vary (qryTotals, qryParts, etc). Your help is
>> >appreciated.
>> >
>> >DoCmd.RunSQL "UPDATE qryAll " & _
>> > "SET qryAll.T = '-1'" & strWhere
>> >
>> >
>> >"Tom van Stiphout" wrote:
>> >
>> >> On Sat, 30 Jan 2010 12:13:01 -0800, NEWER USER
>> >> <(E-Mail Removed)> wrote:
>> >>
>> >> You're right, sorry about that. I thought you wanted to filter a form,
>> >> but you want to run an update query.
>> >>
>> >> The current form can be referenced using Screen.ActiveForm. The
>> >> current RecordSource is Screen.ActiveForm.RecordSource.
>> >>
>> >> -Tom.
>> >> Microsoft Access MVP
>> >>
>> >>
>> >>
>> >>
>> >> >I don't think we connected. Normally, I would place a command button on the
>> >> >form to run the Update SQL based on the open form and any filters that may be
>> >> >applied since opening the form. Howver, datasheet view doesn't allow for a
>> >> >command button, so I chose to place a toolbar assigned to the open form and
>> >> >run a Function (On Action property). If I were to place the toolbar on
>> >> >another form with a differ recorsource, I would have to create a new toolbar
>> >> >and function that references the current form. I want to have one toolbar on
>> >> >several different forms and have the Function recognize the Current Form and
>> >> >recordset. Can this be done?
>> >> >
>> >> >"Tom van Stiphout" wrote:
>> >> >
>> >> >> On Sat, 30 Jan 2010 08:12:06 -0800, NEWER USER
>> >> >> <(E-Mail Removed)> wrote:
>> >> >>
>> >> >> There is a better way to filter: rather than changing the
>> >> >> RecordSource: set the Filter and FilterOn property.
>> >> >>
>> >> >> For readability change your MsgBox statement to something like:
>> >> >> If (MsgBox(strMsg, vbYesNo or vbQuestion, "Warning") = vbNo) Then
>> >> >>
>> >> >> -Tom.
>> >> >> Microsoft Access MVP
>> >> >>
>> >> >>
>> >> >> >I have a toolbar that I want to assign to multiple forms that are viewed in
>> >> >> >Datasheet view mode only. I have assigned a Function on the toolbar to run an
>> >> >> >On Acxtion command. Each form has a different Recordsource (qryAll,
>> >> >> >qryTotals, qryParts, etc). The following works well on one form only. As the
>> >> >> >form and Recordsource change, I need to be able to recognize the current
>> >> >> >Recordsouce so I don't have to create a separate toolbar for each form only
>> >> >> >to change qryAll to qryTotals. Any help appreciated.
>> >> >> >
>> >> >> >Function QTag()
>> >> >> > Dim strWhere As String
>> >> >> > Dim strMsg As String
>> >> >> >
>> >> >> > With CodeContextObject
>> >> >> > strWhere = "WHERE " & .Filter
>> >> >> > End With
>> >> >> >
>> >> >> > strMsg = "FILTERED records will be tagged."
>> >> >> > If (MsgBox(strMsg, 273, "Warning") <> 1) Then
>> >> >> > 'Tag filtered records on form
>> >> >> > DoCmd.CancelEvent
>> >> >> > Exit Function
>> >> >> >End If
>> >> >> >
>> >> >> >DoCmd.SetWarnings False
>> >> >> >
>> >> >> >'This is where I need to vary the recorsource below
>> >> >> >DoCmd.RunSQL "UPDATE qryAll " & _
>> >> >> > "SET qryAll.T = '-1'" & strWhere
>> >> >> >
>> >> >> >DoCmd.Requery
>> >> >> >DoCmd.SetWarnings True
>> >> >> >
>> >> >> >End Fuction
>> >> >> .
>> >> >>
>> >> .
>> >>
>> .
>>

I gave it my best and failed on several attempts Error messages from Type
Mismatch to Object Required. Here is what I have - Object Required Error
returned below.

Function QTagSub()

Dim sql As String
Dim strWhere As String
Dim strMsg As String
Dim frm As Form

With CodeContextObject
strWhere = "WHERE " & .Filter
End With

strMsg = "FILTERED records will be tagged."
If (MsgBox(strMsg, 273, "Warning") <> 1) Then
'Tag filtered records on form
DoCmd.CancelEvent
Exit Function
End If

DoCmd.SetWarnings False
Set frm = Screen.ActiveControl.Name
sql = "update " & frm.RecordSource & " set " & frm.RecordSource & ".T =
'-1'" & strWhere
DoCmd.RunSQL sql
DoCmd.Requery
DoCmd.SetWarnings True
End Function

"Tom van Stiphout" wrote:

> On Sun, 31 Jan 2010 13:05:01 -0800, NEWER USER
> <(E-Mail Removed)> wrote:
>
> Screen.ActiveControl will give you the active control. This may be in
> a subform.
> If it is in a subform then screen.ActiveControl.Parent.Name is not
> equal to screen.ActiveControl.Name.
>
> Can you take it from there?
>
> -Tom.
> Microsoft Access MVP
>
>
>
> >Because this worked so well, I want to go one step further. Suppose I have
> >a Tab Control with multiple subforms on a Main form. You taught me how to
> >reference the Active Main form (below) and I now want to reference the active
> >subform once I have clicked a page on the tab control. How might I do this
> >without a specific subform name?
> >
> >My plan is to build one function and assign it to a command button on
> >several subforms which can perform updates on the same field as I did
> >earlier. I want to get away from using repetitive code only to change the
> >current subform name in the update sql. Can this be done?
> >
> >dim frm as form
> >set frm = screen.activeform
> >
> >"Tom van Stiphout" wrote:
> >
> >> On Sat, 30 Jan 2010 19:33:01 -0800, NEWER USER
> >> <(E-Mail Removed)> wrote:
> >>
> >> You concatenate the correct sql statement. Something like:
> >> dim sql as string
> >> dim frm as form
> >> set frm = screen.activeform
> >> sql = "update " & frm.Recordsource & " set " & frm.recordsource & ".T
> >> = '-1'" & strWhere
> >> docmd.runsql sql
> >>
> >> This would of course assume T is a field in all these queries.
> >>
> >> -Tom.
> >> Microsoft Access MVP
> >>
> >>
> >>
> >> >This is where I stumble. In my code below, what do I replace "qryAll" with?
> >> >As other forms open the Recordsource will be a different query. The
> >> >Recordsource will have to vary (qryTotals, qryParts, etc). Your help is
> >> >appreciated.
> >> >
> >> >DoCmd.RunSQL "UPDATE qryAll " & _
> >> > "SET qryAll.T = '-1'" & strWhere
> >> >
> >> >
> >> >"Tom van Stiphout" wrote:
> >> >
> >> >> On Sat, 30 Jan 2010 12:13:01 -0800, NEWER USER
> >> >> <(E-Mail Removed)> wrote:
> >> >>
> >> >> You're right, sorry about that. I thought you wanted to filter a form,
> >> >> but you want to run an update query.
> >> >>
> >> >> The current form can be referenced using Screen.ActiveForm. The
> >> >> current RecordSource is Screen.ActiveForm.RecordSource.
> >> >>
> >> >> -Tom.
> >> >> Microsoft Access MVP
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> >I don't think we connected. Normally, I would place a command button on the
> >> >> >form to run the Update SQL based on the open form and any filters that may be
> >> >> >applied since opening the form. Howver, datasheet view doesn't allow for a
> >> >> >command button, so I chose to place a toolbar assigned to the open form and
> >> >> >run a Function (On Action property). If I were to place the toolbar on
> >> >> >another form with a differ recorsource, I would have to create a new toolbar
> >> >> >and function that references the current form. I want to have one toolbar on
> >> >> >several different forms and have the Function recognize the Current Form and
> >> >> >recordset. Can this be done?
> >> >> >
> >> >> >"Tom van Stiphout" wrote:
> >> >> >
> >> >> >> On Sat, 30 Jan 2010 08:12:06 -0800, NEWER USER
> >> >> >> <(E-Mail Removed)> wrote:
> >> >> >>
> >> >> >> There is a better way to filter: rather than changing the
> >> >> >> RecordSource: set the Filter and FilterOn property.
> >> >> >>
> >> >> >> For readability change your MsgBox statement to something like:
> >> >> >> If (MsgBox(strMsg, vbYesNo or vbQuestion, "Warning") = vbNo) Then
> >> >> >>
> >> >> >> -Tom.
> >> >> >> Microsoft Access MVP
> >> >> >>
> >> >> >>
> >> >> >> >I have a toolbar that I want to assign to multiple forms that are viewed in
> >> >> >> >Datasheet view mode only. I have assigned a Function on the toolbar to run an
> >> >> >> >On Acxtion command. Each form has a different Recordsource (qryAll,
> >> >> >> >qryTotals, qryParts, etc). The following works well on one form only. As the
> >> >> >> >form and Recordsource change, I need to be able to recognize the current
> >> >> >> >Recordsouce so I don't have to create a separate toolbar for each form only
> >> >> >> >to change qryAll to qryTotals. Any help appreciated.
> >> >> >> >
> >> >> >> >Function QTag()
> >> >> >> > Dim strWhere As String
> >> >> >> > Dim strMsg As String
> >> >> >> >
> >> >> >> > With CodeContextObject
> >> >> >> > strWhere = "WHERE " & .Filter
> >> >> >> > End With
> >> >> >> >
> >> >> >> > strMsg = "FILTERED records will be tagged."
> >> >> >> > If (MsgBox(strMsg, 273, "Warning") <> 1) Then
> >> >> >> > 'Tag filtered records on form
> >> >> >> > DoCmd.CancelEvent
> >> >> >> > Exit Function
> >> >> >> >End If
> >> >> >> >
> >> >> >> >DoCmd.SetWarnings False
> >> >> >> >
> >> >> >> >'This is where I need to vary the recorsource below
> >> >> >> >DoCmd.RunSQL "UPDATE qryAll " & _
> >> >> >> > "SET qryAll.T = '-1'" & strWhere
> >> >> >> >
> >> >> >> >DoCmd.Requery
> >> >> >> >DoCmd.SetWarnings True
> >> >> >> >
> >> >> >> >End Fuction
> >> >> >> .
> >> >> >>
> >> >> .
> >> >>
> >> .
> >>
> .
>