Searching for record using multiple criteria

[Cirrostratus]

I have a form where a user enters data. One of the things the user enters is a "lot size". I need this lot size field to be checked against a query in the database to determine if the quantity is acceptable or not.

The order number in this query is in certain cases missing a leading zero, so I need to truncate this from the user entered form field.

Finally I want the user to be notified if they are trying to exceed the lot size in the query.

My code is as follows:

Public Function RemoveFirstChar(RemFstChar As String) As String
Dim TempString As String
 
          TempString = RemFstChar
            If Left(RemFstChar, 1) = "0" Then
                If Len(RemFstChar) > 1 Then
                    TempString = Right(RemFstChar, Len(RemFstChar) - 1)
                End If
            End If
RemoveFirstChar = TempString
End Function
 
 
Private Sub Lot_Size_AfterUpdate()
 
          Dim strWhere As String
 
          strWhere = "([Order_No]=""& RemoveFirstChar(Forms!frmProdStartEntry!Order_No) & "") AND ([Release_No]=""& Forms!frmProdStartEntry!Release_No &"") AND ([Sequence_No]=""& Forms!frmProdStartEntry!Sequence_No &"")"
 
          Buildable = DLookup("[UnitsOpen]", "SQ_ProdUnitsBuildable", strWhere)
 
        If Forms!frmProdStartEntry!Lot_Size > Buildable Then
          MsgBox "You can only build " & Buildable & "pcs, please revise qty!", vbInformation, ""
          Cancel = True
          Me.Lot_Size.SetFocus
          Exit Sub
        End If
 
          btnStart.SetFocus
End Sub

I cant get this code to work :banghead:. Can you guys help me out and point out what is wrong with it? All the help is greatly appreciated!!

 

[TJPoorman]

What errors are you getting? And what line is the code breaking on?

 

[Cirrostratus]

No errors, it just doesn't return anything. If I put a qty larger than allowed, it still lets me proceed. No message box pop's up.

 

[Cirrostratus]

I guess there is a couple of things that could be going wrong here. The RemoveFirstChar function, I'm not sure if that works inside the dlookup function?

And the Buildable, does it need to be defined or is my message box syntax wrong?

Thanks again!

 

[JHB]

The strWhere is wrong:

strWhere = "([Order_No]=""& RemoveFirstChar(Forms!frmProdStartEntry!Order_No) & "") AND ([Release_No]=""& Forms!frmProdStartEntry!Release_No &"") AND ([Sequence_No]=""& Forms!frmProdStartEntry!Sequence_No &"")"

If the criteria is number then:

[ANumberField]=" & NumberCriteria

If the criteria is Text then:

[ATextField]='" & TextCriteria & "'"

Or:

[ATextField]=" & "'" & TextCriteria & "'"

 

[Cirrostratus]

all of those fields are text, so I changed it to:

strWhere = "([Order_No]='"& RemoveFirstChar(Forms!frmProdStartEntry!Order_No) & "'") AND ([Release_No]='"& Forms!frmProdStartEntry!Release_No &"'") AND ([Sequence_No]='"& Forms!frmProdStartEntry!Sequence_No &"'")"

But now VB is giving a compile error message, "expected end of statement" at the closing parenthesis that I have highlighted in bold. Something is still not right..

 

[pr2-eugin]

Try this..

strWhere = "([Order_No]='" & RemoveFirstChar(Forms!frmProdStartEntry!Order_No) & "'") AND ([Release_No]='" & Forms!frmProdStartEntry!Release_No & "'") AND ([Sequence_No]='" & Forms!frmProdStartEntry!Sequence_No & "'")"

 

[Cirrostratus]

Try this..

strWhere = "([Order_No]='" & RemoveFirstChar(Forms!frmProdStartEntry!Order_No) & "'") AND ([Release_No]='" & Forms!frmProdStartEntry!Release_No & "'") AND ([Sequence_No]='" & Forms!frmProdStartEntry!Sequence_No & "'")"

Yeah, that's still giving the same error message. Very strange?!?

 

[pr2-eugin]

Are all the controls on the same Form from which this code is run? Or is that another Form?

 

[Cirrostratus]

Are all the controls on the same Form from which this code is run? Or is that another Form?

Its all on this one form, the only thing not there is the query in the dlookup.

 

[pr2-eugin]

So just refer to the Controls by using Me. rather than using Forms!FormName!ControlName

strWhere = "([Order_No]='" & RemoveFirstChar([B]Me.[/B]Order_No) & "'") AND ([Release_No]='" & [B]Me.[/B]Release_No & "'") AND ([Sequence_No]='" & [B]Me.[/B]Sequence_No & "'")"

 

