Purchase Order Part 2 any ideas?

[Ade F]

Purchase Order part 2

This is part 2 to the following posting.

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=49702

It appears that having people given this some thought that in order to correct this flaw someone might know of code that could be applied to a button in order to scrap the entire order. Alternatively if anybody could come up with a solution to the original problem this would be great.

This would require a button being placed on the Purchase Order Form that would then scrap the user defined details within the Purchase order form including the input items in the Order_Guts sub form (containing the actual products required). Is there some way of coding a button that would basically not save the two forms and exit?

This would probably cut down on incorrect orders getting attributed to job numbers.

 

[Rich]

As I told you the only sure fire way to do this is with a query that looks for missing child records.
However it seems you now want users to be able to delete the Parent and child records together, providing you have cascade delete records set in the relationships window then deleting the parent record will also delete the child at the same time.
The button wizard will add the code to enable you to do this.

 

[Ade F]

Thanks for the help Rich

I wondered if you could give me some help with the query? Thos how and where's

Unfortunately I cannot give the user deletion rights as this must be set in stone as far as order history is concerned.

This is starting to annoy me as I would love some how to allow a user to delete or undo while creating the purchase order but as soon as the order is exited and saved then they cannot delete any items. I have maybe dug myself a hole here that seems to be getting bigger.

Any ideas?

 

[Rich]

Use the find Unmatched Records query wizard first, you're looking for the Key field of the Parent not being in the child table.
Post back when done or for further help

 

[Ade F]

I have used the find unmatched query

I have used the find Unmatched Records query wizard and it has successfully identified empty orders.

What do you suggest next Rich? Do you have any ideas for the order entry scenario? (as stated above).

btw can you recommend a good VBA book? I'm familiar with C but have had very little in the way of access VB experience.

 

[Rich]

Make sure you test this on a copy of your db, change the query to Delete, then try running it

 

[Ade F]

I have re-assesed this situation

Thanks for the help people.

In terms of referential integrity all cascading has been implemented. I do not have a problem with this area as far as deleting the orders.

My main problem now is letting a user modify or delete items within the actual order creation process. I'm very close to having a robust solution as I have come up with a new strategy for viewing job related / order related details. I few more tweeks and it could be onward and upward.

Many thank to Rich for the query it has helped to find those incomplete order (there was only 1 at this time but it may have been more).

Upon completion of my solution I will post what I have done. This may or may not help other people in this situation.

 

[Ade F]

Getting there...

Hello again readers and helpers,

I just thought I would explain a little further as to my ensuing challenge against the dreaded skating user intent on traversing just about every record with his / her mouse wheel or page up / down. Lets face it; it's a pain in the neck. I have included below three screen shot us to show my progress in my ordering system. I do not confess to knowing it all far from it, I have actually only been familiar with access for about 8 weeks now and the learning curve isn't too bad. I have experience with C programming but none with visual basic although I'm sure with the help of the forum readers and a good book this could be rectified. If you have any questions or comments about the screen shots then please do so accordingly. I'm an engineer and IT general all rounder at the end of the day, and to have access to such a vast array of professional advice has been most useful in my quest to tame the beast that is known as access.

I thank everyone for this forum.

Below is the main screen; I thought a visually pragmatic approach would be the ticket for this small database. As you can see I have grabbed screenshots of the various area pages and placed them directly on the front end. I thought this might bring some form of familiarity to the users (and it appears to be working). A week ago I changed the design and removed the screenshots only to be told by the users that they prefer the actual area representations being in place. The areas from top left down are

Client area, - Far Left Top
Job Details - Far Left Bottom

Order History - Middle Top Left
Employees Details - Middle Bottom Left

Note - Middle Top Right
Suppliers - Middle Bottom Right

Purchase Order - Far Right Middle / Top

Notes Area - Bottom Middle

Admin Area - Bottom right

Below is a screen shot of the order history page providing the user with valuable information regarding the time date and employee who ordered said item, you may have noticed that additional to this is the actual job name (in this case stock) and other information this information is directly provided from the job details area the logic goes as follows

1. Create Clients
2. Create Job
3. Make sure supplier and product exist
3. Create Orders for job

If this logic is not followed then orders simply cannot be placed the reason being I wished for a complete order history viewable by job. We have a few repeat orders and it is easier to copy and paste previous ordered products into fresh purchase orders.

. To secure this are from potential damage I ended up making a copy of the original Order History Page (now in admin area) and basically locked using the data parameters (under properties) such items as

All set to no

Allow filters
Allow delete
Allow additions

etc

This seems to have done the trick as now a user (it appears) can only access purely on a visual basis the information related to purchase orders. As I said previously a copy of this area unlocked is placed under the admin area and only certain users with admin rights can enter and delete as applicable unwanted orders.

Sorry to skip through my explanations so quickly as I only have a limited time for this activity. Beneath this text is a screen shot of the actual purchase orders. To stop the user from creating havoc I have enforced two security standards

1. Disable the page up / down buttons. - http://support.microsoft.com/default.aspx?scid=kb;en-us;132031

2. Disable the mouse wheel (2000+ only I think) - http://members.shaw.ca/glenk/JimWheelMouse2k.zip

This appears to have stopped the potential problem of a user giving a field focus and then spinning that blasted wheel. The effect would be the user viewing previous orders and order history, this is something that I do not wish to happen as I have granted users the ability to delete and modify information regarding ORDERS and ORDER DETAILS (sub form). This then allows a user to modify the contents of the purchase order until they are happy to send it to win fax. Upon sending it is a case of exiting via the provided button and the purchase order is saved and locked only viewable in the order history area. In no other part of the DB (accept admin) can a user delete or modify past purchase orders history. On the top left hand side you see a red button if at any point a wishes to scrap the order (and they infrequently do) he / she can click this button to deleted both ORDER entries and ORDER DETAILS entries for this order (with the use of referential integrity and cascading). Before anyone says that I have used datasheet view for the product entries then yes but I have done this in several areas with meddling from users. The fact is that they re-size back to original state upon exiting the form. Any printing is done by fixed format reports so I don’t have a problem with it.

I do still have one problem that is yet to be cured but I'm working on it it is stated at the start of this post. How can I stop somebody from just filling in the top half of the order and then exiting leaving an order without any products attributed to it? This one will be solved soon with the aid of you helper people I'm sure. If anyone spots a blinding flaw hen please let me know.

 

[Ade F]

No offence intended

I have notes your comment and putting them in place. It was a most ingenious solution.

Although it is nice to hear other opinions on potential solutions.

Thanks for your help Pat.