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

INSERT or UPDATE Procedure Help and Advice!

100+
P: 129
I am working on an automatic Purchase Order Generator where with a click of a button will automatically assign a PONo to certain stock items (based on the SuppNo). The coding i have at present is as follows:

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 IsNull(txtSuppNo) Then
  15.         MsgBox "You CANNOT Assign an Automatic Purchase Order when there are NO SUPPLIER Or ORDER Details.", vbOKOnly, "Criteria Missing"
  16.     Else
  17.     rst.Edit
  18.     strSQL = "INSERT INTO pordhdr ([SuppNo],[PODate],[DueDate]) SELECT forms!frmPOGenerator!txtSuppNo AS SuppNo,[OrderDate],[DueDate] FROM ordhdr WHERE ordhdr.OrderNo = forms!frmPOGenerator!txtOrderNo"
  19.     DoCmd.RunSQL strSQL
  20.     rst.update
  21.     rst.Close
  22.  
  23.     Set rst3 = db.OpenRecordset("SELECT * FROM pordhdr")
  24.  
  25.     rst3.MoveLast
  26.         PO = MsgBox("Purchase Order " & rst3!PONo & " Has Been Created. ", vbOKOnly, "Purchase Order Created")
  27.     rst3.Close
  28.  
  29.     Set rst3 = Nothing
  30.  
  31.     Set rst3 = CurrentDb.OpenRecordset("SELECT * FROM pordhdr")
  32.     rst3.MoveLast
  33.  
  34.     Set rst2 = db.OpenRecordset("SELECT * FROM ordlin")
  35.  
  36.     rst2.Edit
  37.     strSQL = "UPDATE ordlin SET [PONo] = '" & rst3!PONo & "' WHERE ([ordlin.OrderNo] = [forms]![frmPOGenerator]![txtOrderNo] And [ordlin.SuppNo] = [forms]![frmPOGenerator]![txtSuppNo])"
  38.     DoCmd.RunSQL strSQL
  39.     rst2.update
  40.     rst2.Close
  41.     rst3.Close
  42.  
  43.     Set rst2 = Nothing
  44.     Set rst3 = Nothing
  45.  
  46.     DoCmd.SetWarnings True
  47.  
  48.     lstSuppNoSelect.Requery
  49.     End If
I have two problems; one with the coding and two with the attached screenshot i have provided to you. First the coding problem, when i assign the PONo to all stock items with the same OrderNo and SuppNo, the PONo only gets assign to most but not all stock items. For this i dont know why it does this and was hoping maybe there was a flaw in my code. This method is attached to a button called 'Generate' which can be seen in the picture.

My next problem that once the PONo finally assigns itself to all relevant stock items, if another stock item needs to be added i require a button called 'Merge' where it looks at the SuppNo and assigns the New stock item(s) to the same PONo as the rest. I am not fully sure how to do did as the details are located on another row (See picture). I am also not sure if i require an INSERT or UPDATE statement of even if it would work.

Any help or advice on this matter would greatly be appreciated?

Thanks
Attached Images
File Type: bmp Untitled.bmp (19.2 KB, 114 views)
Sep 7 '08 #1
Share this Question
Share on Google+
4 Replies


ADezii
Expert 5K+
P: 8,619
To be perfectly honest with you, I feel as though the entire code block needs to be overhauled, and that you should start from scratch. In my opinion, you will always have major problems with your current logic. You are creating Recordsets which need not be created, running Update and Append operations within the confines of those Recordsets via SQL Statements, the SQL Statement Syntax in certain areas is not correct, the code in general needs to be rewritten. Since there are no Comments, it is not exactly clear what you are trying to accomplish. Forgive me if I seem over critical, but I am just attempting to save you problems down the line. I'll call some other Moderators/Experts in on this, and see what their opinion is on the matter. Kindly be patient and see what they have to say.
Sep 7 '08 #2

