Referring to a field

[wglmb]

I want to use a macro for validation on a field on a form.
I don't want users to be able to enter a company code that does not exist in the COMPANY Form.
The condition I've got is:
[Forms]![TXNS By Company DIAG]!

<>[Forms]![COMPANY]![CODE]

The problem is that this only works if the COMPANY form is open, and then it only checks to see if you've entered the value in the record which is open, i.e. if the 1st record is open, the condition only checks to see if you've entered [i]that[/i] company code, but doesn't check it agains all the others.

So is there some way I can refer to [i]all[/i] entries in a field?

 

[wglmb]

oh...I hate new worlds.
But thanks anayway!

 

[wglmb]

Yes, I tried that, but then my queries don't work.
Hold on, I've just thought...(a miracle)...I'll probably need to remake my queries referring to the field wich is changed to combo-box...I'll try it.
Thanks

 

[wglmb]

Well I tried re-making my query, but it didn't help. Let me explain a bit more, to make it easier for someone to help:

Form: TXNS By Company DIAG:
There is no key field--not data is actually entered
There needs to be one field (currently a combo box: 'Combo0') where users enter a compnay code
The company code must come from the COMPANY table

Table: COMPANY:
Contains a key field
Each record contains a company code ('Code')

Query: TXNS By Company:
Takes data from records in the TRANSACTIONS table
One of the fields is 'Code' (from COMPANY table). The criteria on this is set to [Forms]![TXNS By Company DIAG]![Combo0]

Now that I'm using a combo box, there is validation on the company code in TXNS By...DIAG, but the problem is that my query doesn't seem to be taking it's criteria from it. Whenever I run the query, it doesn't find any records.
I know the query works; I tried replacing the reference in the criteria box with a company code (CRIS), and the query works.
So for some reason the query isn't 'reading' the data entered in the combo box...help! (I hope I've explained clearly...)

 

[wglmb]

Thanks for the quick reply!
(Yes, I know that the form must be open)
Here's a screenshot of the combo's properties:

 

[wglmb]

Aha! I'm getting closer! Thanks!
Yes, the query now works, but now the user must enter the relevant 'key' (an autonumber field in the COMPANY table) rather than the company itself...any way I can stop that?

 

[wglmb]

Aha! Solved!
I simply deleted the 'key' field in the COMPANY table, and made 'Code' the primary key (as they are always unique anyway), and the made the combo box refer to the 'Code' field.

ThankyouThankyouThankyou!