jmllr24,
You've already gotten good input from Sean and John. Both have
already directly suggested or alluded to what I believe is the *real*
problem: Your schema is incorrect.
Please understand that everything entered here is intended for yur
benefit (and that of hundreds of people who lurk and learn) and none
of it should be construed as a put-down.
It is a truism in the Access world that the essence of good design
begins with good data design, i.e. your schema. In simple terms, the
design of your Tables and the Relationships between them constitute
your schema.
Get the schema down correctly and you will be able to create an
application that can be implemented easily and cost effectively.
Further, that correct schema will enable further modification and
enhancement to meet new needs as the business environment or the
business focus changes.
Get the schema wrong and your application will be difficult and
expensive (in time and effort and lost opportunities) to implement,
maintain and enhance.
My guess, admittedly a pre-judgement, is that you are currently
enmeshed in the latter paradigm.
Your schema should be modeled on the real-world universe of entities
and the relationships between them and events you are tracking or
controlling with your application. Once the entities and the relevant
data elements are identified you can begin the process of Normalizing
your data.
Like John, I believe that just the part of your existing schema that
you have revealed would benefit by putting those columns that *always*
repeat for a given set of records into a table and then creating one
or more related tables to deal with the unique information in each
related record. That concept is one of the major hallmarks or a
Relational Database system.
With your schema altered as above it's easy to manage your data entry
by using the Form/SubForm paradigm. There is ample information in the
Help file to help you through that process once you have a correct
schema. You can always post back if you have difficulties. That's
why we're here.
While the whole concept suggested above may seem foreign, awkward and
intimidating, once you have traveled that path and reaped the
benefits, it will become second nature to design your schema that way
and to massage your data using the Form/SubForm paradigm.
If you are going to play with your existing design and data, start by
making a backup copy or two before you begin.
Inferring without facts, I'll try an example:
I assume that the table into which you are entering data has the sense
of "Head of Household" or "Paying Customer" and could contain all of
the data that always repeats in your current schema. (Normalizing to
3rd Normal Form may change a few things). Refer to that table as
"tblCustomer". Let's assume that the unique data comes down to
identifying the individual family member and their car. Leave the
possibility of more than one car per member to a later discussion.
Lets refer to that table as "tblMemberCar". Assuming Autonumber
primary keys, tblMemberCar will have all of the unique data you expect
to find there and will have an additional a long integer field with
the same name as the Primary Key of tblCustomer.
Once your tables are designed, open the Relationships window, bring up
the two tables under discussion, Select tblCustomer.CustomerID and
draw a line to tblMemberCar.Customer. Doubleclick the link just
established and enable Referential Integrity. Enable Automatic
Updates and Cascade Deletes.
Note, all design dimensions in the forms and controls will be
tweakable after you have established working parameters.
Design a new form based on tblCustomer. Give it name like
"frmCustomerData" and a title like "Customer Data". That will be the
data entry form for entering and managing the relevant customer data.
Default view is "Single".
Place your new form in Run Mode and enter at least three *Customer*
records. Make these as near the real information as possible.
Copy the form above with the name "frmManageMemberCar" and a title
like "Member Data". Delete all of the controls except those that help
to identify this customer. Usually that would come down to something
like the customer's full name and primary account number with you.
Turn on that form's Header and Footer. Drag the bottom of the Header
down about 1/4". With Wizards turned on in the Toolbox, Click the
Combobox icon and then draw a combobox control from about 1/3 of the
way across to about 3/4 across. The Wizard will ask what you want to
do. Choose the option to go to a specific record. In the Label for
the new combobox insert "Find Customer". Name the combobox
"cboFindCustomer". To test just this much of your design, place this
form in Run Mode and try selecting from the Customer records you
entered earlier. Each time you select a different record and release
the mouse, the form should display data from that record.
Design a new form based on tblMemberCar. I give forms designed to be
subfomrs the prefix "suf". I would name this form "sufMemberCar".
Default view is "Continuous". For this example make sure that Show
Header and Footer is Off. If possible, design the new form to show a
complete record in a single line across the form. Design the detail
section of the form to take just a little more vertical space than is
required to show the data in the controls. The idea is to eventually
achieve something that looks very much like a Datasheet but that
provides more functionality and protection. Close your form.
You are now at the point where you can place frmManageMemberCar in
design mode such that the entire form takes less than half the screen
and the detail section is in view when you open the Database|:Forms
window. Click and drag sufMemberCar into the detail section of
frmManageMemberCar.. Access Help used to be pretty good in this area
and probably still is.
If all went well you have something that you can tweak to your
satisfaction. If not, post back with a specific issue and someone
will pick up the thread. If my inferences were anywhere close to the
mark then you should have something that will be much more user
friendly than your current implementation. You should also not find a
need to enter redundant data.
There are a few real-world situations in which it is viable to enter
redundant data. Yours does not appear to be one of those. More than
anything else it looks like "committing spreadsheet". Excel is a
premier spreadsheet development and management platform. Access is
*the* premier RAD application development and management platform.
Access and Excel are *not* flawed versions of each other. They have
different platform goals and different means of achieving them.
Reproduced below is a useful list of resources that John often
includes that would very likely be valuable to you.
Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html
The Access Web resources page:
http://www.mvps.org/access/resources/index.html
Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/
A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html
A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal
MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials
HTH
