creating a validation macro on a form.

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

[June7]

I adon't use macros, only VBA, but that condition should work. I've never used the Before/After Update events of the form, only for controls.

[rpeare]

Like june, I don't use macros, just vb code.

Is your field literally a text field that says 'business' or 'personal' etc? or is it linked to a table with types of requests where you have a primary key

i.e.

tblTypes
TypeID TypeName
1      Personal
2      Business

If you have it set up this way (normalized) then you're searching for a text value in a numeric field (if you're storing the ID and not the text value)

You can also try instead of ([field] is null)

isnull(field)

There are some cases where access gets cranky depending on where and how you check for a null value.

[MJRobinson21987]

Like june, I don't use macros, just vb code.

Is your field literally a text field that says 'business' or 'personal' etc? or is it linked to a table with types of requests where you have a primary key

i.e.

tblTypes
TypeID TypeName
1      Personal
2      Business

If you have it set up this way (normalized) then you're searching for a text value in a numeric field (if you're storing the ID and not the text value)

You can also try instead of ([field] is null)

isnull(field)

There are some cases where access gets cranky depending on where and how you check for a null value.

I don't think its a text field - they pick it from a dropdown list. The property sheet says
Control Source: Type of Comp
Row Source: "Personal";"Business"
Row Source Type: Value List
Bound Column: 1
Limit to List: Yes

I don't know how to work in VB code - how would I go about doing that?

[rpeare]

Here's a very simple example