1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

VBA: Excel form that updates current data or add new data

Discussion in 'Business Applications' started by jadown, Mar 22, 2010.

Thread Status:
Not open for further replies.
  1. jadown

    jadown Thread Starter

    Joined:
    Mar 22, 2010
    Messages:
    12
    I built a form called PO Log that contains input text boxes that the user can enter the different iputs that will add an existing row to the worksheet called PO_log. I have the add function working perfectly. But I want to add a function to the form that if the record/data already exist using the PO number as an identifier, to udate the existing record/data rather than adding a completely new row of record/data. Here is the code I have below for the adding function.

    Code:
     
    Private Sub cmdAdd_Click()
    
    Dim iRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("PO_log")
    
    'find first empty row in database
    iRow = ws.Cells(Rows.Count, 1) _
      .End(xlUp).Offset(1, 0).Row
    
    'check for a part number
    If Trim(Me.txtVendor.Value) = "" Then
      Me.txtVendor.SetFocus
      MsgBox "Please enter a Order Info"
      Exit Sub
    End If
    
    'copy the data to the database
    ws.Cells(iRow, 1).Value = Me.txtVendor.Value
    ws.Cells(iRow, 2).Value = Me.txtPONumber.Value
    ws.Cells(iRow, 3).Value = Me.txtPurReqDate.Value
    ws.Cells(iRow, 4).Value = Me.txtRFQSent.Value
    ws.Cells(iRow, 5).Value = Me.txtSentToVen.Value
    ws.Cells(iRow, 6).Value = Me.txtDateOfPo.Value
    ws.Cells(iRow, 7).Value = Me.txtOrderConf.Value
    ws.Cells(iRow, 8).Value = Me.txtAmt.Value
    
    'clear the data
    Me.txtVendor.Value = ""
    Me.txtPONumber.Value = ""
    Me.txtPurReqDate.Value = ""
    Me.txtRFQSent.Value = ""
    Me.txtSentToVen.Value = ""
    Me.txtDateOfPo.Value = ""
    Me.txtOrderConf.Value = ""
    Me.txtAmt.Value = ""
    Me.txtVendor.SetFocus
    
    End Sub
    
    
    Private Sub cmdClose_Click()
        Unload Me
    End Sub
     
    
     
  2. JohnWill

    JohnWill Retired Moderator

    Joined:
    Oct 19, 2002
    Messages:
    106,418
    Closing duplicate.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/911783

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice