Highlighting Duplicates with Conditional Formatting 2

[rccline]

I have an MS Access form that is based on a query. I want to apply conditional formatting to highlight the field on the form if the data is already in the dataset.

the query name is qry_copyrequest.
the table name is tbl_copyrequest
the field with the duplicates is BOOK-PAGE-2012
the datatype is character

Trying to follow microsoft's tutoral, I write:

DCount("*", "qry_copyrequest", "BOOK-PAGE-20120" = " & [BOOK-PAGE-2012]) > 1

Return error "Invalid string."

I am so stumped. Thank you.

Robert

 

[dhookom]

Several issues.
[ol 1]
[li]You have an extra 0 at end of field name[/li]
[li]You shouldn't have a " to the left of =.[/li]
[li]You must add double quotes around text fields.[/li]
[/ol]

This worked for me:

DCount("*","qry_copyrequest","[BOOK-PAGE-2012] =""" & [BOOK-PAGE-2012] & """")>1

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[rccline]

Duane: Thank you very much. This code is not working for me. I have copied the code into the expression exactly.
I applied the same formatting structure on the field RequestID which is a Number, Double. That doesn't work either.

qry_copyrequest: 

SELECT tbl_copyrequest.ID, tbl_copyrequest.RequestID, tbl_copyrequest.DateRequested, tbl_copyrequest.DateReceived, tbl_copyrequest.Index, tbl_copyrequest.[DOCUMENT NAME], tbl_copyrequest.BOOK_TYPE, tbl_copyrequest.[BOOK-PAGE-2012], tbl_copyrequest.InstrNo_2013, tbl_copyrequest.EntireDoc_PgNos, tbl_copyrequest.TotalPages, tbl_copyrequest.Comments, tbl_copyrequest.Duplicate
FROM tbl_copyrequest;

The fields on my form are populating, but the conditional formatting is not applied.

 

[rccline]

Formatting for a value works.

 

[dhookom]

Are you getting an error? Is the background transparent?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[rccline]

I am getting no error. I set the ID to turn red if n>1. And, the background should turn red if the record BOOK-PAGE-2012 is a duplicate.
I checked the record and there are no spaces in 71-151 in either the new record or the original record.

 

[rccline]

Duane:

Again, thank you for trying to help with this issue. I checked the data which was input, wondering if special characters (i.e. the "-") made a difference. It did not.

After entering a value without special characters, the expression is still not evaluating for a duplicate.

I attempted a VBA approach. Something is wrong with the If DCount line. It returns a Compile error: Expected: list separator or )

Private Sub BOOK_PAGE_2012__BeforeUpdate(Cancel As Integer)
    If DCount("qry_copyrequest", "BOOK-PAGE-20212", "BOOK-PAGE-2012='" & Me.BOOK-PAGE-2012.Value & "'") > 1 Then
        MsgBox "The Current BOOK-PAGE has been used already, please supply a different BOOK-PAGE to continue."
        Cancel = True
    End If
End Sub

Robert

 

[dhookom]

I don't believe you answered my question regarding the background transparency.

This all assumes your statement regarding field type of text for [BOOK-PAGE-2012].
This is a screenshot that I made from a test of your request:

Regarding your code, I think your use of special characters in a field name are biting you. I might use underscores in object names but prefer CamelCase so I don't have to add []. Also, your arguments for the DCount() had changed from previous posts.

Private Sub BOOK_PAGE_2012__BeforeUpdate(Cancel As Integer)
    debug.print  Me.[BOOK-PAGE-2012].Value  [COLOR=#4E9A06]'check the value since it might not be set[/color]
    If DCount("*", "qry_copyrequest", "[highlight #FCE94F][[/highlight]BOOK-PAGE-2012[highlight #FCE94F]][/highlight]='" & Me.[highlight #FCE94F][[/highlight]BOOK-PAGE-2012[highlight #FCE94F]][/highlight].Value & "'") > 1 Then
        MsgBox "The Current BOOK-PAGE has been used already, please supply a different BOOK-PAGE to continue."
        Cancel = True
    End If
End Sub

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[rccline]

Duane:

Wow! Thank you for going to such extremes.

As for the background: I had set the background to Red and Text to Bold:White.

Now, I have renamed the fields. BOOK-PAGE-2012 is now BkPg. Still, no formatting when duplicates exist.

I have tried the following code which did not work:

Private Sub BOOK_PAGE_2012__BeforeUpdate(Cancel As Integer)
    Debug.Print Me.[BkPg].Value   'check the value since it might not be set
    If DCount("*", "qry_copyrequest", "[BkPg]='" & Me.[BkPg].Value & "'") > 1 Then
        MsgBox "The Current BOOK-PAGE has been used already, please supply a different BOOK-PAGE to continue."
        Cancel = True
    End If
End Sub

I tried a different code. The first time I entered it, it ran. I then tried to work with the MsgBox. I got an error on Me.BkPg.
I could not revise the code to get it to run again.

Private Sub BOOK_PAGE_2012__BeforeUpdate(Cancel As Integer)
Dim sBkPg As String
Let sBkPg = DLookup("[BkPg]", "tbl_copyrequest", "[BkPg] = '" & Me.BkPg & "'")
' MsgBox "Book-Page already exists for " & BkPg & "!"  'The message box does not work.  

End Sub

 

[Andrzejek]

What is this [tt]Let[/tt]???

Private Sub BOOK_PAGE_2012__BeforeUpdate(Cancel As Integer)
Dim sBkPg As String
[highlight #FCE94F]Let[/highlight] sBkPg = DLookup("[BkPg]", "tbl_copyrequest", "[BkPg] = '" & Me.BkPg & "'")
' MsgBox "Book-Page already exists for " & BkPg & "!"  'The message box does not work.  

End Sub

"I have renamed the fields. BOOK-PAGE-2012 is now BkPg" "so I don't have to add []", but you still do
---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[rccline]

I tried DLookup code I found on Stackoverflow. I modified it to fit my ACCDB.

Option Compare Database
Private Sub BOOK_PAGE_2012__BeforeUpdate(Cancel As Integer)
Dim sBkPg As String
Let sBkPg = DLookup("[BkPg]", "qry_copyrequest", "[BkPg] = '" & Me.BkPg & "'")
MsgBox "This Book & Page already exists in the database.  Enter a different Book & Page."
' Let dateAdded = CStr(DLookup("[Date]", "tblLog", "[Telephone] = '" & Me.Telephone & "'"))
End Sub

When entering a Book and Page, I got the following error. I think this might have been the issue for the DCount() as well. After renaming the control source, the code worked. The MsgBox is not working yet.

Thank you so much Duane. This stuff is so hard to learn! You are awesome.

Robert

 

[rccline]

Andrzejek asked "What is the Let?" I have no idea. It was contained in the code posted on Stackoverflow by "Rick."

I was thinking, "it works!" But, the VBA code does not have any method described to change the color. What changed was renaming the control source.

 

[dhookom]

Did you take a look at the debug window to check the value of BkPg? That's typically a statement I put in prior to even needing error detection.

Debug.Print Me.[BkPg].Value   [COLOR=#4E9A06]'check the value since it might not be set[/color]

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[rccline]

What a great idea. I cannot step through the code.

Private Sub BOOK_PAGE_2012__BeforeUpdate(Cancel As Integer)
Dim sBkPg As String
Debug.Print Me.[BkPg].Value   'check the value since it might not be set
Let sBkPg = DLookup("[BkPg]", "qry_copyrequest", "[BkPg] = '" & Me.BkPg & "'")
MsgBox "This Book & Page already exists in the database.  Enter a different Book & Page."
' Let dateAdded = CStr(DLookup("[Date]", "tblLog", "[Telephone] = '" & Me.Telephone & "'"))
End Sub

 

[dhookom]

The debug/immediate window should have a value in it after executing. What is that value (if any)?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[rccline]

Debug.Print Me.[BkPg].Value 'check the value since it might not be set
Compile error: Variable not yet created in this context.

 

[dhookom]

Your code has;
Let sBkPg = DLookup("[BkPg]", "qry_copyrequest", "[BkPg] = '" & Me.BkPg & "'")

This suggests there is a control named BkPg. You should always add Option Explicit at the top of every module.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[rccline]

I added Option Explicit and removed Option Compare Database.

I ran

Debug.Print Me.[BkPg].Value

in the immediate window.
I am still getting the Compile Error "Variable not yet created in this context."

Is it wrong to have a control named BkPg ? The Conditional Formatting Expression did not work until I renamed the control BkPg.

Conditional Formatting Expression (which is now working):

DCount("*","qry_copyrequest","[BkPg] =""" & [BkPg] & """")>1

 

[dhookom]

I never use .Value since it’s the default property of bound controls. Are you sure the code is running in a form that has a text box with the name BkPg?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[rccline]

I created the sub by creating a Before Update Event Procedure on the form.

Private Sub BOOK_PAGE_2012__BeforeUpdate(Cancel As Integer)
Dim sBkPg As String
Debug.Print Me.[BkPg].Value   'check the value since it might not be set
Let sBkPg = DLookup("[BkPg]", "qry_copyrequest", "[BkPg] = '" & Me.BkPg & "'")

MsgBox "This Book & Page already exists in the database.  Enter a different Book & Page."

End Sub