Add Records

[Mike Dub]

Hi Guys,

Totally new to Access, but I hope this is a straight forward question.

How do you add a new record under the same heading (say a home address)
without having to enter the address each time I add a new record ?

Thanks for your help

Mike

 

[Evi]

By 'Heading' I guess you mean Field but apart from that, I didn't get your
explanation at all!

Do you mean that for example, your database deals with customer orders and
you don't want to have to enter the customer's address each time he makes a
new order?

If yes, then you do this with the structure of your database. You have table
called TblCustomer.
Its primary key is CustID (an autonumber field) it then has the name and
address fields.

Your Order table - TblOrder has
OrderID (primary key field)
OrderDate
CustID (this is a number field. You link CustID from TblCustomer to CustID
in TblOrder in the Relationships window.

This link means that although you only need to enter the CustID number into
TblOrder, all the fields in TblCustomer for that customer are available and
can be displayed in queries, forms and reports.


Or do you mean that

for example, your Orders table also includes the Billing address, which may
not be the same as the customer's home address. But if it is, you want to
say press a button and have the customer's address details entered into the
Billing Address.

This can be done in a form using VBA code, although if the scenario is
similar to this, then, rather than copying the same details (and then
re-copying them if adjustments are made to the home address), the query on
which your invoice reports (and mailing labels) are based, would look at the
Billing Address field and, if nothing has been entered in those fields, it
would use the Customer's Home address as the Billing address.

If you do mean either of the above, please say which and if you need more
details please say which points are unclear.

If you mean neither of these, then you may need to be clearer in asking your
straightforward question.

Evi

 

[Mike Dub]

Thanks Evi,

Thank you for your comprehensive response. Yes you are correct "field" is
the correct term. Say, I have 4 fields in a table (number, name, address, and
in one of the fields (called totals) I need to add more than one piece of
data (a number), maybe up to 8 times linked to main record.

No. Name Address Totals
1234 test name test address 1500
2600
3500
4500
6500

In a standard form, when you add a new record its clears all fields and
starts again, which is great for a entire new entry, but how do I record 8
numbers in one of the fields for the original record entry and maintain the
link to the existing name, address etc. ?


Mike

 

[Evi]

Forms don't actually clear all their fields when you add a new record,
unless you specify that you want them to. I think you may be talking about
a 'Single' or Columnar form where you see blank fields when you 'turn to a
new page'.

It sounds as if you are a newby to databases and haven't understood yet what
benefits they can bring.

As an example, with the scenario you present below, you would have 2 tables.
One containing each customer, just once.
The other table would have (apart from its own Primary Key) a number field
which would match the Primary Key field of the customer table.

In Access, if you need to type the same thing in several different records,
this is often an indication that you need another table.

To enter your data, you would create a main form based on your customer
table with a subform based on the linked table that contains the Totals
field where each customer could have several records attached to him.
Because the subform and mainform are linked, when you are on a customer's
record, any records which you enter into the subform will be linked to him.

There are some good tutorials below which will get you started - some
include a section on creating subforms but unless you have designed your
database correctly it will be a waste of time creating any forms so it might
be an idea to write to the group with your design and what you hope to do
with it, before you dive into forms.

http://www.teacherclick.com/access2003/index.htm (possibly the better of the
two
http://www.fgcu.edu/support/office2000/access/


You will probably have some templates with your copy of Access which contain
forms with subforms (depends on which version of Acc you are using) so do
have a look at how these are designed.

Evi

 

[Mike Dub]

Thanks for the help. I need to start again !

Forms don't actually clear all their fields when you add a new record,
unless you specify that you want them to. I think you may be talking about
a 'Single' or Columnar form where you see blank fields when you 'turn to a
new page'.

It sounds as if you are a newby to databases and haven't understood yet what
benefits they can bring.

As an example, with the scenario you present below, you would have 2 tables.
One containing each customer, just once.
The other table would have (apart from its own Primary Key) a number field
which would match the Primary Key field of the customer table.

In Access, if you need to type the same thing in several different records,
this is often an indication that you need another table.

To enter your data, you would create a main form based on your customer
table with a subform based on the linked table that contains the Totals
field where each customer could have several records attached to him.
Because the subform and mainform are linked, when you are on a customer's
record, any records which you enter into the subform will be linked to him.

There are some good tutorials below which will get you started - some
include a section on creating subforms but unless you have designed your
database correctly it will be a waste of time creating any forms so it might
be an idea to write to the group with your design and what you hope to do
with it, before you dive into forms.

http://www.teacherclick.com/access2003/index.htm (possibly the better of the
two
http://www.fgcu.edu/support/office2000/access/


You will probably have some templates with your copy of Access which contain
forms with subforms (depends on which version of Acc you are using) so do
have a look at how these are designed.

Evi

 

[Evi]

Boy do I know that feeling!!! (Although it usually occurs more gradually,
some time after I have prepared all my forms and reports)
What sort of db do you want to design Mke? Perhaps there is a template out
there which can do some of the work for you. If not, we may be able to clear
up some of the mist. Whatever you do, don't fall into my error and spend
ages on the 'software' before you find out that your table structure is
wrong.
Evi