I have one form "frmInvoiceCash" which can be called from lots of
different places in my application. I currently use the following
code to pick the correct datatsource : -

Private Sub Form_Open(Cancel As Integer)

If IsLoaded("frmunpaid") Then

Forms!frminvoicecash.RecordSource = "SELECT tblInvoice.*FROM
tblInvoice WHERE " & _
"(((tblInvoice.InvoiceID)=[Forms]![frmunpaid]!
[invoiceid]));"
Forms!frminvoicecash.DataEntry = False

ElseIf IsLoaded("frmuninvoiced") Then

Forms!frminvoicecash.RecordSource = "SELECT tblInvoice.*FROM
tblInvoice WHERE " & _
"(((tblInvoice.InvoiceID)=[Forms]![frmuninvoiced]!
[invoiceidun]));"
Forms!frminvoicecash.DataEntry = False

ElseIf IsLoaded("frmallocate") Then

Forms!frminvoicecash.RecordSource = "SELECT tblInvoice.*FROM
tblInvoice WHERE " & _
"(((tblInvoice.InvoiceID)=[Forms]![frmallocate]!
[txtinvoiceid]));"
Forms!frminvoicecash.DataEntry = False

ElseIf IsLoaded("frmproductmovement") Then

Forms!frminvoicecash.RecordSource = "SELECT tblInvoice.*FROM
tblInvoice WHERE " & _
"(((tblInvoice.InvoiceID)=[Forms]!
[frmproductmovement]![invoiceid]));"
Forms!frminvoicecash.DataEntry = False
Forms!frminvoicecash!InvoiceNote = Forms!frminvoicecash!
InvoiceNote & " "

ElseIf IsLoaded("frmstatement") Then

Forms!frminvoicecash.RecordSource = "SELECT tblInvoice.*FROM
tblInvoice WHERE " & _
"(((tblInvoice.InvoiceID)=[Forms]![frmstatement]!
[invoiceid]));"
Forms!frminvoicecash.DataEntry = False
Forms!frminvoicecash!sfmCashInvoicePayment.Visible = False
Forms!frminvoicecash!txtPaid.Visible = False
Forms!frminvoicecash!lblPaid.Visible = False
Forms!frminvoicecash!lblDue.Visible = False
Forms!frminvoicecash!txtDue.Visible = False

ElseIf IsLoaded("frminvsearch") Then

Forms!frminvoicecash.RecordSource = "SELECT tblInvoice.*FROM
tblInvoice WHERE " & _
"(((tblInvoice.InvoiceID)=[Forms]![frminvsearch]!
[txtinvid]));"
Forms!frminvoicecash.DataEntry = False

ElseIf IsLoaded("frmAccountCustomerHistory") Then

Forms!frminvoicecash.RecordSource = "SELECT tblInvoice.*FROM
tblInvoice WHERE " & _
"(((tblInvoice.InvoiceID)=[Forms]!
[frmaccountcustomerhistory]![txtinvoiceid]));"
Forms!frminvoicecash.DataEntry = False

Else

Forms!frminvoicecash.RecordSource = "tblinvoice"
Forms!frminvoicecash.DataEntry = True

End If


Me.Requery

End Sub

There are some issues with this e.g. if you have two of the forms open
then it uses the datasource for the first one in the list.

My question is whether opr not there is an easier way of doing this.
I will be adding at least another three forms to the app which can be
used to open frmInvoiceCash so want to nail this before I add them.

Ultimately only one form should be able to be openend at any one time
to prevent the use of an incorrect datasource

So although there can be more than one 'calling' form open do I presume that
you only ever want instance of frmInvoiceCash open at any one time?
If so, then simply use the OpenArgs parameter of DoCmd.OpenForm to pass the
name of the calling form or indeed the SQL of the RecordSource itself (if
it's not too complex).

Then in the Open event of frmInvoiceCash your can check the OpenArgs
property and set the RecorsSource accordingly.

HTH


"BobbyDazzler" <david.a.mitchell (AT) inbox (DOT) com> wrote

On 2012-01-16 6:07 PM, BobbyDazzler wrote:

Bobby,


Quickly scanning your code it seems that the only part that really
changes is the WHERE clause, the actual table is always tblInvoice. What
I would recommend is that you lose this code and simply pass the WHERE
clause as a parameter when you open the form.

To do this, set the ControlSource for 'frmInvoiceCash' to 'tblInvoice'
and remove the OpenForm code. This leaves a generic form that only needs
to be told which records to display when it's opened. Whenever you want
to call this form, simply calculate the appropriate WHERE clause
beforehand (without the WHERE keyword) and pass it in the DoCmd.OpenForm
command from the calling form. For instance, to call frmInvoiceCash from
frmUnpaid you could use the following instruction behind the button on
frmUnpaid :

DoCmd.OpenForm "frmInvoiceCash", , , "InvoiceID = " & Me.InvoiceId

You may also want to open the form as acDialog to make sure the user
completes and closes this form before it can be called again.

To toggle the .DataEntry flag you could use the optional OpenArgs
parameter of DoCmd.OpenForm to pass a True or False flag to
frmInvoiceCash, then use this instruction in the latter's Form Open event :

Me.DataEntry = NZ(Me.OpenArgs, False)

This saves you a lot of confusing and error-prone decision making and
code maintenance within the form that's being called (but isn't quite
sure by whom) and provides a clean, concise way of calling the form with
the appropriate filter and settings instead. This approach allows you to
truly reuse objects like frmInvoiceCash without having to tweak their
code every time you want to use them.


HTH


Jan

Jon and Jan, thank you both for taking the time to respond to my
question. Both your responses make very good sense! Thanks again

David