Saving updated data record on current line vs saving new record

[Bill Williamson]

Hello,

Current userform has a save button, wich adds data to sheet 1, Added Search Button to find saved data for editing. If I use save button for Updated information it adds the information like new data at the bottom while old version still in the list. I added update button to use only when existing data is modified and I need it to save on the same row the original data was on, But I have a code issue. I am so new at this I cant see what the issue is..

I have Highlighted the line where I get the Run-Time Error '1004'
Application-Defined or Object-defined error

Private Sub Clearform()
'Sub Clearform()
  Dim ctrl As MSForms.Control
  For Each ctrl In Me.Controls
    Select Case TypeName(ctrl)
      Case "TextBox"
        ctrl.Text = ""
      Case "ComboBox"
        ctrl.ListIndex = -1
      Case "CheckBox"
        ctrl.Value = False
    End Select
  Next
End Sub


Private Sub ApperanceCheckBox_Click()
End Sub
Private Sub BRReviewCheckBox_Click()
End Sub
Private Sub BRReview_Click()
End Sub
Private Sub CancelButton_Click()
Unload Me


End Sub
Private Sub CommandButton1_Click()
    'Show User form
        UserForm1.Show


End Sub


Private Sub ClearButton_Click()


'Sub Clearform()
  Dim ctrl As MSForms.Control
  For Each ctrl In Me.Controls
    Select Case TypeName(ctrl)
      Case "TextBox"
        ctrl.Text = ""
      Case "ComboBox"
        ctrl.ListIndex = -1
      Case "CheckBox"
        ctrl.Value = False
    End Select
  Next
  
End Sub




Private Sub UserForm1_Click()
End Sub




Private Sub UserForm1_Initialize()




End Sub
    


Private Sub CMDSearch_Click()


Dim Fnd As Range
    
    With Sheets("Sheet1")
        If .AutoFilterMode Then .AutoFilterMode = False
        If Customer.Value <> "" Then .Range("A1").AutoFilter 1, Me.Customer.Value
        If CSONumber.Value <> "" Then .Range("A1").AutoFilter 2, Me.CSONumber.Value
        If JobNumber.Value <> "" Then .Range("A1").AutoFilter 3, Me.JobNumber.Value
        On Error Resume Next
        Set Fnd = .Range("A2:A" & Rows.Count).SpecialCells(xlVisible)(1)
        On Error GoTo 0
        If Fnd Is Nothing Then
        MsgBox "Search term not found"
    Else
        Customer.Text = Fnd.Value
        CSONumber.Text = Fnd.Offset(, 1).Value
        JobNumber.Text = Fnd.Offset(, 2).Value
        PCWeldType.Value = Fnd.Offset(, 3).Value
        PCWeldGrind.Value = Fnd.Offset(, 4).Value
        PCFinish.Value = Fnd.Offset(, 5).Value
        NonPCWeld.Value = Fnd.Offset(, 6).Value
        NonPCGrind.Value = Fnd.Offset(, 7).Value
        NonPCFinish.Value = Fnd.Offset(, 8).Value
        BRReview.Value = LCase(Fnd.Offset(, 9).Value) = "yes"
        BOMReview.Value = LCase(Fnd.Offset(, 10).Value) = "yes"
        DimReview.Value = LCase(Fnd.Offset(, 11).Value) = "yes"
        WeldReview.Value = LCase(Fnd.Offset(, 12).Value) = "yes"
        Apperance.Value = LCase(Fnd.Offset(, 13).Value) = "yes"
        Complete.Value = LCase(Fnd.Offset(, 14).Value) = "yes"
              
    End If
        'Turns off auto filter, shows all data
        .AutoFilterMode = False
        
    End With
End Sub



Private Sub CMDUpdate_Click()


Dim CurrentRow As Long
'Make Sheet1 Active
    Sheet1.Activate


