Opinions needed on most efficient method for a form

[Jim Evans]

Access 2003 XP Pro

I have an order entry form that contains a subform. The main form collects
information on the new customer, including the types of services they want
to purchase. The subform is for the entry of the actual, specific services
they ore ordering.

Main form contains a checkbox for "Broadband" and one for "Cable TV". The
subform (a continuous form) should list one specific Broadband service
(Residential, Commercial etc.) if the Main form Broadband checkbox is
checked and a specific service for Cable TV if that checkbox is checked.

The problem is that the person responsible for taking new orders just cannot
understand this. When the db contained just Broadband customers, I made thwe
assumption that all new customers would order Broadband so I had her select
just the specific Broadband service from a combo on the main form and
automatically made the entry into the table that holds the specific services
for each customer. This table is used for billing purposes so it is very
important to get the contents correct.

I have added a msgbox to the Clicked event for each of the 2 checkboxes and
it reminds her to make an entry for the service but she still "forgets" to
make all entries...

I am hoping that you can suggest a more efficient way of approaching this
data entry problem that I can implement. All of your suggestions will be
much appreciated.

Jim

 

[John Spencer]

I don't understand why you are using the checkboxes at all.

You are basically storing (and entering) the same data twice.

I would have a table of Services with
ServiceName:
ServiceType: Broadband, Cable, etc

Now in the subform, you would select from a combobox based on the Services
table to specify the service. IF the customer chooses no service then you
have no record. IF the customer chooses one service you have one record and if
the customer chooses multiple services you have multiple records.

If the customer chooses a broadband service, you know that based on ServiceType.
If the customer chooses a cable service, you know that based on ServiceType
If the customer chooses multiple services (unusual, but possible) you know
what type(s) of service they have based on the serviceType field.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Jim Evans]

John,

The checkboxes are just flags that are used when the new customer is entered
into the MasterAccounts(Customers) table. The data being collected on this
form is entered into a NewOrders table and remains there until the
installation of the services is completed, at which time, the installer
completes a from which initiates the entry of the new customer into the
MasterAccounts table. The subform enters the specific aervices ordered into
a ServicesOrdered table, which, when the new customer is added to the
MasterAccounts table enters the specific rows into the BillingItems table.

I agree that just adding the services directly into the subform is the
cleanest way to do thid but, the data entry clerk cannot "remember" to do
this consistently and this results in Work Orders which have no services to
be entered and a customer that is billed for nothing!

I also realize that replacing the data entry clerk with one that can
"remember" important details is the best solution but, I do not have that
ability.

I am looking for a method to force the entry of the services ordered by the
customer into the ServicesOrdered table.
Jim

I don't understand why you are using the checkboxes at all.

You are basically storing (and entering) the same data twice.

I would have a table of Services with
ServiceName:
ServiceType: Broadband, Cable, etc

Now in the subform, you would select from a combobox based on the Services
table to specify the service. IF the customer chooses no service then you
have no record. IF the customer chooses one service you have one record and
if
the customer chooses multiple services you have multiple records.

If the customer chooses a broadband service, you know that based on
ServiceType.
If the customer chooses a cable service, you know that based on ServiceType
If the customer chooses multiple services (unusual, but possible) you know
what type(s) of service they have based on the serviceType field.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County