[TJPoorman]

Try this..

strWhere = "([Order_No]='" & RemoveFirstChar(Forms!frmProdStartEntry!Order_No) & "'") AND ([Release_No]='" & Forms!frmProdStartEntry!Release_No & "'") AND ([Sequence_No]='" & Forms!frmProdStartEntry!Sequence_No & "'")"

your quotes are in the wrong place. it's expecting the end of statement.
Try this:

strWhere = "([Order_No]='" & RemoveFirstChar(Forms!frmProdStartEntry!Order_No) & "') AND ([Release_No]='" & Forms!frmProdStartEntry!Release_No & "') AND ([Sequence_No]='" & Forms!frmProdStartEntry!Sequence_No & "')"

 

[pr2-eugin]

Yes TJPoorman, you are right.. How did I miss that..

 

[Cirrostratus]

That fixed the compile error message, thanks!

But unfortunately the program still doesn't work. If the user enters into the lot_size field a qty larger than the "buildable" it still doesn't produce the msgbox. Does the "buildable" need to be defined? if so how? and can I use that then in the msgbox?

Also, should the RemoveFirstChar function work inside the strWhere?

Thanks again!! We are making progress!

 

[TJPoorman]

You should define Buildable. Try setting a breakpoint on your If statement and hover over the 2 fields to make sure that they are setting correctly.

 

[pr2-eugin]

Okay lets do some debugging..

Private Sub Lot_Size_AfterUpdate()
    Dim strWhere As String, Buildable As Long

    strWhere = "([Order_No]='" & RemoveFirstChar(Me.Order_No) & "') AND ([Release_No]='" & Me.Release_No & "') AND ([Sequence_No]='" & Me.Sequence_No & "')"

    Buildable = Nz(DLookup("[UnitsOpen]", "SQ_ProdUnitsBuildable", strWhere), 0)
    [COLOR=Red][B]MsgBox Buildable[/B][/COLOR]
    
    If Forms!frmProdStartEntry!Lot_Size > Buildable Then
        MsgBox "You can only build " & Buildable & "pcs, please revise qty!", vbInformation, ""
        Cancel = True
        Me.Lot_Size.SetFocus
        Exit Sub
    End If
    btnStart.SetFocus
End Sub

Public Function RemoveFirstChar(RemFstChar As String) As String
    Dim TempString As String
    TempString = RemFstChar
    If Left(RemFstChar, 1) = "0" Then
        If Len(RemFstChar) > 1 Then
            TempString = Right(RemFstChar, Len(RemFstChar) - 1)
        End If
    End If
    RemoveFirstChar = TempString
End Function

See what you get in the MsgBox..

 

[Cirrostratus]

Ok, I tried defining "Buildable" but it came back with an error message on the Buildable = where it said incorrect use of Null or something like that.

Public Function RemoveFirstChar(RemFstChar As String) As String
Dim TempString As String

TempString = RemFstChar
If Left(RemFstChar, 1) = "0" Then
If Len(RemFstChar) > 1 Then
TempString = Right(RemFstChar, Len(RemFstChar) - 1)
End If
End If
RemoveFirstChar = TempString
End Function


Private Sub Lot_Size_AfterUpdate()

Dim strWhere As String
Dim Buildable As Long

strWhere = "([Order_No]='" & RemoveFirstChar(Forms!frmProdStartEntry!Order_No) & "') AND ([Release_No]='" & Forms!frmProdStartEntry!Release_No & "') AND ([Sequence_No]='" & Forms!frmProdStartEntry!Sequence_No & "')"

Buildable = DLookup("[UnitsOpen]", "SQ_ProdUnitsBuildable", strWhere)

If Forms!frmProdStartEntry!Lot_Size > Buildable Then
MsgBox "You can only build " & Buildable & "pcs, please revise qty!", vbInformation, ""
Cancel = True
Me.Lot_Size.SetFocus
Exit Sub
End If

btnStart.SetFocus
End Sub

 

[Cirrostratus]

Okay lets do some debugging..
See what you get in the MsgBox..

That didn't bring back anything, but no error messages either..

 

[TJPoorman]

not sure if it will change anything but try removing the brackets in your DLookup:

Buildable = DLookup("UnitsOpen", "SQ_ProdUnitsBuildable", strWhere)

 

[pr2-eugin]

If you had used the code I have in post#16 you should have atleast got a 0 in msgbox.. Could you copy the code as such and try again please..