Link to home
Start Free TrialLog in
Avatar of Sanjay
SanjayFlag for United States of America

asked on

microsoft access custom vba code to validate certain words

Folks:

I have a text boxt on my Access form called "list_ind_ord" and the only values that are allowed in this box in any combination are the following words:

"QA" and/or "Materials" and/or "Service" and/or "Engineering".

Some combination examples:
QA,Materials
QA, Materials,Service, Engineering
QA Service

Commas are to be allowed by that is it.  Anything else should faults out.
E.g.  Material is not allowed
E.g.  Services is not allowed
E.g. "." is not allowed
etc.

How can this be accomplished programmatically using VBA?  Thanks everyone.
Avatar of IrogSinta
IrogSinta
Flag of United States of America image

Why not just use 4 checkboxes for these choices?
In general terms, you should NOT give users the opportunity of entering 'invalid' data.

(So Irogsinta's suggestion of using checkboxes would achieve this ).
Avatar of Sanjay

ASKER

I could but it would take forever to implement this change due to the regulated nature of our business.
Lock the textbox.

Add 4 checkboxes named:
chkQA, chkMaterials, chkService, and chkEngineering

In the AfterUpdate event of each checkbox, add:
Call CombineChecks

Add the following code to your form:
Sub CombineChecks()
    Dim strTemp As String
    
    If chkQA Then strTemp = strTemp & ", " & "QA"
    If chkMaterials Then strTemp = strTemp & ", " & "Materials"
    If chkService Then strTemp = strTemp & ", " & "Service"
    If chkEngineering Then strTemp = strTemp & ", " & "Engineering"
    Me.list_ind_ord = Mid(strTemp, 3)
    
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sanjay

ASKER

Hi Peter:  Yes changes are difficult on my end since I work for a medical devices company that is heavily regulated by the FDA and so changes take a very long time.........


Anyway how would I use your function in my form?  I would add this function to the afterupdate event of the textbox called "list_ind_ord"? and what would the value be for "pIn"?
Avatar of Sanjay

ASKER

And a big thank you to all the other participating experts.
Ypu will need some code of your own to call this function.
Yes the afterupdate event provcedure of the textbox would be the place to do it.

Something like

'check for comma separated list of acceptable terms
if Checvalues(me.[list_ind_ord]) = true then
' do nothing
else
msgbox "Invalid input"
End if

I find it bizarre that you can add any code you like to an application but can't make the much more sensible change that Ron suggested.
Avatar of Sanjay

ASKER

Thanks Peter.  And agreed with you on the last sentence.