Current Record Source
in [Forms]
|
From: NEWER USER on 21 Jan 2010 21:49 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 22 Jan 2010 11:14 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 22 Jan 2010 13:41 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 22 Jan 2010 14:55 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 22 Jan 2010 15:26 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
|
Next
|
Last
Pages: 1 2 Prev: Import - Import Specs Next: adding up 1 to 25 fields Line totals to a sub totals |