VBA for Form Validation (1 Viewer)

freidaf · Nov 15, 2017

Hello,
I have a form which includes fields for a Machine Name (cboMachID), Activity ID (cboActID) and Total Items Processed (TtlItems).
I need to write a vba statement for the forms before update event that checks the following:
If Activity ID = “P” and Machine ID is not equal to (“F-123-D” or “F-45-D” or “ABC-D” or “EFGH-D”) and TotalItems = 0
Then display a message that you must enter TotalItems.
Thank you for your help.

pbaldy · Nov 15, 2017

This should get you started:

http://www.baldyweb.com/BeforeUpdate.htm

You'll have to repeat the machineID field and use AND

Machine ID <> “F-123-D” AND Machine ID <> “F-45-D”

Unless VBA supports IN(), which I can't recall offhand.

freidaf · Nov 15, 2017

Thank you I appreciate your help. So something like this:

If cboActID = "P And cboMachID <>F-123-D And cboMachID <>F-45-D And cboMachID <>ABC-D And TtlItems = 0 Then
MsgBox "You must enter total items"

pbaldy · Nov 15, 2017

Yes, with quotes around the text values. Personally I like to disambiguate by referring to controls with "Me", but it should work as is. In other words:

Me.cboActID

freidaf · Nov 15, 2017

That did the trick!

Thank You Again!

pbaldy · Nov 15, 2017

Happy to help!

Pat Hartman · Nov 16, 2017

If Activity ID = “P” and Machine ID is not equal to (“F-123-D” or “F-45-D” or “ABC-D” or “EFGH-D”) and TotalItems = 0

I would suggest a schema change. Rather than having to hard code a list of IDs that will likely change, why not add a new column to the table that identifies the "special" IDs. That way it won't matter if you have to add more. You're just checking another column for True or "whatever".

freidaf · Nov 18, 2017

Thank you Pat, that's an excellent suggestion because these Machine names are likely to change. Some will be added and others will be deleted over time.

It's nice to know more than one way of solving my problem. Your help is very much appreciated!

Pat Hartman · Nov 19, 2017

You are very welcome.

VBA for Form Validation (1 Viewer)

freidaf

Registered User.

pbaldy

Wino Moderator

freidaf

Registered User.

pbaldy

Wino Moderator

freidaf

Registered User.

pbaldy

Wino Moderator

Pat Hartman

Super Moderator

freidaf

Registered User.

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread