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!

Expand|Select|Wrap|Line Numbers

 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?

Attached Images

untitled.bmp (17.2 KB, 125 views)

puppydogbuddy

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

Expand|Select|Wrap|Line Numbers

 
    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.

Expand|Select|Wrap|Line Numbers

    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:

Expand|Select|Wrap|Line Numbers

 
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:

Expand|Select|Wrap|Line Numbers

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.

Creating Error Procedures for Grouped Data

Post your reply

Similar topics