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

 

[Andrzejek]

Do you have a control on your Form named [tt]BOOK_PAGE_2012[highlight #FCAF3E]_[/highlight][/tt] with an underscore at the end?
I ask because you have a:[tt]
Private Sub BOOK_PAGE_2012[highlight #FCE94F]_[/highlight]_BeforeUpdate(Cancel As Integer)[/tt]

---- Andy

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

 

[rccline]

I do not know how to create a query to show all names on the controls on the form. I can click through the form in layout mode and look at the control names.

There is a subform on the form that references the control BkPg within the same query as the form.

Name BOOK-PAGE-2012)
Label Name BOOK-PAGE-2012)_Label
Control Source BkPg

 

[dhookom]

I think your issue is somehow related to the ) at the end of your control name. Is that really there? Do you know how that happened? I would try change the name of the control to BkPg and then move your code to the after update of BkPg.

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

 

[Andrzejek]

I added Option Explicit

Assuming this is done to all Forms, the code with references to non-existing controls should not compile...

---- Andy

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

 

[rccline]

(1) I do not remember how the ) got there. I do remember removing it from the form. I did not remove it from the subform.
(2) I deleted the old subform and created a new subform with the control named BkPg.
(3) The backcolor from the Conditional Expression is working.
(4) Debug.Print Me.[BkPg].Value still returnes Compile error: Variable not yet craetred in this control.
(5) The MsgBox does not deliver the message, but the background color does change when a duplicate is created.

It would be good to understand why the VBA does not run, which should run the MsgBox.
But practically speaking, the Conditional Formatting is working.

I hate to continue to take up your time since I have something that works. You have been most generous with your assistance.

 

[dhookom]

You should select the text box where you want the code to run, view the Event properties, click the After Update property to see if there is any code.

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

 

[strongm]

Assuming your form is called Form1 as per your image in your post of 29 Apr 22, wherever you are using Me.BkPg can you please try using Forms!Form1!BkPg instead