Question Entering data into a form

[IannWard]

Here is one for you brain boxes...
I have a form into which I scan a serial number in one of its text boxes, I can then select search and am presented with a report relating to that serial number. All simple so far. Now for the dilemma... The barcode I scan consists of 15 characters like so, 53423PP98765432, numbers-PP-numbers.. the problem I have is that I only need the numbers after the PP's, in other words the last 8 digits. My question is, is there something I could do to make the text box omit the first 7 characters automatically, leaving me with the 8 I need, instead of me having to curser into the middle and manually delete the first 7 characters. I only need the last 8 because of the link with another database that only uses the last 8 digits.

Also, on the device I scan, there are 2 other barcodes, above and below the one I need to scan. If I scan one of the others by mistake, I have to highlight and delete the results to try and scan the middle barcode. The other barcodes also have a different length to the one I need. So is it possible to write some code that says, ok, you have scanned a barcode with 10 or 12 digits, we don't want either of those, so deletes it for me to try again, but then recognises the 15 digit barcode and auto deletes the first 7 characters as mentioned above.

I'm praying that one of you super clever guys or gals can help me with this. Thanks in advance.

Iann

 

[CJ_London]

one way would be to put some code in the before and after update events of the textbox - I'll call it txtbarcode

 Private Sub txtbarcode_BeforeUpdate(Cancel As Integer)
  
     'check code is of right length - reject if not
     If Len(txtbarcode.Text) <> 15 OR isEmpty(txtbarcode.Text) Then
        MsgBox "scanned wrong code!"
        txtbarcode.Undo
        Cancel = True
    End If

 End Sub
  
 Private Sub txtbarcode_AfterUpdate()
  
     'take rightmost 8 characters if code is long enough
     if len(txtbarcode)>8 then txtbarcode = Right(txtbarcode, 8)
    
End Sub

 

[IannWard]

CJ, I bow to your superior knowledge. However, I inserted the code and the results were as follows: when I scanned a barcode with less than 15 characters, I was presented with the message box "Scanned Wrong code", great, that's what I expected. When I scanned the correct code (The middle one on the attached pic)consisting of 15 characters, the first 7 were omitted and I was left with the 8 I need.... Brilliant. However, when presented with the error message for the incorrect amount of characters, I then have to click to get rid of the message, I didn't really want that, I just wanted to carry on scanning until I scanned the correct barcode. I'm sure you can easily tell me the code to avoid the error message, I tried tweaking but to no avail, oh, by the way, when I cleared the error message it didn't "Undo" the current entry, I just had to scan over the top of it.

One other problem, and this is the big one, and I will be in forever in your debt in you can solve this.... The third barcode which is right above the one I want, also consists of 15 characters, however, this on has a - in the middle (see attached pic), can you wright a code that will recognise this and ignore it as per a barcode of less than 15 characters..?

Regards

Iann

 

[CJ_London]

however, this on has a - in the middle

Private Sub txtbarcode_BeforeUpdate(Cancel As Integer)
  
     'check code is of right length - reject if not
     If Len(txtbarcode.Text) <> 15 OR isEmpty(txtbarcode.Text)[COLOR=magenta][COLOR=red] OR instr(txtbarcode.Text,"-")<>0[/COLOR] [/COLOR]Then
        MsgBox "scanned wrong code!"
        txtbarcode.Undo
        Cancel = True
    End If

End Sub

I then have to click to get rid of the message

rather than a message box, create a label (lets call in lblWarning) and set the caption to

Scanned Wrong Code

you could also set back and/or forecolors to red or whatever

then in the form current event put

lblWarning.visible=False

next, modify this code to

Private Sub txtbarcode_BeforeUpdate(Cancel As Integer)
  
     'check code is of right length - reject if not
     If Len(txtbarcode.Text) <> 15 OR isEmpty(txtbarcode.Text) OR instr(txtbarcode.Text,"-")<>0 Then        
         [COLOR=red]lblWarning.visible=True[/COLOR]
         txtbarcode.Undo
        Cancel = True
    End If

End Sub

and in the txtbarcode afterupdate event put

lblWarning.visible=False

when I cleared the error message it didn't "Undo" the current entry, I just had to scan over the top of it.

This code is not tested, so you may need to play around with it a bit but if you are happy to scan over it, that should be OK, once you are in the control, you cannot leave it until you either enter a valid code or nothing (which you might have to do if the code is damaged in some way)

 

[IannWard]

Will test this soon and let you know if all was ok. Many thanks...

 

[IannWard]

All works well but I have one more problem. I now have an item with a barcode that consists of 10 characters. How do I edit your code to allow 15 and 10 character barcodes? Many huge thanks..

Private Sub txtbarcode_BeforeUpdate(Cancel As Integer)

'check code is of right length - reject if not
If Len(txtbarcode.Text) <> 15 OR isEmpty(txtbarcode.Text) OR instr(txtbarcode.Text,"-")<>0 Then
lblWarning.visible=True
txtbarcode.Undo
Cancel = True
End If

End Sub

 

[IannWard]

PS, the label thing didn't work..

 

[CJ_London]

re label thing - OK. Would help more but without knowing what 'didn't work' means I'm not going to speculate

How do I edit your code to allow 15 and 10 character barcodes?

open the vba window, place the cursor after <>0 and start typing - do you really need my help for that? Suggest you try something and if that doesn't work, come back with what you have done and why it doesn't work