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?

 

[Uncle Gizmo]

I think to achieve what you want you are going to have to venture into the world of VBA code.

See this link:

 

[wglmb]

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

 

[Uncle Gizmo]

>>>I don't want users to be able to enter a company code that does not exist in the COMPANY Form<<<<

If the Company code is in a table, then you could use a combo box to restrict the list to only existing company codes.

 

[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...)

 

[Uncle Gizmo]

Yes it is a very clear, I don't think I have seen such a well written question for a long time! Other posters should take note, I can think of one in particular!

What I am looking for, is the column widths, is the first column width, set to 0cm, if so, then your combo box is displaying the second listed value but returning the hidden value in the first column. Therefore it appears that it contains the right value but in fact your query won't work because it is being delivered the wrong value.

The other thing to bear in mind is the query won't work if the form is closed, the form has to be open for it to work.

I think the problem is that a combo box can display one value, but it actually "holds, references" to a different value. Open your combo boxes properties sheet, you will see various properties please post the settings for, column widths, column count, the combo box record source, I think that will do , although if you can, posting a JPEG of it is helpful.

 

[wglmb]

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

 

[Uncle Gizmo]

Swap this over:

Select Company.Key, Company.Code >>>> change it to >>>

Select Company.Code, Company.Key

See if that works..........

 

[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!