By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,855 Members | 1,988 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,855 IT Pros & Developers. It's quick & easy.

Creating Error Procedures for Grouped Data

100+
P: 129
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
  1. Dim db As Database
  2.     Dim rst As Recordset
  3.     Dim rst2 As Recordset
  4.     Dim rst3 As Recordset
  5.     Dim IntPONo As Integer
  6.     Dim strSQL As String
  7.     Dim PO As String
  8.  
  9.     DoCmd.SetWarnings False
  10.  
  11.     Set db = CurrentDb()
  12.     Set rst = db.OpenRecordset("SELECT * FROM pordhdr")
  13.  
  14.     'If the PONo is NULL then you can not Assign to the Stock Details
  15.     'Else INSERT INTO pordhdr the SuppNo, PODate and DueDate to create a PONo
  16.  
  17.     If [txtPONo] > 0 Then
  18.         MsgBox "You CANNOT GENERATE Another Purchase Order to this Order when ONE ALREADY EXISTS!", vbCritical, "Purchase Order Creation Error!"
  19.         Exit Sub
  20.  
  21.     Else
  22.  
  23.     rst.Edit
  24.     strSQL = "INSERT INTO pordhdr ([SuppNo],[PODate],[DueDate]) SELECT forms!frmPOGenerator!txtSuppNo AS SuppNo,[OrderDate],[DueDate] FROM ordhdr WHERE ordhdr.OrderNo = forms!frmPOGenerator!txtOrderNo"
  25.     DoCmd.RunSQL strSQL
  26.     rst.update
  27.     rst.Close
  28.  
  29.     Set rst3 = db.OpenRecordset("SELECT * FROM pordhdr")
  30.  
  31.     'Find the NEW PONo just Assigned to the pordhdr
  32.     'Display Message with PONo Created
  33.  
  34.     rst3.MoveLast
  35.         PO = MsgBox("Purchase Order " & rst3!PONo & " Has Been Created. ", vbOKOnly, "Purchase Order Created")
  36.     rst3.Close
  37.  
  38.     Set rst3 = Nothing
  39.  
  40.     'Find the Last PONo within the pordhdr
  41.  
  42.     Set rst3 = CurrentDb.OpenRecordset("SELECT * FROM pordhdr")
  43.     rst3.MoveLast
  44.  
  45.     Set rst2 = db.OpenRecordset("SELECT * FROM ordlin")
  46.  
  47.     '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
  48.  
  49.     rst2.Edit
  50.     strSQL = "UPDATE ordlin SET [PONo] = '" & rst3!PONo & "' WHERE ([ordlin.OrderNo] = [forms]![frmPOGenerator]![txtOrderNo] And [ordlin.SuppNo] = [forms]![frmPOGenerator]![txtSuppNo] AND ((ordlin.PONo) Is Null))"
  51.     DoCmd.RunSQL strSQL
  52.     rst2.update
  53.     rst2.Close
  54.     rst3.Close
  55.  
  56.     Set rst2 = Nothing
  57.     Set rst3 = Nothing
  58.  
  59.     DoCmd.SetWarnings True
  60.  
  61.     'Refresh the List Box
  62.  
  63.     lstSuppNoSelect.Requery
  64.  
  65.     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
File Type: bmp untitled.bmp (17.2 KB, 101 views)
Oct 2 '08 #1
Share this Question
Share on Google+
4 Replies


puppydogbuddy
Expert 100+
P: 1,923
Try changing your first 4 lines of code to the following and see what happens.
Expand|Select|Wrap|Line Numbers
  1.     Dim db As DAO.Database 
  2.     Dim rst As DAO.Recordset 
  3.     Dim rst2 As DAO.Recordset 
  4.     Dim rst3 As DAO.Recordset 
Oct 3 '08 #2

100+
P: 129
Try changing your first 4 lines of code to the following and see what happens.
Expand|Select|Wrap|Line Numbers
  1.     Dim db As DAO.Database 
  2.     Dim rst As DAO.Recordset 
  3.     Dim rst2 As DAO.Recordset 
  4.     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.
Oct 3 '08 #3

puppydogbuddy
Expert 100+
P: 1,923
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
  1. If [txtPONo] > 0 Then 
  2.  MsgBox "You CANNOT GENERATE Another Purchase Order to this Order when ONE ALREADY EXISTS!", vbCritical, "Purchase Order Creation Error!" 
  3.             Exit Sub 
  4.  
  5. ElseIf  IsNull(PONo) Then
  6.     MsgBox " The PO Number is missing. Can't assign stock details."   
  7.     Exit Sub
  8. Else 
Oct 3 '08 #4

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

Expand|Select|Wrap|Line Numbers
  1. If [txtPONo] > 0 Then 
  2.  MsgBox "You CANNOT GENERATE Another Purchase Order to this Order when ONE ALREADY EXISTS!", vbCritical, "Purchase Order Creation Error!" 
  3.             Exit Sub 
  4.  
  5. ElseIf  IsNull(PONo) Then
  6.     MsgBox " The PO Number is missing. Can't assign stock details."   
  7.     Exit Sub
  8. Else 
Yeah thanks that worked a treat.
Oct 3 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.