Solved Correct way to update data

[The_Doc_Man]

You have the option (with Form_Unload AND/OR with Form_BeforeUpdate) to set CANCEL=TRUE to prevent an update from occurring, either by a SAVE button or navigation away from a record (which does an automatic update) or a form closure (which does an automatic update). At the point where you do a CANCEL=TRUE, the update or unload event will not proceed. The form remains open as though the user action that triggered the event never happened. At that time, you have a chance to do something and a choice of what to do.

You CAN just say "continue to fill in blanks to finish the operation" and then when your user tries to save or close the form, eventually the validation code could say "OK, this is acceptable" - at which moment you would do all of the primary and secondary update actions discussed earlier like updating the details and updating the main-table status.

OR you can say "If you are going to close the form in an incomplete state then I will not allow this to be saved." At which time you would do the UNDO and then DON'T set CANCEL for the Form_Unload. If the triggering event was the Before_Update but an UNLOAD wasn't pending, then it might be more of a puzzle as to what to do.

When I run into this problem, what I do is make it impossible to close the form using any of the standard Access methods. What I do is build a CLOSE button that sets a software flag. Then in the Form_Unload, if the flag isn't set, you can't close the form and none of the side effects of closure can occur. The CLOSE button also checks for whether the form is currently dirty and can issue a message warning the user of unsaved data.

I also use a SAVE button and flag so that the Before_Update can know that update event this wasn't due to an implied navigational save. AND you can put validation code under the SAVE button rather than the Before_Update event, so you don't even TRIGGER the save until it passes validation. In essence, this is the philosophy of "get in their way when they make a mistake so they have the chance to fix their mistake." The final piece of that puzzle is, if I am going to make them manually save the record, allow them the option to undo what they have done by adding an UNDO button. That does a Me.Undo on everything so that there is nothing to be saved - which prevents an Access automatic save from occurring.

 

[lacampeona]

hmmmm I will get lost?

I have a save button, close button
I have to use cancel= true or i have to use Me.undo? and where is better to put? unload or before update? I know you say I have two options but where is good to use it? where experts you use the code?
Ohh I am so sorry if I am stupid I am just in mess now

Can you show me some example how I have to do that?

 

[The_Doc_Man]

You have two options and can choose either. It wasn't intended that you would handle both options, but rather that you could pick the way you wanted to handle it. But I can't answer your question because this is your project. You have the choice of blocking the closure of the form or of just discarding incomplete records. But until you decide how you want to treat this in practical terms, the code will make no difference.

From this question, I think it is time for me to unload some "Old Programmer" proverbs.

1. If you can't do it on paper, you can't do it in Access.

In any project, you have to have goals. Usually, people lay out their goals in a design document or specification. You are building a program to track the actions of your business. In order to do that, you must intimately understand your business flow. You must know the data elements (entities) of your business, whether we are talking employees, clients, sellers, buildings, equipment, sales, purchases... WHATEVER is part of your business. You must know the intimate details of how those things are used. Many of your tables will represent those things. Whatever is done in your business, you must know how it flows.

How you lay out your goals is up to you. But the general approach is to identify the entities you will track whether concrete or abstract. You identify how these entities interact. You lay out your desired outputs. In essence, you must build a roadmap of how you get from inputs to outputs by all of the processes in between. You answer the questions of "how will I do that step?" by looking at the step in the real world and deciding how to perform that step (or record it) in the computer. When you are done, you have a document that should answer all of your "what happens to this entity at this phase of my business?" At that point you have a roadmap. And if you are going somewhere without a map, how in the world will you ever know you have arrived?

2. Access won't tell you anything you didn't tell it first, or at least tell it how to find it for you.

Let's start by understanding that Access is dumber than a box of rocks with regard to the real world. All it knows is data manipulation and making pretty forms and reports. YOU are the subject matter expert. Access cannot learn anything - but you CAN write code to show it how to do some step or another. You have outputs. Access can only tell you those outputs if you make the required inputs available. Sometimes, it becomes necessary to look at an output and work backwards through that map to verify that you will have the inputs you need to produce that output. That is, if you want X, Y, and Z as outputs, you need inputs for X, Y, and Z. AND if you want XYZ as output, you need X, Y, and Z and you ALSO need the formula that mixes them together. Consider this a type of "procedure checking" perhaps. Just remember, you are the one who must make stuff available for Access. It is an ORGANIZING and COMPUTING tool. Not a manufacturing tool.

3. Access can never tell you how to do your business. Only YOU know how to do your business.

People want Access to help them run their business. Yes, it will do that... if you tell it how you run your business. Access will not make a decision for you that you wouldn't have made. This is an important concept.

