Auto Fill fields in table

[StevePlym]

I am creating a database for my small consultancy - around 30 clients.

In addition to the table containing names, addresses, etc. I want a table to
record invoices raised - dates, amounts, date paid, etc.

I'm assuming i make the Client ID field the primary key in the contacts
table. How do I use this to auto-complete the client firstname and surname
into the invoice table? In other words - I want to just be able to enter the
client ID into the invoice table and have it enter the firstname and surname
automatically.

Thanks in advance.


Steve

 

[Scott McDaniel]

I am creating a database for my small consultancy - around 30 clients.

In addition to the table containing names, addresses, etc. I want a table to
record invoices raised - dates, amounts, date paid, etc.

I'm assuming i make the Client ID field the primary key in the contacts
table. How do I use this to auto-complete the client firstname and surname
into the invoice table? In other words - I want to just be able to enter the
client ID into the invoice table and have it enter the firstname and surname
automatically.

You should ONLY store the ClientID in your Invoice table, and NOT the FirstName, LastName, etc ... otherwise, what
happens when you realize you've spelled the client's name incorrectly? You'd then have to go back through the tables and
correct all misspellings ...

Access/Jet is a relational database system; tables should store information that's relevant to itself; for example, an
Invoice table would perhaps store the InvoiceDate, InvoiceAmount, ShippingMethod, Terms etc etc ... those are
"properties" of an Invoice, and would rightly belong in that table, but the client's FirstName and LastName are NOT
properties of the invoice ... they are relevant to the invoice (otherwise how would you know which client the invoice
"belonged" to), but they don't "describe" the invoice, they instead "describe" the Customer, so they should be stored in
the Customer/Client table.

So you'd maintain your setup as you have it - store the ClientID in the Invoice table when a new invoice in generated.
If you need to see the information as a whole (i.e. both Invoice and Client info) then build a query which returns that
information.

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[StevePlym]

OK Scott, that's all understood and accepted.

But what if I want the Invoice table to display the client name rather than
looking through the Client table? or to query that table for a surname?

There must be a way of getting the Invoice table to link and display the
name/ I know it can be done in Corel Paradox as a friend of mine has
attempted to convert me away from Access!

I'm guessing Access is equally up to the task?


Steve

 

[Scott McDaniel]

OK Scott, that's all understood and accepted.

But what if I want the Invoice table to display the client name rather than
looking through the Client table? or to query that table for a surname?

There must be a way of getting the Invoice table to link and display the
name/ I know it can be done in Corel Paradox as a friend of mine has
attempted to convert me away from Access!

Ideally, you should never use the actual Table (i.e. the Datasheet view of the TAble) to enter or update data. Instead,
you'd use a Form and set the form to show a Datasheet view or a Continuous view, both of which can mimic the Table view.

If you do this, then your form can be based on a query that pulls together your Invoice table and your Client/Customer
table; from there, you can display whatever values you wish (including the Customer/Client name, etc). The first step
would be to build a query that returns the information you want, then build a form based on that query.

You'd also use this query to search for Invoices related to a particular Client name ...


Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[StevePlym]

OK, things are becoming a little clearer.

In Paradox I seem to recall my friend linking the two tables, thus showing
the client table details in the Invoice table.

As I'm very new to Access I've a feeling it's going to take me a while to
get my head around all this. Any further guidance will be appreciated and
I'll be rreading the huge book I've bought to help me find my way around the
whole of Office 2007.


Steve

 

[John W. Vinson]

In Paradox I seem to recall my friend linking the two tables, thus showing
the client table details in the Invoice table.

Well, Paradox and Access are both capable database environments... but they
are DIFFERENT database environments. Treating Access as if it were a flawed
implementation of Paradox will be frustrating!

In Access, you *can* use table datasheets for data editing; with Lookup
Fields, you can even put "lookups" (e.g. to the Clients table) into the table
datasheet.

But in practice, this buys you very little benefit, and has many costs. See
http://www.mvps.org/access/lookupfields.htm for a critique. Table datasheets
have *very* limited functionality, and you're much better off just using
Tables as data repositories, and creating Forms to do all interaction with the
data in the tables. on the form, you can very easily put controls such as
Combo Boxes or Listboxes to display (say) client data on the same form with
invoice data.

John W. Vinson [MVP]

 

[StevePlym]

Thank you John.

I've looked at the Listbox idea for getting data from the Client table into
the Invoice table but I was really looking for something which would fill in
the data without further intervention from me.

I take what you say about paradox - that's why I'm trying to build this from
scratch using Access. It's getting very frustrating though!


Steve

 

[SmartbizAustralia]

Have you written a query which joins client table to the invoice
table?

Have you tried creating a form based on the client table and another
form based on the invoice table and then making the invoice form a
subform in the client form?

The when you search a client, you see all their invoices!

Regards,
Tom Bizannes
Microsoft Access Development
Sydney,Australia

 

[John W. Vinson]

Thank you John.

I've looked at the Listbox idea for getting data from the Client table into
the Invoice table but I was really looking for something which would fill in
the data without further intervention from me.

