update allocation alltogether (1 Viewer)

[rainbows]

THE form below shows the items from a product that done need to be purchased because there is enough in stock . but this form allows me to update the allocation for that part . so for example the reqqty for stock number 3096B0010 for this product is 68.8 therefore when i press the update allocation button it will update the alloaction to 425.52 as indicated in the yellow field

what i would like to do is press the update allocation button once and it updates ALL the records at the same time and shows me the results and then i close the form down

the code below i think is a mixture and just needs modifying to suit but i dont know how. any help appreicated

thanks steve

Private Sub cmdUpdate_Click()

10        On Error GoTo cmdUpdate_Click_Error
      '      Dim strSQL As String
      '      Dim rs As Object
      '20    Set rs = Me.RecordsetClone
      '30    With rs
      '40    Do While Not .EOF
      '      'Do Something
      '
      '50      strSQL = "UPDATE StockList SET Allocation = " & Me.txtAllocation _
      '        & " WHERE StockNumber = '" & Me.StockNumber & "';"
      '        Debug.Print strSQL
      '        CurrentDb.Execute strSQL, dbFailOnError
      '60    .MoveNext
      '70    Loop
      '80    End With
      '90    Set rs = Nothing
      '100   MsgBox "All Stocklist Items Updated", vbInformation
      Dim rsTemp As DAO.Recordset
      Dim strSQL As String
      'Create a copy of this forms Recordset

20    Set rsTemp = Me.RecordsetClone
30    rsTemp.MoveFirst
40    Do Until rsTemp.EOF

      'Loop through all records and update records....
50    strSQL = "UPDATE Stocklist SET Allocation = " & Me.Update _
      & " WHERE StockNumber = '" & Me.StockNumber & "';"
60      Debug.Print strSQL
70      CurrentDb.Execute strSQL, dbFailOnError

80    rsTemp.MoveNext
90    Loop
      'Release resources
100   MsgBox "The selected item in Stocklist has been Updated", vbInformation
110   rsTemp.Close

120   Set rsTemp = Nothing

        
140       On Error GoTo 0
150       Exit Sub

cmdUpdate_Click_Error:

160       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdUpdate_Click, line " & Erl & "."

End Sub

 

[Kamayo Ako]

THE form below shows the items from a product that done need to be purchased because there is enough in stock . but this form allows me to update the allocation for that part . so for example the reqqty for stock number 3096B0010 for this product is 68.8 therefore when i press the update allocation button it will update the alloaction to 425.52 as indicated in the yellow field

what i would like to do is press the update allocation button once and it updates ALL the records at the same time and shows me the results and then i close the form down

the code below i think is a mixture and just needs modifying to suit but i dont know how. any help appreicated

thanks steve

View attachment 106132

Private Sub cmdUpdate_Click()

10        On Error GoTo cmdUpdate_Click_Error
      '      Dim strSQL As String
      '      Dim rs As Object
      '20    Set rs = Me.RecordsetClone
      '30    With rs
      '40    Do While Not .EOF
      '      'Do Something
      '
      '50      strSQL = "UPDATE StockList SET Allocation = " & Me.txtAllocation _
      '        & " WHERE StockNumber = '" & Me.StockNumber & "';"
      '        Debug.Print strSQL
      '        CurrentDb.Execute strSQL, dbFailOnError
      '60    .MoveNext
      '70    Loop
      '80    End With
      '90    Set rs = Nothing
      '100   MsgBox "All Stocklist Items Updated", vbInformation
      Dim rsTemp As DAO.Recordset
      Dim strSQL As String
      'Create a copy of this forms Recordset

20    Set rsTemp = Me.RecordsetClone
30    rsTemp.MoveFirst
40    Do Until rsTemp.EOF

      'Loop through all records and update records....
50    strSQL = "UPDATE Stocklist SET Allocation = " & Me.Update _
      & " WHERE StockNumber = '" & Me.StockNumber & "';"
60      Debug.Print strSQL
70      CurrentDb.Execute strSQL, dbFailOnError

80    rsTemp.MoveNext
90    Loop
      'Release resources
100   MsgBox "The selected item in Stocklist has been Updated", vbInformation
110   rsTemp.Close

120   Set rsTemp = Nothing

       
140       On Error GoTo 0
150       Exit Sub

cmdUpdate_Click_Error:

160       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdUpdate_Click, line " & Erl & "."

End Sub

Try this:

Private Sub cmdUpdate_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb

Set rst = db.OpenRecordset("Select Allocation from StockList where StockNumber =" & Me.StockNumber )

With rst
.Edit
!Allocation = !Allocation+ Nz(Me.ReqQty, 0)
.Update
End With

Me.Refresh

End Sub

 

[rainbows]

if i press the top button i get the first error if i press the second button i get the second error

 

[Pat Hartman]

When you don't know how to write the SQL, use the QBE to build it for you.
1. Open the QBE.
2. Select the query that populates this form
3. Select the field you want to update.
4. Change the query type to update
5. Change the Update to to the name of the yellow field.
6. Save

As always, make a backup of the BE before testing a new update query.

You don't need a VBA Loop. Put the update button in the header of the form. Then in the click event of the button.

DoCmd.RunCommand acCmdUpdateRecord
DoCmd.OpenQuery "name of your saved query"

 

[rainbows]

Pat,

