check first character (1 Viewer)

[mdcory]

Need help getting this..

Need to check the value of a barcode in a text box to see what the first character is.

Like this;

If the first character in txtScannedBarcode is "M" then I want it to open frmM
If it is a "B" then I want it to open frmB

and so on. The values would be along the likes of "M1001", "B1110".

I know how to get it to open the forms but unsure how to compare the value of the text box, if it is even possible.

Thanks,
Matthew

 

[Robert88]

Hi mdcory,

Anything is possible, hehe!!!!

Check this code, only written quickly but hope it solves your problem? Attached is the file so you can also see it in that.

Option Compare Database

Private Sub CmdScannedBarcode_Click()
On Error GoTo Err_CmdScannedBarcode_Click

    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim stFormName As String
    Dim MystFormName As String
    
    stFormName = Me.txtScannedBarcode
    
    'next line can be removed only used for test purposes
    MsgBox ("This is the textbox content " & stFormName)
    
    ' This variable contains the first letter of txtScannedBarcode
    MystFormName = Left(stFormName, 1)
    
    'next line can be removed only used for test purposes
    MsgBox ("This is the truncated textbox content " & MystFormName)
    
    If MystFormName = "B" Then
        stDocName = "FormB"
    End If
    
    If MystFormName = "M" Then
        stDocName = "FormM"
    End If
        
    'and so on for every letter required depending
    'on the numbers of forms and different codes
    'Good Luck with it, Robert88.
        
    'The stDocName should now contain the correct
    'Form name to open.
    'This next line can be removed only used for test purposes
    MsgBox ("This is stDocName " & stDocName)
    
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_CmdScannedBarcode_Click:
    Exit Sub

Err_CmdScannedBarcode_Click:
    MsgBox Err.Description
    Resume Exit_CmdScannedBarcode_Click
    
End Sub

Good Luck with it.

Robert88

 

[mdcory]

Thank you, Thank you, Thank you!!! That's exactly what I needed. Works perfectly...

 

[Robert88]

Hi mdcory,

No Problems, glad to help.

Robert88

 

[mdcory]

Anybody see why I get an "Invaild use of null" error when I open a form that has this code behind it? All it is, is a form with an unbound text box and this code.
Thanks,
Matthew

Option Compare Database

Private Sub ScannedBarcode_enter()
On Error GoTo Err_ScannedBarcode_Click

    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim stFormName As String
    Dim MystFormName As String
    
    stFormName = Me.ScannedBarcode
    
    MystFormName = Left(stFormName, 1)
    
    If MystFormName = "B" Then
        stDocName = "frmBV"
        stLinkCriteria = "[BVBarcode]=" & "'" & Me![ScannedBarcode] & "'"
    End If
    
    If MystFormName = "M" Then
        stDocName = "frmMonitor"
        stLinkCriteria = "[MonitorBarcode]=" & "'" & Me![ScannedBarcode] & "'"
    End If
        
    If MystFormName = "O" Then
        stDocName = "frmOther"
        stLinkCriteria = "[OtherBarcode]=" & "'" & Me![ScannedBarcode] & "'"
    End If
    
    If MystFormName = "P" Then
        stDocName = "frmPrinter"
        stLinkCriteria = "[PrinterBarcode]=" & "'" & Me![ScannedBarcode] & "'"
    End If
    
    If MystFormName = "S" Then
        stDocName = "frmMachine"
        stLinkCriteria = "[MachBarcode]=" & "'" & Me![ScannedBarcode] & "'"
    End If

    
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_ScannedBarcode_Click:
    Exit Sub

Err_ScannedBarcode_Click:
    MsgBox Err.Description
    Resume Exit_ScannedBarcode_Click
    

End Sub

 

[Robert88]

Hi mdcory,

Option Compare Database

Private Sub ScannedBarcode_enter()
On Error GoTo Err_ScannedBarcode_Click

Shouldn't the red above be Click? And the code should be in the button and not the form when you enter?.

Due to the txtScannedButton being blank when you first open it the code does not handle a null value and hence the error. One option is to give the txtScannedButton a default value, say "M10110" or additional code to handle the null value of this field. If I find a solution I will paste further, sorry for not providing full answer.

Robert88

 

[Robert88]

Hi mdcory,

A Little insert is required.

Option Compare Database

Private Sub ScannedBarcode_enter()
On Error GoTo Err_ScannedBarcode_Click

    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim stFormName As String
    Dim MystFormName As String
    
    [COLOR="Red"]'Check for Null value of txtScannedBarcode
    'if NULL is true notify user that input required and setfocus
    'back to field for input.
    If IsNull(Me!txtScannedBarcode.Value) = True Then
        MsgBox ("The Scanned Barcode field requires a code")
        Forms!frmFirstCharacterfrm!txtScannedBarcode.SetFocus
        GoTo EndOfProg
    Else
        stFormName = Me!txtScannedBarcode
    End If[/COLOR]    
    MystFormName = Left(stFormName, 1)
    
    If MystFormName = "B" Then
        stDocName = "frmBV"
        stLinkCriteria = "[BVBarcode]=" & "'" & Me![ScannedBarcode] & "'"
    End If

    [COLOR="Red"].
    .
    EndOfProg:[/COLOR]

    End Sub

Replace stFormName = Me.ScannedBarcode with what is above, hopefully this will sove your problem.

 

[mdcory]

Thanks. I thought that if I put it in the enter event of the text box that it would sit and wait until the enter key was hit. Didn't think that it would run when I opened the form. I guess I will put it back behind the button.
Thanks again,
Matthew

 

[Robert88]

Hi Matthew,

Let me know if you have any further problems as did this on the fly. Already noticed that you will have to substitute your form name.

Good Luck

Robert88

 

[gemma-the-husky]

just a thought

rather than all the spaghetti, cant you just assign the form by lefting the input string

eg openform ("myfrm" & left(stringname,1))
then trap the openform event in case its a new letter eg myfrmz.

if the forms are actually different names (ie not myfrma, myfrmb, etc) you can store them in a table, and look them up. A lot easier to maintain the code, and you don't need to change it if you add new forms.

 

[Robert88]

Hi gemma-the-husky,

Could you please paste an example of what you mention. Just wondering if it is easier.

Robert88