Beginner Access 2000 Form Use

[Greg]

I am doing some volunteer work at my church.
I have been trying to keep track of telephone, gas, water, electricity bills
for the church.

I built the initial tables and forms to be used for input of the bill
information.

The first month all went well and data was correctly entered into correct
tables.

This month is frustrating. The record I am trying to enter this month
through the form has very few fields changing from last month. For example,
account number does not change, the monthly local service may change
occasionaly and the long distance charges will change every month.

Is there a way I can access record 1 from the table into the form and change
only the fields necessary, putting the record back as record 2. Everytime I
click on the <* my form gets cleared.

Thank You
Greg

 

[Geoffs]

Hi Greg,
What you should do is have two completely seperate tables, one with the
information for your suppliers and one with the information for the bills. In
the suppliers form you record details that do not change from month to month,
such as their name, address, account number, etc. Make sure that you create a
"SupplierID" field with its data type set to Autonumber, and set it as the
Primary Index - no duplicates. In your bills table you can record the data
that changes, date of bill, amount, etc. and to tie it to the relevant
supplier you include a field in this table which is also called "SupplierID"
which you set as a Number of Long data type, with its index set as duplicates
allowed. This field will store the identity number of the suppliers record in
the "Suppliers" table. It is also good design to ensure that your Bills table
also has an Autonumber Primary Key ("BillID")
On the Form that you use to input the bills data create a dropdown ComboBox
that is populated with the Suppliers ID in column 1 and names in colum 2.
This is bound to the "SupplierID" in your Bills table and will allow you to
see the name of the suppler relevant to that entry.

 

[John Vinson]

I am doing some volunteer work at my church.
I have been trying to keep track of telephone, gas, water, electricity bills
for the church.

I built the initial tables and forms to be used for input of the bill
information.

Could you describe those tables?

The first month all went well and data was correctly entered into correct
tables.

This month is frustrating. The record I am trying to enter this month
through the form has very few fields changing from last month. For example,
account number does not change, the monthly local service may change
occasionaly and the long distance charges will change every month.

Is there a way I can access record 1 from the table into the form and change
only the fields necessary, putting the record back as record 2. Everytime I
click on the <* my form gets cleared.

By properly normalizing and designing your tables, yes you can. It
*sounds* like you are "committing spreadsheet" - perhaps you have a
Gas table, a Water table, and so on, with fields for the account
number, etc. in each table? That's good design for a spreadsheet but
not for a relational database.

A better approach would be to have two tables:

Accounts
AccountID Autonumber <Primary Key>
AccountType <e.g. "Gas", "Electric", "Internet Service", ...>
CompanyName
AccountNumber
Address
City
State
Zip
Website <if you pay online>

Bills
AccountID Long Integer <link to Accounts>
DueDate Date/Time
PaidDate Date/Time
Amount Currency

You could then have a Form based on Accounts, with a combo box on it
(created using the toolbox wizard) to find which account you're
entering; on this you would have a Subform based on the Bills table,
showing the bills for that account.


John W. Vinson[MVP]

 

[PC Datasheet]

From my file ---

Key Stroke To Copy The Previous Record Into A New Record


Ctrl-C for copy and Ctrl-V for paste