macro - IF [query] = "1234" then ...

[TriFith]

Hello everyone,
I found a problem in one of my databases, which seems to be very simple, but I didn´t find how to run it.
I would like to ask you for help with my problem.

I need to do very special macro, which should run a querry and decide what to da regarding the result.

I have a table called "tblList" and one of the columns is logical type called "ActuallStatus".
I also have a querry "qryActualStatus":

SELECT tblList.ActuallStatus FROM List WHERE (((tblList.ID)=[Enter ID:]));

This querry has an actuall status of row with entered ID as a result. So there are only two possible results... TRUE/FALSE

Now I need to write a macro which should choose between submacros regarding the result of querryActuallStatus. I tried several ways how to da that, but I failed everytime. For example here is on of my attempts:

IF [qryActuallStatus]![ActuallStatus] = 0 then
   ...submacro1
ELSE
   ...submacro2
END IF

All of my attempts stuck on the same step. Access always throws an error call, where it says its not able to find object qryActuallStatus.

Can you please advice me how to make this macro?


Thank you very much

[pbaldy]

You can't refer to the query value that way. Simplest is probably a DLookup().

[TriFith]

Thank you advice!
It´s working great with DLookup function.

[pbaldy]

Happy to help, and welcome to the site by the way!

[TriFith]

Thank you for welcome!
Unfortunatelly I stucked on other problem
I am really not to skilled with access programming, I prefer php ot C# programming. So, more than likely I am asking like like noob to very simple questions...
Mu current problem is a login to form...
I have table with list of operators and in a start form i have text box with login. Users have to enter their access code (4 nums) to identify themselves.
Again I need to do macro which will verify if entered code exists as a first step...
I tried to use DLookUp function with IsNull function, but this macro is not functional

If IsNull(DLookUP("[tblOperators]![AccessCode]";"[tblOperators]";"[tblOperators]![AccessCode] = [Forms]![frmStart]![login]") )
Then.........

Can you help me once more please?

[pbaldy]

For starters, try this syntax:

http://access.mvps.org/access/general/gen0018.htm

[TriFith]

Hi,
Finally I found out how to solve my problem.
I tried to use syntax from your link, but it didn´t work. It was throwing an error during preceeding the macro.

Final solution of my problem is:

If IsNull(DLookUp("Name";"tblOperators";"AccessCode=[Forms]![frmUse]![AccessCode]"))

I have no idea why this is working, but I can move forward now
Thank you for inspiration and your time.

[June7]

No, that CANNOT be working. You have enclosed variable within quotes and using semi-colons instead of commas. Assuming AccessCode is a text field:

If IsNull(DLookUp("[Name]","tblOperators","AccessCode='" & [Forms]![frmUse]![AccessCode] & "'")) Then

[TriFith]

I know it shouldn´t be working like that, but it is working... Believe me, I am not joking.
But I tried also your expression too. Access was not able to save this macro, and then I watched the mistake... There should be semicolon instead of comma between parameters of function DLookUp :-D
With semicolons your expression is working too.

But it is a little mistery for me, why my "primitive" expression is working

Thank you all for help!

[pbaldy]

I have seen it work with the form reference inside the quotes, but I've never seen it fail when properly concatenated.

[June7]

You are doing this in a macro, not VBA? That might explain it. I don't use macros.