In English, we have a phrase that we use: The tail is wagging the dog. The Access equivalent would be if your application told you how to run your business in a way that was contrary to the intent of the business, but you let an inanimate object guide your business. If you build an application for your business but it is an imperfect representation, you could find yourself in real trouble if you followed it down your garden path. Remember, except for true AI cases, computers don't come up with new things. Their strength isn't based on creativity, but rather that they can do things QUICKLY and ACCURATELY. But give them a bad formula and they can still be wrong.

When I worked with the U.S. Navy, the biggest project (for which I was a systems administrator for the host system) was a personnel management system. They modeled the heck out of personnel transactions such as addition, separation, transfer, promotion, demotion, training, etc. We gave the bosses at BUPERS all the details they needed to make decisions. We had models for every decision they could possibly make. But THEY made the decisions based on what our DECISION SUPPORT system showed them. They ran the show. We just tracked their choices and helped them push the appropriate orders to the appropriate channels. Our machine never told them what to do, though it sometimes told them what they couldn't do (if, for example, no one was eligible for a particular assignment with requirements). The point is, whatever you design has to support how your business is supposed to run. Back to the map analogy... just remember that the app your are building is the map; the actual business is the territory. Be sure they match up.

 

[gemma-the-husky]

The status belongs in one place only, and that's part of your data analysis

if you have an order, and each order has several lines, then the status probably belongs in the order, but it depends how you deliver and manage the order.

So you might have statuses for 1, New Order, 4, Final Order, 5, Despatched Order, 6, Completed Order, 7, Invoiced, 9, Cancelled
However if an order comprises multiple deliveries, the status (or statuses) management will be different. The status of the order will not be complete until all the deliveries have been made, but each delivery will have its own status independent of the order. You still need to aim for one status per process, and don't duplicate volatile data.

I would also have a numeric status, not a text. If you ever want to change the text, it's much easier with a numeric value. The numbers will generally update in sequence, as illustrated above, but not necessarily, depending on your exact process. Maybe number the statuses 10, 20, 30 etc, if you think you might even need to add more statuses in between these values.

 

[lacampeona]

Hi
yes I agree that status belong to one place. And that place should be the second table. tblUsage. tblB. ?

I will have only 5 statuses. That is now finally. The statuses are only infotmation to the users if he can use that item for very important anaylsis or not...

But how to tell to the user that item is new?

He enter the data-new items in another form to tblA?

My query makes that data from tblA dissapers when you open and close the second form.

and I dont know how to resolve that...
Another query? where to put? Undo event, unload event?
I need some combination of querys and to pur it on these events but I dont know the corrrect way...

 

[gemma-the-husky]

Hi
yes I agree that status belong to one place. And that place should be the second table. tblUsage. tblB. ?

I will have only 5 statuses. That is now finally. The statuses are only infotmation to the users if he can use that item for very important anaylsis or not...

But how to tell to the user that item is new?

He enter the data-new items in another form to tblA?

My query makes that data from tblA dissapers when you open and close the second form.

and I dont know how to resolve that...
Another query? where to put? Undo event, unload event?
I need some combination of querys and to pur it on these events but I dont know the corrrect way...

If the user puts the new items in tblA, then why would the status belong in tblB? Anyway, you can easily store it in tblA when you save the record. If you still want to store it in tblB then at this point (form AfterUpdate event) you could use code to update a status value in tblB by running a SQL update

"update tblB set status = " & newvalue & " where recordid = " & tblB_RecordID

if you using a string for newvalue then it's this syntax. I use chr(34) to avoid having to mess around with """ characters.

"update tblB set status = " & chr(34) newvalue & chr(34) & " where recordid = " & tblB_RecordID

but that comes back to the underlying question of why the status for tblA belongs in tblB.

 

[lacampeona]

Hi

but that comes back to the underlying question of why the status for tblA belongs in tblB.

Becouse users then use that new item from tbl A and make some analysis and analysis is stored in tblB.

Status item is changing all the time...first is new ( new enter in database) then user make analysis ( all that is tblB) status is INuse..then he send the item to another apartment ..status is now sending...then he can relase the item...status is relased and after 200 anylsis or more..he destroy the item..status is destroyed...- this is all tblB

My problem was that when I open and close my second form my tlbB status from tblA dissapear.. i need to show the user that status is new..when he will use the item status will be then in the INuse not anymore new....and so on...

I have a query on unload event in form B that is updating tblA and chaning statuses during all this analysis.... and that is correct only when item is new and if you open and close the item status dissapear...then is working again correctly...

 

[lacampeona]

HI
I try your suggestion
is not ok. I still get status dissaperar when I open and close the formB....
in the begining was ok New and then again dissapear....

 

[GPGeorge]

We can't see your data, so we have no way to know "what suddenly went wrong". That said, I am firmly convinced that in a significant percentage of situations where something that "worked" suddenly "stops working", the cause is a change in the data being processed. It could be a missing value for a required field; it could be a null in a non-nullable field; it could be the wrong datatype in a field, etc. It could be a conflict in Primary or Foreign Keys. But most of the time, it is a problem with new data.

