vba code to go to a clear the content of a text box and move cursor to that text box

After the user clicks the ok button, I want the contents of the of the text box named "Part" to clear and the cursor to move into that text box on my for.  Can someone please help me develop the vba code for this.....Thanks.
If MsgBox("Special Character " & Mid(Me.Part, j, 1) & " Exists.  PLEASE RE-TYPE PART NUMBER CORRECTLY!!!", , "Part Number Authentication") = vbOK Then

Open in new window

sxxguptaAsked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Do it in the BeforeUpdate event and Cancel that event if needed:

Private Sub Part_BeforeUpdate(CAncel as Integer)
Dim j As Integer
For j = 1 To 7
   If InStr("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789", Mid(Me.Part, j, 1)) = 0 Then
      MsgBox "Special Character " & Mid(Me.Part, j, 1) & " Exists.  PLEASE RE-TYPE PART NUMBER CORRECTLY!!!", , "Part Number Authentication"
        Cancel = True
        Me.Part.Value = ""
      End If
     
   End If
Next

End Sub
0
 
hnasrCommented:
Me!Part.SetFocus
Me!Part.Text=""
0
 
sxxguptaAuthor Commented:
This is what I have:
Right now the Part field with special characters prompts the messagebox.  The user clicks Ok and the value is cleared, but the focus moves to the next field and does not remain in the Part field.................

Private Sub Part_AfterUpdate()
Dim j As Integer
For j = 1 To 7
   If InStr("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789", Mid(Me.Part, j, 1)) = 0 Then
      MsgBox "Special Character " & Mid(Me.Part, j, 1) & " Exists.  PLEASE RE-TYPE PART NUMBER CORRECTLY!!!", , "Part Number Authentication"
        Me.Part.Value = ""
        Me.Part.SetFocus
      End If
      
   End If
Next
End Sub

Open in new window

0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Sorry, you should also use

Me.Part.Undo

instead of

Me.Part.Value = ""

Setting the Value could trigger the BeforeUpdate event again, forcing you into an endless loop.
0
 
fhillyer1Commented:
following your logic you run a loop on the 7 characters for the part number
but when you find a non valid character you are still on the loop, you should exit
the loop as soon as the non valid character is found try this and let me know
also notice that you have an extra END IF there
Private Sub Part_AfterUpdate()
Dim j As Integer
For j = 1 To 7
   If InStr("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789", Mid(Me.Part, j, 1)) = 0 Then
        goto Nonvalid
   End If
Next
NonValid:
      MsgBox "Special Character " & Mid(Me.Part, j, 1) & " Exists.  PLEASE RE-TYPE PART NUMBER CORRECTLY!!!", , "Part Number Authentication"
        Me.Part.Value = ""
        Me.Part.SetFocus

End Sub

Open in new window

0
 
GRayLCommented:
It looks like your textbox has the same name as the field Part.  That's a sure recipe for getting lost quickly.  Try changing the name of your form controls by prefixing the field name with an abbreviation representing the control type - in your case, txtPart.  Other examples are lblPart, lbxPart, cboPart, etc.
0
 
sxxguptaAuthor Commented:
Thank you very much LSMC1!.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.