From: NEWER USER on
I have the following Function in a module and using on a toolbar. It works
well on the open form which the toolbar is assigned to. The current Record
Source of my form is qryAll. I want to use this Function on several other
forms each with a different Record Source. How do I go about using a
variable as the Record Source changes? I don't want to create a separate
toolbar for every form. Any help appreciated.

Function QTagProduct()
On Error GoTo QTagProduct_Err

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 Current filtered Records
DoCmd.CancelEvent
Exit Function
End If

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE qryAll " & _
"SET qryAll.T = '-1'" & strWhere
DoCmd.Requery
DoCmd.SetWarnings True

QTagProduct_Exit:
Exit Function

QTagProduct_Err:
MsgBox Err.Description
Resume QTagProduct_Exit

End Function
From: Daryl S on
NEWER USER -

You can get the current form (see Screen.ActiveForm in help) and put it into
a form variable, then you can get the recordsource for that form. Something
like this:

Dim frmCurrForm as Form
Dim strSQL as String

Set frmCurrForm = Screen.ActiveForm 'Gets the current form

strSQL = frmCurrForm.RecordSource 'Get the recordsource for the current
form

Then you can make changes as you want to the strSQL, and update the
frmCurrForm.RecordSource with that.

--
Daryl S


"NEWER USER" wrote:

> I have the following Function in a module and using on a toolbar. It works
> well on the open form which the toolbar is assigned to. The current Record
> Source of my form is qryAll. I want to use this Function on several other
> forms each with a different Record Source. How do I go about using a
> variable as the Record Source changes? I don't want to create a separate
> toolbar for every form. Any help appreciated.
>
> Function QTagProduct()
> On Error GoTo QTagProduct_Err
>
> 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 Current filtered Records
> DoCmd.CancelEvent
> Exit Function
> End If
>
> DoCmd.SetWarnings False
> DoCmd.RunSQL "UPDATE qryAll " & _
> "SET qryAll.T = '-1'" & strWhere
> DoCmd.Requery
> DoCmd.SetWarnings True
>
> QTagProduct_Exit:
> Exit Function
>
> QTagProduct_Err:
> MsgBox Err.Description
> Resume QTagProduct_Exit
>
> End Function
From: NEWER USER on
I updated the Function using your suggestions, but must have done something
wrong as I am getting an error message stating "Cannot find input table or
query 'strSQL'"


Function QFillPSyn()
On Error GoTo QFillPSyn_Err
Dim frmCurrForm As Form
Dim strSQL As String
Dim strWhere As String
Dim strMsg As String

Set frmCurrForm = Screen.ActiveForm 'Gets the current form
strSQL = frmCurrForm.RecordSource 'Get the recordsource for the
current Form

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

strMsg = "Order values for FILTERED records" & vbCrLf & _
"will be updated to Per Car Quantity."
If (MsgBox(strMsg, 273, "Warning") <> 1) Then
'Update Order values for current records on form
DoCmd.CancelEvent
Exit Function
End If

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE strSQL " & _
"SET strSQL.Order = [Per Car]" & strWhere
DoCmd.Requery
DoCmd.SetWarnings True

QFillPSyn_Exit:
Exit Function

QFillPSyn_Err:
MsgBox Err.Description
Resume QFillPSyn_Exit

End Function

"Daryl S" wrote:

> NEWER USER -
>
> You can get the current form (see Screen.ActiveForm in help) and put it into
> a form variable, then you can get the recordsource for that form. Something
> like this:
>
> Dim frmCurrForm as Form
> Dim strSQL as String
>
> Set frmCurrForm = Screen.ActiveForm 'Gets the current form
>
> strSQL = frmCurrForm.RecordSource 'Get the recordsource for the current
> form
>
> Then you can make changes as you want to the strSQL, and update the
> frmCurrForm.RecordSource with that.
>
> --
> Daryl S
>
>
> "NEWER USER" wrote:
>
> > I have the following Function in a module and using on a toolbar. It works
> > well on the open form which the toolbar is assigned to. The current Record
> > Source of my form is qryAll. I want to use this Function on several other
> > forms each with a different Record Source. How do I go about using a
> > variable as the Record Source changes? I don't want to create a separate
> > toolbar for every form. Any help appreciated.
> >
> > Function QTagProduct()
> > On Error GoTo QTagProduct_Err
> >
> > 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 Current filtered Records
> > DoCmd.CancelEvent
> > Exit Function
> > End If
> >
> > DoCmd.SetWarnings False
> > DoCmd.RunSQL "UPDATE qryAll " & _
> > "SET qryAll.T = '-1'" & strWhere
> > DoCmd.Requery
> > DoCmd.SetWarnings True
> >
> > QTagProduct_Exit:
> > Exit Function
> >
> > QTagProduct_Err:
> > MsgBox Err.Description
> > Resume QTagProduct_Exit
> >
> > End Function
From: Daryl S on
Newer User -

