Additional code require for currently working code

[ipbr21054]

Morning all,

The supplied code below i believe is what you require & is currently in use BUT i would like an extra piece of code added to it if i may ask please.
This is how it currently works,
I select ADD NEW CUSTOMER TO DATABASE & complete all fields.
I then select SAVE NEW CUSTOMER TO DATABASE,this then saves the data into my worksheet.
This currently works fine.

The extra piece of code i require is as follows,
Once all the fields are completed & SAVE NEW CUSTOMER TO DATABASE is selected BEFORE it is saved to the worksheet check column A to see if there is a match.
The two items that need to be checked would be Customers name on user form & Customers name which is in Column A on my worksheet.
It would work something like this,
If no match is found then allow the save to go ahead,which is what currently happens.
If there is a match then maybe have message box appear saying customer exists etc.
Allow me to then edit that customers name on the user form etc and then be able to select SAVE NEW CUSTOMER TO DATABASE for the save to go ahead.
This will then allow me NOT to have duplicated customers names in my worksheet.
Check should allow upper case / lower case check and not be 100% specific match etc, Dave Tomms / DAVE TOMMS etc etc

Many thanks & hope i have not over explained it ?

Private Sub UpdateRecord_Click()    Dim i As Integer
    Dim IsNewCustomer As Boolean
    Dim Msg As String
    
    
    IsNewCustomer = CBool(Me.UpdateRecord.Tag)
    
    Msg = "CHANGES SAVED SUCCESSFULLY"
    
    If IsNewCustomer Then
    'New record - check all fields entered
    If Not IsComplete(Form:=Me) Then Exit Sub
        r = StartRow
        Msg = "NEW CUSTOMER SAVED TO DATABASE"
        ws.Range("A6").EntireRow.Insert
        ResetButtons Not IsNewCustomer
        Me.NextRecord.Enabled = True
    End If
    
    On Error GoTo myerror
    Application.EnableEvents = False
    'Add / Update Record
    For i = 1 To UBound(ControlNames)
        With Me.Controls(ControlNames(i))
            'check if date value
            If IsDate(.Text) Then
                ws.Cells(r, i).Value = DateValue(.Text)
            ElseIf i = 15 Then
                ws.Cells(r, i).Value = CDbl(.Text)
            Else
                ws.Cells(r, i).Value = UCase(.Text)
            End If
                ws.Cells(r, i).Font.Size = 11
        End With
    Next i
    
    If IsNewCustomer Then Call ComboBoxCustomersNames_Update
        
    ThisWorkbook.Save
    
    'tell user what happened
    MsgBox Msg, 48, Msg
    
myerror:
Application.EnableEvents = True
'something went wrong tell user
If Err > 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub


Sub ResetButtons(ByVal Status As Boolean)
    
    With Me.NewRecord
        .Caption = IIf(Status, "CANCEL", "ADD NEW CUSTOMER TO DATABASE")
        .BackColor = IIf(Status, &HFF&, &H8000000F)
        .ForeColor = IIf(Status, &HFFFFFF, &H0&)
        .Tag = Not Status
    Me.ComboBoxCustomersNames.Enabled = CBool(.Tag)
    End With
    
    With Me.UpdateRecord
        .Caption = IIf(Status, "SAVE NEW CUSTOMER TO DATABASE", "SAVE CHANGES FOR THIS CUSTOMER")
        .Tag = Status
    End With
End Sub

 

[ipbr21054]

Im very happy with they way this is going forward.
Here are a couple of things i have noticed.
By no way am i complaining but mentioning what is happening.

When the user form is open & i make an edit to an existing customers info in one of the other text boxes , I select SAVE CHANGES FOR THIS CUSTOMER & now the message CUSTOMER ALREADY EXITS pops up & doesn't save the edit to the existing customer.
Not sure how to allow an existing customer to have an edit saved so the code doesn't think it is a new entry,this then shows the code is working correctly.
I am now trying to think of a work around for this.

 

[ipbr21054]

This might be a bit extreme but a button on the user form which when pressed allows me to edit an existing customer & for the save to go ahead,this button would then disable the code which currently doesn't allow duplicate customers in my worksheet column A.
After the save has completed successfully then the button would come back to life etc ?

What do you think ?

Many thanks

 

[dchaney]

Move the following from Private Sub UpdateRecord_Click() to Private Sub NewRecord_Click()

    Set c = Sheets("DATABASE").Range("A:A").Find(txtCustomer.Text, LookIn:=xlValues)    
    If Not c Is Nothing Then
          MsgBox "Customer already Exists, file did not update"
          Exit Sub
    End If

The code you shared below is for the updating button, you want to place this on the Add New button code

 

[ipbr21054]

Sorry my mistake,
Have now cut out that part of the code and placed it where advised.
I now have it as shown.

