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

mageeg

Board Regular
Joined
Jun 6, 2021
Messages
81
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:

VBA Code:
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:

Code:
  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?
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,917
Office Version
  1. 2019
Platform
  1. Windows
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

Rich (BB code):
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

Board Regular
Joined
Jun 6, 2021
Messages
81
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

Rich (BB code):
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,917
Office Version
  1. 2019
Platform
  1. Windows
You just add a line in the Edit Button code like this

Rich (BB code):
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

Board Regular
Joined
Jun 6, 2021
Messages
81
You just add a line in the Edit Button code like this

Rich (BB code):
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,917
Office Version
  1. 2019
Platform
  1. Windows
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
 

Forum statistics

Threads
1,147,632
Messages
5,742,232
Members
423,714
Latest member
ftp2jz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top