Double-click on a subform record to open that record in another fo

[Guest]

Hi!

My database is linking Customers and Invoices.
I use three table: tblCustomers showing all details for each customer,
tblInvoices showing the detail of each invoice (identified by a unique
number), and a third table tblLink that connects customers and invoices.

Here's what I would like to do:

In form frmCustomers I have a subfrom which shows all the invoice numbers
that have been used for a specific customer.

I would like to be able to double click on one invoice number in that
subform which would open the form frmInvoices directly on that specific
invoice.

Thanks in advance for your help!

DLH

 

[Nikos Yannacopoulos]

My database is linking Customers and Invoices.

I use three table: tblCustomers showing all details for each customer,
tblInvoices showing the detail of each invoice (identified by a unique
number),

so far so good...

and a third table tblLink that connects customers and invoices.

Ouch! Why would you do that? Any chance you bill several customers in a
single invoice? Don't think so.

A table like the one you describe would be used in the case of a
many-to-many relationship like, for instance, between students and
classes, where each student takes many classes, and each class is taken
by many students. In this case, you have a one-to-many relationship: a
customer may have several invoices, but each invoice belongs to a single
customer. In this case, no intermediate table is required, just add a
CustomerID field to the Invoices table, and link the two on this field.

Furthermore, chances are you need an InvoiceDetails table as well... if
an invoice may contain several different items (products, services
rendered etc) then you need this table, with an InvoiceNo field (linked
to the same field in the Invoices table), ProductID (or ServiceID or
whatever), Quantity, UnitPrice. Normally you would also have a Products
table, with ProductID (the InvoiceDetails ProductID field is joined to
this one), Description etc. The only case in which you do not really
need an InvoiceDetails table is if your invoices will only ever include
a single item/service (unlikely).

Having straightened out your data design, you can then revisit your
forms' design and automation.

The general rule is: always give your data structure all the time
required to make sure you have a good, robust, normalized design. Don't
cut corners, there is a high price to be paid for that sooner or later
(mostly sooner than later!). If at some point further down you
development you find there is a problem with your design (like in this
case), don't try pulling any tricks, just go back and fix your design. A
quick and dirty trick will seemingly solve the problem at that point,
but will also make you need another one before you know it, and then
another, and so on. The result is a complex, underperforming database
which takes a lot more time and effort to build, and is a nightmare to
maintain.

HTH,
Nikos