Data Prefix show error if not correct

[Sneale]

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]

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]

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]

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:

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]

Yet another construct:

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]

And another

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]

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]

again using Table as suggested in Post#7:

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

sample data:

Prefix	Description
P	Part Number
L	Location
Z	Package
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