This is probably easy for you, but it has totally stumped me!

[mjrobinson21987]

Hello Forumers,
I've been trying to teach myself Access 2007 for a little while, and I come to you now seeking guidance on a problem I've been having.

I need to create a macro which validates a rule when the user goes to save the form. I'm trying to get it to say that if the request is for a business comp, than a patron number is required. (but not for a personal comp request)

the user selects business from a dropdown menu (choices are either business or personal), and the patron number is entered in a field which only accepts numbers.

I've been using the following code (and some variations of it, hoping it will work) in the conditions column of the macro builder:

([Forms]![Staff Comp Request Form]![Type of Comp]="Business") AND ([Forms]![Staff Comp Request Form]![Patron Number] Is Null)

So, my questions are this:
1. (I think the area in red is the part that needs to be changed) - How do I tell Access that Business is a value in the field? And will that also stop it from assuming that it also needs a patron number for personal comps?
2. Is this macro better placed in the save button (on click) or on the entire form (before update)?

Thank you so much in advance for any help you all are able to give!

 

[cookiegary01]

Hi,

[Type of comp] is the drop down menu? If yes:
In your Macro condition, you are trying to refer to a text field within the drop down, while most likely the stored value "bound column" of the drop down, contains the number auto number. So that will not work.

In the macro try:
([Forms]![Staff Comp Request Form]![Type of Comp].[column](1)="Business" AND ([Forms]![Staff Comp Request Form]![Patron Number] Is Null)

Note:Column(1) refers to the Column containing the "business" text.

 

[mjrobinson21987]

Hi Cookiegary,
Thanks for your response - You are right that [type of comp] is a dropdown menu.
I tried copying that directly into the conditions column, but I had made a mistake in the name of the title, here's what it got changed to:

([Forms]![Comp Request Form]![Type of Comp].[BoundColumn](1)="Business") And ([Forms]![Comp Request Form]![Patron Number] Is Null)

When I try to test it, a dialogue box comes up that says "type mismatch"
Any idea what that means?

 

[cookiegary01]

Hi
Just remove Bound.

 

[mjrobinson21987]

I just did, and retested it, and it still says type mismatch:

([Forms]![Comp Request Form]![Type of Comp].[Column](1)="Business") And ([Forms]![Comp Request Form]![Patron Number] Is Null)

 

[cookiegary01]

Hi,

That means "Business" is not in column 1 in the query of your drop down.
(Check the query: The first column is usually Zero, so don't count that).
Now you must enter the correct column number inside the ().

If you know in which column "Business" is in your drop down query, then you enter that column number -1 in the brackets. So if you see it is in the 3rd column then enter Column (2).

 

[mjrobinson21987]

does it matter if the source type is listed as a value list? Should I change it?

([Forms]![Comp Request Form]![Type of Comp].[Column](1)=[Business]) And ([Forms]![Comp Request Form]![Patron Number] Is Null)

is coming up with a dialogue box that says:

"Microsoft Access can't find the name 'Business' you entered in the expression.

You may have specified a control that wasn't on the current object without specifying the correct form or report context.

To refer to a control on another form or report, precede the control name with the name of a collection, usually either Forms or Reports, and the name of the form or report to which the control belongs.

for example, Forms![Products]![Units in Stock]."

 

[cookiegary01]

[Business] refers to an object/fieldname in your statement.
It is not a field but a value so do not enclose it in [] but use "Business" instead.
When you have changed it,you must Find the column in the recordsource of your drop down. (This is the problem)
Add the column between the (). Remember that the utmost left column has column nr "0". So don't count the first column.
Once you have done this it should work.

 

[tobyjenkins]

If you want to find out the value of the dropdown box when the user changes it, you could always catch ti and put it in a message box yourself...

msgbox me![Type of Comp].value

That could go in the onChange event listed in the dropdown's properties... at least then you know whether you need to match up a string, like 'Business' or an autonumber.