Data Prefix show error if not correct (1 Viewer)

Sneale

New member
Local time
Today, 13:12
Joined
Aug 26, 2019
Messages
26
Hello,

I am working on a simple database to do a physical inventory in a warehouse. I have a table with 3 fields, PartNumber, PackageID, and Location.

I have a form where the user will scan the data from each package linked to those fields on the table. The barcode data that they will be scanning has a unique prefix for each data type. For example, part number begins with P, location begins with Z, and package ID starts with S, M, or G.

I need to find a way to have an error message pop up and not allow the entry to be saved if the scanned data does not match the prefix required for the field. Is there anyone that could assist me with this?

Thanks in advance

Steve
 

June7

AWF VIP
Local time
Today, 09:12
Joined
Mar 9, 2014
Messages
5,423
Possibly use textbox BeforeUpdate event.

If Left(Me.PartNumber, 1) <> "P" Then
Cancel = True
Me.PartNumber = Null
MsgBox "Entered data is not valid for this field."
End If
 

Sneale

New member
Local time
Today, 13:12
Joined
Aug 26, 2019
Messages
26
That works well for the fields PartNumber and Location where there is only one possible prefix. in the Package ID field, there are 3 potential prefixes. how would that have to be written?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:12
Joined
Feb 28, 2001
Messages
26,999
Code:
Dim PkgID as String
...
PkgID = Left(Me.PackageID, 1)
If ( PkgID = "S" ) OR ( PkgID = "M" ) OR ( PkgID = "G" ) Then
'do nothing
ELSE
    Me.PackageID = Null
    Cancel = TRUE
    MsgBox "Entered data is not valid for this field."
END IF
...

There is also the possibility of writing it this way:

Code:
IF ( PkgID <> "S" ) AND ( PkgID <> "M" ) AND ( PkgID <> "G" Then
    Cancel = TRUE
    etc. etc.
END IF

Works either way. You would use the first method if there was something affirmative to be done if they match. Then you would have an affirmative path and a negative path. The second method would be for negative path only.
 

cheekybuddha

AWF VIP
Local time
Today, 17:12
Joined
Jul 21, 2014
Messages
2,237
Yet another construct:
Code:
Dim PkgID as String
...
PkgID = Left(Me.PackageID, 1)
Select Case PkgID
Case "S", "M", "G"
  'do nothing
Case Else
    Me.PackageID = Null
    Cancel = TRUE
    MsgBox "Entered data is not valid for this field."
End Select
...
 

June7

AWF VIP
Local time
Today, 09:12
Joined
Mar 9, 2014
Messages
5,423
And another
Code:
If Not Me.PackageID Like "[S,M,G]*" Then
    Me.PackageID = Null
    Cancel = TRUE
    MsgBox "Entered data is not valid for this field."
End If
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:12
Joined
Sep 21, 2011
Messages
14,044
I can see where a new prefix will come into effect in the future, so I'd go with a table to start with. :)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:12
Joined
May 7, 2009
Messages
19,169
again using Table as suggested in Post#7:

tblPrefix (table)
prefix (short text, 2 length)
description (short text, 255)

sample data:



PrefixDescription
PPart Number
LLocation
ZPackage
S
M
G


your validation code:

private sub txtbarcode_BeforeUpdate(cancel as integer)
cancel = (DCount("1", "tblPrefix", "Prefix = '" & Left(Me!txtbarcode, 1) & "'") = 0)
if cancel then
Msgbox "Code is not valid!"
end if
end sub
 

Users who are viewing this thread

Top Bottom