Well... that's where you're making the mistake. The Invoice table should
contain the ClientID, *AND NOTHING ELSE*.

Relational databases use the "Grandmother's Pantry Principle" - "a place - ONE
place! - for everything, everything in its place". Information about clients
should be stored in the Clients table; the Invoices table should have the
ClientID. You can *display* other data from the Client table on the form. One
way is to have a Combo Box on the form bound to the ClientID field, but
displaying the client name. The Combo's Row Source query can include other
fields from the client table (address, phone, ...); you can put textboxes on
the form with control sources like

=comboboxname.Column(n)

where n is the zero based index of the desired field.

Again... don't expect the Invoice table to display *anything* from the client
table. It won't, and it shouldn't.

John W. Vinson [MVP]

 

[StevePlym]

This sounds like it's getting way too complicated for someone like me!

I'm not a programmer and just want to be able to design a form which can be
seen on screen showing a list of invoices. Each form should display invoice
number, client ID - along with their name, which I guess will come from the
Cleint table - invoice amount, date issued, date paid and the number of days
between issue and payment.

I'm slowly getting to grips with designing the form - although even that
brings some challenges to my brain.

I shall persevere

 

[John W. Vinson]

This sounds like it's getting way too complicated for someone like me!

I'm not a programmer and just want to be able to design a form which can be
seen on screen showing a list of invoices. Each form should display invoice
number, client ID - along with their name, which I guess will come from the
Cleint table - invoice amount, date issued, date paid and the number of days
between issue and payment.

It appears that all of these fields come from the invoice table, except for
the client name. If the ClientID is an Autonumber (that is, meaningless to the
user) then perhaps you should consider not displaying it AT ALL. Instead, have
a Combo Box (based on the client table) on the form, bound to the ClientID;
this can easily be set to store the clientID while displaying the client name.


John W. Vinson [MVP]

 

[StevePlym]

Yes, all the information on the form will be from the Invoice table - except
Firstname and Surname.

The Client ID is not AutoNumber and my hope is to link the tables using
Client ID as the Key and thus display Firstname and Surname on the Invoice
form.

I'm going to have to spend more time reading about form design though as my
first attempt resulted in me not being able to move the fields around the
form!


Steve

 

[John W. Vinson]

Yes, all the information on the form will be from the Invoice table - except
Firstname and Surname.

The Client ID is not AutoNumber and my hope is to link the tables using
Client ID as the Key and thus display Firstname and Surname on the Invoice
form.

Fine... by all means use ClientID as the key; that's actually what I was
suggesting. The question is, does the user need to see ClientID? Is it
meaningful to the user - do they want to select clients by ID, and then just
see the name; or would they find it more useful to select clients by name (and
store the ID)? You can do it either way!

You can base the combo on a query like

SELECT ClientID, Surname & ", " & Firstname AS ClientName FROM Clients ORDER
BY Surname, Firstname;

and set the ColumnWidths property of the combo box to

0;1.25

to display the name and conceal the ID; or, you can use a nonzero width for
the ID, and put a textbox on the form next to the combo box with its control
source set to

=comboboxname.Column(1)

so that the combo will display the ID and the textbox will display the
selected name.

I'm going to have to spend more time reading about form design though as my
first attempt resulted in me not being able to move the fields around the
form!

Erm? First off, there are no FIELDS on a form - fields are in tables. There
are CONTROLS on the form (bound to fields, or to expressions, or even unbound
as needed).

And how are you trying to move them around, and what's stopping you?

John W. Vinson [MVP]

 

[StevePlym]

Thank you John.

Firstly, I'm going to be the only user.

Secondly, it's more a question of completing the details on the Invoice
form. I want to be able to complete the Firstname and Surname without having
to remember the details. I'd much rather complete have these details entered
automatically from the Client table - hence the reasons for using the Client
ID as the link.

Am I on the right road here? Will Access allow this kind of link and display
the information in the form?

When referring to the Invoice form I'd like to search using variious things:
Client ID and Surname would be the obvious choices.


Steve

 

[John W. Vinson]

Thank you John.

Firstly, I'm going to be the only user.

Secondly, it's more a question of completing the details on the Invoice
form. I want to be able to complete the Firstname and Surname without having
to remember the details. I'd much rather complete have these details entered
automatically from the Client table - hence the reasons for using the Client
ID as the link.

Am I on the right road here? Will Access allow this kind of link and display
the information in the form?

When referring to the Invoice form I'd like to search using variious things:
Client ID and Surname would be the obvious choices.

<frustration>

Data is NOT STORED IN THE INVOICE FORM.

Data is stored in the invoice *table*, and is only DISPLAYED on the invoice
form.

If you are attempting to store the client name in the invoice table, *you are
making a mistake*. The client name should NOT be stored in the invoice
table... period. It's incorrect design, it's redundant, it's a waste of space,
it's an open invitation to data errors.

I've told you twice or three times in this thread how to display the client
name on the Invoice form. Have you *tried* implementing my suggestions, or are
you still concentrating on (incorrectly, and redundantly) storing the name
into the invoice table?

John W. Vinson [MVP]