Compare strings!

[Jar80]

I have form that contains information about workers, now i want that when i insert exactly same worker twice and press button then it informs that there are already same person in the database and do not close the form.

So i think that can be done by, compare strings, but how?

 

[afryer]

Hi,

I think the easiest way to do this will be either to make the worker name a unique key or a primary key, which will cause an error when entered or simply to do a dlookup command to check whether the name exists before trying to enter it.

I think the unique key idea is the best because you don't really have to do anything, just trap the error.

Hope this helps

Andrew

 

[p27br]

Hi,

add the following code to the beforeupdate of your textbox control

Private Sub txtWorker_BeforeUpdate(Cancel As Integer)
    If WorkerExists Then
        MsgBox "This worker already exists or an error occured !"
        Cancel = True
    End If
End Sub

End Sub
Function WorkerExists() As Boolean
    
    Dim rst As ADODB.Recordset
    Dim strSQL As String
    
    On Error GoTo ErrorHandler
    
    Set rst = New ADODB.Recordset
    
    strSQL = "SELECT WORKER " & _
             "FROM tblDataset " & _
             "WHERE WORKER = '" & Me!txtWORKER.Value & "'"
             
    With rst
        .Open strSQL, CurrentProject.Connection, adOpenStatic, adLockOptimistic
        If .EOF Or .BOF Then
            WorkerExists = False
        Else
            WorkerExists = True
        End If
    End With
    
ExitHere:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Exit Function
    
ErrorHandler:
    Select Case Err.Number
        Case Else
            MsgBox Err.Number & " " & Err.Description
            WorkerExists = True
    End Select
    Resume ExitHere
End Function

 

[Jar80]

i wnat that works when i insert exactly the same worker name that already exist there.

Sorry i don´t understand iam newbie!

 

[Jar80]

i want that works when i insert exactly the same worker name that already exist there.

Sorry i don´t understand iam newbie!

 

[p27br]

just a thought : what if two workers have the same name ?

eg John Smith ?

I wouldn't try to compare the names only, you may want to add a social security number field and do a check on that, it is more likely to uniquely identify a worker.

 

[p27br]

you have to ask yourself which of the fields in your Workers table is the best candidate to be unique. The name is not usually a good choice, because you could have 2 Smiths or Jones,..
That's why I suggested you use the social security number or an ID number, depending in which country you are a person can be identified in different ways.

If you open the form in design view, create a button, call it cmdCheck. Then open the command button's property window and select the event tab. Select the on click event and choose event procedure then click the 3 dot button to the right, you open the form's VBA module.
copy/paste the following code

    If WorkerExists Then
        MsgBox "This worker already exists or an error occured !"
        Cancel = True
    End If

then under the End Sub, copy/paste this code :

Function WorkerExists() As Boolean
    
    Dim rst As ADODB.Recordset
    Dim strSQL As String
    
    On Error GoTo ErrorHandler
    
    Set rst = New ADODB.Recordset
    
    strSQL = "SELECT WORKER " & _
             "FROM tblDataset " & _
             "WHERE WORKER = '" & Me!txtWORKER.Value & "'"
             
    With rst
        .Open strSQL, CurrentProject.Connection, adOpenStatic, adLockOptimistic
        If .EOF Or .BOF Then
            WorkerExists = False
        Else
            WorkerExists = True
        End If
    End With
    
ExitHere:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Exit Function
    
ErrorHandler:
    Select Case Err.Number
        Case Else
            MsgBox Err.Number & " " & Err.Description
            WorkerExists = True
    End Select
    Resume ExitHere
End Function

 

[p27br]

there is another way, I just remembered :

create an autonumber field as primary key (WorkerId)
You can add a unique index on the worker name, this will prevent the same name being entered twice.

In table design, click on the lightening icon (indexes) and add a new index. Set unique to YES.