DCount problem

[edojanssen]

I'm building a db which holds vehicles and their driver. The form Vehicles has a subform which contains the drivers and the begindate and enddate. What I want is to secure this form so that no duplicate records can be made. If you enter a drivers name there must be a check that the driver is not on another vehicle and no enddate. When the enddate is added, the driver can be selected to be entered on other vehicles.

I already tried but I get a syntax error:

    If DCount("[Bestuurder]", "qryBestuurderCheck", "[Bestuurder]='" & Forms![sfrmKentekens]![Bestuurder] & "'") Then '> 0 Then
        MsgBox "De gekozen bestuurder bestaat al " & _
        vbCrLf & "bij een ander kenteken!"
        Me.Bestuurder.Undo
    Else
    End If

Who can help me out?

 

[Alc]

Is this any better?

If DCount("Bestuurder", "qryBestuurderCheck", "Bestuurder = '" & Forms![sfrmKentekens]![Bestuurder] & "'") > 0 Then
        MsgBox "De gekozen bestuurder bestaat al " & _
        vbCrLf & "bij een ander kenteken!"
        Me.Bestuurder.Undo
End If

 

[edojanssen]

It doesn't work. Now I got error 2450: 'can't find form sfrmKentekens'.

I've put the code in the before_update-event of the field [Bestuurder] in the subform sfrmKentekens. Don't know if that makes any difference.

 

[Alc]

At the risk of sounding patronising (but honestly not meaning to), is the form definitely called 'sfrmKentekens'?

 

[edojanssen]

The subform is called sfrmKentekens, the mainform is frmKentekens. I've attached a part of my db. I hope this will make it easier to solve the problem.

 

[Alc]

Soryy about the delay, the boss was insisting I actual did some work (the nerve! )

I tried changing the expression to

If DCount("Bestuurder", "qryBestuurderCheck", "Bestuurder = '" & Me![Bestuurder] & "'") > 0 Then
        MsgBox "De gekozen bestuurder bestaat al " & _
        vbCrLf & "bij een ander kenteken!"
        Me.Bestuurder.Undo
End If

It now runs, and I got the message displayed on the few I tested it for, but my German(?) isn't up to knowing if it's displaying it when it should.

 

[edojanssen]

Hi Alc,

It's working like the way I wanted. I've changed the msgBox a little so the user will know at which vehicle the double driver excists.

Dim strKenteken As String
strKenteken = DLookup("Kenteken", "qryBestuurderCheck", "Bestuurder = '" & Me![Bestuurder] & "'")

If DCount("Bestuurder", "qryBestuurderCheck", "Bestuurder = '" & Me![Bestuurder] & "'") > 0 Then
        MsgBox "De gekozen bestuurder bestaat al " & _
        vbCrLf & "bij kenteken " & strKenteken & "!"
        Me.Undo
End If

I've added your name to the credits of the program.
Thanks a lot
Edo

 

[Alc]

No problem, glad to be of help and thanks for letting me know it worked out okay.