New Form Clearing Old Form For PO's

[jillnoble]

Hi all,

Hope you had a good Christmas and Happy New Year to everyone. Sorry, but back here wanting some advice please. I originally created a PO system on excel for our company, using a Macro to issue a new PO for every entry and automatically creating a PDF for each PO. However, since being part of Business 365 it doesn't seem to work if individuals go into the form when others are in it. So, I thought, as we use Access for lots of other processes within our company it would be better to use it to create PO's. But, I cannot seem to set the form to 1. bring up all the PO's for my manager to accept and release the PO and load a new PO whenever a user creates one. I don't want it set a new form for every entry (as I already do this for other forms for data/time sheet entry) but I need it to clear all info when a new PO is raised. I've googles it but I don't see, to be able to find a solution. Please find attached a very rough PO example... Any help very much appreciated. Thank you in advance.

 

[theDBguy]

You mentioned 365, would everyone be using this Access database while working in the same work location?

 

[jillnoble]

Yes, and the Macro I built for Excel doesn't like 2 people being in it at the same time

 

[jillnoble]

We all use the database i've built at the same time without any problems including this estimate one but I cannot seem to apply the same rule to the PO form??

 

[theDBguy]

We all use the database i've built at the same time without any problems including this estimate one but I cannot seem to apply the same rule to the PO form??

Quick question, did you split your database to a front end and back end?

 

[The_Doc_Man]

You listed several business goals. As starting advice to help you deconvolute your problem, follow the old rule, "Divide and Conquer." Break your goals into parts and divide/subdivide until you can conquer each division.

I cannot seem to set the form to 1. bring up all the PO's for my manager to accept and release the PO and load a new PO whenever a user creates one. I don't want it set a new form for every entry (as I already do this for other forms for data/time sheet entry) but I need it to clear all info when a new PO is raised.

Preparing the system for a new PO is a separate task from bringing POs to the manager which is then a separate task from accepting the PO which may be different from releasing the PO. You can drive yourself nuts by treating this monolithically. Beat the parts into submission. Don't let them gang up on you. I'm perhaps saying this in a joking manner, but the ability to compartmentalize tasks, to force them to be separate... that is crucial to developing ANY multi-step, multi-responsibility process. That is fundamental part of the "divide and conquer" method of development.

 

[jillnoble]

Quick question, did you split your database to a front end and back end?

No, I'm not really a DB expert, Have just muddled along with my companies requirements. No experience in DB's at all I'm afraid.

 

[jillnoble]

You listed several business goals. As starting advice to help you deconvolute your problem, follow the old rule, "Divide and Conquer." Break your goals into parts and divide/subdivide until you can conquer each division.



Preparing the system for a new PO is a separate task from bringing POs to the manager which is then a separate task from accepting the PO which may be different from releasing the PO. You can drive yourself nuts by treating this monolithically. Beat the parts into submission. Don't let them gang up on you. I'm perhaps saying this in a joking manner, but the ability to compartmentalize tasks, to force them to be separate... that is crucial to developing ANY multi-step, multi-responsibility process. That is fundamental part of the "divide and conquer" method of development.

Ok, so now you've completely confused me!! I only want a PO system that gives each PO a new number and clears the old form for a new one?? Bless you Doc Man but I think I may have over confused things!

 

[Pat Hartman]

Your schema needs to be fixed first.

1. You need to separate the PO data into PO and PODetails just like Order and OrderDetails or Employee and Dependents. It doesn't matter if you think that no customer would order more than 10 items. There is no reason to design the application to limit the PO to 10 items so the items stuff belongs in a PODetails table so you can have as many or as few Items as each PO actually needs. Remember, this is Access. It is not Excel.
2. Every table needs a PK. Do NOT use PONumber as the PK, use an autonumber and let the PONumber be just a reference. This will save you a lot of work should you ever want to change the length or format of your PONumber. The users will still see it and search on it.
3. Never name all your autonumbers ID. That just causes confusion. Each PK should have a unique and meaningful name.
4. Your FK's should then mimic the name of the PK they point to. That makes it easier to see connections without actually making them but you should make relationships regardless. AND enforce RI.
5. Then we get to the object names. They should contain only letters (upper/lower case), numbers, or the underscore. They should NEVER contain embedded spaces or special characters. VBA does not allow embedded spaces or special characters and so will force all unacceptable names to be enclosed in square brackets [] which makes for annoying typing and difficulty reading the code.

Basics first and that means the tables. They are your foundation.

 

[jillnoble]

Your schema needs to be fixed first.

1. You need to separate the PO data into PO and PODetails just like Order and OrderDetails or Invoice and InvoiceDetails. It doesn't matter if you think that no customer would order more than 10 items. There is no reason to design the application to limit the PO to 10 items so the items stuff belongs in a PODetails table so you can have as many or as few Items as each PO actually needs.
2. Every table needs a PK. Do NOT use PONumber as the PK, use an autonumber and let the PONumber be just a reference. This will save you a lot of work should you ever want to change the length or format of your PONumber. The users will still see it and search on it.
3. Never name all your autonumbers ID. That just causes confusion. Each PK should have a unique and meaningful name.
4. Your FK's should then mimic the name of the PK they point to. That makes it easier to see connections without actually making them but you should make relationships regardless. AND enforce RI.
5. Then we get to the object names. They should contain only letters (upper/lower case), numbers, or the underscore. They should NEVER contain embedded spaces or special characters. VBA does not allow embedded spaces or special characters and so will force all unacceptable names to be enclosed in square brackets [] which makes for annoying typing and difficulty reading the code.