strSQL is not a table or query name. It is the SQL statement for the
current recordsource. You will need to parse out the table name from this
strSQL. Something like this:

Dim intPosition As Integer
Dim TblQryName As String

intPosition = InStr(1, strSQL, "FROM", vbTextCompare) + 4 'Add 4 for the
length of "FROM "
TblQryName = Right(strSQL, Len(strSQL) - intPosition)
intPosition = InStr(1, TblQryName, " ", vbTextCompare) - 1 'Find first
space after name, and back up one
TblQryName = Left(TblQryName, intPosition)

Once you have the TblQryName, you will need to change your DoCmd statement
to use this - but not within the quotes. Like this:

DoCmd.RunSQL "UPDATE " & TblQryName & _
"SET " & TblQryName & ".Order = [Per Car]" & strWhere

Step through this in debug mode in case I missed a space or digit...
Let us know!
--
Daryl S


"NEWER USER" wrote:

> I updated the Function using your suggestions, but must have done something
> wrong as I am getting an error message stating "Cannot find input table or
> query 'strSQL'"
>
>
> Function QFillPSyn()
> On Error GoTo QFillPSyn_Err
> Dim frmCurrForm As Form
> Dim strSQL As String
> Dim strWhere As String
> Dim strMsg As String
>
> Set frmCurrForm = Screen.ActiveForm 'Gets the current form
> strSQL = frmCurrForm.RecordSource 'Get the recordsource for the
> current Form
>
> With CodeContextObject
> strWhere = "WHERE " & .Filter
> End With
>
> strMsg = "Order values for FILTERED records" & vbCrLf & _
> "will be updated to Per Car Quantity."
> If (MsgBox(strMsg, 273, "Warning") <> 1) Then
> 'Update Order values for current records on form
> DoCmd.CancelEvent
> Exit Function
> End If
>
> DoCmd.SetWarnings False
> DoCmd.RunSQL "UPDATE strSQL " & _
> "SET strSQL.Order = [Per Car]" & strWhere
> DoCmd.Requery
> DoCmd.SetWarnings True
>
> QFillPSyn_Exit:
> Exit Function
>
> QFillPSyn_Err:
> MsgBox Err.Description
> Resume QFillPSyn_Exit
>
> End Function
>
> "Daryl S" wrote:
>
> > NEWER USER -
> >
> > You can get the current form (see Screen.ActiveForm in help) and put it into
> > a form variable, then you can get the recordsource for that form. Something
> > like this:
> >
> > Dim frmCurrForm as Form
> > Dim strSQL as String
> >
> > Set frmCurrForm = Screen.ActiveForm 'Gets the current form
> >
> > strSQL = frmCurrForm.RecordSource 'Get the recordsource for the current
> > form
> >
> > Then you can make changes as you want to the strSQL, and update the
> > frmCurrForm.RecordSource with that.
> >
> > --
> > Daryl S
> >
> >
> > "NEWER USER" wrote:
> >
> > > I have the following Function in a module and using on a toolbar. It works
> > > well on the open form which the toolbar is assigned to. The current Record
> > > Source of my form is qryAll. I want to use this Function on several other
> > > forms each with a different Record Source. How do I go about using a
> > > variable as the Record Source changes? I don't want to create a separate
> > > toolbar for every form. Any help appreciated.
> > >
> > > Function QTagProduct()
> > > On Error GoTo QTagProduct_Err
> > >
> > > 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 Current filtered Records
> > > DoCmd.CancelEvent
> > > Exit Function
> > > End If
> > >
> > > DoCmd.SetWarnings False
> > > DoCmd.RunSQL "UPDATE qryAll " & _
> > > "SET qryAll.T = '-1'" & strWhere
> > > DoCmd.Requery
> > > DoCmd.SetWarnings True
> > >
> > > QTagProduct_Exit:
> > > Exit Function
> > >
> > > QTagProduct_Err:
> > > MsgBox Err.Description
> > > Resume QTagProduct_Exit
> > >
> > > End Function
From: NEWER USER on
After updating more, I am getting an error Invalid procedure call or
argumnet. I appreciate all the help.

Function QFillPSyn()
On Error GoTo QFillPSyn_Err
Dim strWhere As String
Dim strMsg As String
Dim intPosition As Integer
Dim TblQryName As String
Dim strSQL As String
Dim frmCurrForm As Form

Set frmCurrForm = Screen.ActiveForm 'Gets the current form
strSQL = frmCurrForm.RecordSource 'Get the recordsource for the
current Form
intPosition = InStr(1, strSQL, "FROM", vbTextCompare) + 4 'Add 4 for
the length of "FROM "
TblQryName = right(strSQL, Len(strSQL) - intPosition)
intPosition = InStr(1, TblQryName, " ", vbTextCompare) - 1 'Find first
space after name, and back up one
TblQryName = Left(TblQryName, intPosition)

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

