Referring to subform - qualier/collection error messages

[Donna]

I have a frmCustomer with a subform frmCustomerTour. When the user selects
the tour for the customer, a customer price is calculated and saved in a
textbox "customerprice". I have a form frmAccount with a subform
frmTransaction. This form tracks all financial transactions for the various
tours for this customer (charges, payments, credits, etc.)

I would like to put a command button on the frmCustomerTour that would
automatically open the frmAccount & its subform frmTransaction and
automatically fill in the charge for the tour.

This is the code I have so far for that button:

Dim stLinkCriteria As String, form1 As String, form2 As String
Dim form3 As String, form4 As String

form1 = "frmAccount"
form2 = "frmTransaction"
form3 = "frmCustomer"
form4 = "frmCustomerTour"

stLinkCriteria = "[CID]=" & Me![CID]
DoCmd.OpenForm form1, , , stLinkCriteria
form1!form2.Form![CustomerPrice] = form3!form4.Form![charge]

I keep getting the error message "qualifier must be a collection" and form4
is highlighted. Can you please tell me what I'm doing wrong?

 

[RuralGuy]

I have a frmCustomer with a subform frmCustomerTour. When the user
selects the tour for the customer, a customer price is calculated and
saved in a textbox "customerprice". I have a form frmAccount with a
subform frmTransaction. This form tracks all financial transactions for
the various tours for this customer (charges, payments, credits, etc.)

I would like to put a command button on the frmCustomerTour that would
automatically open the frmAccount & its subform frmTransaction and
automatically fill in the charge for the tour.

This is the code I have so far for that button:

Dim stLinkCriteria As String, form1 As String, form2 As String
Dim form3 As String, form4 As String

form1 = "frmAccount"
form2 = "frmTransaction"
form3 = "frmCustomer"
form4 = "frmCustomerTour"

stLinkCriteria = "[CID]=" & Me![CID]
DoCmd.OpenForm form1, , , stLinkCriteria
form1!form2.Form![CustomerPrice] = form3!form4.Form![charge]

I keep getting the error message "qualifier must be a collection" and
form4 is highlighted. Can you please tell me what I'm doing wrong?

Hi Donna,

You need to go through the Forms collection:

From the SubForm "frmCustomerTour":
Forms!frmAccount!frmTransaction.Form![CustomerPrice] = Me.[charge]

I personally prefer to pass data like this in the OpenArgs Parameter:
DoCmd.OpenForm form1, , , stLinkCriteria, , , Me.[charge]

Then have the next form do all of the rest of the work. Three years from now
when someone else tries to figure out how the CustomerPrice is populated by
opening frmAccount they will be lost. It make alterations in the future more
modular and self contained. Just my $0.02.

hth

 

[Donna]

Okay, that gets the frmAccount & it's subform frmTransactions to open but it
doesn't write anything into the next record. There is an autonumber field
[TRID] on that form. Is there something I need to do to activate that first?

I have a frmCustomer with a subform frmCustomerTour. When the user
selects the tour for the customer, a customer price is calculated and
saved in a textbox "customerprice". I have a form frmAccount with a
subform frmTransaction. This form tracks all financial transactions for
the various tours for this customer (charges, payments, credits, etc.)

I would like to put a command button on the frmCustomerTour that would
automatically open the frmAccount & its subform frmTransaction and
automatically fill in the charge for the tour.

This is the code I have so far for that button:

Dim stLinkCriteria As String, form1 As String, form2 As String
Dim form3 As String, form4 As String

form1 = "frmAccount"
form2 = "frmTransaction"
form3 = "frmCustomer"
form4 = "frmCustomerTour"

stLinkCriteria = "[CID]=" & Me![CID]
DoCmd.OpenForm form1, , , stLinkCriteria
form1!form2.Form![CustomerPrice] = form3!form4.Form![charge]

I keep getting the error message "qualifier must be a collection" and
form4 is highlighted. Can you please tell me what I'm doing wrong?

Hi Donna,

You need to go through the Forms collection:

From the SubForm "frmCustomerTour":
Forms!frmAccount!frmTransaction.Form![CustomerPrice] = Me.[charge]

I personally prefer to pass data like this in the OpenArgs Parameter:
DoCmd.OpenForm form1, , , stLinkCriteria, , , Me.[charge]

Then have the next form do all of the rest of the work. Three years from now
when someone else tries to figure out how the CustomerPrice is populated by
opening frmAccount they will be lost. It make alterations in the future more
modular and self contained. Just my $0.02.

hth

 

[RuralGuy]

Okay, that gets the frmAccount & it's subform frmTransactions to open
but it doesn't write anything into the next record. There is an
autonumber field [TRID] on that form. Is there something I need to do to
activate that first?

I personally prefer to pass data like this in the OpenArgs Parameter:
DoCmd.OpenForm form1, , , stLinkCriteria, , , Me.[charge]

Hi Donna,

Are you trying to use the OpenArgs method? Is this a new record on
frmAccount or frmTransaction?

 

[Donna]

I'm using the click sub for a command button that sits on the customer tour
form and yes, it will always be a new record on the frmTransaction. When a
client signs up for a tour, the cost of that tour needs to automatically
populate the transaction form that is the subform on the account for that
client.

Okay, that gets the frmAccount & it's subform frmTransactions to open
but it doesn't write anything into the next record. There is an
autonumber field [TRID] on that form. Is there something I need to do to
activate that first?

I personally prefer to pass data like this in the OpenArgs Parameter:
DoCmd.OpenForm form1, , , stLinkCriteria, , , Me.[charge]

Hi Donna,

Are you trying to use the OpenArgs method? Is this a new record on
frmAccount or frmTransaction?

 

[RuralGuy]

I'm using the click sub for a command button that sits on the customer
tour form and yes, it will always be a new record on the frmTransaction.
When a client signs up for a tour, the cost of that tour needs to
automatically populate the transaction form that is the subform on the
account for that client.

Hi Donna,

Will you need to fill in additional information in the frmTransaction? Why
not just add a new record and put the cost in it before you open the form
and it will just be there on the new record?

You do not need forms to place data in records. Forms are just windows
into a recordset so a human can view or modify the data. Access can do
anything you want to a table without ever seeing a form on the screen.

 

[Donna]

I am really new to writing VBA. HOw would I do that? - and yes, there would
be other fields like date, and Trip ID that would need to be populated also.

 

[RuralGuy]

I am really new to writing VBA. HOw would I do that? - and yes, there
would be other fields like date, and Trip ID that would need to be
populated also.

How about supplying real names of tables and fields. How much information do
you know before you open frmAccount? We could easily place all of this data
in the table before opening frmAccount. What tables/query's are bound to
each form? That means "Record Source" of each form.

 

[Donna]

After reading parts of a book on VBA for Access, I came up with the
following. It seems to be working. If you can think of a reason why I
shouldn't do this, please let me know.
on the Click property of the command button that sits on the
CustomerTourDataEntry form, is the following code:

Dim stLinkCriteria As String, form2 As String
form2 = "frmTransactionDataEntry"
stLinkCriteria = "[CID]=" & Me![CID]


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenForm form2, , , stLinkCriteria
DoCmd.GoToRecord acDataForm, form2, acGoTo, 5
Form_frmTransactionDataEntry.TID = Form_frmCustomerTourDataEntry.TID
Form_frmTransactionDataEntry.Type = "Charge"
Form_frmTransactionDataEntry.Charge_DR =
Form_frmCustomerTourDataEntry.CustomerPrice
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70