Clearing a text box on form as well as stopping VBA code (1 Viewer)

[Sneale]

Hello, I have 2 issues that I need assistance with. First, I want to have the text box clear out if the incorrect info is input to it. I have tried Me.Location = "" and Me.Location = Null after the message box, neither of which seem to work.

I also have the below code and need it to stop when the first set of code gives the error message. right now, if the user enters anything in the Location text box, the first code will run, then it automatically runs the second code where it beeps and goes to the next record. I want it to stop if the incorrect data is input and only run the second code when the data is correct. Can anyone provide assistance?

Private Sub Location_BeforeUpdate(Cancel As Integer)
If Not Me.Location Like "[Z]*" Then
    Me.Location = Null
    Cancel = True
    Beep
    MsgBox "Entered data is not a Location."
End Sub



Private Sub txtLocation_Exit(Cancel As Integer)
Dim X As Long
    For X = 1 To 3
    Beep
    Sleep 1000
   Next
  
End Sub

 

[bastanu]

Maybe try:

Private Sub Location_BeforeUpdate(Cancel As Integer)
If Not Me.Location Like "[Z]*" Then
    'Me.Location = Null
    Cancel = True
    Me.Undo
    Beep
    MsgBox "Entered data is not a Location."
End Sub

In the second procedure you have a different control (txtLocation vs. Location) so not sure what you're trying to do with that.

Cheers,

 

[Sneale]

Maybe try:

Private Sub Location_BeforeUpdate(Cancel As Integer)
If Not Me.Location Like "[Z]*" Then
    'Me.Location = Null
    Cancel = True
    Me.Undo
    Beep
    MsgBox "Entered data is not a Location."
End Sub

In the second procedure you have a different control (txtLocation vs. Location) so not sure what you're trying to do with that.

Cheers,

That works, but I only want it to clear the location field. this cleared all fields on the form.

As for txtLocation vs. Location, that was a typo on my end. I have adjusted that but still have the issue where it will allow the incorrect data to be saved and go to the next record.

 

[bastanu]

You need to add Cancel=True to the Exit sub to keep the focus in the textbox.

 

[Sneale]

You need to add Cancel=True to the Exit sub to keep the focus in the textbox.

Sorry I don't follow. I know very little about VBA and am building this on the fly. Do you by chance have an example?

 

[bastanu]

Private Sub Location_Exit(Cancel As Integer)
Dim X As Long
    For X = 1 To 3
    Beep
    Sleep 1000
   Next
 Cancel=True  'stops the user from leaving the box'
End Sub

 

[Sneale]

That works great! Thank you so much for the help!

 

[bastanu]

Glad to help!

 

[Sneale]

Still running into an issue where all the text boxes on the form are being cleared with the Me.Undo Is there a way to clear just the one text box? I have 3 that if the data is scanned incorrectly, I just want that one to clear out. so like below, if the user scans lets say a part number to the package field, they get the error message and it only clears the package field on the form. not the entirety of it.

Option Compare Database

Private Sub txtSupplier_BeforeUpdate(Cancel As Integer)
If Not Me.Supplier Like "[V]*" Then
    Me.Supplier = Null
    Cancel = True
    Me.Undo
       Beep
    MsgBox "Entered data is not a Supplier.""
End If
End Sub

Private Sub txtPackage_BeforeUpdate(Cancel As Integer)
If Not Me.Package Like "[S,M,G]*" Then
    Me.Package = Null
    Cancel = True
    Me.Undo
    Beep
    MsgBox "Entered data is not a package ID."
      
End If
End Sub

Private Sub txtLocation_BeforeUpdate(Cancel As Integer)
If Not Me.Location Like "[Z]*" Then
    Me.Location = Null
    Cancel = True
    Me.Undo
    Beep
    MsgBox "Entered data is not a Location."
    
End If

End Sub

Private Sub Location_Exit(Cancel As Integer)

Dim X As Long
For X = 1 To 3
Beep
Sleep 1000

   Next
 Cancel = True 'stops the user from leaving the box'
