Solved Highlight Duplicated Customer in a form (1 Viewer)

[Number11]

I am trying to get this to work in a sub form, to highlight the customers number in red and yellow background but its not working, the field in a text field


DCount("*","Customer_main","[Service_Number]=" & Chr(34) & [Service_Number] & Chr(34))>1

 

[Minty]

Where is this DCount being used ?
In a query, on a control, in conditional formatting or something else?
Some context would help.

 

[Number11]

Where is this DCount being used ?
In a query, on a control, in conditional formatting or something else?
Some context would help.

Its on a sub form - conditional formatting on the field Service_Number

 

[Minty]

Why not add a query that counts the number of records and left join that to your sub-form data source.
Use the number from the query result to drive the CF.

I suspect using a DCount in the CF will be very slow/flickery.

 

[Number11]

Why not add a query that counts the number of records and left join that to your sub-form data source.
Use the number from the query result to drive the CF.

I suspect using a DCount in the CF will be very slow/flickery.

Thats a good points, its just the file process is that access shows the user a final form that i wanted it to show any duplicates before the user clicks a button to create a report, but maybe thats the best way so query to run and get duplicated and then how would i show on the subform?

 

[Minty]

Bring the count from the totals query onto the form as a hidden text box, remember it's linked to the row by the service number from the underlying form's query.
Then use that in the conditional formatting.

 

[Number11]

Bring the count from the totals query onto the form as a hidden text box, remember it's linked to the row by the service number from the underlying form's query.
Then use that in the conditional formatting.

not sure how to do that

 

[Minty]

Can you show us the rowsource for the sub-form or is it directly connected to a table?

 

[Number11]

Can you show us the rowsource for the sub-form or is it directly connected to a table?

The sourcerow is the Table called Customer_main

 

[Minty]

Create a totals query, without seeing your table this is guess work, but the sql will be something like

SELECT Count(Service_Number) FROM Customer_main
Group By Service_Number, CustomerID

If that returns the numbers you are looking for save it as qryCustServiceNos

 

[Number11]

Create a totals query, without seeing your table this is guess work, but the sql will be something like

SELECT Count(Service_Number) FROM Customer_main
Group By Service_Number, CustomerID

If that returns the numbers you are looking for save it as qryCustServiceNos

Thanks so i now got that query work now, so how do i then go about highlighting the customer within the form in red using the new query i wonder

 

[Minty]

Join that query to your subforms table, use a left join and make sure you can still edit the table.
Save that as a new query and bring in the count field as part of the forms record source.

You can now use that number to drive the conditional formatting.

 

[Number11]

Conditional formatting i need to set on the "Service_Number" and get it to look at the new Count field and if greater than 2 turn the Text Red

Expression Is ([CountOfService_Number]="2") doesn't work

 

[Minty]

CountOfService_Number > 2
or
CountOfService_Number >= 2

Depending on how you want it to work.
It's a number, not a string, I think the quotes might confuse it.

 

[Number11]

yeah that got it working but unfortunately now the form is un-updateable

 

[Minty]

Join that query to your subforms table, use a left join and make sure you can still edit the table.
Save that as a new query and bring in the count field as part of the forms record source.

You can now use that number to drive the conditional formatting.

I did explicitly ask you to check that, in post #12.
I tend to forget that Access gets funny with aggregated queries and updates, even though you aren't trying to update that field and are outer joined to it. (SQL Server allows you to do this sort of thing without complaint...)

You could try using a DCount in the source query, but it is inefficient and could slow the form compared to the single aggregate query.

 

[Number11]

I did explicitly ask you to check that, in post #12.
I tend to forget that Access gets funny with aggregated queries and updates, even though you aren't trying to update that field and are outer joined to it. (SQL Server allows you to do this sort of thing without complaint...)

You could try using a DCount in the source query, but it is inefficient and could slow the form compared to the single aggregate query.

thanks i got around it by creating a new table with the combined data and its all working now