Results 1 to 2 of 2
  1. #1
    shod90 is offline Competent Performer
    Windows 8 Access 2016
    Join Date
    Jan 2016
    Posts
    134

    Loop through Continuous form and change percentage value

    Dear Gents,


    I have a form with a continuous form that show all my products in one page , One of the columns is profit percentage , I want to make a button that changes all the profit percentage for all records based on Sell price and Buy Price , I tried to write this code , But this code only changes one record !
    Code:
    Dim i As Integer
        Set rs = Me.RecordsetClone
            If Not (rs.BOF And rs.EOF) Then 'make sure we have records to loop through
                rs.MoveFirst 'make sure we're on the first record
                Do While Not rs.EOF 'keep looping until we reach the end of the recordset
    
    
                MsgBox ItemID
                PercentValue = SellPrice * (PercentValue / 100)
                Me.Refresh
                rs.MoveNext
                Loop
            End If
        rs.Close
        Set rs = Nothing
    Any Help please ?

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by shod90 View Post
    I want to make a button that changes all the profit percentage for all records based on Sell price and Buy Price...
    Quote Originally Posted by shod90 View Post
    PercentValue = SellPrice * (PercentValue / 100)
    Where is BuyPrice? Currently you replace profit percentage with profit value!

    And easier and faster would be an update query.

    Create a saved query like
    Code:
    uqUpdateMyTable = UPDATE tblMyTable SET PercentValue = 100*((SellPrice - BuyPrice)/BuyPrice)
    Create a procedure like
    Code:
    Public Sub UpdateTable()
       On Error GoTo Err_Update
    
        Dim strQueryName As String
        
        DoCmd.SetWarnings (WarningsOff)
        strQueryName = "uqUpdateMyTable"
        MsgBox "MyTable is updated"
        DoCmd.OpenQuery strQueryName, acNormal, acEdit
        
    Exit_UpdateTable:
        DoCmd.SetWarnings (WarningsOn)
        Exit Sub
    Err_Update:
        MsgBox Err.Description
        Resume Exit_UpdateTable
    End Sub
    Call the procedure from button's OnClick event. And you can change the procedure so it takes table name as parameter - then you can use the same procedure to run different update queries.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. loop through continuous form
    By shod90 in forum Forms
    Replies: 11
    Last Post: 10-15-2018, 03:18 PM
  2. Replies: 10
    Last Post: 06-19-2015, 09:02 AM
  3. Replies: 14
    Last Post: 01-15-2015, 02:41 PM
  4. Replies: 14
    Last Post: 06-28-2014, 10:52 AM
  5. Replies: 3
    Last Post: 09-23-2013, 06:42 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums