Recordset problem(s)

[bbrazeau]

I have attached my database. I have a command button "Update_Inventory"on the Sales_Orders form that I would like to have subtract the order qty for each part used in the order from a field called Parts_In_Stock. If you look at the VBA code for this command button you will see what I have so far. If you click "Update_Inventory" a message box displays the correct amount and Part_ID to subtract from, however it cycles thru all the records and doesn't actually make any changes.I think I can get the code to make the change using .edit on the appropriate field(I may have to include it in the query) and I plan to put a check box field in the Orders table that will disable the command button if the order has already been applied to inventory. Could someone help me determine an SQL clause I could use to restrict the Recordset to the current Sales_Order record? Also, Addmittedly I'm not extreemly well versed in Access so if I'm just way off base with the entire concept clue me in please?? Thank You.

[June7]

Ideally, you would not 'update inventory'. Conventional wisdom is 'enter raw data, do calcs in reports'. This would mean entering transactions of incoming (purchases) and outgoing (sales) products. Then in a report would summarize each set of transactions by product and subtract to determine product status.

[bbrazeau]

I know that there are pitfalls to the method I'm using, but I would still like to work through this exercise. I have attached the SQL clause I'm trying to use. I get an error..."the SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect". Following is my code. the debugger hangs on this line. Could someone please help?

"Set rstUpDateInventory = dbsHT1.OpenRecordset(strSQL)"

 
Private Sub Update_Inventory_Click()
Dim px As Integer
Dim p As Integer
Dim intI As Integer
Dim strSQL As String 'SQL statement
Dim dbsHT1 As DAO.Database
Dim rstUpDateInventory As DAO.Recordset
Set dbsHT1 = CurrentDb
strSQL = "SELECT  Orders_ID, Product_ID, Qty_Ordered,Part_ID, Part_Qty_Req, FROM [Orders_Details Query] WHERE (Orders_ID = [Orders_Details Query].Orders_ID );"

'Set rstUpDateInventory = dbsHT1.OpenRecordset("Orders_Details Query") 'How can I limit Recordset to this Orders Details?
Set rstUpDateInventory = dbsHT1.OpenRecordset(strSQL)
  'If the recordset is empty, exit.
   If rstUpDateInventory.EOF Then Exit Sub
   
 intI = 1
   With rstUpDateInventory
        Do Until .EOF
            px = ![Qty_Ordered] * ![Part_Qty_Req]
            p = ![Part_ID]
            MsgBox ("Subtract " & px & " from Part_ID  " & p) 'How do I perform this action
            .MoveNext
            intI = intI + 1
        Loop
   End With
   rstUpDateInventory.Close
   dbsHT1.Close
   Set rstUpDateInventory = Nothing
   Set dbsHT1 = Nothing
       
End Sub

[June7]

There is an extra comma before FROM.

The WHERE clause is odd. It references itself for the filter criteria. What are you trying to do? If you want to limit the recordset to a single Orders_ID, must pass to it a parameter. This can be by reference to a control on a form where user inputs the criteria.

[bbrazeau]

Thank you June7. As I addmitted I'm no access guru more like a rank novice. I removed the comma and the error was gone. I added a variable, set it to the current records Orders_ID and used the variable in the "WHERE" part of my select statement(see SQL below). I know that I may not be approaching this the correct way, but in my research I read Allen Brownes tutorial about "Inventory control" and my hope is that I will be able to make my method work. I could not easily determine how to make his example work with assemblies. Mine does. I plan to add a check to the code that won't allow the button to be used more than once, and I'm not going to allow users to edit the "Parts_In_Stock" field. If my "Parts_In_Stock" field somehow develops errors, I will go in and adjust the value, which is the same as a "Stock Take" table Allen describes. I think?? I'm not done yet. Still need to change the values and add the check to make sure the button only gets used once. Again thank you very much!

 
strSQL = "SELECT Orders_ID, Product_ID, Qty_Ordered,Part_ID, Part_Qty_Req FROM [Orders_Details Query] WHERE ( " & O_ID & " =[Orders_Details Query].Orders_ID );"

[bbrazeau]

Updated SQL statement: This is less complicated! Took me awhile to figure it out. As I said I'm no access guru

 
strSQL = "SELECT  * FROM [Orders_Details Query] WHERE ( " & O_ID & " =[Orders_Details Query].Orders_ID  );"

[bbrazeau]

I now have another problem.When I go to ".Edit" the recordset, I get an error saying "Cannot update. Database or object is read only." The debugger hangs on the ".Edit" line. I opened the recordset as "dbOpenDynaset" shouldn't this allow edits?

 
Private Sub Update_Inventory_Click()
Dim px As Integer
Dim p As Integer
Dim intI As Integer
Dim O_ID As Long
Dim strSQL As String 'SQL statement
O_ID = Me.Orders_ID
Dim dbsHT1 As DAO.Database
Dim rstUpDateInventory As DAO.Recordset
 
Set dbsHT1 = CurrentDb
'strSQL = "SELECT Orders_ID, Product_ID, Qty_Ordered,Part_ID, Part_Qty_Req FROM [Orders_Details Query] WHERE ( " & O_ID & " =[Orders_Details Query].Orders_ID );"
strSQL = "SELECT * FROM [Orders_Details Query] WHERE ( " & O_ID & " =[Orders_Details Query].Orders_ID );"
 
Set rstUpDateInventory = dbsHT1.OpenRecordset(strSQL, dbOpenDynaset)
'If the recordset is empty, exit.
If rstUpDateInventory.EOF Then Exit Sub
 
intI = 1
With rstUpDateInventory
Do Until .EOF
px = ![Qty_Ordered] * ![Part_Qty_Req]
p = ![Part_ID]
px = ![Parts_In_Stock] - px
.Edit
![Parts_In_Stock] = px
.Update
MsgBox ("After this parts in stock should be " & px) 'How do I perform this action
.MoveNext
intI = intI + 1
Loop
End With
rstUpDateInventory.Close
dbsHT1.Close
Set rstUpDateInventory = Nothing
Set dbsHT1 = Nothing
 
End Sub

[June7]

According to the http://msdn.microsoft.com/en-us/libr...ffice.12).aspx dbOpenDynaset isn't needed.

MsgBox has two forms: a function and a simple popup. The function is structured with parens and must be part of an expression. Example:
intResponse = MsgBox("Do you want to continue", vbYesNo)

Simple popup:
MsgBox "After this parts in stock should be " & px

[bbrazeau]

Thank you June7. I am aware that my Msgbox code does not change any records, I was just using it to check some of the values I was getting.
I think I may have a ceated a recordset that is not updatable??? Is there such a thing? My recordset is from a subform query if that makes a difference. I've been reading, a bit and have a question... Could it be that I can't update the field value I want to update because it is taken from a table and brougth intop a query??? If anyone has heard of this please let me know. Thanks. P.S. My database is attached to a previous post in this thread

[June7]

It is possible the query is not updatable and that is issue. If it were an updatable query, building recordset would be okay. Why are you building recordset from query and not table anyway?

I know MsgBox does not change records, I did not suggest it would. My comments about the MsgBox are in response to your question in the procedure code. As is, that MsgBox should error.

[bbrazeau]

Hi June7, I used the query for the recordset because it brought together the fields I needed (from Products,Product_Details,Parts,Orders, and Order_details tables)to step thru the Order line by line and retreive quantities of individual parts in the order. On My form the Orders_Details_Query_subform is visible, but I was planning on hidding it once the kinks are worked out. The msgbox I comment in and out to toggle checking the information my code returns.I guess I should learn to use Debug.Print instead. Could I build a recordset that would return the correct fields from the tables I need? I need to get to the Part_ID, Part_Qty_Req, and Qty_Ordered for each Orders_Details_ID used in an Order. I have attached my DB again.

