forms, junction tables, and many-to-many relationships

[RMP]

I am new to access and databases, but I have some academic coding experience.
If this is the incorrect forum in which to pose this question, I apologize.

I have two questions. First, how do I setup a form to lookup the
corresponding records of a particular computer and invoice (based on
SerialNumber or InvoiceNumber)

Second, how do I setup a form for adding new computers/invoices so that I
can do it all on one form. For example, I order a new computer, get it in,
and begin keying in the information. When I enter information into the form,
it creates a new computer record, a new (or uses an existing invoice if the
invoice number is found) and then creates a linking entry in the junction
table.

I am working with three tables:
Computers:
ComputerID (replication ID, auto number)
SerialNumber
Name
etc

Invoices:
InvoiceID (replication ID, auto number)
InvoiceNumber
PurchaseDate
etc

ComputerInvoice:
ComputerInvoiceID(replication id, auto number)
ComputerID
InvoiceID

I currently have the relationships in Access setup in the following manner:

Computers.ComputerID 1 -> many ComputerInvoice.ComputerID (enforced ref
integrity)
Inovices.InvoiceID 1 -> many ComputerInvoice.InvoiceID (enforced ref
integrity)

Any help or suggestions would be greatly appreciated.

 

[Tore]

Are you sure you need a many to many relationship between computers and
invoices? If you do, what you suggest in your 3 tables seem to be ok. Things
will be simpler with a one to many relationship (One invoice can have many
computers, but not the other way around).

Your question: First, how do I setup a form to lookup the
corresponding records of a particular computer and invoice (based on
SerialNumber or InvoiceNumber)

This is normally solved in a subform enclosed in an outer main form. The
subform can be synchronized with its outer parent form in many ways. The
simplest is to use link fields. Computer data (serialNo) in the outer form
can be used to show corresponding Invoice records in the subform. The subform
data will then consist of a query based on both your tables ComputerInvoices
and Invoices. In form designer you can fill in wich datafields that are used
to synchronize main form and subform.

Your question:Second, how do I setup a form for adding new
computers/invoices so that I can do it all on one form.

You will normally add a new record before you type in data for a new
computer. You can add a new record using the navigation buttons in the bottom
of every form, or you can make a button "Add computer" that can open a new
form for you.

In your computer form you can add a drop-down list (combobox) that allows
you to select the invoice for your computer (if the invoice exists). I guess
you may also need a button "Add Invoice" to open a new form where you can
tyoe in data for a new invoice.

Regards

Tore