Updating existing data on a sheet

**etuckram** · 07-02-2012, 03:43 AM

I've gone so far with project of mine and I'm about to just a add some details. And thanks for the community on helping newbies like me. Everything is working fine.

Clicking the Add to DB button will add new items but not existing one.

now, what I want to do is to first, populate the existing TIN and then do the necessary update.

Please see attached file

Here are the codes anyway:

'This adds the data on the WorkSheet named DBPIT"
Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("DBPIT")

ws.Activate

'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

If Application.WorksheetFunction.CountIf(Worksheets("DBPIT").Range("B:B"), txtTIN.Text) > 0 Then
        MsgBox ("That order already exists on your database. If you want to update your order, click the Update Order Button")
        Cancel = True
   
Else

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtDate.Value
ws.Cells(iRow, 2).Value = Me.txtTIN.Value
ws.Cells(iRow, 3).Value = Me.txtEntName.Value
ws.Cells(iRow, 4).Value = Me.txtAdd.Value
ws.Cells(iRow, 5).Value = Me.txtOM.Value
ws.Cells(iRow, 6).Value = Me.cboSvcType.Value
ws.Cells(iRow, 7).Value = Me.cboAccType.Value
ws.Cells(iRow, 8).Value = Me.txtCktID.Value
ws.Cells(iRow, 9).Value = Me.txtLinePort.Value
ws.Cells(iRow, 10).Value = Me.txtLocName.Value
ws.Cells(iRow, 11).Value = Me.txtLocID.Value
ws.Cells(iRow, 12).Value = Me.txtHubID.Value
ws.Cells(iRow, 13).Value = Me.txtDvcName.Value
ws.Cells(iRow, 14).Value = Me.txtNat.Value
ws.Cells(iRow, 15).Value = Me.txtOut.Value
ws.Cells(iRow, 16).Value = Me.txtDomain.Value
ws.Cells(iRow, 17).Value = Me.txtTN.Value
ws.Cells(iRow, 18).Value = Me.txtCsip1.Value
ws.Cells(iRow, 19).Value = Me.txtCssip1.Value
ws.Cells(iRow, 20).Value = Me.txtCssPort1.Value
ws.Cells(iRow, 21).Value = Me.txtCpe1a.Value
ws.Cells(iRow, 22).Value = Me.txtCpe2a.Value
ws.Cells(iRow, 23).Value = Me.txtCsFQDN1.Value
ws.Cells(iRow, 24).Value = Me.txtCsFQDN2.Value
ws.Cells(iRow, 25).Value = Me.txtCsip2.Value
ws.Cells(iRow, 26).Value = Me.txtCssip2.Value
ws.Cells(iRow, 27).Value = Me.txtCssPort2.Value
ws.Cells(iRow, 28).Value = Me.txtCpe1b.Value
ws.Cells(iRow, 29).Value = Me.txtCpe2b.Value
ws.Cells(iRow, 30).Value = Me.txtCktID2.Value
ws.Cells(iRow, 31).Value = Me.txtVPN.Value
ws.Cells(iRow, 32).Value = Me.txtRouter.Value
ws.Cells(iRow, 33).Value = Me.txtVRF.Value
ws.Cells(iRow, 34).Value = Me.txtPE.Value
ws.Cells(iRow, 35).Value = Me.txtCE.Value
ws.Cells(iRow, 36).Value = Me.txtVLAN.Value
ws.Cells(iRow, 37).Value = Me.txtSO.Value
ws.Cells(iRow, 38).Value = Me.txtTPSDate.Value
ws.Cells(iRow, 39).Value = Me.txtOVC.Value
ws.Cells(iRow, 40).Value = Me.txtPITDate.Value
ws.Cells(iRow, 41).Value = Me.txtISR.Value
ws.Cells(iRow, 42).Value = Me.txtLD.Value
ws.Cells(iRow, 43).Value = Me.txtLocal.Value

Worksheets("TN DB").Cells(iRow, 1).Value = Me.txtNat.Value
Worksheets("TN DB").Cells(iRow, 2).Value = Me.txtLD.Value
Worksheets("TN DB").Cells(iRow, 3).Value = Me.txtLocal.Value

End If

End Sub


'Im stuck with this:

Private Sub cmdUpdate_Click()

