Forms

[Aardvark]

Dear all,

I have a database with a customer details table linked to an invoices table.
The invoice table only contains the Customer ID, invoice
number, invoice date, and other invoice details (a memo), the customer table
has Customer ID, Title, first name, last name, memo, address line 1, address
line 2 etc, and general phone numbers and email. Customer ID (autonumber)
is the primary key in both the client table and the invoice table.

I have a form for the client details and I am trying to set up a second form
for the invoices in which I can enter a customers title first name and last
name followed by the invoice details which will assign the information
correctly, linking the customer to the invoice.

Any ideas?

Thanks,

Danny

 

[Albert D. Kallal]

The best way to approach this is to simply bring up the customer form,a nd
then have a sub-form that lets you add a invoice. This approach means that
ms-access will set the "key" value used in the invoice for you
automatically.

This approach also means you don't need a bunch of code to copy/display the
customer information since you are already on the customer form!

You sub-form could be behind a tab if you want, and thus you can have a lot
more screen space availing...

You could also perhaps just have a button on the customer form that lets you
launch the invoice form...but when they are separate, then YOU MUST write
code to set the correct customer ID in the invoice. With a sub-form, you
don't have to write this code. Further, a sub-form means you can "view" or
edit or "see" the existing invoices for the customer.

All in all, you get a lot of benefits with the sub-form......and no code
needed...

 

[Roxie Aho]

-----Original Message-----
Dear all,

I have a database with a customer details table linked to an invoices table.
The invoice table only contains the Customer ID, invoice
number, invoice date, and other invoice details (a memo), the customer table
has Customer ID, Title, first name, last name, memo, address line 1, address
line 2 etc, and general phone numbers and email. Customer ID (autonumber)
is the primary key in both the client table and the

invoice table.

Do you mean Customer ID is the primary key in the Customer
table and the foreign key in the Invoice table?

I have a form for the client details and I am trying to set up a second form
for the invoices in which I can enter a customers title first name and last
name followed by the invoice details which will assign the information
correctly, linking the customer to the invoice.

Any ideas?

You might use a little different structure. Instead of
using a memo field for invoice details, create another
table named Invoice Details in which Invoice ID is the
primary key in the Invoice table and the foreign key in
the Invoice details table. Primary key in details is
DetailID

Base frmInvoice on the invoice table and set its Data
Entry property to True to give you a new record every time
it opens. It should contain the fields InvoiceID,
CompanyID, InvoiceDate and others you choose.

Add a combo box to the form with a Row Souce of SELECT
[tblCompany].[cCompanyID], [tblCompany].[cCompany] FROM
[tblCompany]. Column 1, the CompanyID is the bound
column, but its Column Width can be set to 0 so the user
sees the Company Name in the drop down. The After Update
event of the combo is simply Me.CompanyID =
Me.cboSelectcompany, which is the name of the combo box.
When the user selects the company, the CompanyID field is
populated.

I suggest an Invoice Details subform based on the Invoice
Details table. Fields would be DetailID (primary),
InvoiceID from table invoice, Item and Amount. You could
add a date field if the items have different dates. In
the Form Footer you could put a totals field in which you
would =Sum(Amount)

Open frmInvoice in design view and drag the Details form
onto frmInvoice. Link the two forms by InvoiceID.

Your invoice is a report that is based on two queries.

The first is qryInvoice which takes data from tblInvoice
and tblInvoiceDetail, linked by InvoiceID. It contains
fields InvoiceID, CompanyID, InvoiceDate, DetailID, Item,
Amount and InvoiceNumber.

The second query is qryPrintInvoice, which takes data from
qryInvoice and tblCompany (individual, name, address, etc)

Build your invoice using the report wizard, group on
InvoiceID and sum amount.

Good Luck

Roxie Aho
roxiea at usinternet.com

 

[Aardvark]

Thanks to you both. Albert in your method would I have to re add the full
client details every time I wanted to add a new invoice, or could I just get
away with the names?

Roxie, thanks a bundle! Nice instructions, very complete and clear (I am an
access newbie and I think I can follow them ).

I will try both methods

Thanks again,
Danny