Check ItemID and status value (1 Viewer)

[lacampeona]

Hello Experts

I need some ideas how to make something.

I have a tableA which constains field status and I have field ID and ItemID.
Field status can have three values. Field status can be empty (field contain no data, empty field), entered or approved.

Now my problem is how to check that the field status for specific ItemID contains entered or approved?

Example
I have to tell to the user if the all statuses for ItemID (example:A001, it could be any other ItemID) contains status approved then he can destroy the itemID A001.
( in my case ID is only: ID=4)

If the itemID A001 does not contain all the statuses approved (ID=, ID= 1, ID= 3),he must check his data. the status for user to be able to destroy the item must be approved.

I was trying to use dLookup but I didnt get what i what. I manage to get results for one field. hmmm?

Imagine...we have :

tableA

ID status ItemID

ID=1 A001
ID=2 entered A002
ID=3 entered A001
ID=4 approved A001
ID=5 approved A002
ID=6 A003
ID=7 A003

Thanks is advance

 

[Uncle Gizmo]

Do you mean you want all of the Items deleted if any one of them is "approved"? Let's say you run a query against the data and the query discovers that ID=4 is Approved. ID 4 contains Item ID "A001" So you want to delete all of the records which contain Item ID "A001"?

 

[lacampeona]

Hello
I need to check which ID contains approwed...and then only delete itemID A001. that mean that user cant use no more itemID A001 and he cant create new ID records. with delete or destroy i mean just that the user cant use it anymore..i will make it disabled.

 

[arnelgp]

you can use:

DLookup("status", "tableA", "ID = " & Val(DMax("ID", "tableA", "itemID = 'A001'") & ""))

 

[lacampeona]

Where is best to put it?
i was thinking to have that in button called ( destroy) ?
or i have to make some text box on the form to check that and then use that text box in the button?
hmmm
thank you

 

[lacampeona]

Hello Arnelgp
i tried your solution but i am getting error?
hmmm

 

[The_Doc_Man]

If you completely delete the record, you run into the chance that someone can later come back and create a new version of it. You have to retain a record of its prior - and now blocked - existence. Access needs a way to remember that the particular ID cannot be re-used.

You need to look at what you told us in post #3, which at least seems to contradict what you said in post #1. It MAY be that I'm merely confused so don't take this as a stiff criticism. More like me scratching my head a bit.

 

[lacampeona]

Hello The_Doc_Man,

yes I make mistake telling what I want. I do not want to delete any records.

I confuse word delete and destroyed. With destroyed I mean that this itemID will not be in use anymore. This mean that if the item will be destroyed ( I will make msgbox that user can not use it anymore) the user cant create new ID for that item anymore.

I use destroyed becouse in my plan is that the item will not be available anymore ( and status of the item will be destroyed) if all the ID for that ItemID will be approved.
Once all ID for that ItemID will be approved the user will have option to destroy item. This mean if while item is being in use and suddenly item falls on the floor then this item is not anymore in condition in use and if user want to destroyed he can do that only if all previous records for that item are approved.

I am so sorry for make that mess with delete and destroy.

I will apreciate any suggestions or examples . Yesterday I try to use the Arnelgp suggestion but I didnt make nothing. Maybe I am doing something wrong.

Thanks in advance