Macros I hope

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I work for a company that does surveys. We have a set of
demographics for each zipcode ie: 50 men 45 to 55+, women
18 to 24, etc. Each zipcode has a different amt and age
criteria. How could I set the form up to disallow entries
when each criteria is filled or can I at all? Otherwise
it looks as though one person would have to keep
monitoring the entries to see if we have reached the qty.
needed. Help please as I am hoping this system is going
to save unecessary work.
 
I work for a company that does surveys. We have a set of
demographics for each zipcode ie: 50 men 45 to 55+, women
18 to 24, etc. Each zipcode has a different amt and age
criteria. How could I set the form up to disallow entries
when each criteria is filled or can I at all? Otherwise
it looks as though one person would have to keep
monitoring the entries to see if we have reached the qty.
needed. Help please as I am hoping this system is going
to save unecessary work.
Click to expand...

Macros are going to be a rather limited and difficult way to do this.
I'd suggest instead having a Table of zipcodes with the needed
criteria; use some very simple VBA code in the Form's BeforeUpdate
event to check for validity. For example:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Me!txtGender <> DLookUp("[AllowedGender", "ValidityTable", _
"[Zip] = '" & Me!txtZip & "'") Then
Cancel = True
MsgBox "You can't use gender " & Me!txtGender & " in this zip"
Exit Sub
End If
If Me!txtAge < DLookUp("MinAge", "ValidityTable", _
"[Zip] = '" & Me!txtZip & "'") Then
Cancel = True
MsgBox "Below minimum age for this zip"
Exit Sub
End If

<etc for the other criteria>
 
Back
Top