End Sub

 

[bastanu]

You have the same error again in the location code, comment out the Me.Undo to fix the code:
Me.ts=[B]txt[/B]Location=Null

EDIT: just saw the Exit sub; so what is the name of the textbox control bound to the Location field? Is it txtLocation? If yes then that is what you need to null. Right now you show two events for two different location controls....

Cheers,

 

[Sneale]

You have the same error again in the location code, comment out the Me.Undo to fix the code:
Me.ts=[B]txt[/B]Location=Null

EDIT: just saw the Exit sub; so what is the name of the textbox control bound to the Location field? Is it txtLocation? If yes then that is what you need to null. Right now you show two events for two different location controls....

Cheers,

The controls are all named beginning with txt so txtSupplier, txtPackage, txtLocation

 

[bastanu]

So reference the control(s) and not the bound fields:

Option Compare Database
Option Explicit 'you should have this on top of every module!!!!!!!!!!!!!!!!!!

Private Sub txtSupplier_BeforeUpdate(Cancel As Integer)
If Not Me.txtSupplier Like "[V]*" Then
    Me.txtSupplier = Null
    Cancel = True
    'Me.Undo
       Beep
    MsgBox "Entered data is not a Supplier.""
End If
End Sub

Private Sub txtSupplier_Exit(Cancel As Integer)
Dim X As Long
For X = 1 To 3
    Beep
    Sleep 1000
Next
Cancel = True 'stops the user from leaving the box'
End Sub

Private Sub txtPackage_BeforeUpdate(Cancel As Integer)
If Not Me.txtPackage Like "[S,M,G]*" Then
    Me.txtPackage = Null
    Cancel = True
    'Me.Undo
    Beep
    MsgBox "Entered data is not a package ID."     
End If
End Sub

Private Sub txtPackage_Exit(Cancel As Integer)
Dim X As Long
For X = 1 To 3
    Beep
    Sleep 1000
Next
Cancel = True 'stops the user from leaving the box'
End Sub

Private Sub txtLocation_BeforeUpdate(Cancel As Integer)
If Not Me.txtLocation Like "[Z]*" Then
    Me.txtLocation = Null
    Cancel = True
    'Me.Undo
    Beep
    MsgBox "Entered data is not a Location."   
End If
End Sub

Private Sub txtLocation_Exit(Cancel As Integer)
Dim X As Long
For X = 1 To 3
        Beep
        Sleep 1000
Next
Cancel = True 'stops the user from leaving the box'
End Sub

Cheers,

 

[Pat Hartman]

Your code, as it is written, will not properly validate the data since control level event code cannot ensure that fields are not null. Try using the mouse to skip over a field. Did the control events run? A better place to put the code is the Form's beforeUpdate event.
Also:
1. Change the Access settings to always require Option Explicit. The point of this is to find certain types of errors at compile time rather than at run time. You don't want your user finding errors that you should have found during testing. Also, when working with variables, if you don't use Option Explicit, you can use Customer in some places and Custmer in others. This bug will be very hard to find if you don't force it to generate a compile error.
2. Do NOT remove the value the user typed. He should always be able to see the invalid value. Removing it leaves him wondering if he had the wrong information or typed it incorrectly.
3. Don't use a loop to keep the user from exiting from a control Your attitude toward validation is punitive. You don't want to punish the user for making mistakes. You want to ensure that he fixes his errors. To do that, you need to put your code in the correct event. Having the code in the FORM's BeforeUpdate event, you can prevent the bad data from being saved without punishing the user. Unless the user backs out the form changes using the Escape key twice or a cancel button if you've provided one, Access is not going to let him leave the form so you don't need to do it yourself.
4. Your validation is very strange and will certainly need to change as your data changes. Why would Supplier ALWAYS start with a V? That is just silly. If you want to ensure that a supplier is entered, then:

If Me.Supplier & "" = "" Then
    Cancel = True
    Me.Supplier.SetFocus
    Msgbox "Supplier is required", vbOKOnly
    Exit Sub
