Search code examples
validationreplacerule

Change word when entered in MS Access?

From my understanding, validation rules can't CHANGE things- I tried the replace fn in the validation rule and it did nothing but wouldn't let me enter the word I wanted to change.

I want 'asst' to change to 'assistant' and 'prof' to change to 'professor' when entered in Access. Any ideas on how to do this?

Been looking for an hour and haven't found anything- any input appreciated!

Solution

  • I would implement an AfterUpdate event on the textbox you are validating. You can click the Event tab of the TextBox properties window to create the event.

    Private Sub txtMyTextBox_AfterUpdate()

If Me!txtMyTextBox = "prof" Then Me!txtMyTextBox = "Professor"

End Sub

    Or something like

    Private Sub txtMyTextBox_AfterUpdate()

If InStr(1, Me!txtMyTextBox, "prof ") <> 0 Then Me!txtMyTextBox = Replace(Me!txtMyTextBox, "prof ", "Professor ")

End Sub