If Application.WorksheetFunction.CountIf(Worksheets("DBPIT").Range("B:B"), txtTIN.Text) = 0 Then
        MsgBox ("You can't update that order yet. Add it on the Database first.")
        Cancel = True
Else


End If

End Sub

**arlu1201** · 07-02-2012, 06:40 AM

You have not assigned "ws" to anything. Which sheet is ws referring to?

You can assign it using set ws=worksheets("Sheet1")

What is the error you are receiving? When you click on Debug, which line of code is highlighted?

**etuckram** · 07-02-2012, 09:02 AM

Originally Posted by arlu1201

You have not assigned "ws" to anything. Which sheet is ws referring to?

You can assign it using set ws=worksheets("Sheet1")

What is the error you are receiving? When you click on Debug, which line of code is highlighted?

here is the code:

Set ws = Worksheets("DBPIT")

I don't have a problem adding data, I just need to know how to update existing data that I've entered.

**arlu1201** · 07-02-2012, 09:12 AM

If you need to edit all the data that is already entered, you can clear the contents of the rows and refill them.

If its only partial data that needs to be edited, you will need to lookup some key field based on which you can then find the row in your data and edit the other fields.

**etuckram** · 07-02-2012, 10:22 AM

Originally Posted by arlu1201

If you need to edit all the data that is already entered, you can clear the contents of the rows and refill them.

If its only partial data that needs to be edited, you will need to lookup some key field based on which you can then find the row in your data and edit the other fields.

I'll try to explain the flow.

Enter TIN? -> New? -> Yes -> Search DBPIT -> Populate all values

Update order, same TIN just want to edit for example the address or the date.

**etuckram** · 07-04-2012, 06:26 AM

I'm almost there.

