I really need some assistance on this one. What I need done is fair beyond my Access capabilities at

[LonnieCAR]

I really need some assistance on this one. What I need done is fair beyond my Access capabilities at this present time. Long story short, one of my employees built a database that prints forms and keeps records for each entry into the system. Due to recent regulations with upper management for our ISO certifications they now want the database to show a history for each order. Upper management was hoping to use the “HRMS File Number” field as a point of reference. Is there any way the current database can be manipulated to search by this field and pull-up every order associated to this number? I would have our company’s IT guy look at it, but every time he alters or adds something other parts of the database start to mess-up. Any help would be much appreciated. I have attached a zipped copy of what I am working on. Thank you.

[CJ_London]

Not clear in what way you want to achieve/present your requirement, but why not on your addandorderanddetails form simply right click on the HRMS file number field and filter for the number you want (or add a combobox to do the same). This will limit the records available to view (in the case of 200000 there are 4 records) so stepping through them gives you a history - and if you want it in some sort of date order, right click on the shipdate field and sort it.

At the very least if this is showing you all the data you need for your requirement, you know you can use the number in the way you want.

[rpeare]

What do you mean 'history for each order'. You want to look at a HRMS File Number and pull any order associated with that HRMS number? that's pretty easily done.

Create this query:

SELECT Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate, Orders.HRMSFileNumber, Orders.ShipDate, Orders.ShippingMethodID, Orders.FreightCharge, Orders.Taxes, Orders.CoverWrap, Orders.Comment, Orders.PONumber, Orders.InStock, Orders.CoverColor, Orders.ShipToID, Orders.Field1, OrderDetails.OrderDetailID, OrderDetails.OrderID, OrderDetails.ProductID, OrderDetails.ProductName, OrderDetails.Quantity, OrderDetails.UnitPrice, OrderDetails.Discount, OrderDetails.CoverColor, OrderDetails.Leg, OrderDetails.Length, OrderDetails.Thickness
FROM Orders INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
WHERE (((Orders.HRMSFileNumber)=[forms]![addanorderanddetails]![txtHRMSNumber]));

save this query as qryHRMSLookup

Put a button on your form
in the ON CLICK event of the button put this:

docmd.openquery "QryHRMSLookup"

This is a simple example if you actually wanted to print a report based on the query you could build that and so on.

[orange]

What exactly do you mean by History?
You could certainly report each OrderDetail (and Product etc) for each Order by HRMSFileNumber.

I note that there are OrderDetail records without a Product??

Order Detail ID	CountOfOrderDetailID
22	1
83	1
84	1
85	1
92	1
127	1
130	1

Your IT guy should not be "playing database/testing" in your production database.
You may wish to research data management practices, or database management procedures.
Sample via Google search data management

Most experienced database people would have separate Development, Production, Maintenance and backup/archive set up.

OOopps! I see rpeare responded while I was typing.

[LonnieCAR]

Orange,I think the primary goal is to just lookup all the times the same file number has been used to compare all the details associated with previous entries. I think rpeare's option will work for now. If there was some way to generate this data into a printable report this may be even better.Thanks all.PS... I literally have no idea what you're mentioning above... hence my super novice-ness.I reattached the database with changes.

[LonnieCAR]

Actually, I just got off the phone with management and they'd like a button or search bar where we could enter the "HRMS File Number" and each time it is clicked view each instance of that number being used. They are wanting to compare the work order orders. Is there anyway to create a button that would allow me to pull up and view all the work orders associated with 1 HRMS file number?

[rpeare]

yes, just put a prompt in the query in the criteria row where it's currently

[forms]![formname]![fieldname] put in

[Enter the HRMS number]

When you run the query you'll be prompted with a little box that says 'Enter the HRMS number'

[orange]

A major issue with your set up in my view is this

I would have our company’s IT guy look at it, but every time he alters  or adds something other parts of the database start to mess-up.

Your organization needs some sort of oversight on how your database is managed. Especially now that senior management is looking to set some new policy/guidelines/requirements.

From a data integrity point of view, every order detail record would have a non-null Product.
Null/undefined records in OrderDetails is a symptom of a wider problem.

rpeare's approach will work to identify all Orders, all OrderDetails etc for any given HRMSFileNumber.

[rpeare]

Just one other thing, Ajax has pointed out something very relevant. I don't know how much time/effort you want to put into this database but you should really look at limiting what your users see and have access to change. With the current database design it's very easy for someone to accidentally or intentionally change data elements which may lead to screw ups in shipping/receiving/inventory etc. I would back up Ajax's suggestion that you try to limit what your users see either by making your forms unbound controls (in other words users can't change data unless your code allows it) or by forcing the user to look up a specific record before modifying it. The current design basically allows access to all records when you open the form which is not a great idea.

[LonnieCAR]

Thank you for all the support. I do agree with making this database more refined for future use. It is just that it only has two users at this point. I will definitely be back on here in the future to find out the best methods for making this system more secure and better.