Solved Correct way to update data

[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?

 

[The_Doc_Man]

I'm going to make a generic statement here. When you say you update something and then later see that it appears to have not been updated...

Frequently this comes from a type of normalization violation in which you have duplicate secondary characteristics in two tables. You update one, but the other copy of the characteristic is not updated. Think back to our earlier discussion of "Status" where you wanted both the Parent table and the Child table to retain the status, but we convinced you that status only belongs in one place as a matter of normalization. And your problem was that if you changed the status in one place, it didn't automatically update in the other place.

What you are describing now sounds extremely similar to the previous "Status" problem, in which you change something someplace but not in another place, so you get conflicting or out-of-date statuses. One of the principles of normalization is that you have a definite place where some characteristic is stored. We sometimes call that the "definitive authority" for the particular item. In our previous discussions about status, you came around to realize that the transaction table was the definitive authority for Status. To see that status, you had to always query it from the definitive source and anything else that needed that status should have ALSO queried that source rather than having a separate and distinct copy.

The idea here is "isolation of purpose." There should only be one place to look for a sample status AND its location while it is in that status, including department and any other dynamically changing locator information.

This problem with "change of department" sounds similar to the earlier problems. Maybe it isn't, but the symptoms you described sound extremely similar. For what it is worth, if the problem really IS simply incomplete normalization, you aren't alone in making that mistake. It happens a lot, particularly when the project is very complex and has lots of "moving parts" (or data interactions, if you prefer).

 

[IbrBadri]

@The_Doc_Man
Hey, I just want to thanks you for all the time you spend on this threads and the efforts to help.

 

[The_Doc_Man]

@The_Doc_Man
Hey, I just want to thanks you for all the time you spend on this threads and the efforts to help.

No problem, glad to help when I can. It keeps me from roaming the streets and getting into trouble.