If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Setting criteria for field entry

Hi,
I'm not sure if this is possible as I can't seem to find anything on it!
I have several fields. If I enter "1" in Field 7, how can I tell the database that if field 7 = 1, then field 3 must equal "1", "3" or "4"?

I've tried different version of the above in an if statement but no luck! For example:
IIF ([tblname]![field7] = "1" AND [tblname]![Field3] = "1" OR "3" OR "4", "", "msgbox")

I'm not sure how to get the message box up either? This is the extent of my coding knowledge, putting in expressions in queries etc.
any help would be great,
thanks
Sue

How are you entering data into field 7? Is it on a form? If so, then in the event OnExit for the box Field7 do something like

PHP Code:

If Me.Field7 = 1 then Redo:Result = InputBox("What value would you like for Field 3?" & _ vbnewline & "You must enter 1,3, or 4.", "Field 3?") If Result = "1" or Result = "3" or Result = "4" Then me.Field3 = ResultElse goto Redo End IfEnd If

This is pretty crude way of doing it (and untested admitedly). If you want more of a listbox look, you could create a custom form that pops up on the exit of Field 7, then have these 3 values on that form that the user can select. This would require a little bit more programming, but might look better and be more user-friendly.

the only way to do this in plain vanila access is to do the data capture in a form and impose this sort of logic in visual basic functions called as required. Server databases can have triggers to impose this sort of logic, Access/JET doesn't support triggers

Id suggest you create a function that checks the values of field3 & field7 (incidentally I think you should consider what you call your columns/variables in you tables Fieldx isnt particularly helpful).

say

private function IsValid() as boolean
isValid=FALSE 'set a default value
if field7=1 then
if field3=1 or field3=3 or field3=4 then
isvalid=true
else
isvalid=false
msgbox (If you have set a value of 1 in field7 then fiiled3 must be one of 1,3 OR 4',vbcritical)
endif
else
isvalid=true
endif
end function

place a call to that fuicntion in the follwoing events
field3 lostfocus
field7 lostfocus
Id also suggest you place a in the forms before update event but here use the form

cancel=isvalid() 'the cancel stops updating the recordset if the settign is invalid

Hi,
I have called my columns more appropriate names, I just thought this as an easier way of explaining what I needed.
I'm not sure if I conveyed it very well.
I have a form for different experiments (expno).
Each experiment has several criteria, ie. date started, purpose, procedure used, category.

The user will go in to the form and choose from a list box for each crtieria, i.e.
Purpose - biological 1, toxicology 2, environmental 3. Then the will put in procedure and then category.
When they get to category there are several options. If they choose a particular option, for the example No 1, then because of protocol, if this is 1, the Purpose must be either 2 or 3. If it isn't, the user would need to go back and check what they have chosen.
Does that make sense?
So
If [category] = "1", then [purpose] must equal either "1" or "3". If it doesn't then ...msgbox "please check Purpose field".
Does make it clearer?

I really appreciate the help, the all that coding was a little beyond my knowledge. I vaguely understand but not sure where I would put it all!!
thanks again,
Sue

i hope this helps you understand, ive color coded it as follows:red: check the purpose field being = to 1 or 3blue: Error message displayedGreen : additonal cirteria, to enable it just remove the yellow single quote

Last edited by loquin; 01-12-07 at 13:58.
Reason: Lime green was unreadable on gray background...

This sort of works but the message only comes up if category = anything but 1! Can I add a line in to say if purpose equals anything but 1 then do nothing, else if if does = 1, the category must = 1 or 3. If no do message?
thanks
Sue