VBA Data Entry Form - How to edit with validations set?

[mageeg]

Hi All, sorry for the long post.. looking for some advice

I have created an excel database, where the user uses an entry form to enter data, which then is transferred to the "database" sheet.

I then have an edit button, which allows the user to choose a row of the database, in which the data from that row is returned back into the fields on the form for the user to edit. See some code below:

Me.txtRowNumber.Value = Application.WorksheetFunction.Match(Me.lstDatabase.List(Me.lstDatabase.ListIndex, 0), _
    ThisWorkbook.Sheets("Database").Range("A:A"), 0)
    
     Me.ModelNo.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 1)
    
    Me.PartNo.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 2)
    
    Me.WorksOrderNo.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 3)
    
    Me.SerialNo.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 4)
    
    Me.MaterialNo.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 5)
    
    Me.SerialNumber.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 6)
    
    Me.txtType.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 7)
    
    Me.txtSize.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 8)
    
    Me.txtWKPRESS.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 9)
    
    Me.txtCertDate.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 10)
 
    Me.BatchNo.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 11)
    
    Me.JobNo.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 12)
    
    Me.DateOfManufacture.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 13)
    
    
   MultiPage1.Value = 0
    MsgBox "Please make the required changes and click on 'Save' button to update.", vbOKOnly + vbInformation, "Edit"

This all works well.

However, i have recently added validations to some fields in the data entry. E.g. a Works Order Number can only be used once so that there is no duplicate entry for this in the database. this is done like so:

  If Application.WorksheetFunction.CountIf(sh.Columns("D:D"), frmForm.WorksOrderNo.Value) > 0 Then
        MsgBox frmForm.WorksOrderNo.Value & " - This Works Order Number already exists", vbOKOnly, "PLEASE TRY AGAIN!"
        Exit Sub
    End If

These validations then cause a problem when it comes to editing, as when i click my edit button, edit some data then press save, it gives you the "Works Order Number already in use error" as this number is already in the database.

How should i avoid this?

 

[dmt32]

Hi,
one suggestion, in your edit codes that returns the record to the userform, add a line of code that changes the Save button caption from Save to Update.

Then in code that checks for duplicate entry include a test to check Save button Caption

If Application.WorksheetFunction.CountIf(sh.Columns("D:D"), frmForm.WorksOrderNo.Value) > 0 And Me.cmbSave.Caption = "Save" Then
        MsgBox frmForm.WorksOrderNo.Value & " - This Works Order Number already exists", vbOKOnly, "PLEASE TRY AGAIN!"
        Exit Sub
    End If

This should then ignore the check code when in Update mode.

You will need to change caption back to Save once update process complete.

Hope helpful

Dave

 

[mageeg]

Hi,
one suggestion, in your edit codes that returns the record to the userform, add a line of code that changes the Save button caption from Save to Update.

Then in code that checks for duplicate entry include a test to check Save button Caption

If Application.WorksheetFunction.CountIf(sh.Columns("D:D"), frmForm.WorksOrderNo.Value) > 0 And Me.cmbSave.Caption = "Save" Then
        MsgBox frmForm.WorksOrderNo.Value & " - This Works Order Number already exists", vbOKOnly, "PLEASE TRY AGAIN!"
        Exit Sub
    End If

This should then ignore the check code when in Update mode.

You will need to change caption back to Save once update process complete.

Hope helpful

Dave

Hi Dave,

Thanks for your input, that does sound like a really good idea!

How easy would it be to update the caption of the save button on the original form from 'save' to 'update' once edit is clicked, and then back again after edit mode?

If possible could you provide sample code as to how I would go about doing this? that would be a huge help!

Thank you again for your idea it sounds good!

 

[dmt32]

You just add a line in the Edit Button code like this

Me.cmbSave.Caption = "Update"

change the Save commandbutton name as required

Do remember to change it back to Save once update process complete.

Dave

 

[mageeg]

You just add a line in the Edit Button code like this

Me.cmbSave.Caption = "Update"

change the Save commandbutton name as required

Do remember to change it back to Save once update process complete.

Dave

Ok perfect thank you

How would you recommend to change back from update to save. Maybe when the reset button on form is pressed?

Or could i do it after update is pressed to go back to save?

 

[dmt32]

Ok perfect thank you

How would you recommend to change back from update to save. Maybe when the reset button on form is pressed?

Or could i do it after update is pressed to go back to save?

If the record in the form is cleared after the updated is completed then change button back to Save.

Dave