Restrict Duplicate Record (1 Viewer)

[aftab1965]

If DCount("*", "[HistoryCM]", "[Local_ID] = '" & Me.Local_ID & "' AND [Noun] = '" & Me.Noun & "'") And Nz(Me.Status, 0) = 0 Or Nz(Me.Status, 0) <> "Open" > 0 Then
    MsgBox "The Corrective Maintenance of this Equipment is already Open. Open CM of An Equipment cannot be duplicated"
    Me.Status.SetFocus
    
    Cancel = True

I want to restrict duplication of record in a table [HistoryCM] if Filed Local_ID and Noun are same and status is “Open” . I used following VBA, but its not working. Is there any friend available to help me?

 

[June7]

What does "not working" mean - error message, wrong result, nothing happens?

If Local_ID is a number type field, don't use apostrophe delimiters.

Why do you have two Nz() for Status? If Status is a text field: Nz(Me.Status, "") <> "Open"

Expression is mixing AND and OR operators - parentheses are critical.

Your expression will process as: (This AND This) OR This

But perhaps you want: This AND (This OR This)

 

[aftab1965]

What does "not working" mean - error message, wrong result, nothing happens?

If Local_ID is a number type field, don't use apostrophe delimiters.

Why do you have two Nz() for Status? If Status is a text field: Nz(Me.Status, "") <> "Open"

Expression is mixing AND and OR operators - parentheses are critical.

Your expression will process as: (This AND This) OR This

But perhaps you want: This AND (This OR This)

Thanks for an early reply.
Its a compile error.
Local_ID is Text field.
If you have free time, Can you please share correct code

 

[Isaac]

Hmm. I think you want

If DCount("*", "[HistoryCM]", "[Local_ID] = '" & Me.Local_ID & "' AND [Noun] = '" & Me.Noun & "'") > 0 And (Nz(Me.Status, 0) = 0 Or Nz(Me.Status, 0) <> "Open") Then
    MsgBox "The Corrective Maintenance of this Equipment is already Open. Open CM of An Equipment cannot be duplicated"
    Me.Status.SetFocus
    
    Cancel = True

 

[aftab1965]

Hmm. I think you want

If DCount("*", "[HistoryCM]", "[Local_ID] = '" & Me.Local_ID & "' AND [Noun] = '" & Me.Noun & "'") > 0 And (Nz(Me.Status, 0) = 0 Or Nz(Me.Status, 0) <> "Open") Then
    MsgBox "The Corrective Maintenance of this Equipment is already Open. Open CM of An Equipment cannot be duplicated"
    Me.Status.SetFocus
   
    Cancel = True

Thanks for your time, but same compile error again

 

[Isaac]

see June's questions about datatypes and code accordingly.
text fields need single quotes, number fields need nothing, date fields need octothorpe

 

[June7]

The If Then looks good so which line is triggering the compile error?

Not showing End If line - is there one?

 

[aftab1965]

Thanks all of you for a guidance.