Subform...Please Help

[Guest]

I have 2 tables and one main form trying to get the 2nd table into the main
form as a subform. I get it in and while I go to each field it goes to the
last field on the subform and starts a new record on the main form, that's
fine but I need the subform to start at the first field starting a new
record.

1st Table: Plan Number - Primary Key to Sales Table which has Primay key as
Sales ID.

As I enter data into the subform i receive an error message: The Express is
typed incorrectly or it is too complex to be evaluated. For example a numeric
may contain too many complicated elements. When I open the 1st table the
subsheet for the 2nd table is with it.

Any help will be appreciated

 

[Arvin Meyer [MVP]]

Go into the design view of the subform. (open it independently, without the
mainform being opened, or as a separate window from the main form). Select
View >>> Tab Order. Adjust the controls in the order you want them, with
drag n' drop.

 

[Guest]

All the tabs are in the correct order. When I go to my main form it starts a
new record, which is great that's what I want it to do. But when I get to the
subform it starts at the last field.

Thanks

 

[John W. Vinson]

All the tabs are in the correct order. When I go to my main form it starts a
new record, which is great that's what I want it to do. But when I get to the
subform it starts at the last field.

You could put code in the subform's Activate event to set focus to the desired
control.

John W. Vinson [MVP]

 

[Guest]

Can you help me with the code? What would and where would I enter it in to?

Thanks

 

[John W. Vinson]

Can you help me with the code? What would and where would I enter it in to?

Open the Subform in design view. View its Properties; on the Events tab find
the Activate event. Click the ... icon by it and choose Code Builder. Access
will put you into the VBA editor window and automatically fill in the Sub and
End Sub lines; edit it by adding just one line in between;

Private Sub Form_Activate()
Me!controlname.SetFocus
End Sub

where controlname is the name of the control that you want to have the focus
when you move to the subform.

John W. Vinson [MVP]

 

[Guest]

Ok I did that and still nothing. What is happening is in the last field on
the subform after entering data goes to the 1st field on the main form, which
I want the only problem is the subform stays at the last field. Is this
something in 2007?

I have 2 tables:
1st table has:
Plan Number - Primary Key
Plan Name
Old Name
other info about Plan

2nd table
Plan Number - relationship to Plan Number in Plans table
Customer
Date Sold
Cost
Charges
Total this is the last field which has Currency that I need to enter
Then I have them in Form and Subform

What am I doing wrong?

Thanks

 

[John W. Vinson]

Ok I did that and still nothing. What is happening is in the last field on
the subform after entering data goes to the 1st field on the main form, which
I want the only problem is the subform stays at the last field. Is this
something in 2007?

I don't have 2007 installed so I don't know. In all versions I know, the focus
should stay on the subform; depending on the Cycle property of the subform, it
will go to the first control in the same record or in a new record. Unless you
have code on the subform to do so, it should not set focus back to the main
form!

The fields in the table are irrelevant... *except*...

Total this is the last field which has Currency that I need to enter

What is the Control SOurce property of this field? Why should you be
*entering* a total anyway, as opposed to having it automatically calculated?

John W. Vinson [MVP]

 

[Guest]

Control Source for the Total field is Total. I guess I never thought of
having it automatically calulate, or I'm just not sure of how to do it. I
guess that would make sense as i have other fields such as Direct bill, Web
charges, TPM Charge and Cost i guess it would work better. Can you help with
the calulating of them?

Thanks

 

[John W. Vinson]

Control Source for the Total field is Total. I guess I never thought of
having it automatically calulate, or I'm just not sure of how to do it. I
guess that would make sense as i have other fields such as Direct bill, Web
charges, TPM Charge and Cost i guess it would work better. Can you help with
the calulating of them?

Not without knowing the structure of your tables and the nature of the data.

In general though, totals should NOT be stored at all, in *any* table; they
can and should be calculated on demand. If there are multiple costs in
different fields in one table, you can use a query with a calculated field
such as

Total: [Direct] + [Web] + [TPM] + [Cost]

If, on the other hand, you do the "right thing" and split the costs out into a
second table with fields CostType, CostDate and Cost, you can use a Totals
query to sum them up.

John W. Vinson [MVP]

 

[Guest]

Ok Let me explain:
I have 3 different sheets that have a Plan Name with a Plan Number that is
sold to a Customer and purchased in 3 different ways. WebOrders, Direct Bill,
and E-Plans.

Each Plan Name is sold to a customer in these 3 ways. On my 1st sheet there
is a Plan Name, Plan Number and other things about the plan...

2nd for Eplans there is a Cost, E-Plan Charge, TPM Charge and Total.

For the 3rd Web Orders there is a Direct Bill, Web Charge, Auth Charge and
TPM Charge.
So would it best to just make one table and add a total field on bottom with
the totals from the charges? Then maybe add a combo box to where I can select
how it was purchased? I'm sorry but this is all new to me and I'm really
trying to grasp this whole thing. Any hwlp would really be appreciated. You
don't know how much you have helped this far.

Thanks

Control Source for the Total field is Total. I guess I never thought of
having it automatically calulate, or I'm just not sure of how to do it. I
guess that would make sense as i have other fields such as Direct bill, Web
charges, TPM Charge and Cost i guess it would work better. Can you help with
the calulating of them?

Not without knowing the structure of your tables and the nature of the data.

In general though, totals should NOT be stored at all, in *any* table; they
can and should be calculated on demand. If there are multiple costs in
different fields in one table, you can use a query with a calculated field
such as

Total: [Direct] + [Web] + [TPM] + [Cost]

If, on the other hand, you do the "right thing" and split the costs out into a
second table with fields CostType, CostDate and Cost, you can use a Totals
query to sum them up.

John W. Vinson [MVP]

 

[John W. Vinson]

Ok Let me explain:
I have 3 different sheets

Excel uses sheets. Access uses tables. Are you using three different tables
for these kinds of sales? If so, that may be the source of your difficulties!

that have a Plan Name with a Plan Number that is
sold to a Customer and purchased in 3 different ways. WebOrders, Direct Bill,
and E-Plans.

So that's an *attribute* of a sale. That means there should be a field for
PlanType with one of these three values.

Each Plan Name is sold to a customer in these 3 ways. On my 1st sheet there
is a Plan Name, Plan Number and other things about the plan...

Again... "sheets". Don't confuse Forms - data entry and data presentation
tools - with tables! Your Table structure is what's basic.

2nd for Eplans there is a Cost, E-Plan Charge, TPM Charge and Total.

I have no idea what this means. You're using jargon terms from your business
that I don't know.

For the 3rd Web Orders there is a Direct Bill, Web Charge, Auth Charge and
TPM Charge.
Ditto.

So would it best to just make one table and add a total field on bottom with
the totals from the charges? Then maybe add a combo box to where I can select
how it was purchased? I'm sorry but this is all new to me and I'm really
trying to grasp this whole thing. Any hwlp would really be appreciated. You
don't know how much you have helped this far.

Can one customer buy more than one plan (perhaps over time)? Does a plan have
a timespan? I really don't know WHAT you're selling.

You should almost certainly have one table for Customers; you should probably
have it related one-to-many to a single table for Sales. You would certainly
NOT have any field or textbox *in your table* for totals; the only time you
would ever open the table is for debugging. You very well might have a textbox
in the Footer of your form or subform to total the various charges; but that
total need not and should not be stored anywhere in any table. Here's my blurb
on the subject of derived data:

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

John W. Vinson [MVP]