100+
P: 129
To be perfectly honest with you, I feel as though the entire code block needs to be overhauled, and that you should start from scratch. In my opinion, you will always have major problems with your current logic. You are creating Recordsets which need not be created, running Update and Append operations within the confines of those Recordsets via SQL Statements, the SQL Statement Syntax in certain areas is not correct, the code in general needs to be rewritten. Since there are no Comments, it is not exactly clear what you are trying to accomplish. Forgive me if I seem over critical, but I am just attempting to save you problems down the line. I'll call some other Moderators/Experts in on this, and see what their opinion is on the matter. Kindly be patient and see what they have to say.
Here is the code with Comments, so you may understand what i was doing!

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 SuppNo is NULL then no PONo can be Assigned to the Stock Details
  15.     'Else INSERT INTO pordhdr the SuppNo, PODate and DueDate to create a PONo
  16.  
  17.     If IsNull(txtSuppNo) Then
  18.         MsgBox "You CANNOT Assign an Automatic Purchase Order when there are NO SUPPLIER Or ORDER Details.", vbOKOnly, "Criteria Missing"
  19.     Else
  20.     rst.Edit
  21.     strSQL = "INSERT INTO pordhdr ([SuppNo],[PODate],[DueDate]) SELECT forms!frmPOGenerator!txtSuppNo AS SuppNo,[OrderDate],[DueDate] FROM ordhdr WHERE ordhdr.OrderNo = forms!frmPOGenerator!txtOrderNo"
  22.     DoCmd.RunSQL strSQL
  23.     rst.update
  24.     rst.Close
  25.  
  26.     Set rst3 = db.OpenRecordset("SELECT * FROM pordhdr")
  27.  
  28.     'Find the NEW PONo just Assigned to the pordhdr
  29.     'Display Message with PONo Created
  30.  
  31.     rst3.MoveLast
  32.         PO = MsgBox("Purchase Order " & rst3!PONo & " Has Been Created. ", vbOKOnly, "Purchase Order Created")
  33.     rst3.Close
  34.  
  35.     Set rst3 = Nothing
  36.  
  37.     'Find the Last PONo within the pordhdr
  38.  
  39.     Set rst3 = CurrentDb.OpenRecordset("SELECT * FROM pordhdr")
  40.     rst3.MoveLast
  41.  
  42.     Set rst2 = db.OpenRecordset("SELECT * FROM ordlin")
  43.  
  44.     '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
  45.  
  46.     rst2.Edit
  47.     strSQL = "UPDATE ordlin SET [PONo] = '" & rst3!PONo & "' WHERE ([ordlin.OrderNo] = [forms]![frmPOGenerator]![txtOrderNo] And [ordlin.SuppNo] = [forms]![frmPOGenerator]![txtSuppNo])"
  48.     DoCmd.RunSQL strSQL
  49.     rst2.update
  50.     rst2.Close
  51.     rst3.Close
  52.  
  53.     Set rst2 = Nothing
  54.     Set rst3 = Nothing
  55.  
  56.     DoCmd.SetWarnings True
  57.  
  58.     'Refresh the List Box
  59.  
  60.     lstSuppNoSelect.Requery
  61.  
  62.     End If
Sep 7 '08 #3

FishVal
Expert 2.5K+
P: 2,653
Hello, Constantine.

I quite agree with ADezii. Your code really looks unreasonably kludgy.
The logic is too uncertain, so if you even get it work, it is expected to crash from too many reasons.

Frankly speaking, I would not try to repair the code, but let him RIP and start from scratch. Even before - building clear and inambigious program logic. ;)

If you want to discuss the ways to get a robust and effective solution you should at least:
  • post metadata of all datasets involved.
    Here is an example of how to post table MetaData :
    Table Name=tblStudent
    Expand|Select|Wrap|Line Numbers
    1. Field; Type; IndexInfo
    2. StudentID; AutoNumber; PK
    3. Family; String; FK
    4. Name; String
    5. University; String; FK
    6. Mark; Numeric
    7. LastAttendance; Date/Time
  • specify buisiness logic and of desired action and program logic as you see it so far.

Kind regards,
Fish
Sep 7 '08 #4

Expert Mod 2.5K+
P: 2,545
Hi Constantine Al. I looked at this yesterday, left it, came back to it, and like my colleagues on the forum I'm puzzled by it. Normally, purchase order references are created before the stock items for the purchase order are selected - for the simple reason that there is a one-to-many relationship between purchase order and order line.

Most applications I have seen (and my own too) implement this using a form and subform - the main form for the order where the supplier details are selected and the normal other order items are filled in (e.g. delivery address), and the subform for the order lines. The main-form to subform parent-child links take care of assigning the order reference to each stock line.

Working in VBA code has particular downsides. One of which is that the code has to be bullet-proof, and it is impossible to guarantee that such code is free from error. At least when using the built-in functionality you can guarantee that the functionality has been tested by tens of thousands of users over a combined span of many hundreds of person-years; the resources required to do this for our own home-written code just don't exist to any extent, so using a code-based solution to implement what Access can already do without code (in a different way) is not necessarily a good idea.

Just some observations on my part.

I mention this because I discovered a simple bug in one of my own class modules which has been thoroughly tested over the past year, in a routine to transfer data to Excel. The bug was the declaration of an integer variable as a record counter instead of a long. It did not show up in all the testing I did - but working on live data, on one Excel sheet in particular out of more than 300, an overflow error arose. Tracing it took some time, but to cut this long story short in just this one sheet were there more than 32,767 records to be transferred (the limit of the value of an integer variable). The counter could not hold the value - hence the overflow error. Why I defined it as integer instead of long I don't know. OK, this is not your situation - but I was confident my code was correct (ha!), and over the course of a full year I had output around 20,000 worksheets in total without error - until this particular situation arose.

Anyhow, like my colleagues I would suggest doing it differently, particularly in view of your problems with apparent errors that are difficult to explain.

-Stewart
Sep 8 '08 #5

Post your reply

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