Solved How to find specific value?

[lacampeona]

Hello experts, I need some help with extracting some value from a table. I was thinking to use Dlookup function in my form procedure.
But then I get stuck.

I have tableA who stores general data about items.
Then I have tableB who stores data about usage for that items.
The table A is mother and tableB are the children. They are connected with ItemID-primary key.

This mean tblA have
ItemID
1
2
3

tbl B have:
UsageID ItemID status
1 1
2 1 entered
3 2 approved
4 1 aprroved
5 3 entered


I need to check if some field contains specific value.
If the status field in tblB is "empty"( UsageID 1) I need to tell to the user he can not use this item becouse is not approved.
if the status field in tblB "entered "(UsageID 2,5) I need to tell to the user he can not use this item becouse is not approved.
if the status field is tblB "approved" (UsageID 3, 4) then user can use specific item.
From this case user can only use item 3 and 4, but I get stuck...

My problem is that what I make now...acess check entire table and when he finds the first item who is empty or entered he tell to the user he can not use that item.
Becouse User want to use item 3 who is approved and he can not used it becouse item 1 is empty and item 2 is entered.
How to create that correctly? that dloolukup can search the values only in the children table with specific Usage ID and not to entire table?

What I have for now is not what I want: I want to use item 3 and I cant becouse usageID1 and UsageID 2 are not in approved status.

I have this in my form event on click on- combo box named NamenUporabe.

Case Me.StanjeSproscanja & "" = "(3) Sproscena" And Me.NamenUporabe = "RednaAnaliza"

If DLookup("[UporabaKID]", "tblUporabaKolon", "IsNull([status]) and [SproscanjeUstrezno]='DA'") Then
MsgBox "You can not use that item.Status is not approved."
DoCmd.Close acForm, "frmKoloneUporabaSUB"
ElseIf DLookup("[UporabaKID]", "tblUporabaKolon", "[status] = 'entered' and [SproscanjeUstrezno]='DA'") Then
MsgBox "You can not use that item.Status is not approved."
DoCmd.Close acForm, "frmKoloneUporabaSUB"
End If
End Select
End Sub

What I can do? thank you very much.

 

[MajP]

I like to make helper functions

Public Function isApproved(ItemID as long) as boolean
  isApproved = dcount("*",tblB, "Status = 'Approved' AND itemID = " & itemID) > 0
end function
Public Function isEntered(ItemID as long) as boolean
  isEntered = dcount("*",tblB, "Status = 'Entered' AND itemID = " & itemID) > 0
end function
Public Function isEmptyStatus(ItemID as long) as boolean
  isEmpty = dcount("*",tblB, "Status is Null AND itemID = " & itemID) > 0
end function

Now this is a lot easier to read in code

if IsApproved(me.someItemID) then ....

also your logic seems to be reveresed. Check first if it is approved. If it is not in the approve check it is clearly not approved. No need to check empty or entered.

 

[lacampeona]

Hello MayP
Yes I know what you mean about the no need to check empty or entered. I need to do that becouse i need to prevent that user go further and used it.
He can use only if is approved.

I am trying your solution now and I get little error: variable not defined ...access marked me in yellow the tblB?
hmmm

I need to put this public funcion in module?
what about If I have 3 tables more to check? I create 3 new public funcions only I change the name of the table?

thank you

 

[MajP]

Yes I know what you mean about the no need to check empty or entered. I need to do that becouse i need to prevent that user go further and used it.
He can use only if is approved.

Does not make sense. If I simply check if they are "Approved" I know the answer. They are either approved or not. I do not need to check if "Empty" or "Entered".
then only reason to do those checks if you had a different message

If isApproved(me.ItemID) then
  Do Something
elseif IsEntered(me.itemID) then
  msgbox "Your Item is Entered, but not yet approved"
elseif isEmptyStatus(me.itemID) then
  msgbox "Your item is in a not started status"
else
  msgbox "Your item is not in the Useage table at all
end if

But according to you, there is only one message so simply

if not isApproved(me.itemID) then
  msgbox "Your item is not approved"
else
  do something since approved
end if

 

[MajP]

need to put this public funcion in module?
what about If I have 3 tables more to check? I create 3 new public funcions only I change the name of the table?

They should be public functions in a standard module so you can use them anywhere. You do not have to make these functions, but as you can see they simplify the logic, are easy to read, and reusable throughout the db.

If you can share your database, or even just the tables we can help easier.

My fault on tblB. I forgot to put a literal name in "quotes". Would have to be "tblB" since it is not a variable but a string.

 

[lacampeona]

Hi
yes you are right, I wanted to tell to the user what is wrong if he can not go to new record. Becouse that I use entered and empty.

Now i get some errors in module :

I insert that in module: Is that correct?

my code now in the form:

Case Me.StanjeSproscanja & "" = "(3) Sproscena" And Me.NamenUporabe = "RednaAnaliza"

Me.StatusKolone = "(3) Sproscena"
Me.SproscanjeUstrezno = "DA"
Me.StatusKolone.Enabled = False
Me.SproscanjeUstrezno.Enabled = False

'criteria3 = "[SproscanjeUstrezno] = 'DA' AND [UporabaKID] = " & Me.UporabaKID
'criteria4 = "IsNull([status]) And (UporabaKID = " & UporabaKID & ")"
'criteria5 = "[status] = 'entered' AND [UporabaKID] = " & Me.UporabaKID


If isApproved(Me.UporabaKID) Then
MsgBox "AAAA"
ElseIf isEntered(Me.UporabaKID) Then
MsgBox "Your Item is Entered, but not yet approved"
ElseIf isEmptyStatus(Me.UporabaKID) Then
MsgBox "Your item is in a not started status"
Else
MsgBox "Your item is not in the Useage table at all"
End If
End Select
End Sub

 

[MajP]

When you make a function the name of the function needs to match the assignment. So another Typo on my end

Public SomeFunctionName () as Datatype
SomeFunctionName = some value
end Function

Should be
IsEmptyStatus = ....

 

[MajP]

One way to simplify even further is make some helper queries with only the fields you need. This is especially helpful when you have a complex critiera.

make a query that only returns the records that meet the criteria
qryIsEntered

select UporabaKID from yourTable where status = 'Entered'

Now it is even easier to make your helper functions. Just have to check for existence.

  Public Function isEntered(KID as long) as boolean
      isEntered = (dcount("*","qryIsEntered","Uporabaid = " & KID) > 0)
end function

 

[lacampeona]

hi
yess also my mistake..I didnt notice that before...i am blind all day with my dlookup code who isnt working..ohh
thank you very much.
I will try to implement your suggestions.

 

[lacampeona]

Hi
I think I finally managed it. I will do a lot of testing to see if what I am planing/doing is correct or not.
Yes you are right, with that I resolve 2 problems,..no need to extra messages if is entered or is empty....ohhh I complicated it all.
Thank you very much.

Now this is a lot easier to read in code

if IsApproved(me.someItemID) then ....

also your logic seems to be reveresed. Check first if it is approved. If it is not in the approve check it is clearly not approved. No need to check empty or entered.

if not isApproved(me.itemID) then
msgbox "Your item is not approved"
else
do something since approved
end if