• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 245
  • Last Modified:

Access 2007 Form help

In Access 2007 we have a form with a subform that we use to create invoices.  The main form contains the company information such as point of contact, address, phone, etc..  The subform has the items that were purchased.  Our question is how can we save the invoice in case we have to refer to it at a later date.  The way it is set up now the main form references the company ID and when we query by that we bring up everything that company has ordered in the past.  We just want to reference a specific invoice.

We may have set this up wrong from the beginning.  I hope not.  Any help will be appreciated.
0
ndbadger
Asked:
ndbadger
  • 4
  • 2
  • 2
2 Solutions
 
Dale FyeCommented:
Generally, you would have a "company" table, an "Purchases" table (Purchase, PurchaseDate, CompanyID, Discount, ...), and then a PurchaseDetails table (PurchaseID, ItemID, Amount)
0
 
rj8820Commented:
Your question is a little too general and the answer would depend on the architecture of your application and data.

In general you should allocate a unique invoice number at the time the first product is entered into the subform and mark the invoice as 'un-issued'. That way you can come back to it if you know the invoice number provided the invoice is open or un-issued.

If you actually print the invoice and hand it to a customer you should mark the invoice as issued and that closes it off. You can't then select it to add more product.
0
 
ndbadgerAuthor Commented:
Fyed,

We have two tables.  Company and Records.  Company just has the company profile information.  Records keeps all of the department information as well as the items purchased however these are listed individually.  So for us to bring up specific invoices we would have to recreate this process and add that third table?
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
ndbadgerAuthor Commented:
rj8820,

I suppose you are right as far as being to general of a question.  Our problem is that there wasnt an invoice number added to this form.  The only way to bring up the purchases is to scroll through the records by company and see which items have not been paid for.  
0
 
rj8820Commented:
Well you need to think about the database design and make the table structure emulate real-life.

It seems to me that what you have is company (or "customer" I assume??), then within each company you have a number of invoices, then within each invoice you have a number of products. That's what your table structure needs to be. So from a company or customer you can see all the invoices in their file. Then selecting an invoice will lead you to the products on that invoice.

The way your forms etc work should mirror the database design.

So the basic table structure should be...

Company:
Name
Address
etc.

Invoices:
Company
Invoice Number (could be an auto number)
Date of invoice
etc.

Products:
Company
Invoice Number
Product
quantity
etc.
0
 
Dale FyeCommented:
Probably, unless your Records table contains an InvoiceID or a InvoiceDate (assuming only one invoice per company per day).

Generaly, a form designed for entering items purchased would not have the main form based on the company.  It would be based upon the Invoices table.  Since one of the fields in that table would be a CompanyID, I would normally have a combo box to allow selection of an existing company, and when that company is selected, I would probably display a subform for viewing and updating that company's information.  I would have a separate subform based on the PurcaseDetails table and linked to the main form on the InvoiceID field.
0
 
ndbadgerAuthor Commented:
I spent all weekend working on this and I'm still stuck.  I have a main form now and one subform linked to the main showing company information when I select the company name using a combo box.  I also have that linked to the Invoice records table and everything is saving perfect up to now.

My next issue is selecting the products.  I have a third table now that lists all of the items that we invoice for.  What I cant figure out is how to select these items and have them saved to the table by the invoice number.  I can do one item at a time but I cant get it to work when I have multiple items.
0
 
ndbadgerAuthor Commented:
I finally got it to work.  Thanks to both of you.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now