'Update Records
Answer = MsgBox("Are you sure you want to update?", vbYesNo + vbQuestion, "Update Record")
    If Answer = vbYes Then
        Cells(CurrentRow, 1).Value = Customer.Value
        Cells(CurrentRow, 2).Value = CSONumber.Value
        Cells(CurrentRow, 3).Value = JobNumber.Value
        Cells(CurrentRow, 4).Value = PCWeldType.Value
        Cells(CurrentRow, 5).Value = PCWeldGrind.Value
        Cells(CurrentRow, 6).Value = PCFinish.Value
        Cells(CurrentRow, 7).Value = NonPCWeld.Value
        Cells(CurrentRow, 8).Value = NonPCGrind.Value
        Cells(CurrentRow, 9).Value = NonPCFinish.Value
    
        If BRReview.Value = True Then Cells(CurrentRow, 10).Value = "Yes"
        If BRReview.Value = False Then Cells(CurrentRow, 10).Value = "No"
        
        If BOMReview.Value = True Then Cells(CurrentRow, 11).Value = "Yes"
        If BOMReview.Value = False Then Cells(CurrentRow, 11).Value = "No"
        
        If DimReview.Value = True Then Cells(CurrentRow, 12).Value = "Yes"
        If DimReview.Value = False Then Cells(CurrentRow, 12).Value = "No"
        
        If WeldReview.Value = True Then Cells(CurrentRow, 13).Value = "Yes"
        If WeldReview.Value = False Then Cells(CurrentRow, 13).Value = "No"
          
        If Apperance.Value = True Then Cells(CurrentRow, 14).Value = "Yes"
        If Apperance.Value = False Then Cells(CurrentRow, 14).Value = "No"
         
        If Complete.Value = True Then Cells(CurrentRow, 15).Value = "Yes"
        If Complete.Value = False Then Cells(CurrentRow, 15).Value = "No"


End If








End Sub


Private Sub OKButton_Click()
Dim EmptyRow As Long
'Make Sheet1 Active
    Sheet1.Activate


'Determine Empty Row
EmptyRow = WorksheetFunction.CountA(Range("A:A")) + 1




'Transfer Information
Cells(EmptyRow, 1).Value = Customer.Value
Cells(EmptyRow, 2).Value = CSONumber.Value
Cells(EmptyRow, 3).Value = JobNumber.Value
Cells(EmptyRow, 4).Value = PCWeldType.Value
Cells(EmptyRow, 5).Value = PCWeldGrind.Value
Cells(EmptyRow, 6).Value = PCFinish.Value
Cells(EmptyRow, 7).Value = NonPCWeld.Value
Cells(EmptyRow, 8).Value = NonPCGrind.Value
Cells(EmptyRow, 9).Value = NonPCFinish.Value


If BRReview.Value = True Then Cells(EmptyRow, 10).Value = "Yes"
If BRReview.Value = False Then Cells(EmptyRow, 10).Value = "No"


If BOMReview.Value = True Then Cells(EmptyRow, 11).Value = "Yes"
If BOMReview.Value = False Then Cells(EmptyRow, 11).Value = "No"


If DimReview.Value = True Then Cells(EmptyRow, 12).Value = "Yes"
If DimReview.Value = False Then Cells(EmptyRow, 12).Value = "No"


If WeldReview.Value = True Then Cells(EmptyRow, 13).Value = "Yes"
If WeldReview.Value = False Then Cells(EmptyRow, 13).Value = "No"
  
If Apperance.Value = True Then Cells(EmptyRow, 14).Value = "Yes"
If Apperance.Value = False Then Cells(EmptyRow, 14).Value = "No"
 
If Complete.Value = True Then Cells(EmptyRow, 15).Value = "Yes"
If Complete.Value = False Then Cells(EmptyRow, 15).Value = "No"




'Sub Clearform()
  Dim ctrl As MSForms.Control
  For Each ctrl In Me.Controls
    Select Case TypeName(ctrl)
      Case "TextBox"
        ctrl.Text = ""
      Case "ComboBox"
        ctrl.ListIndex = -1
      Case "CheckBox"
        ctrl.Value = False
    End Select
  Next






End Sub




Private Sub UserForm_Click()


Call UserForm1_Initialize






End Sub

Any Help is appreciated
thank you


Bill Williamson

 

[dmt32]

Hi,
I have downloaded & will have a look asap but may be bit slow as on granddad duties next couple days.
The issue you highlight some relate to errors in code I spotted

