Referencing Forms
in [Forms]
|
Prev: inserting a new record from a pop-up, and returning to the main form at that record.
Next: Using Findfirst recordset to search memo field type
From: NEWER USER on 3 Apr 2010 19:27 I have a main form with a subform. On the main form is a command button that opens a pop-up form with unbound fields. I use these fields as Search fields to build a SQL and apply it as the recordsource of the subform The following codes works well and fast. I want to use the pop-up form on several other main forms that contain the same subform as the first. How do I reference the form that is active when the pop-up form was called so I don't have to create several pop-up forms only to change the main form reference in the code? Private Sub cmdAFilter_Click() Dim strSQL As String strSQL = "Select * from qryProduct where ProductID > 0 " If Not IsNull(Me.txtOE) Then strSQL = strSQL & " AND [NumberID] like '*" & Me.txtOE & "*'" End If If Not IsNull(Me.txtIMC) Then strSQL = strSQL & " AND [JNumberID] like '*" & Me.txtIMC & "*'" End If If Not IsNull(Me.txtDesc) Then strSQL = strSQL & " AND [SDescription] like '*" & Me.txtDesc & "*'" End If If Not IsNull(Me.txtBrand) Then strSQL = strSQL & " AND [Brand] like '*" & Me.txtBrand & "*'" End If If Not IsNull(Me.txtRegion) Then strSQL = strSQL & " AND tblType.[Make] like '*" & Me.txtRegion & "*'" End If If Not IsNull(Me.txtMfg) Then strSQL = strSQL & " AND [Mfg] like '*" & Me.txtMfg & "*'" End If If Not IsNull(Me.txtNotes) Then strSQL = strSQL & " AND [Notes] like '*" & Me.txtNotes & "*'" End If If Not IsNull(Me.txtClass) Then strSQL = strSQL & " AND [Code] like '*" & Me.txtClass & "*'" End If If Not IsNull(Me.txtMover) Then strSQL = strSQL & " AND [CO] like '*" & Me.txtMover & "*'" End If If Not IsNull(Me.txtGroup) Then strSQL = strSQL & " AND [PrdGroup] like '*" & Me.txtGroup & "*'" End If Forms!frmProduct!fsubNumbers.Form.RecordSource = strSQL DoCmd.Close acForm, Me.Name Forms!frmProduct!fsubNumbers.SetFocus End Sub I have other forms named frmProductSync frmOrderEntry frmAlternates
From: Stuart McCall on 3 Apr 2010 19:41 "NEWER USER" <NEWERUSER(a)discussions.microsoft.com> wrote in message news:A0D85A72-9AF2-4919-94C1-4060D2A5A951(a)microsoft.com... >I have a main form with a subform. On the main form is a command button >that > opens a pop-up form with unbound fields. I use these fields as Search > fields > to build a SQL and apply it as the recordsource of the subform The > following > codes works well and fast. I want to use the pop-up form on several other > main forms that contain the same subform as the first. How do I reference > the form that is active when the pop-up form was called so I don't have to > create several pop-up forms only to change the main form reference in the > code? > > Private Sub cmdAFilter_Click() > Dim strSQL As String > strSQL = "Select * from qryProduct where ProductID > 0 " > If Not IsNull(Me.txtOE) Then > strSQL = strSQL & " AND [NumberID] like '*" & Me.txtOE & "*'" > End If > If Not IsNull(Me.txtIMC) Then > strSQL = strSQL & " AND [JNumberID] like '*" & Me.txtIMC & "*'" > End If > If Not IsNull(Me.txtDesc) Then > strSQL = strSQL & " AND [SDescription] like '*" & Me.txtDesc & "*'" > End If > If Not IsNull(Me.txtBrand) Then > strSQL = strSQL & " AND [Brand] like '*" & Me.txtBrand & "*'" > End If > If Not IsNull(Me.txtRegion) Then > strSQL = strSQL & " AND tblType.[Make] like '*" & Me.txtRegion & "*'" > End If > If Not IsNull(Me.txtMfg) Then > strSQL = strSQL & " AND [Mfg] like '*" & Me.txtMfg & "*'" > End If > If Not IsNull(Me.txtNotes) Then > strSQL = strSQL & " AND [Notes] like '*" & Me.txtNotes & "*'" > End If > If Not IsNull(Me.txtClass) Then > strSQL = strSQL & " AND [Code] like '*" & Me.txtClass & "*'" > End If > If Not IsNull(Me.txtMover) Then > strSQL = strSQL & " AND [CO] like '*" & Me.txtMover & "*'" > End If > If Not IsNull(Me.txtGroup) Then > strSQL = strSQL & " AND [PrdGroup] like '*" & Me.txtGroup & "*'" > End If > Forms!frmProduct!fsubNumbers.Form.RecordSource = strSQL > DoCmd.Close acForm, Me.Name > Forms!frmProduct!fsubNumbers.SetFocus > > End Sub > > I have other forms named > frmProductSync > frmOrderEntry > frmAlternates Replace Forms!frmProduct with Screen.ActiveForm in both the line that assigns the SQL and the line that sets focus. Better still: With Screen.ActiveForm !fsubNumbers.Form.RecordSource = strSQL DoCmd.Close acForm, Me.Name !fsubNumbers.SetFocus End With The subform controls on each main form must be named fsubNumbers for this to work.
From: Stuart McCall on 3 Apr 2010 20:14 "Stuart McCall" <smccall(a)myunrealbox.com> wrote in message news:u1cy7c40KHA.348(a)TK2MSFTNGP02.phx.gbl... > "NEWER USER" <NEWERUSER(a)discussions.microsoft.com> wrote in message > news:A0D85A72-9AF2-4919-94C1-4060D2A5A951(a)microsoft.com... >>I have a main form with a subform. On the main form is a command button >>that >> opens a pop-up form with unbound fields. I use these fields as Search >> fields >> to build a SQL and apply it as the recordsource of the subform The >> following >> codes works well and fast. I want to use the pop-up form on several >> other >> main forms that contain the same subform as the first. How do I >> reference >> the form that is active when the pop-up form was called so I don't have >> to >> create several pop-up forms only to change the main form reference in the >> code? >> >> Private Sub cmdAFilter_Click() >> Dim strSQL As String >> strSQL = "Select * from qryProduct where ProductID > 0 " >> If Not IsNull(Me.txtOE) Then >> strSQL = strSQL & " AND [NumberID] like '*" & Me.txtOE & "*'" >> End If >> If Not IsNull(Me.txtIMC) Then >> strSQL = strSQL & " AND [JNumberID] like '*" & Me.txtIMC & "*'" >> End If >> If Not IsNull(Me.txtDesc) Then >> strSQL = strSQL & " AND [SDescription] like '*" & Me.txtDesc & "*'" >> End If >> If Not IsNull(Me.txtBrand) Then >> strSQL = strSQL & " AND [Brand] like '*" & Me.txtBrand & "*'" >> End If >> If Not IsNull(Me.txtRegion) Then >> strSQL = strSQL & " AND tblType.[Make] like '*" & Me.txtRegion & "*'" >> End If >> If Not IsNull(Me.txtMfg) Then >> strSQL = strSQL & " AND [Mfg] like '*" & Me.txtMfg & "*'" >> End If >> If Not IsNull(Me.txtNotes) Then >> strSQL = strSQL & " AND [Notes] like '*" & Me.txtNotes & "*'" >> End If >> If Not IsNull(Me.txtClass) Then >> strSQL = strSQL & " AND [Code] like '*" & Me.txtClass & "*'" >> End If >> If Not IsNull(Me.txtMover) Then >> strSQL = strSQL & " AND [CO] like '*" & Me.txtMover & "*'" >> End If >> If Not IsNull(Me.txtGroup) Then >> strSQL = strSQL & " AND [PrdGroup] like '*" & Me.txtGroup & "*'" >> End If >> Forms!frmProduct!fsubNumbers.Form.RecordSource = strSQL >> DoCmd.Close acForm, Me.Name >> Forms!frmProduct!fsubNumbers.SetFocus >> >> End Sub >> >> I have other forms named >> frmProductSync >> frmOrderEntry >> frmAlternates > > Replace Forms!frmProduct with Screen.ActiveForm in both the line that > assigns the SQL and the line that sets focus. Better still: > > With Screen.ActiveForm > !fsubNumbers.Form.RecordSource = strSQL > DoCmd.Close acForm, Me.Name > !fsubNumbers.SetFocus > End With > > The subform controls on each main form must be named fsubNumbers for this > to work. My mistake. That would not work. Pass the name of the calling form to your dialog form via the OpenArgs parameter of the OpenForm command, then use it like this: With Forms(Me.OpenArgs)!fsubNumbers .Form.RecordSource = strSQL .SetFocus End With DoCmd.Close acForm, Me.Name
From: NEWER USER on 3 Apr 2010 21:07 I tried both methods: replace Forms!frmProduct with Screen.ActiveForm and With Screen.ActiveForm > !fsubNumbers.Form.RecordSource = strSQL > DoCmd.Close acForm, Me.Name > !fsubNumbers.SetFocus Both instances gave me an error message that fsubNumbers can not be found. Any ideas? I did this in the existing code and will apply to other forms once corrected. fsubNumbers is the subform name on other forms. "Stuart McCall" wrote: > "NEWER USER" <NEWERUSER(a)discussions.microsoft.com> wrote in message > news:A0D85A72-9AF2-4919-94C1-4060D2A5A951(a)microsoft.com... > >I have a main form with a subform. On the main form is a command button > >that > > opens a pop-up form with unbound fields. I use these fields as Search > > fields > > to build a SQL and apply it as the recordsource of the subform The > > following > > codes works well and fast. I want to use the pop-up form on several other > > main forms that contain the same subform as the first. How do I reference > > the form that is active when the pop-up form was called so I don't have to > > create several pop-up forms only to change the main form reference in the > > code? > > > > Private Sub cmdAFilter_Click() > > Dim strSQL As String > > strSQL = "Select * from qryProduct where ProductID > 0 " > > If Not IsNull(Me.txtOE) Then > > strSQL = strSQL & " AND [NumberID] like '*" & Me.txtOE & "*'" > > End If > > If Not IsNull(Me.txtIMC) Then > > strSQL = strSQL & " AND [JNumberID] like '*" & Me.txtIMC & "*'" > > End If > > If Not IsNull(Me.txtDesc) Then > > strSQL = strSQL & " AND [SDescription] like '*" & Me.txtDesc & "*'" > > End If > > If Not IsNull(Me.txtBrand) Then > > strSQL = strSQL & " AND [Brand] like '*" & Me.txtBrand & "*'" > > End If > > If Not IsNull(Me.txtRegion) Then > > strSQL = strSQL & " AND tblType.[Make] like '*" & Me.txtRegion & "*'" > > End If > > If Not IsNull(Me.txtMfg) Then > > strSQL = strSQL & " AND [Mfg] like '*" & Me.txtMfg & "*'" > > End If > > If Not IsNull(Me.txtNotes) Then > > strSQL = strSQL & " AND [Notes] like '*" & Me.txtNotes & "*'" > > End If > > If Not IsNull(Me.txtClass) Then > > strSQL = strSQL & " AND [Code] like '*" & Me.txtClass & "*'" > > End If > > If Not IsNull(Me.txtMover) Then > > strSQL = strSQL & " AND [CO] like '*" & Me.txtMover & "*'" > > End If > > If Not IsNull(Me.txtGroup) Then > > strSQL = strSQL & " AND [PrdGroup] like '*" & Me.txtGroup & "*'" > > End If > > Forms!frmProduct!fsubNumbers.Form.RecordSource = strSQL > > DoCmd.Close acForm, Me.Name > > Forms!frmProduct!fsubNumbers.SetFocus > > > > End Sub > > > > I have other forms named > > frmProductSync > > frmOrderEntry > > frmAlternates > > Replace Forms!frmProduct with Screen.ActiveForm in both the line that > assigns the SQL and the line that sets focus. Better still: > > With Screen.ActiveForm > !fsubNumbers.Form.RecordSource = strSQL > DoCmd.Close acForm, Me.Name > !fsubNumbers.SetFocus > End With > > The subform controls on each main form must be named fsubNumbers for this to > work. > > > . >
From: NEWER USER on 3 Apr 2010 21:26
You nailed it this time. Thanks for all the help and Happy Easter. "Stuart McCall" wrote: > "Stuart McCall" <smccall(a)myunrealbox.com> wrote in message > news:u1cy7c40KHA.348(a)TK2MSFTNGP02.phx.gbl... > > "NEWER USER" <NEWERUSER(a)discussions.microsoft.com> wrote in message > > news:A0D85A72-9AF2-4919-94C1-4060D2A5A951(a)microsoft.com... > >>I have a main form with a subform. On the main form is a command button > >>that > >> opens a pop-up form with unbound fields. I use these fields as Search > >> fields > >> to build a SQL and apply it as the recordsource of the subform The > >> following > >> codes works well and fast. I want to use the pop-up form on several > >> other > >> main forms that contain the same subform as the first. How do I > >> reference > >> the form that is active when the pop-up form was called so I don't have > >> to > >> create several pop-up forms only to change the main form reference in the > >> code? > >> > >> Private Sub cmdAFilter_Click() > >> Dim strSQL As String > >> strSQL = "Select * from qryProduct where ProductID > 0 " > >> If Not IsNull(Me.txtOE) Then > >> strSQL = strSQL & " AND [NumberID] like '*" & Me.txtOE & "*'" > >> End If > >> If Not IsNull(Me.txtIMC) Then > >> strSQL = strSQL & " AND [JNumberID] like '*" & Me.txtIMC & "*'" > >> End If > >> If Not IsNull(Me.txtDesc) Then > >> strSQL = strSQL & " AND [SDescription] like '*" & Me.txtDesc & "*'" > >> End If > >> If Not IsNull(Me.txtBrand) Then > >> strSQL = strSQL & " AND [Brand] like '*" & Me.txtBrand & "*'" > >> End If > >> If Not IsNull(Me.txtRegion) Then > >> strSQL = strSQL & " AND tblType.[Make] like '*" & Me.txtRegion & "*'" > >> End If > >> If Not IsNull(Me.txtMfg) Then > >> strSQL = strSQL & " AND [Mfg] like '*" & Me.txtMfg & "*'" > >> End If > >> If Not IsNull(Me.txtNotes) Then > >> strSQL = strSQL & " AND [Notes] like '*" & Me.txtNotes & "*'" > >> End If > >> If Not IsNull(Me.txtClass) Then > >> strSQL = strSQL & " AND [Code] like '*" & Me.txtClass & "*'" > >> End If > >> If Not IsNull(Me.txtMover) Then > >> strSQL = strSQL & " AND [CO] like '*" & Me.txtMover & "*'" > >> End If > >> If Not IsNull(Me.txtGroup) Then > >> strSQL = strSQL & " AND [PrdGroup] like '*" & Me.txtGroup & "*'" > >> End If > >> Forms!frmProduct!fsubNumbers.Form.RecordSource = strSQL > >> DoCmd.Close acForm, Me.Name > >> Forms!frmProduct!fsubNumbers.SetFocus > >> > >> End Sub > >> > >> I have other forms named > >> frmProductSync > >> frmOrderEntry > >> frmAlternates > > > > Replace Forms!frmProduct with Screen.ActiveForm in both the line that > > assigns the SQL and the line that sets focus. Better still: > > > > With Screen.ActiveForm > > !fsubNumbers.Form.RecordSource = strSQL > > DoCmd.Close acForm, Me.Name > > !fsubNumbers.SetFocus > > End With > > > > The subform controls on each main form must be named fsubNumbers for this > > to work. > > My mistake. That would not work. > > Pass the name of the calling form to your dialog form via the OpenArgs > parameter of the OpenForm command, then use it like this: > > With Forms(Me.OpenArgs)!fsubNumbers > .Form.RecordSource = strSQL > .SetFocus > End With > DoCmd.Close acForm, Me.Name > > > . > |