Dual Filter by Form?

[mikeycan]

I have a database that has three main forms [frmRA - Invoices], [frmRR -
Arrangements] and [frmRR - Review Summary]. The invoice form contains all the
detailed information about an invoice, including references to any
arranagement the invoice might be assoicated to. An Arrangment is a
clustering of invoices, and the form is just view of all invoices in a
particular arrangment. Now the Review Summary form lists invoice numbers and
arrangement numbers. A user can double click on an invoice number and be
taken to the appropriate invoice ([frmRA - Invoices]) or a user can double
click on an arrangement number and be taken to the appropriate arrangement
([frmRR - Arrangements]). Works great.

In addition, on the invoice form there is a subform where the user can
double click to view the arrangement related to the invoice, and vice versa
on the arrangement form (double click on invoice to see invoice). Same VB
code as on the Review Summary double click, yet these do not work?! Instead
the user is prompted for either the arrangement number or invoice number
depending on the direction they are trying to view. Is there anyway to make
both work?

I found the code from reading some Discussion threads. I am not a
programmer. The following is the code set on double click:

Private Sub ArrangementNumber_DblClick(Cancel As Integer)
' If no Arrangement has been selected, open the form
' to add a record.
If IsNull(Me![ArrangementNumber]) Then
DoCmd.OpenForm "frmRR - Arrangement Master", DataMode:=acFormAdd
Else
' Open the form to the selected record.
DoCmd.OpenForm "frmRR - Arrangement Master", _
WhereCondition:="[ArrangementNumber]=[Forms]![frmRA - Invoices
with Arrangements]![ArrangementNumber]"
End If
End Sub

Private Sub InvoiceNumber_DblClick(Cancel As Integer)
' If no invoice has been selected, open the form
' to add a record.
If IsNull(Me![InvoiceNumber]) Then
DoCmd.OpenForm "frmRA - Invoices", DataMode:=acFormAdd
Else
' Open the form to the selected record.
DoCmd.OpenForm "frmRA - Invoices", _
WhereCondition:="[Invoice Num]=Forms![frmRR - Arrangement -
Detail]![InvoiceNumber]"
End If
End Sub

 

[Dale Fye]

Try it like this:

' Open the form to the selected record.
DoCmd.OpenForm "frmRR - Arrangement Master", _
WhereCondition:="[ArrangementNumber]=" & [Forms]![frmRA -
Invoices with Arrangements]![ArrangementNumber]

When you take the reference to the control outside of the quotes, you force
Access to evaluate that portion before passing it. You will need to do this
with both of your OpenForm methods.

HTH
Dale

 

[mikeycan]

Hello Dale,

Thank you very much for help. The change in the code worked for one of my
forms, but one still prompts the user for the Invoice number. I forgot to
mention that the code is on a sub form. So is the one that worked. Once I
made a full reference it worked. So I am confused why it would not work on
the other form. Here is the revised code (that does not work).

' Open the form to the selected record.
DoCmd.OpenForm "frmRA - Invoices", _
WhereCondition:="[Invoice Num]=" & [Forms]![frmRR - Arrangement
Master]![frmRR - Arrangements - Detail]![InvoiceNumber]
End If
End Sub

Try it like this:

' Open the form to the selected record.
DoCmd.OpenForm "frmRR - Arrangement Master", _
WhereCondition:="[ArrangementNumber]=" & [Forms]![frmRA -
Invoices with Arrangements]![ArrangementNumber]

When you take the reference to the control outside of the quotes, you force
Access to evaluate that portion before passing it. You will need to do this
with both of your OpenForm methods.

HTH
Dale

I have a database that has three main forms [frmRA - Invoices], [frmRR -
Arrangements] and [frmRR - Review Summary]. The invoice form contains all
the
detailed information about an invoice, including references to any
arranagement the invoice might be assoicated to. An Arrangment is a
clustering of invoices, and the form is just view of all invoices in a
particular arrangment. Now the Review Summary form lists invoice numbers
and
arrangement numbers. A user can double click on an invoice number and be
taken to the appropriate invoice ([frmRA - Invoices]) or a user can double
click on an arrangement number and be taken to the appropriate arrangement
([frmRR - Arrangements]). Works great.

In addition, on the invoice form there is a subform where the user can
double click to view the arrangement related to the invoice, and vice
versa
on the arrangement form (double click on invoice to see invoice). Same VB
code as on the Review Summary double click, yet these do not work?!
Instead
the user is prompted for either the arrangement number or invoice number
depending on the direction they are trying to view. Is there anyway to
make
both work?

I found the code from reading some Discussion threads. I am not a
programmer. The following is the code set on double click:

Private Sub ArrangementNumber_DblClick(Cancel As Integer)
' If no Arrangement has been selected, open the form
' to add a record.
If IsNull(Me![ArrangementNumber]) Then
DoCmd.OpenForm "frmRR - Arrangement Master", DataMode:=acFormAdd
Else
' Open the form to the selected record.
DoCmd.OpenForm "frmRR - Arrangement Master", _
WhereCondition:="[ArrangementNumber]=[Forms]![frmRA - Invoices
with Arrangements]![ArrangementNumber]"
End If
End Sub

Private Sub InvoiceNumber_DblClick(Cancel As Integer)
' If no invoice has been selected, open the form
' to add a record.
If IsNull(Me![InvoiceNumber]) Then
DoCmd.OpenForm "frmRA - Invoices", DataMode:=acFormAdd
Else
' Open the form to the selected record.
DoCmd.OpenForm "frmRA - Invoices", _
WhereCondition:="[Invoice Num]=Forms![frmRR - Arrangement -
Detail]![InvoiceNumber]"
End If
End Sub