I have a userform of many textbox. What I want to happen is to dont have any duplicate TIN. all data will be populated based on TIN. Now, all data will be added to worksheet name "DBPIT". You can retrieve the information back to userform by entering the TIN number. After which, I desired to edit the values on that certain TIN (TIN can't be edited, else a new TIN will be added). You can't also edit an information if its not yet added on the database. See my codes please...

Here is my code to paste everything to the worksheet "DBPIT"

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("DBPIT")

ws.Activate

If Trim(Me.txtTIN.Value) = "" Then
  Me.txtTIN.SetFocus
  MsgBox "Please enter the TIN"
  Exit Sub
End If

'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

If Application.WorksheetFunction.CountIf(Worksheets("DBPIT").Range("B:B"), txtTIN.Text) > 0 Then
        MsgBox ("That order already exists on your database. If you want to update your order, click the Update Order Button")
        Cancel = True

 
Else

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtDate.Value
ws.Cells(iRow, 2).Value = Me.txtTIN.Value
ws.Cells(iRow, 3).Value = Me.txtEntName.Value
ws.Cells(iRow, 4).Value = Me.txtAdd.Value
ws.Cells(iRow, 5).Value = Me.txtOM.Value
ws.Cells(iRow, 6).Value = Me.cboSvcType.Value
ws.Cells(iRow, 7).Value = Me.cboAccType.Value
ws.Cells(iRow, 8).Value = Me.txtCktID.Value
ws.Cells(iRow, 9).Value = Me.txtLinePort.Value
ws.Cells(iRow, 10).Value = Me.txtLocName.Value
ws.Cells(iRow, 11).Value = Me.txtLocID.Value
ws.Cells(iRow, 12).Value = Me.txtHubID.Value
ws.Cells(iRow, 13).Value = Me.txtDvcName.Value
ws.Cells(iRow, 14).Value = Me.txtNat.Value
ws.Cells(iRow, 15).Value = Me.txtOut.Value
ws.Cells(iRow, 16).Value = Me.txtDomain.Value
ws.Cells(iRow, 17).Value = Me.txtTN.Value
ws.Cells(iRow, 18).Value = Me.txtCsip1.Value
ws.Cells(iRow, 19).Value = Me.txtCssip1.Value
ws.Cells(iRow, 20).Value = Me.txtCssPort1.Value
ws.Cells(iRow, 21).Value = Me.txtCpe1a.Value
ws.Cells(iRow, 22).Value = Me.txtCpe2a.Value
ws.Cells(iRow, 23).Value = Me.txtCsFQDN1.Value
ws.Cells(iRow, 24).Value = Me.txtCsFQDN2.Value
ws.Cells(iRow, 25).Value = Me.txtCsip2.Value
ws.Cells(iRow, 26).Value = Me.txtCssip2.Value
ws.Cells(iRow, 27).Value = Me.txtCssPort2.Value
ws.Cells(iRow, 28).Value = Me.txtCpe1b.Value
ws.Cells(iRow, 29).Value = Me.txtCpe2b.Value
ws.Cells(iRow, 30).Value = Me.txtCktID2.Value
ws.Cells(iRow, 31).Value = Me.txtVPN.Value
ws.Cells(iRow, 32).Value = Me.txtRouter.Value
ws.Cells(iRow, 33).Value = Me.txtVRF.Value
ws.Cells(iRow, 34).Value = Me.txtPE.Value
ws.Cells(iRow, 35).Value = Me.txtCE.Value
ws.Cells(iRow, 36).Value = Me.txtVLAN.Value
ws.Cells(iRow, 37).Value = Me.txtSO.Value
ws.Cells(iRow, 38).Value = Me.txtTPSDate.Value
ws.Cells(iRow, 39).Value = Me.txtOVC.Value
ws.Cells(iRow, 40).Value = Me.txtPITDate.Value
ws.Cells(iRow, 41).Value = Me.txtISR.Value
ws.Cells(iRow, 42).Value = Me.txtLD.Value
ws.Cells(iRow, 43).Value = Me.txtLocal.Value

End If

ThisWorkbook.Save
End Sub

now, this is my code to edit the information that was already added.

Private Sub cmdUpdate_Click()
Dim Y As Long

If Application.WorksheetFunction.CountIf(Worksheets("DBPIT").Range("B:B"), txtTIN.Text) = 0 Then
        MsgBox ("You can't update that order yet. Add it on the Database first.")
        Cancel = True
Else

            Y = Application.CountA(Sheets("DBPIT").Range("A:A"))
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 0).Value = txtDate.Text
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 1).Value = txtTIN.Text
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 2).Value = txtEntName.Text
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 3).Value = txtAdd.Text
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 4).Value = txtOM.Text
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 5).Value = cboSvcType.Text
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 6).Value = cboAccType.Text
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 7).Value = txtCktID.Text
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 8).Value = txtLinePort.Text
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 9).Value = txtLocName.Text
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 10).Value = txtLocID.Text
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 11).Value = txtHubID.Text
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 12).Value = txtDvcName.Text
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 13).Value = txtNat.Text
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 14).Value = txtOut.Text
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 15).Value = txtDomain.Text
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 16).Value = txtTN.Text
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 17).Value = txtCsip1.Text
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 18).Value = txtCssip1.Text
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 19).Value = txtCssPort1.Text
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 20).Value = txtCpe1a.Text
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 21).Value = txtCpe2a.Text
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 22).Value = txtCsFQDN1.Text
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 23).Value = txtCsFQDN2.Text
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 24).Value = txtCsip2.Text
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 25).Value = txtCssip2.Text
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 26).Value = txtCssPort2.Text
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 27).Value = txtCpe1b.Text
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 28).Value = txtCpe2b.Text
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 29).Value = txtCktID2.Text
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 30).Value = txtVPN.Text
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 31).Value = txtRouter.Text
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 32).Value = txtVRF.Text
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 33).Value = txtPE.Text
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 34).Value = txtCE.Text
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 35).Value = txtVLAN.Text
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 36).Value = txtSO.Text
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 37).Value = txtTPSDate.Text
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 38).Value = txtOVC.Text
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 39).Value = txtPITDate.Text
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 40).Value = txtISR.Text
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 41).Value = txtLD.Text
            Worksheets("DBPIT").Range("A:A").End(xlUp).Offset(Y, 42).Value = txtLocal.Text
          
End If

End Sub

now, the problem is, everytime I pull up information based on TIN and then edit some of the information, it will be added at the last row but will not edit the existing data.

Please help. Thank you. Please see attached file.

**etuckram** · 07-04-2012, 08:48 PM

anyone that can help? please?

**etuckram** · 07-06-2012, 01:25 PM

anyone please? I badly need help

**Nathaly** · 07-06-2012, 03:08 PM

