Referencing Forms

N

NEWER USER

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
 
S

Stuart McCall

NEWER USER said:
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[/QUOTE]

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.
Click to expand...
 
S

Stuart McCall

Stuart McCall said:
NEWER USER said:
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[/QUOTE]

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.[/QUOTE]

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
Click to expand...
Click to expand...
 
N

NEWER USER

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
Click to expand...

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 said:
NEWER USER said:
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[/QUOTE]

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.


.
[/QUOTE]
Click to expand...
Click to expand...
 
N

NEWER USER

You nailed it this time. Thanks for all the help and Happy Easter.

Stuart McCall said:
Stuart McCall said:
NEWER USER said:
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[/QUOTE]

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.[/QUOTE]

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


.
[/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Urgent Help Need - Adding Date Selection to Main Form 6
Please Please Help - Run-time Error 2
Filter Command Button 2
Excel Export Filtered Form Data To Excel 0
Syntax error in Update statement 3
Sql Statement errors 5
Continuous form search problem 5
Can't get the correct quotes or syntax on strSQL multiple WHEREclause 3

Top