Private Sub NewRecord_Click()    Dim i As Integer
    Dim IsNewCustomer As Boolean
        Set c = Sheets("DATABASE").Range("A:A").Find(txtCustomer.Text, LookIn:=xlValues)
    
    If Not c Is Nothing Then
          MsgBox "[COLOR=#333333]Customer already Exists, file did not update[/COLOR]"
          Exit Sub
    End If
    IsNewCustomer = CBool(Me.NewRecord.Tag)
    
    Navigate Direction:=IIf(IsNewCustomer, xlNone, xlPrevious)




    'if new customer, add Date
    If IsNewCustomer Then
        Me.txtJobDate.Text = Format(Date, "dd/mm/yyyy")
        Me.txtCustomer.SetFocus
    End If
    
    ResetButtons IsNewCustomer




End Sub

If this is correct then this is what now happens.
I can edit an existing customer & then save the details,i also then see the message CHANGES SAVED SUCCESSFULLY for this customer.

I would now like to add a customer so by clicking ADD NEW CUSTOMER TO DATABASE i see the message box saying CUSTOMER ALREADY EXITS,possibly because the fields are showing the previous customers details in each text box.

If i remove the code shown below back to what it was originally then click ADD NEW CUSTOMER TO DATABASE then i am presented with all text boxes cleared awaiting for new customers details to be input.

        Set c = Sheets("DATABASE").Range("A:A").Find(txtCustomer.Text, LookIn:=xlValues)    
    If Not c Is Nothing Then
          MsgBox "[COLOR=#333333]Customer already Exists, file did not update[/COLOR]"
          Exit Sub
    End If

 

[dchaney]

If the box on the top Named "Customer Name" has a customer name in it that already exists then the code will not allow you to add that user... I thought that is what you wanted?

Also the way you have the database form setup it will grab the data from Row 6, if you want to add a new customer you have to add a new row first.

 

[ipbr21054]

Yes that is correct as i want to be informed if customer exists,this works well.

The way the form is set up is when i add a new customer into the user form then click on save etc this customer is then saved into Row 6

Example.
I open up user form and make an edit to a customer in the worksheet database.
I click SAVE CHANGES FOR THIS CUSTOMER & i then see the message box advising CHANGES SAVED SUCCESSFULLY,i then click on OK & message box is gone.
What i now see on my screen is the photo shown below.

I now need to add a fresh customer to my database so i then click ADD NEW CUSTOMER TO DATABASE.
Now without the code below what then happens is all the text boxes are cleared,i then enter the fresh customers details in each field & select save.
This fresh customer is now in my worksheet database.

BUT
With the code when i click ADD NEW CUSTOMER TO DATABASE the message box pops up advising me CUSTOMER ALREADY EXISTS.

    Set c = Sheets("DATABASE").Range("A:A").Find(txtCustomer.Text, LookIn:=xlValues)    
    If Not c Is Nothing Then
          MsgBox "CUSTOMER ALREADY EXISTS, ADD (?) ETC AFTER THERE NAME"
          Exit Sub
    End If

The code sees the text boxes have data and thinking that i have just entered it & now advising me this customer exists.
When in fact the data was saved after an edit,really i think these boxes should be cleared,false positive etc.
With the boxes clear the code doesn't see anything,its job is to search for a duplicate AFTER i enter data into each text box.
With me on this ??

 

[ipbr21054]

As a test lets code to clear these text boxes after edit has been saved.
These are the fields.
txtCustomer
txtVehicle
txtRegistrationNumber
txtJobDate
txtChassisNumber
txtVehicleYear
txtProgrammerCloner
txtBlankUsed
txtKeyCode
txtBiting
txtKeySupplied
txtButtons
txtTransponderChip
txtJobAction
txtPaid

 

[ipbr21054]

Somehow add these just after CHANGES SAVED SUCCESSFULLY in UpdateRecord ?

Me.txtCustomer = ""
Me.txtVehicle = ""
Me.txtRegistrationNumber = ""
Me.txtJobDate = ""
Me.txtChassisNumber = ""
Me.txtVehicleYear = ""
Me.txtProgrammerCloner = ""
Me.txtBlankUsed = ""
Me.txtKeyCode = ""
Me.txtBiting = ""
Me.txtKeySupplied = ""
Me.txtButtons = ""
Me.txtTransponderChip = ""
Me.txtJobAction = ""
Me.txtPaid = ""

 

[dchaney]

I see what your doing now, you are using the same buttons, just changing the caption of them when you save a new or existing customer, let me play on it a bit will let you know.

 

[ipbr21054]

Ok
Im am using a copy and trying to clear the fields after the edit etc so i can click add customer etc.
This is me playing with it but i await your input.

Many thanks