Hi There. Did your question ever get answered or did you ever figure it out. I have the similar situation

**arlu1201** · 07-06-2012, 03:09 PM

Nathaly,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

**etuckram** · 07-26-2012, 05:09 AM

This solved my problem:

Private Sub cmdUpdate_Click()

If Application.WorksheetFunction.CountIf(Worksheets("DBPIT").Range("A:A"), txtTIN.Text) = 0 Then
        MsgBox ("You can't update that order yet. Add it on the Database first.")
      Cancel = True
Else

Dim uRow As Long
Dim Answer As Integer

    uRow = Range("A:A").Find(What:=txtTIN.Value, After:=Cells(1, 1), LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Row


        Cells(uRow, 1).Value = txtTIN.Value
        Cells(uRow, 2).Value = txtDate.Value
        Cells(uRow, 3).Value = txtEntName.Value
        Cells(uRow, 4).Value = txtAdd.Value
        Cells(uRow, 5).Value = txtOM.Value
        Cells(uRow, 6).Value = cboSvcType.Value
        Cells(uRow, 7).Value = cboAccType.Value
        Cells(uRow, 8).Value = txtCktID.Value
        Cells(uRow, 9).Value = txtLinePort.Value
        Cells(uRow, 10).Value = txtLocName.Value
        Cells(uRow, 11).Value = txtLocID.Value
        Cells(uRow, 12).Value = txtHubID.Value
        Cells(uRow, 13).Value = txtDvcName.Value
        Cells(uRow, 14).Value = txtNat.Value
        Cells(uRow, 15).Value = txtOut.Value
        Cells(uRow, 16).Value = txtDomain.Value
        Cells(uRow, 17).Value = txtTN.Value
        Cells(uRow, 18).Value = txtCsip1.Value
        Cells(uRow, 19).Value = txtCssip1.Value
        Cells(uRow, 20).Value = txtCssPort1.Value
        Cells(uRow, 21).Value = txtCpe1a.Value
        Cells(uRow, 22).Value = txtCpe2a.Value
        Cells(uRow, 23).Value = txtCsFQDN1.Value
        Cells(uRow, 24).Value = txtCsFQDN2.Value
        Cells(uRow, 25).Value = txtCsip2.Value
        Cells(uRow, 26).Value = txtCssip2.Value
        Cells(uRow, 27).Value = txtCssPort2.Value
        Cells(uRow, 28).Value = txtCpe1b.Value
        Cells(uRow, 29).Value = txtCpe2b.Value
        Cells(uRow, 30).Value = txtCktID2.Value
        Cells(uRow, 31).Value = txtVPN.Value
        Cells(uRow, 32).Value = txtRouter.Value
        Cells(uRow, 33).Value = txtVRF.Value
        Cells(uRow, 34).Value = txtPE.Value
        Cells(uRow, 35).Value = txtCE.Value
        Cells(uRow, 36).Value = txtVLAN.Value
        Cells(uRow, 37).Value = txtSO.Value
        Cells(uRow, 38).Value = txtTPSDate.Value
        Cells(uRow, 39).Value = txtOVC.Value
        Cells(uRow, 40).Value = txtPITDate.Value
        Cells(uRow, 41).Value = txtISR.Value
        Cells(uRow, 42).Value = txtLD.Value
        Cells(uRow, 43).Value = txtLocal.Value
        Cells(uRow, 44).Value = txtCktID3.Value
        Cells(uRow, 45).Value = txtIDAGW.Value
        Cells(uRow, 46).Value = txtIDASE.Value
        Cells(uRow, 47).Value = txtIDAWAN.Value
        Cells(uRow, 48).Value = txtIDALAN.Value
        Cells(uRow, 49).Value = txtIPSecReq.Value
        Cells(uRow, 50).Value = txtIPSecSub.Value
        
          
End If
            
End Sub

Updating existing data on a sheet

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Updating existing data on a sheet

Re: Updating existing data on a sheet

Re: Updating existing data on a sheet

Re: Updating existing data on a sheet

Re: Updating existing data on a sheet

Re: Updating existing data on a sheet

Re: Updating existing data on a sheet

Re: Updating existing data on a sheet

Re: Updating existing data on a sheet

Re: Updating existing data on a sheet

Re: Updating existing data on a sheet

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions