Msg Box pop up based on certain criteria

[rpk740]

Access 2000 Database using VBA. Windows XP SP2.
English.

I have a form which contains some bits of information. Item Number, Purchase Cost, Standard Cost, and a Purchase Order Number.

In the event that there is a Purchase Cost entered which is > then 0 but the Standard = 0, I would like a popup to display which halts the process until a Standard gets entered.

I am not a coder but I can usually cobble the code together through some trial and error. This one has me stuck but I feel it is something simple.

For the time being, while testing this I have a button which executes the code when clicked. Right now nothing happens when it clicks despite the error conditions being present. No errors, which tells me that something is incomplete.

Here is the code:

Private Sub Command14_Click()
Dim item_no As String
Dim loc As String
Dim std_cost As Double
Dim sql_s As String
Dim db As dao.Database
Dim rst As dao.Recordset
Dim sqlstr As String
Dim ord_no As String
Dim exp_unit_cost As Double
Dim right_ord_no As String
Dim stk_loc As String
Dim right_val As Double
Dim exp_val As Double
Dim std_val As Double
 
Set db = CurrentDb()
 
sqlstr = "SELECT item_no, loc, std_cost FROM [dbo_iminvloc_sql] WHERE item_no = '" & Me![item_no] & "'"
 
Set rst = db.OpenRecordset(sqlstr)
 
ord_no = Me![ord_no]
right_ord_no = Right(Me![ord_no], 2)
exp_unit_cost = Me![exp_unit_cost]
std_cost = rst![std_cost]
 
 
If right_ord_no = "00" Then right_val = 1
 
If expected_unit_cost > 0 Then exp_val = 1
 
If std_cost = 0 Then std_val = 1
 
If right_val + exp_val + std_val = 3 Then
MsgBox "No Standard Exists for this item. Please have a standard entered." & vbNewLine & vbNewLine & _
        "Quitting...", vbCritical, "E-Mail Merger"
 
 
 
End If
End Sub
 

[MMcCarthy]

Try this instead ...

Private Sub Command14_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim sqlstr As String
Dim right_val As Integer
Dim exp_val As Integer
Dim std_val As Integer
 
    Set db = CurrentDb()
 
    sqlstr = "SELECT item_no, loc, std_cost FROM [dbo_iminvloc_sql] WHERE item_no = '" & Me![item_no] & "'"
 
    Set rst = db.OpenRecordset(sqlstr)
 
    rst.MoveFirst
    If Right(Me![ord_no], 2) = "00" Then right_val = 1
    If Me![exp_unit_cost] > 0 Then exp_val = 1
    If rst![std_cost] = 0 Then std_val = 1
    If right_val + exp_val + std_val = 3 Then
        MsgBox "No Standard Exists for this item. Please have a standard entered." & vbNewLine & vbNewLine & _
        "Quitting...", vbCritical, "E-Mail Merger"
    End If
 
    rst.Close
    Set rst = Nothing
 
End Sub