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

 

[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

 

[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

 

[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

 

[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

 

[lacampeona]

Hi Pat
I apoligize if i didnt say nothing before but yes i liked your presntation in your videos.
You are exlaning very clearly so is very nice to follow.
Thank you
PS: Do you have plans fore more videos like this in future?

 

[lacampeona]

Hi
Yes ,I read some time ago about algoritms and SHA hash functions.
It is interesting to me to know How all that started how access know how to encyprt all that..how the person who write all that know to do it?
I know that some data can be encrypted in access..like the table with passwords...so nobody can then reverse back the password..

I am intereting in these topic but maybe can this can be very difficult to understand becouse I am still solo learning begginer....I dont know.

for me would be a chalenge that somebody explain me how is all this working and then with some example to learn how is that working...

Thank you

 

[lacampeona]

Yes I agree
Of course I dont want to learn breaking into passwords. I am sure that this is very complicated.

My goal is to learn to write my own functions and codes.

I just mencioned sha and password becouse some people are saying that access is not so protected database and that is easy to enter and break it.

 

[lacampeona]

Yes I understand.
I like very much Access and becouse of that I start to learn.
If I will manage to complete my database I think my department at work will help me to connect the database to sql server? I hope they will know what are they doing?
In my work already i have another program which I use for making some measurments and this program use oracle and oracle is then managed from India people.

 

[lacampeona]

Hi Pat
Thanks for your explanation.

But what you mean with that? That my database will be very slow?

If your app uses old fashioned "Access" techniques that you see recommended here all the time, you will not be happy with the result once you upsize.

Thank you