Validate your data when this query goes wrong.

 

[lacampeona]

Hi
How i do that??
What exatlly i have to do?what you mean?
Validate your data when this query goes wrong?

 

[GPGeorge]

Hi
How i do that??
What exatlly i have to do?what you mean?
Validate your data when this query goes wrong?

Start by identifying the actual error that occurs, not "something went wrong". Did the error message tell you you can't insert duplicate values in a field, for example? Did the error message tell you that one of the records has the wrong datatype? Or was there some other, specific error? What is that specific error message?

"Validate your data" means to review the records to be returned by the update query and verify that all of the fields in all of the records are appropriate. If you have to, review each record one at a time until you see one or more that don't meet the requirements of the destination table.

 

[CarlettoFed]

Attach a sample file.

 

[GPGeorge]

No, that's not relevant. The CONTROLS on the form are locked, not the underlying fields.

Please answer my previous questions in post #53.

 

[lacampeona]

Hi GPGeorge

Start by identifying the actual error that occurs, not "something went wrong". Did the error message tell you you can't insert duplicate values in a field, for example? Did the error message tell you that one of the records has the wrong datatype? Or was there some other, specific error? What is that specific error message?

Well I dont get no message nothing. I just refresh the form and I notice that specific fields are not updated corretcly.
I will attach my sample database and explain the correct procedure of what that form do.

 

[GPGeorge]

"...I notice that specific fields are not updated corretcly."

Let's start there, then.

A) What would the "correct" values be?
B) What actually displays instead?
C) How are they different?
D) How do you decide one is correct and the other is not?

Again, the more detail you provide, the better chance someone has of helping.

I admit to being a little bit displeased. We asked repeatedly for details. You ignored the requests because you don't think them relevant, but didn't bother to tell us why you think that.

As a reminder, we can't see your application, so you are on the hook for communicating about it here.

When you do attach an accdb and provide a description, please include precisely the answers to the questions above. Don't expect us to guess.

 

[lacampeona]

Ok
I am using first form called : frmVnosKolone ( in this form users enters new item)
then second form is frmKoloneUporabaSUB ( this is the form where users enter data for the item usage)
all the new entered data can be seen in the third form called frmVnosKolonePregled

Fields that are important for me to be updated in the my update query are : StatusKolone, SistemKolone, OddelekLastnik. - for that i am using update query called qryStatusKolone.

for testing items you can use any item from form called frmVnosKolonePregled.

i start thinking if maybe that all my problems starts if users dont enter all the data in the frmKoloneUporabaSUB ( i have here validation rule?) and becouse that query dont want to update data correctly anymore? ohhhh

 

[GPGeorge]

One last attempt at clarification, and then I'm out.

What does it mean to say, "...the query dont want to update data correctly anymore?"

What would correct data look like?
What actually does happen instead?

 

[lacampeona]

I am so sorry if in my prevoius posts I didnt the correct answers. I hope I explained better.
Georgee you are so fast.. i am here writing the explanation

lets take the field OddelekLastnik ( owner of the item, some department)

Let's start there, then.

A) What would the "correct" values be?
if i change the name of the apartment from QC_SMART to QC_PORT and then in the form I see the QC_SMART again i know that query is not working anymore.

B) What actually displays instead?
it displays the last value entered in that field

C) How are they different?
it remain the same value. if i enter QC_PORT then i see the last value that was QC_SMART

D) How do you decide one is correct and the other is not?
if i change from QC_SMART to QC_PORT I expect that my value must be QC_SMART.

 

[GPGeorge]

I am so sorry if in my prevoius posts I didnt the correct answers. I hope I explained better.
Georgee you are so fast.. i am here writing the explanation

lets take the field OddelekLastnik ( owner of the item, some department)

Let's start there, then.

A) What would the "correct" values be?
if i change the name of the apartment from QC_SMART to QC_PORT and then in the form I see the QC_SMART again i know that query is not working anymore.

B) What actually displays instead?
it displays the last value entered in that field

C) How are they different?
it remain the same value. if i enter QC_PORT then i see the last value that was QC_SMART

D) How do you decide one is correct and the other is not?
if i change from QC_SMART to QC_PORT I expect that my value must be QC_SMART.

That's what we need. Thanks.

It would help to know whether the change does occur in the underlying table or not. Forms don't automatically refresh to show changes in the data in the table. If you close and reopen the form, do you see the new values?

 

[GPGeorge]

"yes if you close and open the form called frmVnosKolonePregled ( you choose from the left underlined KID_ you also must see the changes.."

I tan think part of the problem is language related. You say, "must also see the changes", but do you, or do you not, actually see the changes?