this is the sql i use as i also use it for other information and the form only uses part of it as you can see the yellow field is only an a check for the operator to see what the allocation would be after running this query . although it is not needed . it is just , the reqdqty being added to the allocation.

i tried to follow your instructions but got lost on item 5.

thanks steve



SELECT [Material Details 91 days3].StockNumber, [Material Details 91 days3].ReqQty, [Material Details 91 days3].Balance, [Material Details 91 days3].StockQty, [Material Details 91 days3].qtyoutstanding1, [Material Details 91 days3].qtyoutstanding, [Material Details 91 days3].Unit, [Material Details 91 days3].allocation, [Material Details 91 days3].SumOfOrderQty, [Material Details 91 days3].SumOfProductQty, [ReqQty]+[Allocation] AS [Update]
FROM [Material Details 91 days3]
WHERE ((([Material Details 91 days3].qtyoutstanding)<=0));

 

[Pat Hartman]

You need to change the query type to update in step 4. That adds rows to the grid so you can specify a calculation since the yellow field is the result of the calculation. just use the calculation as the value for the cell that says Uptate to.

You only need to select the field you want to update and you need the Where clause. Never select extra fields. There is no point and it only provides options for errors. Ignore the query and start with the base table. That way you won't have any problem with updating if one of the queries in the stack is not updateable.

However, The process is not normalized anyway since you can calculate the value in a query any way. The logical problem with storing the calculated value is it is correct today but incorrect tomorrow since other accounts will fall into the criteria.

 

[rainbows]

hi . i think i have done what you said but i am getting error messages

the query is called material details 91 days not purchased ( append query} the orginal query before appending it is copy odf material details 91 days not purchased
the form is called frm91daysnotpurchased of which is where the error arrives and will not open. i a Have attached a test sample of the database so you can hopefully look at what i am doing wrong

thanks
steve

i am trying to get the reqqty to add to the allocation all in one go rather than get each button to do it . the numbers in yellow are just a ref of what it should be.

 

[Kamayo Ako]

if i press the top button i get the first error if i press the second button i get the second error


View attachment 106133



View attachment 106134

check this form i did some : frmPCONotPurchased

empty - Copy like eddie rev1.rar

drive.google.com

 

[rainbows]

hi , i cannot open it

 

[arnelgp]

Try your form.

 

[Kamayo Ako]

hi , i cannot open it

hope you can open this. Yet, i recommend Mr. Arnelgp file.

 

[gemma-the-husky]

this from @Kamayo Ako failed

Set rst = db.OpenRecordset("Select Allocation from StockList where StockNumber =" & Me.StockNumber )

It failed because although you called it [stocknumber], it's not a numeric number, it's actually a text string.

So you need the syntax

either
Set rst = db.OpenRecordset("Select Allocation from StockList where StockNumber = '" & Me.StockNumber & "'")

or
Set rst = db.OpenRecordset("Select Allocation from StockList where StockNumber =" & chr(34) & Me.StockNumber & chr(34) )

I prefer the second syntax, as I find code much easier to follow with chr(34) (which is a " character), rather than trying to include quotation marks within quote marks.

 

[rainbows]

thanks to all of you arnelgp is working great , saved a lot of time

steve

 

[Kamayo Ako]

this from @Kamayo Ako failed



It failed because although you called it [stocknumber], it's not a numeric number, it's actually a text string.

So you need the syntax

either
Set rst = db.OpenRecordset("Select Allocation from StockList where StockNumber = '" & Me.StockNumber & "'")

or
Set rst = db.OpenRecordset("Select Allocation from StockList where StockNumber =" & chr(34) & Me.StockNumber & chr(34) )

I prefer the second syntax, as I find code much easier to follow with chr(34) (which is a " character), rather than trying to include quotation marks within quote marks.

check the file which i just attached lately.

 

[Pat Hartman]

1. Action queries cannot be used as RowSources or RecordSources. I was helping you to build an action query. In the QBE, you can look at what the query selects but usually, that is useless with an update query since the only field would be the one you are updating.
2. Please don't use .Rar to zip your files. Most of use can't unzip them when you use a non-standard method.

 

[rainbows]

i am not sure if i should use another post or not but now the people using this database seen the amount of time it saves them after looking at the modifications Arnelgp did on the database he attached on his Post .they have asked if the same thing can be done on the form called " purchasing form" with navigation form " supplier purchase orders " at this time it updates the fields onorderqty and allocation then the status is set to "ordered" you can see that line one is 230 and 320 if i press the update button it will change them both to 240 and 420 because they look at the fields "qty reqd" and "qty required" these being what the qty required to fullfil an sales order and qty reqd the purchase will purchase as he might put some in stock

thanks steve

 

[Pat Hartman]

You don't need a VBA loop. You need an update query that you rn from a button on the main form.

The buttons on the individual rows should just update the current row by calculating the new value and populating the calculated field. Of course if this form is not bound, then you need to perform whatever update procedure you have designed.

 

[rainbows]

Pat
i have managed to do the query and it works but i need to only work if the field = ordered in the "supplier p/o details " form

thanks steve

 

[Pat Hartman]

Use a WHERE clause in the update query:

Where SomeField = Forms!yourformname!SomeField

Start by creating a select query with the where clause so you know you are selecting the correct records. Then change the query type and add the update stuff.

PS, please don't post pictures of SQL. Post actual strings. They are much easier to see and we can format them to make them easier to read if you haven't formated the query.