I'm still having problems

[Accessidiot]

I'm new to using access.
I have created a database to help with filling a paper form system we use
currently.
The current paper form has about 100 entries where the user enters 1 of 5
codes regarding checking systems on a vehicle, the codes are "ok", "Serious
fault", "Minor Fault", "Advice Only" and "N/A".
Buliding the database to present is fine, but I've hit 2 stumbling blocks,
The first problem should be easy to resolve (But I can't!!) Once a vehicle's
details have been entered, the user simply enters the registration plate
details in a combo box and a form loads with all the details- No problem-
This form then has a subform where the user enters combo boxes to enter one
of the five codes. However I have to load the main form in read only to
display the vehicle details, but then I need the subform to be in add mode.
So is it possible to load the main form in read only and the subform in add
mode?

My second problem is taxing me. When the user checks one of 100 combo boxes
with Serious, Minor, or advise they then need to enter a description of the
fault and a description of the remedy. I've tried showing or hiding text
boxes and also using a subform for each combo box, But this makes the form
too big and cumbersome.

On the original paper form there is simply a set of boxes where the user
enters the code number for the fault, the severity of the fault and the
descriptions.
Is it possible for me to re create this, but with the program checking the
combo boxes for thier value then entering the code for the fault, the
severity of the fault and then the user can add the descriptions. I would
prefer not to have 100 text boxes as again this makes the form cumbersome 20
or so is plenty?

Thanks for your help
Everyone here is very helpful and certainly has a wealth of knowledge, I
appreciate it. I'd still be at the first hurdle without your help.

Ian

 

[Rod Plastow]

Hello again Ian,

Let me answer your last question first. Yes it is possible to recreate your
paper system but you should start with the database design. Once you have
this you can turn your attention to your form design. I know Access
encourages you to start designing forms first but this approach soon leads to
trouble.

OK, you need a number of tables.

Vehicle. This should contain all the static/standing data about a vehicle
including registration number. Think whether you want (now or in the future)
to relate this table to other tables such as Customer/Owner, Manufacturer,
Supplier, etc. If you do make provision in the table for foreign keys.
Foreign keys are long integers if you follow the practice of using the
autonumber feature for all primary keys.

Fault. This table simply lists the fault codes and related descriptions.

Inspection (you may have a better name such as Service or Visit). This
table identifies an occasion and will doubtless contain a date and other
information pertinent to that occasion.

InspectionRecord (or whatever name you choose) is a table that relates a
Vehicle with an Inspection with one or more Faults. You indicate that the
number of such records is fixed. This design however allows an unlimited
number of faults for every Inspection and I advise against imposing fixed
limits in your database design. Introduce the limit if you wish at the
'front-end' GUI but not in you database that should remain as flexible as
possible ('normalized' is the techie term). This table will have three
foreign keys in every record: one for Vehicle, one for Inspection and one for
Fault. You should include a memo field for additional comments.

I recommend you relate the tables using the Tools/Relationships function
from the main menu bar.

As you design each table you may wish to specify the LookUp properties of
each foreign key such that a meaningful text appears rather than a primary
key number. You can choose combo boxes or list boxes. This also has the
advantage that Access automatically places a control of the appropriate type
on your forms when you design them.

Space here is somewhat limited so I can't describe in great detail the
complete design but the foregoing should get you started in the right
direction.

Returning to your first 'stumbling block,' have you considered displaying
your 'main form' in a sub form holder? You can simply then disable the
holder and your users cannot access any data on the form shown. Link the sub
form to the primary key of your Vehicle table that hopefully is the bound
value of your main combo box. Your data entry form thus has two sub forms,
one disabled displaying the chosen vehicle details and one enabled for
selection of faults and entry of additional comments. In fact thinking about
it this second form is itself probably a main form and sub form - the main
form being the Inspection and the sub form being a continous form for fault
information.

I know I've really only 'scratched the surface' so get back if you need more
help.

Regards,

Rod

 

[Rod Plastow]

Later........

No one spotted my deliberate error. The Inspection table should have the
vehicle id as a foreign key and the InspectionRecord does not need the
vehicle id.

Rod

P.S. Ian send me your email address to (e-mail address removed) and I will send
you a sample mdb for most of this.

 

[Accessidiot]

If you didn't get my email rod its (e-mail address removed)
Thanks
Ian