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?
