Solved Update field and Validation criteria (1 Viewer)

cheekybuddha

AWF VIP
Local time
Today, 13:16
Joined
Jul 21, 2014
Messages
2,237
I guess we need also to know what Kolona is and Uporaba Kolon.

Is it Items and Items Analysis?

You have a lot of the same fields in each table.

Status should probably be in the analysis table
 

lacampeona

Registered User.
Local time
Today, 14:16
Joined
Dec 28, 2015
Messages
392
Hi
Kolona is Items
UporabaKolon means Evidence of items usage

yes some fields must be the same

i have status ( status of the entry....if the entry is signed or approved ) and statusKolone ( status of the item) this is the problem

when you borroved the item to use the items have one status lets say ( now is on storage (1) when you make a anaylsis ( tblUporabaKolon) status is no more storage..becouse was used..now has to be Inuse (2) or something else...so that mean...when user make a analysis the item status statusKolone must be also changed in the first table tblKolone...when user make the analysis in the form ( data is tblUporabaKolon) is automaticly changed in tblUporabaKolon and becouse i want to updated also in the tblKolone i make that update statment in the field StatusKolone who updates the statusKolone also in the tblKolone

maybe i have done all that wrong?
 

cheekybuddha

AWF VIP
Local time
Today, 13:16
Joined
Jul 21, 2014
Messages
2,237
If the status of the item can be only one of the statuses you listed in Post #20 at a time, then you need only store it in the Items table.

Or can the same item be used in more than one analysis at the same time? (Seems unlikely)
 

lacampeona

Registered User.
Local time
Today, 14:16
Joined
Dec 28, 2015
Messages
392
Yes that is correct. It is important the first table tblKolone for the user- user can find the correct items very fast

the same item cant be use more than one analysis at the same time, but the same item can be used for diffened purposes in different days and with usage will have differend statuses

but I also need to have all the history for the items how is being used, let say first was on storage..then 10 days after was in use..in 30 days was sended and in 60 days was destroyed.

when user will check for the specific item all the histroy he must see all these statuses and he must know what is the current status of the item and that is the tblKolone

but when user will want to use the item he will only look the first table ( becouse he must know which items is free to use...if item is destroyed he cant used, or if the item is not relased he cant use it for normal analysis...)...
 

lacampeona

Registered User.
Local time
Today, 14:16
Joined
Dec 28, 2015
Messages
392
the problem is now when my user will click the the statuskolone in the form without saving status will automaticly changed - my update statment

he will then close the form and we will have statusKolone ( tblKolone) lets say sended in reality the item is not sended is in use (tblUporabaKolon)

and that is not correct i have to prevent that.... he cant go to my combobox and click nothing
:(
 

cheekybuddha

AWF VIP
Local time
Today, 13:16
Joined
Jul 21, 2014
Messages
2,237
but I also need to have all the history for the items how is being used, let say first was on storage..then 10 days after was in use..in 30 days was sended and in 60 days was destroyed.
Then you probably need another table to record the Item status, with the dates when the status changes

This was previously suggested to you in this thread
 

lacampeona

Registered User.
Local time
Today, 14:16
Joined
Dec 28, 2015
Messages
392
Yes i Have it
this is all in the tblUporabaKolone and I see all the changes made on statuses..who when what status all
that is all correct

the problem is like i say the first table....when user use the item the status item must also change in the first table tblKolone- becouse this table is where user look which item will choose



1650201939742.png
 

lacampeona

Registered User.
Local time
Today, 14:16
Joined
Dec 28, 2015
Messages
392
with update statment i managed to changes the status also in the the first table that is ok
but the problem is when user just click the status field change the status and he want to use that...
then i have two different statuses
in the tblUporabaKolon - I dont have entry becouse my valudaion rule didt not save the record- that is corrrect
and the tblKolone has a status that was changed just becouse the user click and is not correct
i have to prevent to my user go to that combo box and click the status...he must enter all the data and then change the status
status cant update if all the field are not entered- my valudation rule work only that this update statment is doing all me wrong :(
 

cheekybuddha

AWF VIP
Local time
Today, 13:16
Joined
Jul 21, 2014
Messages
2,237
when user use the item the status item must also change in the first table tblKolone- becouse this table is where user look which item will choose
No, you must just look up from tblUporabaKolone, using the KolonaID.

Store the status in one place only.

Does DatumUporabe include time as well as just the date? It would be good if it did, because you can use that to find the latest status.
 

lacampeona

Registered User.
Local time
Today, 14:16
Joined
Dec 28, 2015
Messages
392
Yes I have it...i have two dates ( datumVnosa...the date when the entry is made) and datumUporabe when the item was used in reality becouse users will enter data for past days also....

yes i also want to make it like you say, and i have it done but then
but the person who want to have in that way says me that user will lost too much time if he will have to use filter and choose the correct KolonaID item to see what is the latest status...
becouse of that then i make that update statment ....so the user will not have to search and make filter on KolonaID becouse he can make a errors and loose to much time..becouse all that ..i am here crazy thinking what user can click what user can make bad and i need to prevent all that...
:oops:


1650202886389.png
 

cheekybuddha

AWF VIP
Local time
Today, 13:16
Joined
Jul 21, 2014
Messages
2,237
if he will have to use filter and choose the correct KolonaID item to see what is the latest status...
You don't have to filter.

Either you build it in to your query, or use a DLookup().

Create a new query - qryItemStatus:
SQL:
SELECT
  uk.KolonaID,
  uk.StatusKolone
FROM tblUporabaKolone uk
INNER JOIN (
  SELECT
    uk1.KolonaID
    MAX(uk1.DatumVnosa) AS MaxDate
  FROM tblUporabaKolone uk1
  GROUP BY
    uk1.KolonaID
) d
        ON uk.KolonaID = d.KolonaID
       AND uk.DatumVnosa = d.MaxDate
;

Then, base your form on:
SQL:
SELECT
  k.*,
  s.StatusKolone AS CurrentStatus
FROM tblKolone2 k
LEFT JOIN qryItemStatus s
       ON k.KolonaID = s.KolonaID
;
 

lacampeona

Registered User.
Local time
Today, 14:16
Joined
Dec 28, 2015
Messages
392
ok i will try to implement that
i will report you later
thanks for all your answers
 

lacampeona

Registered User.
Local time
Today, 14:16
Joined
Dec 28, 2015
Messages
392
Ok I make your suggestion.
now i have to think if that is correct....i will make some crazy combinations to see if that is correct and if user will know what that mean
hmmmm




1650210730308.png
 

cheekybuddha

AWF VIP
Local time
Today, 13:16
Joined
Jul 21, 2014
Messages
2,237
You may have problems when DatumUporabe is different from DatumVnosa.

That's why it would be better if DatumUporabe contained time as well.
 

lacampeona

Registered User.
Local time
Today, 14:16
Joined
Dec 28, 2015
Messages
392
yes I know what you mean but users are entering data for days ago and that is why they dont know the time exatly...they only know the date

the time then can be seen in the instrument when they make the analysis
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:16
Joined
Feb 19, 2002
Messages
42,981
I have to use the update query becouse the status is important in two different tables.
As the others have said, each piece of data should be stored in one and only one place.

Also, you have design issues with your tables. I can't tell how bad the problem is without a translation and the complete database but:

1. Using ID as the default name for an autonumber is less than helpful. It may be "fashionable" but for those of us who think all columns should have meaningful names, it is most definitely NOT useful and forces someone looking at your database to actually review the relationship window to understand the relationships when just using a rational naming scheme would highlight them as you look at the tables themselves.
2. tblUporabaKolon has a named PK field but it also has an "ID" field. There is absolutely NO reason to have an autonumber in a table if you are not going to use the autonumber as the PK. And in fact, not using the autonumber as the PK caused the autonumber to loose its seed in some versions of Access. Most people, even if they have a unique single field to use as a PK, will choose to use an autonumber for reasons that are beyond discussion at this point.
3. In that same table, I see a field named KolonaID. There is also a field with that exact name in tblKolone2 but it is NOT the PK. This is a problem. Relationships are FK to PK. Therefore, KolonalID should be the PK in tblKolone2 and it is not. So, either get rid of the autonumber and make KolonalID the PK OR give the ID field a meaningful name and use that same name as the FK in tblUporabKolon.
4. My naming convention uses the "ID" suffix ONLY for fields that are autonumbers or FKs to autonumbers. Otherwise I would use Num or CD as the suffix for a number generated by a different application and referenced here.
5. I also don't see any relationships defined between the tables. Relationships are your friends. They are there to enforce Referential Integrity and prevent certain types of data anomalies but only if you bother with them. They are not something you add later. They are something you start with as soon as you finish the second table. That way you never accumulate bad data that needs to get cleaned out.
 

lacampeona

Registered User.
Local time
Today, 14:16
Joined
Dec 28, 2015
Messages
392
Hello Pat,

thank you very much for your further explanation. I will make relationship.
Yes I agree when I will resolve all my problems I will asy you guys here to look at my relationship and I will explain what mean all that.

In the table tblKolone I did not put the KolonaID as PK key becouse is special number is not autonumber. Becouse of that I use the ID as autonumber.

I have code that generates that special number K2022-0001, K2022-0002... You mean that Access will know how to make with that code the next autonumber?

Will Access know to create K2022-0003 and so on without code If I say that is autonumber? and then K2022-0004? I want to say that mean K2022 is this year,,,and next year the numbers must start with K2023?

I always thought that autonumber mean that acc ess will done automaticly numbers and becouse I have that specaial number KolonaID who is generate by vba code.

The ID from tblKolone2 is autonumber and the ID in the second table tblUporabaKolon is number.
I was not sure if Access can make relationshio with my special number KolonaID becouse of that I use just a number.
 

Users who are viewing this thread

Top Bottom