How to Apply Current Recordsource?

[NEWER USER]

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

 

[Tom van Stiphout]

On Sat, 30 Jan 2010 08:12:06 -0800, NEWER USER

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

 

[NEWER USER]

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]

On Sat, 30 Jan 2010 12:13:01 -0800, NEWER USER

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

 

[NEWER USER]

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]

On Sat, 30 Jan 2010 19:33:01 -0800, NEWER USER

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

 

[NEWER USER]

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

 

[NEWER USER]

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]

On Sun, 31 Jan 2010 13:05:01 -0800, NEWER USER

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

 

[NEWER USER]

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

 

[Tokyo Alex]

I'm no expert, but won't
Set frm = Screen.ActiveControl.Name
give you the name of the control?

I think you want
Set frm = Screen.ActiveControl.Parent.Name

This should give you the name of the form that the currently active control
is on.

Hope this helps,
Alex.

 

[NEWER USER]

Whether I type .Name or .Parent.Name, I get an error message Object Required.

 

[Douglas J. Steele]

Looks like an error in the code that was suggested.

Since you're trying to instantiate a form, you need

Set frm = Screen.ActiveControl.Parent

If you wanted to know the name of the parent form, you'd use
Screen.ActiveControl.Parent.Name

 

[NEWER USER]

That did the Trick. Thanks for solving the problem Douglas.