Couple challenges to overcome with Invoicing/Order Form

[Schnotz]

Hi, I'm new to the forum and new to Access. I'm putting together an ordering database for our helpdesk staff to use to improve the way it's done now, which is by excel spreadsheets. I've successfully migrated the excel spreadsheet list of sites and parts into new tables and now I'm building the order form. I'm having 2 main challenges now:



1. Under normal circumstances we will bill the same site that we ship to but under other circumstances we may need to bill another "head office" and ship to a different site. So, we have a "bill to" address and a "ship to" address and they seem to be linked together. When I select a site in the bill to list it mirrors the same information in the ship to list. We need them to be unlinked so we can select different sites, based on the same table. Is that possible or do I need to make a copy of the "Site List" table and call it something like "Site List2" to generate different results?

2. I'm building the order subform and I have the order subform as a seperate table linked to another table that has all our parts that we supply and prices. What we need is when selecting the part description it auto populates the partno and retailprice. I'm pretty clear on calculating the extended price based on the qty entered but when I select a part description based on the price list none of the other information carries over.

I've attached a screenshot to help better describe the challenges I'm facing, I'm sure it's an easy fix but just need some directional guidance on how to fix the issue. I've also attached a zip file of the database and form for a better eyes on look at the issue.

QPS_Form.zip

[ssanfu]

The attachments didn't attach

You might look at the Northwind dB to see how they set up the tables/queries/forms/reports for invoicing. There is a invoicing template on the microsoft site under A2007 templates that might help.

[Schnotz]

Thanks for the reply Ssanfu. I've attached the files now, a screenshot of the QPS form and the accdb file. I've had a look at the Northwind example and the ordering system doesn't really work either...you can select the product but it doesn't update the retail price.

[ssanfu]

1. Under normal circumstances we will bill the same site that we ship to but under other circumstances we may need to bill another "head office" and ship to a different site. So, we have a "bill to" address and a "ship to" address and they seem to be linked together. When I select a site in the bill to list it mirrors the same information in the ship to list. We need them to be unlinked so we can select different sites, based on the same table. Is that possible or do I need to make a copy of the "Site List" table and call it something like "Site List2" to generate different results?

You have the shippers controls bound to the same fields as the customers. If you look at the relationship window of the Northwind database, you will see that there is a customer table and a shipper table with FKs into the Orders table.

2.

I'm not sure about this one due to the use of look-up fields.


------- Also...
You have tables bound to your forms. I (almost) always use queries for ease of sorting & filtering
You have a lot of look-up fields. These are considered "evil". See http://access.mvps.org/access/tencommandments.htm Follow the link in #2
You are using Macros. I never use macros, only VBA code. At this point, it is really hard for me to even read the macros.

No relationships set in the relationship window. Not necessary, but it helps. Enforces relational integrity and auto joins.

[Schnotz]

thanks again ssanfu,

I have cleaned up the qps_form file and attached it. I really desire to learn how to do this myself but please be gentle with me, as I'm a novice. If you or anyone else could point me to some articles on how to do the things needed for this project I'd greatly appreciate it. I have purchase Access 2007 for dummies so I can learn how to do this back to front but really need to make some progress on this project to demo to my group and management that I can do this on my own.

Please refer to the form QPS_form in the file attached and the associated tables and queries.

1. The bill to and ship to address is linked to the Live Sites table. I realize that I'll need to build a query to populate these fields but if I can't get the order form fields when selecting the part description linked and the other fields populated with the correct information, I won't be able to get the address linked either. It works now, just as an example but I realize that I'll need to build it correctly when I understand the nature of queries.

IN A NUTSHELL, I want to be able to select one site and populate the boxes for that particular site on the left, then do the same with the fields on the right for a completely different site (if needed). I should be able to select them independently of each other.

2. The order form itself should be the simplest to do, it only has 3 fields to reference...the partno, description and retail price. I built the query and linked it to the combo box and it's working in the way it should but how do I get the corresponding partno and retail price in the query to populate in the form/table?

IN A NUTSHELL, I'm able to select the part description in the combo box but the partno and retail price won't autofill (is that the correct term?) to the corresponding boxes on the form.

I put in a relationship between the partQ (query) and the orderf (order form) in the logical way I think the 2 should relate...partno to partno, description to description, retailprice to retailprice. The extended price will be calculated based on the qty entered and retail price of the selected item.

I know this should be really basic stuff to know but I really do desire to learn the nuts and bolts of the process so I can do more database projects to help our office get out of Excel Hell that we seem to be in. I know it can be done better with databases.

Just point me to the basic material that I need to learn and I will study it and try to apply it myself to this project.

Copy of QPS_Form.zip

[ssanfu]

I have to use a friend's computer that has A2K7 since I have A2K, so it will be a while before I can take a look. Hopefully someone else will jump in..... I will look at it as soon as I can.

[Schnotz]

Thanks again Ssanfu, you're a champion! I got some help at work today to get the order part working. Now, I'm moving on to tackle the unlinking of the address boxes at the top of the form. They should be independent of each other, what would be the best way to go about that? Thanks again, you're magic!

[ssanfu]

I don't remember your table structure exactly, but you could set it up alot like the Northwind database, with the additiion of a Shippers table.


The orders table record source is a query with two tables. The orders table has a field for the customerID (FK) and fields to save the shipping address. To select the customer, the form has a combo box to select the customer. The customer address fields are bound to the fields in the query from the customer table.
The shipping customer/address fields are bound to fields in the orders tables for the shipping address (one order will be shipped to one address). there can be cascading combo box to select a shipping address (cascaded from the customer combo box). There can be a "copy" button that would copy the address field data to the shipping address fields. Or you could just type in the shipping address.

Clear as mud???