We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Access 2007 Form help

Medium Priority
257 Views
Last Modified: 2012-05-11
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.
Comment
Watch Question

Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
Generally, you would have a "company" table, an "Purchases" table (Purchase, PurchaseDate, CompanyID, Discount, ...), and then a PurchaseDetails table (PurchaseID, ItemID, Amount)

Commented:
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.

Author

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?

Author

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.  
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

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.

Author

Commented:
I finally got it to work.  Thanks to both of you.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.