Solved Update field and Validation criteria

[cheekybuddha]

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]

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]

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]

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]

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]

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]

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

 

[lacampeona]

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]

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]

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...

 

[cheekybuddha]

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:

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:

SELECT
  k.*,
  s.StatusKolone AS CurrentStatus
FROM tblKolone2 k
LEFT JOIN qryItemStatus s
       ON k.KolonaID = s.KolonaID
;

 

[lacampeona]

ok i will try to implement that
i will report you later
thanks for all your answers

 

[cheekybuddha]

Is your language Bosnian?

 

[lacampeona]

no, Is Slovenian.

 

[lacampeona]

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

 

[cheekybuddha]

You may have problems when DatumUporabe is different from DatumVnosa.

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

 

[lacampeona]

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

 

[cheekybuddha]

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

Ok, so it's ok to use DatumVnosa then

 

[lacampeona]

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.

 

[lacampeona]

Hi,
thank you very much for explanation and sample database.
I will check all my fields and make correct relationship and logic PK and FK keys.

I also must say that on this forum I find a lot of good sample databases ,good advices and examples of codes. All experts that are answering questions, resolve problems deserve special thanks. This is realy nice. You are sharing your knowledge with others. And you are doing all that for free.

If I want to watch movies on Netflix I have to pay and here If I need help I dont have to pay nothing. That is very important. Becouse sometimes you have people who have money and they can buy databases but they dont know how is working and they are not interested and sometimes you have normal people who wants to learn and dont have so much money. In another web pages I find examples and you have to pay 10, 20, 30 or more $ just for one database to just see what the database is doing, and here you can find, see the code and study how to implement that later in your database. This is realy very nice and good idea for begginers and others to learn.

When I was young one day I click by mistake MS Access. Ms access opens and I didn know what to do? In word I knew that I have to write letters or something..In access I just check the menus,...and then close the program. I was like surprised what is that, what you have to do in that program who is using that and why?

If someone would tell me that me in the future will know to write codes and making butttons or that I will be capable to make a MS program I would just say ahhh that is impossible. Becouse I dont have so much knowledge to write all the codes by myself. And here with all your help everythink is possible.

Without all that help that you guys offer here to us bad/medium begginers and others ( we send question and in minutes we already have answers!) our databases could not progress.

Thank you very much for all your time, patience and examples.