Basics first and that means the tables. They are your foundation.

Bless you and thank you Pat, but I really do not understand what you mean. I have built a DB for my company not knowing anything really about how it works and unfortunately only found out about RI when I was quite some way in. What we have works for us, rightly or wrongly. I'm looking for a simple answer to my question but if it cannot be done, please ignore me,,,

 

[GPGeorge]

Bless you and thank you Pat, but I really do not understand what you mean. I have built a DB for my company not knowing anything really about how it works and unfortunately only found out about RI when I was quite some way in. What we have works for us, rightly or wrongly. I'm looking for a simple answer to my question but if it cannot be done, please ignore me,,,

Unfortunately, creating a usable relational database is quite a bit more complicated than layout out a spreadsheet, and that means the foundational skills required are also more advanced. A "simple" answer is unlikely to be very helpful.

Most of us would agree that Access is a more appropriate tool than Excel, but that comes at a cost. That cost is learning how to design tables in a relational database. We'll be more than willing to assist you in doing that. First, though, comes the commitment to doing it properly.

I suggest you use the two Northwind templates to help you get started.

They are both similar enough to what you require that you should be able to learn a good deal from them about what you need to do. You might start with the Starter Edition to help get your feet under you with the tables required. The Developer Edition is much closer, though, to a truly usable application and will be a great second step.

 

[The_Doc_Man]

Jill, we would not ignore you - but basically you have poked the hornet's nest and we will be buzzing around this problem now that you have asked the question.

Your discussion of having a business object (you called it a PO) - that needs to be filled in and passed to someone else for approval, then passed along again for other disposition - is a common thread. I worked in a U.S. Navy office where EVERYTHING (in my case, mostly engineering change orders or ECOs) went through five or six layers of review, with an individual approval for each stage, never the same person for two successive stages, and more meetings than you could shake a stick at to resolve differences.

The question regarding a single database being used by multiple people is based on this problem: IF your design is that only one person at a time can use the database, that PERHAPS isn't optimum but if you follow your own rules, it isn't dangerous either. IF your design is such that more than one person can use your database at the same time, you SERIOUSLY risk a situation that we call "database corruption." A corrupted database would be quite capable of losing data after any one of several common "gotcha" events.

In essence, when you start actively sharing data with others, you need to set it up in a way to prevent what we call "destructive interference" (in more common terms, "left hand doesn't know what right hand is doing at the moment.") Splitting a database is a strategy to allow relatively safe simultaneous access to the DB tables. But only you would know if that is a requirement. We can't help you directly. We can only advise. If you believe it would be correct to do so, just ask and we can advise.

 

[LarryE]

Hi all,

Hope you had a good Christmas and Happy New Year to everyone. Sorry, but back here wanting some advice please. I originally created a PO system on excel for our company, using a Macro to issue a new PO for every entry and automatically creating a PDF for each PO. However, since being part of Business 365 it doesn't seem to work if individuals go into the form when others are in it. So, I thought, as we use Access for lots of other processes within our company it would be better to use it to create PO's. But, I cannot seem to set the form to 1. bring up all the PO's for my manager to accept and release the PO and load a new PO whenever a user creates one. I don't want it set a new form for every entry (as I already do this for other forms for data/time sheet entry) but I need it to clear all info when a new PO is raised. I've googles it but I don't see, to be able to find a solution. Please find attached a very rough PO example... Any help very much appreciated. Thank you in advance.

Here is your file back re-designed so you can at least use the tables and design forms for input. Please take note of the Relationships:

Also note how field names have been changed so they do not include spaces and special characters which ACCESS does not allow. In addition, I created Primary Key and Foreign Key relationships as appropriate. The PurchaseOrders table is what is known as a Junction table because it holds SupplierID and RequestorID Foreign Key references as well as each PurchaseOrderID PrimaryKey. The new OrderItems table holds all the individual Purchase Order items for each Purchase Order. Each Purchase Order may have multiple OrderItems.

Create a new OrderItems form with all the fields in the OrderItems table. Then make that form a sub-form of the Purchase Orders form.

This ACCESS file you created had numerous problems in table design and field name design. It was never going to work as a relational model.

Please take the time to investigate how relational databases work, how to build normalized tables and create relationships between them. What I gave you back is just a starting point, but gives you some idea how these things work.

You are welcome. No charge.

 

[Pat Hartman]

@jillnoble I tried to figure out what your problem was but I couldn't reconcile your form with what you were saying it did. I have no idea how you are printing a PDF when none of the items are bound. How is the PDF getting its data.

You really can't use the app that you posted for anything. Generally, I try to give the actual "simple" answer if there is one along with the lecture about doing things right, but this time I was at a loss so I just went with the lecture

At the moment, I'm fixing the form to work the way it should and we'll go from there. I also changed all the Column and other object names to conform to VBA standards to avoid coding annoyances. Simple VBA is simpler than Access macros. You are probably already familiar with it if you build Excel models because you have to write VBA to make your Excel models functional. Give me a little bit and I'll post something very basic.

 

[Pat Hartman]

Ok, here's a start. Now we can get to solving your problem. I couldn't tell what you were using to determine open PO's so I arbitrarily assumed they would be those with null values in RequestBy. Easy to change once we find out your rules.

I added a menu so you could see how to use the same form three different ways. There's a little code but not much. You should look at it and pay attention to the events it is in.

 

[jillnoble]

Bless you all, there is so much I can take from all of these. As ever, this Forum has been extremely helpful.