I note you have a next button on the form - is it your intention to navigate the records using < Previous Next > buttons? As currently, all updates / new records, the form is cleared.

Dave

 

[Bill Williamson]

Dave,

Eventually if more than one "Record" of search item was found I would like to "Next" through to find neccesarry log. Customer name will repeate the most often but ocasionally the CSO Number will also have more than one match. The way it searches now only brings up first occurance of data. Wasnt sure if what I wanted was possible.

Having them clear after saving or updating is preferable.

BTW, Thanks for teaching me about the dropbox app. I have a feeling it will be very hand knowledge.

Regards,

Bill Williamson

 

[Bill Williamson]

Dave,

Just wanted to give an update on my progress. I have been working on the form , have almost all the bugs worked out.
the Save and the Update button are both working now. I used a combination of the old and new code you wrote to get it to work.
I will attach the link to current file version. You may see potential problems for me. Im going to start working on the Next and Prev buttons but I have to start a new thread for it. I dont want to break the rules. The Search is working, have a couple of minor glitches. I have not figured out why my error message for no results stopped working. and still have the two pop up messages to click on for updating instead of one, but its a minor thing.
I am excited to be learing somthing new, but there is so much to learn about VBA its a little Scary. I have been watching a lot of YouTube videos, they are great for the how but not so well with the why....

https://www.dropbox.com/s/jvz52b3gjvo2r9x/BOM Review Setup3.xlsm?dl=0

Thanks,

Bill Williamson

 

[dmt32]

hi,
sorry for belated replay but been really poorly with flu like symptoms.

I see you have been getting help in your other thread & hopefully all resolved now

Dave

 

[Bill Williamson]

ok, I belive I can post this here, kinda same topic. If I need to move it to a new thread please advise.

I am still having a little glitch between, Updating an edited record and adding an edited record.

If I do a search, edit the Data, then Use the "Update Button" it correctly updates the record.

If I edit the Data then try to "Add Record" as new, it just updates current row instead of adding it to last row.

The "Add Record" Works for New data created, but not for searched data.

I hope that didnt confuse you.

https://www.dropbox.com/s/lybjwvd9adh7y4w/BOM Setup(Current) - Copy.xlsm?dl=0

Thank you for any advice.

 

[dmt32]

Hi,
If you use the search facility and a record is returned - you have an existing record & Update button is enabled and Add Record disabled. This action is designed to prevent duplicates.

To Add a New Record, you would normally be entering a completely new record in your form and press Add New Record.

If I understand you correctly, you seem to want the option to Add a New Record From A Found Record?

Assuming you are using updated code, you may be able to do this so long as you change one of the three search values in the Textboxes to a completely new value & then press Search Button again. This should report record not found & re-set the status of the Update & New Record Buttons.

Dave

 

[Bill Williamson]

You are correct, Sometimes I have to enter several jobs for the Same Customer.
Job # Changes slightly but usually most of the Data is Same or similar. So this way I can pull up one that is close, modify then save it as new.

You are correct, that worked, extra step But I kinda like the security of not having duplicates.

Well Problem solved.

Thank you Dave.

Moving on to "Next" problem with the Next Button.


Best Regards,

Bill Williamson

 

[dmt32]

You are correct, Sometimes I have to enter several jobs for the Same Customer.
Job # Changes slightly but usually most of the Data is Same or similar. So this way I can pull up one that is close, modify then save it as new.

You are correct, that worked, extra step But I kinda like the security of not having duplicates.

Well Problem solved.

Thank you Dave.

Moving on to "Next" problem with the Next Button.


Best Regards,

Bill Williamson

Did you try my updated version with next Button?

Dave

 

[Bill Williamson]

That was the one without the "Prev." Button.
I looked at it and seen that it was a lot different, did not know if it would be compatible with the current version?

 

[dmt32]

That was the one without the "Prev." Button.
I looked at it and seen that it was a lot different, did not know if it would be compatible with the current version?

Copy you provided at time only had a Next Button & I updated code accordingly - but you since started another thread where you have been getting good help with a solution although similar in parts, is tad different to mine.

It would not be fair to other contributor to work on same problem between two threads & suspect against rules.

Probably best you finish tidying up your project issues with other Contributor.

Dave