Creating Error Procedures for Grouped Data

[Constantine AI]

Hi I am trying to input some error messages into my system. I have come across a slight problem with one of them. i have a query which filters and groups data together displaying details on a form. I then have a button called "Generate" which looks up the last entry from a particular table to assign a Purchase number to the grouped record. This way its assigns the value to all relevant records. This works by the way! However when i tried to incorporate error procedures into the code i receive type mismatch or false error. The field i generate and base my code on is a Number field. Here is my coding!

Dim db As Database
    Dim rst As Recordset
    Dim rst2 As Recordset
    Dim rst3 As Recordset
    Dim IntPONo As Integer
    Dim strSQL As String
    Dim PO As String
 
    DoCmd.SetWarnings False
 
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("SELECT * FROM pordhdr")
 
    'If the PONo is NULL then you can not Assign to the Stock Details
    'Else INSERT INTO pordhdr the SuppNo, PODate and DueDate to create a PONo
 
     If [txtPONo] > 0 Then
        MsgBox "You CANNOT GENERATE Another Purchase Order to this Order when ONE ALREADY EXISTS!", vbCritical, "Purchase Order Creation Error!"
        Exit Sub
 
    Else
 
    rst.Edit
    strSQL = "INSERT INTO pordhdr ([SuppNo],[PODate],[DueDate]) SELECT forms!frmPOGenerator!txtSuppNo AS SuppNo,[OrderDate],[DueDate] FROM ordhdr WHERE ordhdr.OrderNo = forms!frmPOGenerator!txtOrderNo"
    DoCmd.RunSQL strSQL
    rst.update
    rst.Close
 
    Set rst3 = db.OpenRecordset("SELECT * FROM pordhdr")
 
    'Find the NEW PONo just Assigned to the pordhdr
    'Display Message with PONo Created
 
    rst3.MoveLast
        PO = MsgBox("Purchase Order " & rst3!PONo & " Has Been Created. ", vbOKOnly, "Purchase Order Created")
    rst3.Close
 
    Set rst3 = Nothing
 
    'Find the Last PONo within the pordhdr
 
    Set rst3 = CurrentDb.OpenRecordset("SELECT * FROM pordhdr")
    rst3.MoveLast
 
    Set rst2 = db.OpenRecordset("SELECT * FROM ordlin")
 
    'UPDATE the PONo Field (Based on SuppNo and OrderNo with the last PONo above) within the ordlin Table therefore Assigning the Stock Details with a Purchase Order
 
    rst2.Edit
    strSQL = "UPDATE ordlin SET [PONo] = '" & rst3!PONo & "' WHERE ([ordlin.OrderNo] = [forms]![frmPOGenerator]![txtOrderNo] And [ordlin.SuppNo] = [forms]![frmPOGenerator]![txtSuppNo] AND ((ordlin.PONo) Is Null))"
    DoCmd.RunSQL strSQL
    rst2.update
    rst2.Close
    rst3.Close
 
    Set rst2 = Nothing
    Set rst3 = Nothing
 
    DoCmd.SetWarnings True
 
    'Refresh the List Box
 
    lstSuppNoSelect.Requery
 
     End If

From the Image where there is a NULL entry i want the main code to work and if they click on an entry where data is present then do nothing.

Ignore the main detail of code as that is fine, please just look at the if statement and the screenshot, any advice would be much appreciated?

[puppydogbuddy]

Try changing your first 4 lines of code to the following and see what happens.

    Dim db As DAO.Database 
    Dim rst As DAO.Recordset 
    Dim rst2 As DAO.Recordset 
    Dim rst3 As DAO.Recordset 

[Constantine AI]

Try changing your first 4 lines of code to the following and see what happens.

    Dim db As DAO.Database 
    Dim rst As DAO.Recordset 
    Dim rst2 As DAO.Recordset 
    Dim rst3 As DAO.Recordset 

Nope no luck it brings up the error message for both circumstances instead of one of them. It is as if the grouped data with NO PONo assigned to them are taking space with something causing the error to run. The record is blank though as you can see from the screenshot.

[puppydogbuddy]

Nope no luck it brings up the error message for both circumstances instead of one of them. It is as if the grouped data with NO PONo assigned to them are taking space with something causing the error to run. The record is blank though as you can see from the screenshot.

In addition to the above changes, change lines 15 thru 21 of your code to the following:

If [txtPONo] > 0 Then 
 MsgBox "You CANNOT GENERATE Another Purchase Order to this Order when ONE ALREADY EXISTS!", vbCritical, "Purchase Order Creation Error!" 
            Exit Sub 
 
ElseIf  IsNull(PONo) Then
    MsgBox " The PO Number is missing. Can't assign stock details."   
    Exit Sub
Else 

[Constantine AI]

In addition to the above changes, change lines 15 thru 21 of your code to the following:

If [txtPONo] > 0 Then 
 MsgBox "You CANNOT GENERATE Another Purchase Order to this Order when ONE ALREADY EXISTS!", vbCritical, "Purchase Order Creation Error!" 
            Exit Sub 
 
ElseIf  IsNull(PONo) Then
    MsgBox " The PO Number is missing. Can't assign stock details."   
    Exit Sub
Else 

Yeah thanks that worked a treat.