ShippingBox

Option Compare Database
Option Explicit

Private Sub Save_Click()

Dim CurrentConcat As String
Dim PastConcat As String
Dim PastConcatGrab As Boolean
Dim txtbox As String


Dim PastPrice As Currency
Dim ExpPrice As Currency


CurrentConcat = Me!CurrentConcat 'Set current Concatenated ID to the ID on the form

PastConcatGrab = IsNull(DLookup("[CalConcatID]", "qryBoxHistory", "[CalConcatID] = CurrentConcat"))

    If PastConcatGrab = True Then 'If lookup shows that no boxes have been entered under that ID then a message is displayed and the values saved
        txtbox = MsgBox("This is a new box, no past prices exist", vbOKOnly, "Information")
        DoCmd.RunCommand acCmdSaveRecord
    Else
        PastConcat = DLookup("[CalConcatID]", "qryBoxHistory", "[CalConcatID] = CurrentConcat")
        PastPrice = DMin([qryBoxHistory]![Price Each], "qryBoxHistory", "[CalConcatID] = 133377")
        ExpPrice = Me![Price]
        If ExpPrice > PastPrice Then
            txtbox = MsgBox("This Box is not being supplied at a discounted rate, box has NOT been saved", vbOKOnly, "Information")
        Else
            txtbox = MsgBox("This box is discounted, This price has been saved", vbOKOnly, "Information")
        End If
End If
End Sub