Using tab featuer in a form and getting multiple records per clien

[Amy Schmid]

Hello,

I have a form that is created from a table (CLIENTS). I have nine tabs and
on each tab is a subform created from a table (FS DATA). When I had someone
enter the data into the form/subforms, it created a separate record in the
table for each tab instead of being all one record.

How do I fix this so that when we input data, everything holds on one record
per client number?

Please let me know if you need more information to be able to help.

Thank you,
Amy

 

[Beetle]

You appear to be saying that you have 9 different subforms all based on
the same table. Is this correct? If so, Why?

 

[Amy Schmid]

We have a very large table that has financial statement data. The form is
the way we are inputing the data.

Each tab is for a section of the financial statement:
Sales
Milk
Cull and Calf
Crop Sales

Live Stock
Purchase Feed
Breeding

We had started with a secion table linking to the detail table linking to
the data table and ran into all kinds of trouble. We finally realized that
consolidating the detail and data table would simplify things. Then instead
of the section table, we created tabs to replace it.

Does that make sense?

Thanks,
Amy

 

[Beetle]

I'm not sure what your interpretation of a very large table is, but if it
has more than say 20 or 30 fields, it is probably incorrect. There are
always exceptions of course, but in most cases a table with more than
that many fields should be re-examined.

Just based on the small amount of example data you posted, I would
say you need to separate your data into different tables. It's hard to
be more specific without knowing more.

The problem you have now is that when you change tabs, the fields you
were editing in the previous tab have already been saved to the table
and Access has no way of knowing that you want to edit an existing
record, or which existing record you want to edit. I suppose you could
write some VBA code to determine which record was most recently
modified by using the LastModified property, then use Bookmark to move
to that record. The code would need to fire every time you change tabs.
IMO, this would not be a very reliable way for your form to function. Plus,
if your tables are improperly designed, then this is only one of many
problems you are likely to end up having with your application.

Perhaps someone else will offer another solution, but IMO you need
to at least re-consider your table structure.

 

[Amy Schmid]

We have somewhere around 60 items on our table, not counting the auto numbers
and items that link to other tables.

It sounds like each subform set of data should be it's own table. Then
create the subforms from the new tables.

Am I right?
Amy

 

[Amy Schmid]

Thinking this through a bit: we started this process with table structure as
follows:
Sectoin Table
Detail Table
Data table

We ran into issues with the linking and getting the data to connect properly
so we got rid of Section Table and created the tabs. We then put all the
detail on the Data Table rather than trying to link things and we here we are.

So what structure should I be creating and how do I link these so that I can
have subforms on the tabs so I can have my main form come from my Client
Table?

Thanks so much!

 

[Beetle]

Well, I know very little about your data. You have made references to
Crop Sales, Livestock and Milk, so it would appear that you are tracking
financial information for Ranchers, Farmers, and Dairy Farmers?? (I'm
just guessing here). This could actually be somewhat complicated but
it's hard to say. There are certainly people in this group that are more
qualified than I am to give you advice on the proper structure for something
like this, but they would need to know quite a bit more about your data.

Having said that, the general idea is to decide how many real world
"entities" are represented by your data. Then you determine what
distinct, non-repeating attributes apply to each of those entities. The
entities become tables, and the attributes become fields within those
tables. You also need to determine not only how the entities are related
to each other, but what *type* each relationship is (one-to-many,
many-to-many, etc.)

Using a rather simple example of a farmers who sell crops, you have three
"entities", so to speak. The farmers, the crops, and the sale of those crops.
Since a farmer can sell more than one type of crop, and a certain crop type
can be sold by more than one farmer, this relationship is many-to-many.
In this case you need three tables;

tblFarmers
********
FarmerID (Primary Key)
FirstName
LastName
Address
(other fields related specifically to a farmer)

tblCrops
******
CropID (PK)
CropDescription
(other fields related to a crop)

tblCropSales
*********
FarmerID (Foreign Key to tblFarmers)
CropID (FK to tblCrops)
SaleDate
etc.

A somewhat oversimplified example I know, but maybe it will help
point you in the right direction. Apologies if I'm explaining things you
already know.

 

[Amy Schmid]

Sean,

This is exactly what I needed to know. We were going at it from the wrong
angle.

I will be sure to post again if I have problems but this is a good starting
point for me.

Thanks!
Amy