Refer to field in a table, without opening it! 1

[aarondewberry]

To simplify a problem i have....

Lets say I have a table, (tbl1), with 2 fields in it, (AgentName) & (SecurityNumber).

I now have a form (frm1) with a control on it, (Agnt), that shows the 'AgentName' from tbl1.

I now have a button, (but1), that, when pressed, opens another form, (frm2).

But.... heres the question.

I only want to be able open frm2, using but1, when the SecurityNumber of the AgentName is "A".
Else
msgbox "Access denied........."

Remember SecurityNumber is not shown anywhere on frm1, only AgentName!


Any help would be appreciated.

Thanks

 

[jadixon]

You can put the SecurityNumber of frm1 as a hidden field. The user cannot see it but it is available to you to use. You would set it up just like the AgentName, but set its visible property to false.

 

[aarondewberry]

Hi jadixon

Thanks for the reply.
I've had a similar problem before with VBA recognising queries within tables... ref: thread702-1481504

Anyways, this unfortunately does not work. Once added, even if you change the settings to visible on the SecurityNumber, the code still won't recognise it.... unless you click the SecurityNumber when frm1 is in normal view, before clicking but1 (set focus /black it out etc...) Then it works.
I've even tried putting SecurityNumber.Requery at the start of the Sub, but this still does not work.
Any other suggestions?

 

[Remou]

Use Dlookup to check the security number.

 

[aarondewberry]

Thanks Remou

Its all so easy when you know how!

Thanks again guys!!!