Using form to create/update record

I am trying to use this form to update a table. What I want to happen is the user enters data into qty received and selects a status (damaged, missing, etc). When they select "received" I want to run a query that subtracts the number entered from the qty field in the purchasing table and adds one to the inventory qty table.
Test.accdb
lordofbeondAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
I don't think you're going to be able to do this without setting up a primary on your purchasing table.

<When they select "received" I want to run a query that subtracts... >

You've got multiple orders for the same part/same vendor, so how do you match up which line in your purchasing table to subtract the Qty Received from?

Further, you are using an unbound control for Qty Recieved.  As-is anything you enter in this textbox will show up in all records.

What you need to do to fix this is:

1.  Add an auto number (primary key) to your purchasing table
2.  Rename your Qty Field to something more descript like QtyOrdered (at least in the form, if not in the table)
3.  Add a field called QtyReceived to your table and bind the corresponding textbox on your form to that field
4.  Create an unbound textbox on your form labeled Remaining on Order, and set its control source to
       = QtyOrdered - nz(qtyReceived,0)

This will display (not store) your calculated data - and will make it simple for you to manipulate/calculate data from this table in various other ways.




 
0
mbizupCommented:
You may also need to go back to the drawing board on the query for that form.  I don't think it is going to consistently give you the correct product description of the part ordered,
0
lordofbeondAuthor Commented:
1.  Add an auto number (primary key) to your purchasing table
2.  Rename your Qty Field to something more descript like QtyOrdered (at least in the form, if not in the table)
3.  Add a field called QtyReceived to your table and bind the corresponding textbox on your form to that field

I added those... Much better idea (for later reporting)
This will help but I need to make note of damaged items. Any Idea how to do that? I was going to add a note field to mark what the damage is, but I was going to keep my posts to 1 question at a time.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

lordofbeondAuthor Commented:
You may also need to go back to the drawing board on the query for that form.  I don't think it is going to consistently give you the correct product description of the part ordered,

I planned on it... There are not part numbers for every part (for example they had SEE DRAWING for a part number).
0
mbizupCommented:
Your tables and relationships as they stand are going to prevent you from doing this and much, much more.

You need primary keys on all of the tables involved.

Then you need to restructure your tables like this:

Products

You need a Product ID to uniquely identify these records


Purchasing
You need an OrderID to uniquly identify orders, and use the ProductID to uniquely identify the product/vendor combination for a given order.


You really cant do much without making these fundamental changes.
0
mbizupCommented:
Sorry - I missed your posts while I was digging into this.

I think regarding your questions, you should focus on design first and then functionality in later questions.

You are rapidly reaching a roadblock the way things are going.
0
mbizupCommented:
Btw - I have no objection to you deleting this thread and reconsidering your approach to this....
0
lordofbeondAuthor Commented:
Ahhhh I gotcha... I made those fixes and realized I could drop vendor ID from some other tables.. I made the changes.

I'm still unsure how to make new records for noting damaged parts
Test.accdb
0
mbizupCommented:
<I could drop vendor ID from some other tables>

Yeah - if you have a part number used as a foreign key in any table, you automatically have the ability to look up related information like description, vendor, etc.

Another consideration with this type of database is manufacturers vs vendors. (do you see a need in your application for differentiating between who makes a product and who sells it?).


I'll take a look at your new sample... those changes may be what it takes to at least work out the update.
0
mbizupCommented:
That looks MUCH better!

This should do what you need for the qty remaining on order 'update' that you want.

I worked this into your query.

- Added a field to your query LeftOnOrder: QtyOrdered - QtyReceived
- Added a field to your form to show that update
- This does NOT store the update in your table

A standard for this type of database is to store qty ordered and a qty received, calculating what is remaining on order and displaying it as needed (those two fields give you everything you need to know).

This avoids storing redundant data, and greatly simplifies reporting the data you need to see.

As far as the 'received' option in the dropdown goes... that sounds like something you should change to "order complete" (rather than received) - selectable by a puchasing person who verifies that all parts have been received.


Test.accdb
0
lordofbeondAuthor Commented:
I see what you did there but it actually doesn't solve the problem. They want to have a form of traceability on damaged parts (IE what Project and how its damaged) so the office can get refunds or do send backs. I need to be able to enter damaged parts.

I'm thinking that I could make a damaged parts form that creates a record and adds the value of [qty dmged] to received. Unless you can think of a better way (either way you deserve the points for this thread you have been a tremendous help)
0
mbizupCommented:
<I could make a damaged parts form that creates a record and adds the value of [qty dmged] to received.>

Ok - your order form can handle parts received as we've done.

Damaged parts are a different matter, and as I think you're suspecting, may not work on your purchasing form.

Depending on the level of detail needed, you may be able to add a column for "Qty Damaged", and work with that - but it sounds like you need more detail than that.

Here's a thought...

Create a table for "Parts returned" (tblPartsReturned)

ReturnID  - Autonumber, PK
OrderID - Foreign Key to link to the purchasing table
ProductID - The product
QtyReturned - how many were returned
ReasonForReturn - Text reason (selectable from a drop down, maybe)
Comments - Memo field


That ReasonForReturn is optional - just a thought that you may have reasons other than 'damaged' for returning things.

For data entry, you could use a mainform/subform setup linking your master records (Orders) to the child records (Parts Returned).

This is a good tutorial for that setup:

http://office.microsoft.com/en-us/access-help/create-a-form-that-contains-a-subform-a-one-to-many-form-HA010098674.aspx

The reason you want to set up the forms this way is to ensure that your returns automatically get 'tied' to the correct orders (traceability).

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lordofbeondAuthor Commented:
Both of those would probably work. I am going to close the thread and give you your points. I will see how specific they want to be and post another thread if I hit a snag.


Thank you for your help... it has made me change the structure of a few different things that seem to have greatly improved the structure.
0
lordofbeondAuthor Commented:
Thanks for all the help... I thought it would be a simple question but it seems the structure was causing most of my problems
0
mbizupCommented:
Glad to help out -

Another thought on table design here is to have a seperate table, linked to your purchasing table to track qty received, date recieved, etc - and include a checkbox or some other field to mark items as damaged as they are being received.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.