Validate table entries during macro

I have a table, (dta output) that I want to ensure has been properly filled prior to completing a macro.

The validation is that field (type) cannot be all "0"s.  At least one must be a "2".

If the value is not correct, the system that this table feeds will crash.

I would like to show a warning box "you have not selected any administrators " and halt the macro.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
Please be clear on what:
"cannot be all "0"s."
How many zeros are we talking about?

In other words, you will have to elaborate on this "Type" field and clearly explain what it can contain and what each "Number" represents?
TIgerVAuthor Commented:
The value is "0" "1" or "2"

All the records cannot be "0" or the database this feeds will crash.

At least one of the records must indicate a "2" in that field.

There may be 1500 records total.
Jeffrey CoachmanMIS LiasonCommented:
Still confused...
1. Is this value based on a Text field in a table, or a number field in the table?
2. What does this mean:
   "The value is "0" "1" or "2""
3. Please post an exact example of both Invalid value cases (a value with "all zeros", and a value with a missing "2")

4. Is this system validating "existing " records, ...or just new records...
Or both?

You can create one (temporary) system to validate all existing records.
Then once this is done, can then create a system to validate records as they are created.

5. Do you also need validation to make sure the values is a certain "length"

FWIW here is code to validate a single record of three characters in length (for your requirements, and also must be three characters only)

Private Sub Command2_Click()
'Cannot be 3 zeros
If Me.txtType = "000" Then
    MsgBox "Cannot be all zeros."
    Exit Sub
End If
'Must contan a 2
If InStr(Me.txtType, "2") = 0 Then
    MsgBox "Must contain at least one 2."
    Exit Sub
End If
'Must be three characters
If Len(Me.txtType) <> 3 Then
    MsgBox "must be three numbers"
    Exit Sub
End If

'Do whatever you like if the value s valid.
End Sub

Open in new window

Robert ShermanOwnerCommented:
If you're talking about an entire table's worth of records where one of the records must contain a 2 inside of a specific field, and you're doing this inside of a macro, I believe you can use an "if" step along with the DCount() function to make sure at least one record has a 2 in a specified field.

Do this by adding an "If" step to the macro in the appropriate place where you want to stop processing if the condition is not met, and under the "Conditional expression" field you'll enter the function.   Since I don't know the actual names of your table or field here is a sample:

DCount("[FieldName]", "TableName", "[FieldName] = 2") < 1

Then, for the "Then" portion of the If step, you can specify a MessageBox action followed by a StopMacro function.

This will abort further processing of the macro if there is less than one record where the field you specify contains a 2.

NOTE that I'm assuming the field in question is of Numeric type.   If it is a string (it really shouldn't be) you will need to surround the 2 with single quotes (apostrophes) as in "[FieldName] = '2'"

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.