Solved Unload event and update status

[lacampeona]

Hello experts,

I have two tables, tblA and tblB.
In tbl A are all the items, tblB is storing the daily usage of the items. Both tables has field status and this status field must be the same in both tables.

I am using this line of code to perform status update from tblB to the tbl A.
I am using Unload event from my formB.

currentdb.execute "UPDATE tblA SET tblA.Status= '" & Me!Status & "' WHERE tblA.ID = " & Me.ID

On Unload event of the form, I synchronize the "2nd" table with the first?

This is working perfectly but what I notice is that sometimes the status is not always from the last record. Sometimes the status is from some record earlier.
What I can do?
Is my event that I am using correct? How to make correct that my update is always from the last record?On which event is the best solution to use the code?
I am sure that when I save the record and close the form the status is updared correctly but then suddenly after some time the status is from some record earlier.
hmmmm
Thank you

 

[Minty]

You shouldn't keep the status in two places, if you can reliably get it from one place, as it can lead to precisely the issues you are seeing.

A query with a select of the max(dateInTableB) joined back to the ID would always give you the current status for all the records?

 

[lacampeona]

Yes I know that I shouldn't keep the status in two places ( a lot of experts told me that here) but how then to show the correct status to the user in form A?( from tblA)?

Imagine I have form A ( from tblA) this table contains all the items that are entered. Here the status is New, becouse user enters new items in the table.
Lets say that user now use the Item1 from tblA who is New, he make some analysis and now the status must be In Use. Now this new status which is now In Use must be shown in my form A. So the user can see and say Ok Item1 is In use.... I will choose another item...
Now I am using that Unload event to update the status which is 98% correct..only sometimes and I dont know why I get the status from some record earlier..and that can then confuse my user...


So you think is better that i put on my formA some new field and i make inside Dlookup from the tblB for the status?

 

[Minty]

There's a reason you were advised to calculate the status.

Your query would return all the statuses even for new products, so a simple join will allow you to display it on a form for tblA, either as text or in a combo. Don't use a Dlookup, they don't scale well.

If you post up the structure for both tables I'm sure we can write the queries required.

 

[lacampeona]

I also try it to use query like you say. I put it on the Unload event

'CurrentDb.Execute "Update tblKapilare Set StatusKapilare = DLookup('LastOfStatus','qryStatusKomercKapilareZStanje001','CID = ' & [CID])"

is like you say max(dateInTableB)

SELECT tblUporabaKapilar.CID, Last(tblUporabaKapilar.StatusKapilare) AS LastOfStatus
FROM tblUporabaKapilar
GROUP BY tblUporabaKapilar.CID;

but then I have a problem...if user go to the form B open a close the formB then I have a empty field in tblA, becouse the user didnt make no new entry he didnt use the item...?

then I have on the tableA some record that has status New and some items that his status is empty field?

 

[lacampeona]

 

[Minty]

Not if you write the query correctly - if there is no record in tblB you simply need to return the default status - "New"?

Also, don't use Last() it will give you strange results. Always use Max() if you have a suitable field.

 

[lacampeona]

Ok yes you are right I will change that and yes I need to have back status New..but how I can write that? can you show me some example?

Is correct to use my query on Unload event? Should I use another event?
thank you

 

[Pat Hartman]

I'm not going to chime in about why storing the status in multiple places is wrong. You've been told enough. But I will tell you why your code does not update tblB with the correct value.

The unload event runs when the form unloads. Who knows how many records a user moved to after he updated the status on one record. If you want to do this, and I strongly recommend that you rethink the whole thing, then the CORRECT event to do the update from is the AfterUpdate event of the form. This event only runs if the record is changed and it runs AFTER the record has been saved. If the record hasn't changed, there is no point in updating a different table. There is nothing that changed.

 

[Minty]

Ok yes you are right I will change that and yes I need to have back status New..but how I can write that? can you show me some example?

Is correct to use my query on Unload event? Should I use another event?
thank you

I'm suggesting you don't use the query to update the record at all.
Just use it to provide an always accurate calculated status for all the parts.

 

[lacampeona]

Hi Pat, Hi Minty
yes I will take note of all your suggestions.
I will try to use After Update and test it and then I will think how to convince my users that they will have to look for the status from another form that I will create and explain then that status has to be in one table and that must be from tblB.
thank you very much.

 

[Minty]

You can display the value in your other form, use a combo linked to the part number in your main form with the status query as a rowsource.
NO NEED for any after-update event!

Your query will always return the current status, not sure how many other ways I can explain it.

 

[lacampeona]

Hi
yes I will try to make that with my query.
thanks again

 

[Pat Hartman]

then I will think how to convince my users that they will have to look for the status from another form

Relational databases are very different from spreadsheets. You store your data ONCE and then you reference it from whenever else you need to see it.

I only told you why your present method wasn't working so you would learn a little about what form events are used for. They are NOT arbitrary. Every event is triggered by a single, specific action and the event exists with a task in mind that you might want to handle at the point in time when the event fires. It is very important that you understand the form's event model. If you don't understand it, you can never get your code into the correct event to be effective.

 

[lacampeona]

Hi Pat,
thank you very much for your explanation.
Where I can learn more about form's event model?
I will start looking on internet but if you have something more detail explained I would apreciate if you can share with us, the beginers in ms access.
Thank you very much

 

[Pat Hartman]

The MS documentation is poor at best but it does exist. You can start there. I created two videos AND a database you can play with that displays events as they happen. You don't have to create an account to watch the videos.

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

 

[lacampeona]

Hi Pat
I just downloaded your database.
I will play to see and learn how is working.
Thank you very much for your excelent work you make and for all you are sharing with us beginers.
PS: Remember Michael Jordan?
thank you

 

[Pat Hartman]

PS: Remember Michael Jordan?

The basketball player? Yes.
You're welcome.

Look at the videos before playing with the database.

 

[lacampeona]

Yes I just watched the videos, first video= 29 min, 2 video= 11 min.
I mean you are the Michael Jordan in MS Access world. From the early start of the program until now already 30 years later.
Now I will start to play with the database.
Thank you

 

[Pat Hartman]

Glad you liked them