I have the below code that was given to me, how can i trim the leading space when users put in front of name when running macro to add a new record? (name is on "C1" cell in excel)
I have tried to use 'trim' in various parts, but no luck please help.
Code:Private Sub CommandButton3_Click() Dim check(2) As Variant Dim cnn As ADODB.Connection Set cnn = New ADODB.Connection cnn.Provider = "Microsoft.ACE.OLEDB.12.0" cnn.ConnectionString = "Data Source=Z:\desktop\Database.accdb" cnn.Open Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset rst.Open "datatable", cnn, adOpenForwardOnly, adLockOptimistic, adCmdTable ''''''''''''''''''''''''''''''''''''''''''''''''''''' Check if customer already exist then do not add check(0) = ActiveSheet.Range("Customer") If IsNull(check(0)) Then Exit Sub Else check(1) = 0 Do If rst![Name] = check(0) Then check(1) = 1 End If rst.MoveNext Loop Until rst.EOF End If '''''''''''''''''''''''''''''''''''''''''''''''''''' If record do not exist then add If check(1) = 0 Then rst.AddNew rst![Name] = Sheets("Quote").Range("C1") rst![Address] = Sheets("Quote").Range("C2") rst![phone] = Sheets("Quote").Range("C3") rst.Update End If rst.Close cnn.Close End Sub