CODEWITHSUNDEEP
ms-accessvbams-access-2010
Rekar
Rekar

Reputation: 495

Ms Access 2010 DMax from another table (MS Access cant find the field '|1' referred to in your expression)

I have been trying to get Dmax, if that is the proper method, to return the max value in the column. The form has a control that checks to validate the entered value in the On Change event. The event should check another table (Material PO Datasheet) to grab the max value. If the value in the textbox(PO_NUM) is less than the Max value of the other table, a message box pops up informing of the situation and undo's the input into the box. However, I am stuck on the Dmax line.

Private Sub PO_Num_Change()

Dim MaxNum As Long
DoCmd.OpenTable "MATERIAL PO DATASHEET"

MaxNum = DMax([MATERIAL PO DATASHEET]![PO NUMBER], [MATERIAL PO DATASHEET])

If Me.PO_Num.Value <= MaxNum Then
    strMsg = "This PO Exists!"
        If MsgBox(strMsg, vbExclamation, "Confirm") = vbOK Then
            Me.Undo
        Else
            Me.Undo
        End If

End If
DoCmd.Close , "MATERIAL PO DATASHEET"
End Sub

The error I receive is "Runtime Error 2465: MS Access cant find the field '|1' referred to in your expression". This error made me think that something may have been mistyped or something, but all the fields seem to match. The table is opened on the DoCmd line(no issues), and stops at DMax.

I have also tried:

MaxNum = DMax([PO NUMBER], "MATERIAL PO DATASHEET")

and 

MaxNum = DMax([PO NUMBER], [MATERIAL PO DATASHEET])

I appreciate any feedback,

Thank you,

Michael

Upvotes: 0

Views: 191

Answers (1)

Doug Coats
Doug Coats

Reputation: 7117

Private Sub PO_Num_Change()
    Dim MaxNum As Long
    MaxNum = DMax("[PO NUMBER]", "[MATERIAL PO DATASHEET]")
    If Me.PO_Num.Value <= MaxNum Then
        strMsg = "This PO Exists!"
        If MsgBox(strMsg, vbExclamation, "Confirm") = vbOK Then
            Me.Undo
        Else
            Me.Undo
        End If
    End If
End Sub

All parameters of Dxxxx functions need to be in quotes

Alternative way to write this (without unnecessary variables)

Private Sub PO_Num_Change()
    If Me.PO_Num.Value <= DMax("[PO NUMBER]", "[MATERIAL PO DATASHEET]") Then
        strMsg = "This PO Exists!"
        If MsgBox(strMsg, vbExclamation, "Confirm") = vbOK Then
            Me.Undo
        Else
            Me.Undo
        End If
    End If
End Sub

Also I think this might even be more simplistic

 IF IsNull(DLookup("[PO NUMBER]", "[MATERIAL PO DATASHEET]", "[PO Number]=" & Me.PO_Num))= False Then
     MsgBox "Found it"
 Else
     'Nothing
 END if

Upvotes: 1

Related Questions