Access 2000: change Rowsource in Subform2 from Subform1

[Cabby]

The code below is associated with my Subform1. When Mainform opens I
get 2 warnings that I have
made invalid references to the property Form/Report. Once I get past the 2
warnings, everything seems to work fine.
How can I reference the Rowsource in Subform2 without the invalid
references?

TIA
Cabby



Private Sub Form_Current()
' This code created by Form Wizard.
Me.Parent![ctrlSubform2].Requery

If IsLoaded("Expenses_ByDate") Then
Me.Parent![ctrlSubform2].Form!Description.RowSource =
fProductList(Me!Payee)
Else
Me.Parent![ctrlSubform2].Form!Description.RowSource = fProductList()
End If
End Sub

 

[tina]

well, first of all, are you sure that the error messages are being generated
by the Current event you've posted? when you open the main form, the Open,
Load, and Current events of each of the two subforms fire, and then the same
events for the main form. when the first error message appears, is there a
Debug button available? if so, have you clicked on it, to see what line of
code in which module is highlighted? and then have you done the same for the
second error message?

the quickest way to troubleshoot code is to first track down exactly what
line of code is erring out.

hth

 

[Cabby]

Thank you for your reply. I have checked and as "Expenses_ByDate" Mainform
is opening,
the statement "IsLoaded("Expenses_ByDate") " is found to be True twice.
After this, the mainform and
its subforms are fully open.
I have no Open, Load Or Current events for mainform or Subform2. Of these 3
events, only the Current event
for Subform1 is active.

Cabby

well, first of all, are you sure that the error messages are being
generated
by the Current event you've posted?

Private Sub Form_Current()
' This code created by Form Wizard.
Me.Parent![ctrlSubform2].Requery

If IsLoaded("Expenses_ByDate") Then
Me.Parent![ctrlSubform2].Form!Description.RowSource =
fProductList(Me!Payee)
Else
Me.Parent![ctrlSubform2].Form!Description.RowSource = fProductList()
End If
End Sub

 

[tina]

well, i doubt the the IsLoaded() function is appropriate here. since you're
opening the main form Expenses_ByDate, what's the point in checking to see
if it is open? when you open a form that contains a bound subform, the
events fire in the following order:

subform open
subform load
subform current
mainform open
mainform load
mainform current

so, frankly, i'm surprised that the IsLoaded() function is returning True
when called from the subform's Current event, since the mainform's Open
event has not fired yet. but the order of events is obviously the reason
that the code is erring out on that line.

at any rate, the purpose of the rest of your code seems to be to limit the
records returned by the RowSource of a combo box control on subform2,
depending on...what? if you can explain your purpose, i'll work with you to
find a solution.

hth

Thank you for your reply. I have checked and as "Expenses_ByDate" Mainform
is opening,
the statement "IsLoaded("Expenses_ByDate") " is found to be True twice.
After this, the mainform and
its subforms are fully open.
I have no Open, Load Or Current events for mainform or Subform2. Of these 3
events, only the Current event
for Subform1 is active.

Cabby

well, first of all, are you sure that the error messages are being
generated
by the Current event you've posted?

Private Sub Form_Current()
' This code created by Form Wizard.
Me.Parent![ctrlSubform2].Requery

If IsLoaded("Expenses_ByDate") Then
Me.Parent![ctrlSubform2].Form!Description.RowSource =
fProductList(Me!Payee)
Else
Me.Parent![ctrlSubform2].Form!Description.RowSource = fProductList()
End If
End Sub

 

[Cabby]

Tina,

well, i doubt the the IsLoaded() function is appropriate here.

Main reason I use IsLoaded() is to differentiate between when I open subform
as a form or
as a subform.

at any rate, the purpose of the rest of your code seems to be to limit the
records returned by the RowSource of a combo box control on subform2,
depending on...what? if you can explain your purpose, i'll work with you
to
find a solution.

depending on the Payee of the current record in Subform1. Payee and Supplier
mean the same thing.

hth

Mainform consists of a combobox for date of expenses, 2 subforms and some
running totals
to confirm that I have included all the details and a Save Button. When
running totals
equal totals on my invoice, I click Save and data is saved in Subform1 as
Subtotal, Tax and Total.(BTW, not all items are taxable, so this is why I
store
all three fields.

Recordsource for Main is "Last 15 Days of Expenses" -
"Expenses(qry) Between DMax(E_Date, Expenses) And (DMax(E_Date,
Expenses) - 15)
Fields are Expense_ID, E_Date

Child/Master links between Mainform and Subform1 are E_Date/E_Date

Subform1 Recordsource is "Expenses"
Fields are Expense_ID, Payee, E_Date, Subtotal, Tax, Total

Child/Master links between Subform1 and Subform2 are
Expense_ID/[ctrlSubform1].Form!Expense_ID

Subform2 Recordsource is "Expense Details"
Fields are ExpenseDetail_ID, Expense_ID, Description, Qty, Unitprice, etc

This form has been setup as similar as possible to "Customer Orders" from
Northwind.

Cabby

 

[tina]

comments inline.

Tina,


Main reason I use IsLoaded() is to differentiate between when I open subform
as a form or
as a subform.

if you're opening subform1 by itself as a stand-alone form, at times, then
the code on the Open event is going to err anyway - the Else statement,
which would run if IsLoaded() returns False, is referring to a second
subform on a parent form, neither of which is available when subform1 is
opened independently.

or are you talking about opening subform2 by itself? at any rate, there's no
point in running code on subform1.

depending on the Payee of the current record in Subform1. Payee and Supplier
mean the same thing.

assuming that you mean you sometimes open *subform2* (not subform1) by
itself as a stand-alone form, suggest you set the RowSource of the form to
fProductList() in that form's Load event, so that is the "default" rowsource
regardless of when the form is opened. then open the main form in design
view, and on the Main form's Current event, add code to re-set the RowSource
in subform2 (remember that the main form's Current event runs *after* the
subforms have opened), as

Me!ctrlSubform2.Form!Description.RowSource =
fProductList(Me!ctrlSubform1.Form!Payee)
Me!ctrlSubform2.Form!Description.Requery

then in the ctrlSubform2 Enter event procedure (we're still in the main form
design view, here), run the above code again, so that the
Description.RowSource is always based on the current Payee value in
subform1. you may be tempted to run the code in subform1's Current event,
but i wouldn't recommend it; subform2 would have to reliably load before
subform1 each time you open the main form, and you can't depend on that
happening.

hth

hth

Mainform consists of a combobox for date of expenses, 2 subforms and some
running totals
to confirm that I have included all the details and a Save Button. When
running totals
equal totals on my invoice, I click Save and data is saved in Subform1 as
Subtotal, Tax and Total.(BTW, not all items are taxable, so this is why I
store
all three fields.

Recordsource for Main is "Last 15 Days of Expenses" -
"Expenses(qry) Between DMax(E_Date, Expenses) And (DMax(E_Date,
Expenses) - 15)
Fields are Expense_ID, E_Date

Child/Master links between Mainform and Subform1 are E_Date/E_Date

Subform1 Recordsource is "Expenses"
Fields are Expense_ID, Payee, E_Date, Subtotal, Tax, Total

Child/Master links between Subform1 and Subform2 are
Expense_ID/[ctrlSubform1].Form!Expense_ID

Subform2 Recordsource is "Expense Details"
Fields are ExpenseDetail_ID, Expense_ID, Description, Qty, Unitprice, etc

This form has been setup as similar as possible to "Customer Orders" from
Northwind.

Cabby