[June7]

Cannot have all these fields in an updatable query. You can have the product and quantity selected in one subform and then show the calculation of the component parts in other subform.

Why are you doing a lookup on the Order_ID field to show Order Date in the Orders_Details table and in the subform? There is no reason to repeat the order ID or the order date in the subform.

See attached mod of your project. Changing the Qty_Ordered is immediately reflected in the calcs, but selecting/changing product is erratic. Not sure how to solve. Maybe code to force requery of the subform. I deleted stuff to get file size under 500kb. I will remove the file after you respond or in a few days.

EDIT: purpose served, file removed

[bbrazeau]

Hi June7, I think I got it.(see code below) I called a function (UpdateInStock) in my proceedure (Update_Inventory_Click) and passed it 2 arguments. Seems to work. Now I want to add a checkbox control to Orders and use it to limit the Update_Inventory_Click to a one time deal. Thank you for your help. If you'd like me to post the whole DB just let me know.

 
Private Sub Update_Inventory_Click()
Dim px As Integer
Dim p As Integer
Dim intI As Integer
Dim O_ID As Long
Dim strSQL As String 'SQL statement
O_ID = Me.Orders_ID
Dim dbsHT1 As Dao.Database
Dim rstUpDateInventory As Dao.Recordset
Dim rstParts_In_Stock As Dao.Recordset

Set dbsHT1 = CurrentDb
strSQL = "SELECT  * FROM [Orders_Details Query] WHERE ( " & O_ID & " =[Orders_Details Query].Orders_ID  );"

Set rstUpDateInventory = dbsHT1.OpenRecordset(strSQL, dbOpenDynaset)
  'If the recordset is empty, exit.
   If rstUpDateInventory.EOF Then Exit Sub
   
 intI = 1
   With rstUpDateInventory
        Do Until .EOF
            px = ![Qty_Ordered] * ![Part_Qty_Req]
            p = ![Part_ID]
            px = ![Parts_In_Stock] - px
                UpdateInStock p, px 'Function call passing Part_ID and Adjusted Parts_In_Stock
            .MoveNext
            intI = intI + 1
        Loop
   End With
   rstUpDateInventory.Close
   dbsHT1.Close
   Set rstUpDateInventory = Nothing
   Set dbsHT1 = Nothing
       
End Sub
Public Function UpdateInStock(P_ID As Integer, P_Amt As Integer)
Dim ppx, pp As Integer
Dim dbsHT2 As Dao.Database
Dim rstParts_In_Stock As Dao.Recordset
ppx = P_Amt
pp = P_ID
Set dbsHT2 = CurrentDb
Set rstParts_In_Stock = dbsHT2.OpenRecordset("Parts", dbOpenTable)
    With rstParts_In_Stock
        .Index = "PrimaryKey"
        .Seek "=", pp
        .Edit
        ![Parts_In_Stock] = ppx
        .Update
        rstParts_In_Stock.Close
        dbsHT2.Close
    End With
Set rstParts_In_Stock = Nothing
Set dbsHT2 = Nothing
End Function

[bbrazeau]

Hi June7, I wonder if I might ask for one more favor? I have attached my DB and have updated it to limit the "UpDateInventory" button on the "Sales_Orders" form to one time use. I also used "Environ("USERNAME")" which I found in one of your other posts, to populate an uneditable text field in the record. I tested it with the first record and it seems to work. I would like someone to press the button on the 2nd record and see if it works. The DB can be tested more times if more Sales Orders are added and you can also open the Orders table and set the checkboxes to false, and delete the strings in the "Order_Closed_By" field to reset the records. Thanks again for your help. Any opinions or insights by anyone on this DB would also be appreciated

[June7]

I pressed the button and the record updated in Orders table. I didn't think to check the Parts stock quantity before so don't know if that went correctly.