strMsg = "Order values for FILTERED records" & vbCrLf & _
"will be updated to Per Car Quantity."
If (MsgBox(strMsg, 273, "Warning") <> 1) Then
'Update Order values for current records on form
DoCmd.CancelEvent
Exit Function
End If

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE " & TblQryName & _
"SET " & TblQryName & ".Order = [Per Car]" & strWhere
DoCmd.Requery
DoCmd.SetWarnings True

QFillPSyn_Exit:
Exit Function

QFillPSyn_Err:
MsgBox Err.Description
Resume QFillPSyn_Exit

End Function

"Daryl S" wrote:

> Newer User -
>
> strSQL is not a table or query name. It is the SQL statement for the
> current recordsource. You will need to parse out the table name from this
> strSQL. Something like this:
>
> Dim intPosition As Integer
> Dim TblQryName As String
>
> intPosition = InStr(1, strSQL, "FROM", vbTextCompare) + 4 'Add 4 for the
> length of "FROM "
> TblQryName = Right(strSQL, Len(strSQL) - intPosition)
> intPosition = InStr(1, TblQryName, " ", vbTextCompare) - 1 'Find first
> space after name, and back up one
> TblQryName = Left(TblQryName, intPosition)
>
> Once you have the TblQryName, you will need to change your DoCmd statement
> to use this - but not within the quotes. Like this:
>
> DoCmd.RunSQL "UPDATE " & TblQryName & _
> "SET " & TblQryName & ".Order = [Per Car]" & strWhere
>
> Step through this in debug mode in case I missed a space or digit...
> Let us know!
> --
> Daryl S
>
>
> "NEWER USER" wrote:
>
> > I updated the Function using your suggestions, but must have done something
> > wrong as I am getting an error message stating "Cannot find input table or
> > query 'strSQL'"
> >
> >
> > Function QFillPSyn()
> > On Error GoTo QFillPSyn_Err
> > Dim frmCurrForm As Form
> > Dim strSQL As String
> > Dim strWhere As String
> > Dim strMsg As String
> >
> > Set frmCurrForm = Screen.ActiveForm 'Gets the current form
> > strSQL = frmCurrForm.RecordSource 'Get the recordsource for the
> > current Form
> >
> > With CodeContextObject
> > strWhere = "WHERE " & .Filter
> > End With
> >
> > strMsg = "Order values for FILTERED records" & vbCrLf & _
> > "will be updated to Per Car Quantity."
> > If (MsgBox(strMsg, 273, "Warning") <> 1) Then
> > 'Update Order values for current records on form
> > DoCmd.CancelEvent
> > Exit Function
> > End If
> >
> > DoCmd.SetWarnings False
> > DoCmd.RunSQL "UPDATE strSQL " & _
> > "SET strSQL.Order = [Per Car]" & strWhere
> > DoCmd.Requery
> > DoCmd.SetWarnings True
> >
> > QFillPSyn_Exit:
> > Exit Function
> >
> > QFillPSyn_Err:
> > MsgBox Err.Description
> > Resume QFillPSyn_Exit
> >
> > End Function
> >
> > "Daryl S" wrote:
> >
> > > NEWER USER -
> > >
> > > You can get the current form (see Screen.ActiveForm in help) and put it into
> > > a form variable, then you can get the recordsource for that form. Something
> > > like this:
> > >
> > > Dim frmCurrForm as Form
> > > Dim strSQL as String
> > >
> > > Set frmCurrForm = Screen.ActiveForm 'Gets the current form
> > >
> > > strSQL = frmCurrForm.RecordSource 'Get the recordsource for the current
> > > form
> > >
> > > Then you can make changes as you want to the strSQL, and update the
> > > frmCurrForm.RecordSource with that.
> > >
> > > --
> > > Daryl S
> > >
> > >
> > > "NEWER USER" wrote:
> > >
> > > > I have the following Function in a module and using on a toolbar. It works
> > > > well on the open form which the toolbar is assigned to. The current Record
> > > > Source of my form is qryAll. I want to use this Function on several other
> > > > forms each with a different Record Source. How do I go about using a
> > > > variable as the Record Source changes? I don't want to create a separate
> > > > toolbar for every form. Any help appreciated.
> > > >
> > > > Function QTagProduct()
> > > > On Error GoTo QTagProduct_Err
> > > >
> > > > 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 Current filtered Records
> > > > DoCmd.CancelEvent
> > > > Exit Function
> > > > End If
> > > >
> > > > DoCmd.SetWarnings False
> > > > DoCmd.RunSQL "UPDATE qryAll " & _
> > > > "SET qryAll.T = '-1'" & strWhere
> > > > DoCmd.Requery
> > > > DoCmd.SetWarnings True
> > > >
> > > > QTagProduct_Exit:
> > > > Exit Function
> > > >
> > > > QTagProduct_Err:
> > > > MsgBox Err.Description
> > > > Resume QTagProduct_Exit
> > > >
> > > > End Function