End If

Also, go back to your table definitions and for ALL text fields that are required, make the AllowZeroLengthString = No. I'll attach a database that will help you clean up some settings without having to touch them one by one.

Option Compare Database
Option Explicit

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not Me.Supplier Like "[V]*" Then
    Cancel = True
    Me.txtSupplier.SetFocus
    MsgBox "Entered data is not a Supplier.""
    Exit Sub
End If

If Not Me.Package Like "[S,M,G]*" Then
    Cancel = True
    Me.txtPackage.SetFocus
    MsgBox "Entered data is not a package ID."
    Exit Sub
End If

If Not Me.Location Like "[Z]*" Then
    Cancel = True
    Me.Location.SetFocus
    MsgBox "Entered data is not a Location."
    Exit Sub
End If

End Sub

 

[Sneale]

Your code, as it is written, will not properly validate the data since control level event code cannot ensure that fields are not null. Try using the mouse to skip over a field. Did the control events run? A better place to put the code is the Form's beforeUpdate event.
Also:
1. Change the Access settings to always require Option Explicit. The point of this is to find certain types of errors at compile time rather than at run time. You don't want your user finding errors that you should have found during testing. Also, when working with variables, if you don't use Option Explicit, you can use Customer in some places and Custmer in others. This bug will be very hard to find if you don't force it to generate a compile error.
2. Do NOT remove the value the user typed. He should always be able to see the invalid value. Removing it leaves him wondering if he had the wrong information or typed it incorrectly.
3. Don't use a loop to keep the user from exiting from a control Your attitude toward validation is punitive. You don't want to punish the user for making mistakes. You want to ensure that he fixes his errors. To do that, you need to put your code in the correct event. Having the code in the FORM's BeforeUpdate event, you can prevent the bad data from being saved without punishing the user. Unless the user backs out the form changes using the Escape key twice or a cancel button if you've provided one, Access is not going to let him leave the form so you don't need to do it yourself.
4. Your validation is very strange and will certainly need to change as your data changes. Why would Supplier ALWAYS start with a V? That is just silly. If you want to ensure that a supplier is entered, then:

If Me.Supplier & "" = "" Then
    Cancel = True
    Me.Supplier.SetFocus
    Msgbox "Supplier is required", vbOKOnly
    Exit Sub
End If

Also, go back to your table definitions and for ALL text fields that are required, make the AllowZeroLengthString = No. I'll attach a database that will help you clean up some settings without having to touch them one by one.

Option Compare Database
Option Explicit

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not Me.Supplier Like "[V]*" Then
    Cancel = True
    Me.txtSupplier.SetFocus
    MsgBox "Entered data is not a Supplier.""
    Exit Sub
End If

If Not Me.Package Like "[S,M,G]*" Then
    Cancel = True
    Me.txtPackage.SetFocus
    MsgBox "Entered data is not a package ID."
    Exit Sub
End If

If Not Me.Location Like "[Z]*" Then
    Cancel = True
    Me.Location.SetFocus
    MsgBox "Entered data is not a Location."
    Exit Sub
End If

End Sub

Thank you for the information!

What I am making is a database to compile a warehouse inventory. The barcodes on the packages all have a prefix (v for suppler, z for location, p for part number etc.) so the warehouse management system know which barcode is scanned.

I was planning on making this a split database so I can have several users entering data from the warehouse floor at the same time. (unless you know of a better way to do it) we have around 13,000 packages to scan and only 2 days to do it in, so i was looking for a fast way to get everything compiled. Once it is completed, I am pulling the data into PowerBI and reconciling with our WMS.

 

[Pat Hartman]

Splitting the database is the best way. Actually, it is the only safe way to have a multi-user environment. Also make sure that each user has his own personal copy of the FE. The ONLY shared file is the BE.

There are lots of distribution methods you can use so you don't have to visit each PC for each update to the FE. I use a batch file because it is very simple. There are more complex methods using Access itself. You can find links to both here.