update allocation alltogether

R

rainbows

Registered User.
Local time
Today, 01:34
Joined
Apr 21, 2017
Messages
428
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

1674998177625.png





Code: 
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
 
rainbows said:
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




Code: 
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
Click to expand...
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
 
if i press the top button i get the first error if i press the second button i get the second error


1675003932806.png




1675003988614.png
 
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));
 
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

1675066754270.png


1675067064168.png


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.
 

Attachments

this from @Kamayo Ako failed

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

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.
 
thanks to all of you arnelgp is working great , saved a lot of time

steve
 
gemma-the-husky said:
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.
Click to expand...
check the file which i just attached lately.
 
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





1675156538768.png
 
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


1675191445965.png


1675191477951.png


1675191616902.png
 
You must log in or register to reply here.

Similar threads

W
The problem has been resolved and the program can now be executed. Thank you very much.
Replies
6
Views
1,048
Ken Sheridan
Ken Sheridan
C
Solved Treeview address
Replies
8
Views
531
ClaraBarton
C
C
Solved Error 3021: No Current record
Replies
2
Views
465
Consonanza
C
C
Solved Node level path
Replies
3
Views
289
ClaraBarton
C
C
Solved Position form to added record
Replies
8
Views
392
ClaraBarton
C

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom