+ Reply to Thread
Results 1 to 17 of 17

Updating Data from Previously Submitted Form Entry

  1. #1
    Registered User
    Join Date
    06-05-2018
    Location
    Minneapolis, MN
    MS-Off Ver
    365 ProPlus
    Posts
    31

    Updating Data from Previously Submitted Form Entry

    Hello,

    I need help with the code for updating a data has has been submitted through a form. I have a form that is for recording our budget Purchase Orders. Sometimes we don't have the PO number right away, but still want to put in a place holder with the other data and then come back and add the PO number later. I need help with code that will let me scroll through the current list of submitted POs and be able to click an "update" button if I change/add the PO number (or any of the fields, if someone had entered it wrong).

    I have added a scroll spin button and label to go through "x of x" record and an update button but need help with the coding, please!

    Thanks in advance for the help!
    Danielle
    Attached Files Attached Files
    Last edited by heimk008; 09-18-2018 at 09:19 AM.

  2. #2
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office 365
    Posts
    1,784

    Re: Updating Data from Previously Submitted Form Entry

    Added an additional button to empty the controls for new entry.
    Try this

    PHP Code: 
    Private Sub CommandButton1_Click()
        
    With Sheet1
            
    If Application.WorksheetFunction.CountIf(.Range("C:C"), CStr(Me.txtPO.Value)) > 0 Then
                iRow 
    = .Range("C:C").Find(CStr(Me.txtPO.Value), , xlValuesxlWhole).Row
                        Me
    .txtName.Value = .Cells(iRow1).Value
                        Me
    .cboOwner.Value = .Cells(iRow2).Value
                        Me
    .txtPO.Value = .Cells(iRow3).Value
                        Me
    .txtPOAmount.Value = .Cells(iRow4).Value
                        Me
    .cboBudget.Value = .Cells(iRow5).Value
                        Me
    .txtDate.Value = .Cells(iRow6).Value
            End 
    If
        
    End With
    End Sub

    Private Sub SpinButton1_SpinDown()
        
    With Sheet1
            
    If iRow = .Range("A" Rows.Count).End(xlUp).Row Then
                MsgBox 
    "It is the last record"vbInformation
            
    Else
            
    iRow iRow 1
                    Me
    .txtName.Value = .Cells(iRow1).Value
                    Me
    .cboOwner.Value = .Cells(iRow2).Value
                    Me
    .txtPO.Value = .Cells(iRow3).Value
                    Me
    .txtPOAmount.Value = .Cells(iRow4).Value
                    Me
    .cboBudget.Value = .Cells(iRow5).Value
                    Me
    .txtDate.Value = .Cells(iRow6).Value
            End 
    If
        
    End With
    End Sub

    Private Sub SpinButton1_SpinUp()
        
    With Sheet1
            
    If iRow 2 Then
                MsgBox 
    "It is the first record"vbInformation
            
    Else
                
    iRow iRow 1
                    Me
    .txtName.Value = .Cells(iRow1).Value
                    Me
    .cboOwner.Value = .Cells(iRow2).Value
                    Me
    .txtPO.Value = .Cells(iRow3).Value
                    Me
    .txtPOAmount.Value = .Cells(iRow4).Value
                    Me
    .cboBudget.Value = .Cells(iRow5).Value
                    Me
    .txtDate.Value = .Cells(iRow6).Value
            End 
    If
        
    End With
    End Sub 
    Attached Files Attached Files
    Last edited by ImranBhatti; 09-17-2018 at 03:03 PM.
    Teach me Excel VBA

  3. #3
    Registered User
    Join Date
    06-05-2018
    Location
    Minneapolis, MN
    MS-Off Ver
    365 ProPlus
    Posts
    31

    Re: Updating Data from Previously Submitted Form Entry

    the code you sent doesn't seem to have anything for updating an existing line. I need to be able to spin through the rows and then update something within one.

  4. #4
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office 365
    Posts
    1,784

    Re: Updating Data from Previously Submitted Form Entry

    Yes there was a mistake. Try the attached.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by ImranBhatti; 09-18-2018 at 02:28 AM.

  5. #5
    Registered User
    Join Date
    06-05-2018
    Location
    Minneapolis, MN
    MS-Off Ver
    365 ProPlus
    Posts
    31

    Re: Updating Data from Previously Submitted Form Entry

    thank you so much! had to add "unprotect" the code but otherwise worked perfect!

  6. #6
    Registered User
    Join Date
    06-05-2018
    Location
    Minneapolis, MN
    MS-Off Ver
    365 ProPlus
    Posts
    31

    Re: Updating Data from Previously Submitted Form Entry

    ImranBhatti, would you be able to help me with the code for the Invoices too? trying to do it myself but getting lost under the 'Private Sub UserForm_Initialize()' part...

  7. #7
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office 365
    Posts
    1,784

    Re: Updating Data from Previously Submitted Form Entry

    What do you want the Invoice form to do other than what is doing currently?

  8. #8
    Registered User
    Join Date
    06-05-2018
    Location
    Minneapolis, MN
    MS-Off Ver
    365 ProPlus
    Posts
    31

    Re: Updating Data from Previously Submitted Form Entry

    I want it to have an update and new button just like the PO. i have them created, but need help with the coding again...
    Attached Files Attached Files

  9. #9
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office 365
    Posts
    1,784

    Re: Updating Data from Previously Submitted Form Entry

    Your yourself have replicated the code for Invoice form. I have just rectified some mistakes. See the attached.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    06-05-2018
    Location
    Minneapolis, MN
    MS-Off Ver
    365 ProPlus
    Posts
    31

    Re: Updating Data from Previously Submitted Form Entry

    The number of records doesn't show up right way, only once i click the button...

  11. #11
    Registered User
    Join Date
    06-05-2018
    Location
    Minneapolis, MN
    MS-Off Ver
    365 ProPlus
    Posts
    31

    Re: Updating Data from Previously Submitted Form Entry

    Also, in POs once i click the up spin arrow a few times, i won't let me go back down. and if i click the invoice up arrow more than once it gives me an error

  12. #12
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office 365
    Posts
    1,784

    Re: Updating Data from Previously Submitted Form Entry

    Which form are you talking about, PO or Invoices?

  13. #13
    Registered User
    Join Date
    06-05-2018
    Location
    Minneapolis, MN
    MS-Off Ver
    365 ProPlus
    Posts
    31

    Re: Updating Data from Previously Submitted Form Entry

    There are errors with both:

    PO Form:
    - once I click the up spin arrow a few times, it won't let me go back down

    Invoice Form:
    - The number of records don't show up right way, but they do once I click the arrow
    - When I click the up arrow more than once it gives me an error and won't let me keep going

  14. #14
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office 365
    Posts
    1,784

    Re: Updating Data from Previously Submitted Form Entry

    Quote Originally Posted by heimk008 View Post

    PO Form:
    - once I click the up spin arrow a few times, it won't let me go back down
    The file in that I have attached in post #9 is working fine for me.

    Quote Originally Posted by heimk008 View Post
    Invoice Form:
    - The number of records don't show up right way, but they do once I click the arrow
    - When I click the up arrow more than once it gives me an error and won't let me keep going
    Add the this line in the Userform_Initialize event of Invoice form

    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    06-05-2018
    Location
    Minneapolis, MN
    MS-Off Ver
    365 ProPlus
    Posts
    31

    Re: Updating Data from Previously Submitted Form Entry

    Quote Originally Posted by ImranBhatti View Post
    The file in that I have attached in post #9 is working fine for me.
    When I go up 5+ times it often won't let me go back down, but sometimes it does for no reason... any clue what could be happening?

  16. #16
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office 365
    Posts
    1,784

    Re: Updating Data from Previously Submitted Form Entry

    Maybe due to sample duplicate records.

  17. #17
    Registered User
    Join Date
    06-05-2018
    Location
    Minneapolis, MN
    MS-Off Ver
    365 ProPlus
    Posts
    31

    Re: Updating Data from Previously Submitted Form Entry

    there aren't any duplicate record in the PO table... and if there were, how would i work past this?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Automating an email once form is submitted (access 2013)
    By TapiwaMaseko in forum Access Tables & Databases
    Replies: 0
    Last Post: 09-03-2017, 03:52 PM
  2. [SOLVED] User Form, On every submitted entry, receive an error "Invalid Property Value"
    By NikhilDixit in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-04-2017, 08:57 AM
  3. Populating VBA userform and Fetch previously entered data edit and make new entry
    By vijaynadiad in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-07-2013, 11:59 AM
  4. Replies: 2
    Last Post: 06-07-2013, 09:08 AM
  5. Replies: 1
    Last Post: 05-01-2012, 03:12 PM
  6. Replies: 0
    Last Post: 04-05-2012, 10:37 AM
  7. Stop updating previously saved linked spreadsheets
    By Derric in forum Excel General
    Replies: 2
    Last Post: 11-22-2007, 11:42 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1