Help Modifying a piece of VBA (1 Viewer)

Morning All ,

I Hope everyone is well.

I am still fairly new to access, and not a frequent user so go steady on me please.

With a current database i have this piece of VBA code that checks a barcode has been entered on the previous station/table before you can scan in on the current station.

We now however have a certain part number that doesn't get scanned at a station is there a way to edit the code below to achieve this.

Private Sub TxtBarInp_BeforeUpdate(Cancel As Integer)

Dim Answer As Variant

Answer = DLookup("[BARCODE]", "tbllogGEARCUTTING", "[BARCODE] = '" & TxtBarInp & "'")

If IsNull(Answer) Then
MsgBox "CRANKSHAFT HAS NOT BEEN SIGNED OFF AT GEARCUTTING CELL" & vbCrLf & "Please Contact your T.LEADER.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"

Cancel = True
Me.TxtBarInp.Undo


End If
End Sub

The Barcode we are scanning is 12 digit 110007312345, the first 7 digits are the part number and the following 5 the unique identifier.
its the 1100073 part number that i want the code to exclude from the check on this station.

Many thanks in advance for any help.

Kind regards

Dale

You can use the Left() function to extract the first 7 characters, so something like

Run before your DLookup() should suffice.

Morning Minty

Firstly many thanks for your reply.

This method worked great, however i have just thought i can secure the process much better with a small adjustment. i just think i have a few issues with way i am laying the code out.

Private Sub TxtBarInp_BeforeUpdate(Cancel As Integer)

If Left(Me.TxtBarInp, 7) = "1100073" Then

Dim Answer As Variant

Answer = DLookup("[BARCODE]", "tbllogBALANCING", "[BARCODE] = '" & TxtBarInp & "'")

If IsNull(Answer) Then
MsgBox "CRANKSHAFT HAS NOT BEEN SIGNED OFF AT BALANCING CELL" & vbCrLf & "Please Contact your T.LEADER.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"

Cancel = True
Me.TxtBarInp.Undo

End If

Else

Dim Answer As Variant

Answer = DLookup("[BARCODE]", "tbllogGEARCUTTING", "[BARCODE] = '" & TxtBarInp & "'")

If IsNull(Answer) Then
MsgBox "CRANKSHAFT HAS NOT BEEN SIGNED OFF AT GEARCUTTING CELL" & vbCrLf & "Please Contact your T.LEADER.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"

Cancel = True
Me.TxtBarInp.Undo


End If
End If
End Sub

I thought this code would ensure the part was scanned on the previous station, but if the part number was 1100073 it would check the part was scanned on the station 2 previous.

Any pointers?

Cheers

Cross posted (also with an answer) at https://www.utteraccess.com/forum/index.php?showtopic=2052275&hl=

Please read this link: https://www.excelguru.ca/content.php?184

Morning Isladogs,

I apologise, having read the link i fully understand what you mean.

Sorry for any issues my cross posting has caused, i was just being a little naive hoping to get help quicker.

Kind regards

No problems. Common mistake for new members.
Several of us look on multiple forums but not everyone does so