UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Highlight Duplicate Numbers, Any Versions    
 
   
Quinto1
post Aug 2 2018, 07:26 AM
Post#1



Posts: 68
Joined: 23-April 16



Good morning all
Is it possible to highlight duplicate numbers in the same control field in a table or query?
Thanks
Quinto

Go to the top of the page
 
GroverParkGeorge
post Aug 2 2018, 07:36 AM
Post#2


UA Admin
Posts: 33,260
Joined: 20-June 02
From: Newcastle, WA


In a table or query? Not really, no.

Moreover, we probably don't even want users to SEE tables and queries directly, so it would be a low-priority thing to do in any case.

That said, you CAN do some things in forms or reports, with conditional formatting to do that.

On the other hand, in MS Access and other RSBMS's like SQL Server, you can expose duplicates in a table by creating a query using the "Find Duplicates Query Wizard" in Access, or a similar query in SQL Server, Oracle, etc.

Attached File  FindDuplicates.jpg ( 44.44K )Number of downloads: 0

--------------------
Go to the top of the page
 
Quinto1
post Aug 2 2018, 07:46 AM
Post#3



Posts: 68
Joined: 23-April 16



I thought so
Thank you
Go to the top of the page
 
BruceM
post Aug 2 2018, 07:47 AM
Post#4


UtterAccess VIP
Posts: 7,574
Joined: 24-May 10
From: Downeast Maine


To add a little to George's point about conditional formatting, if you want the look of a table, a form in datasheet view resembles a table or query, and a report can be formatted to resemble a table printout.
Go to the top of the page
 
Quinto1
post Aug 2 2018, 08:16 AM
Post#5



Posts: 68
Joined: 23-April 16



OK I added the form as continuous form to give me the same look as the table.
I need to be warned if a duplicate number is entered in the first control field. It can be valid but I need that warning to continue with the next record.
Is that possible? I tried conditional formatting but I failed to get done. It would be great if I could just add a color to those duplicate numbers

Quinto
This post has been edited by Quinto1: Aug 2 2018, 08:18 AM
Go to the top of the page
 
GroverParkGeorge
post Aug 2 2018, 08:36 AM
Post#6


UA Admin
Posts: 33,260
Joined: 20-June 02
From: Newcastle, WA


What rule determines whether "a number" is duplicated?


--------------------
Go to the top of the page
 
Quinto1
post Aug 2 2018, 09:00 AM
Post#7



Posts: 68
Joined: 23-April 16



The first field the form is [BusNumber], I have to allow duplicate but I would like to be warned that that number exist in the table in the same fiels so I can evaluate the rest of the information to make sure the to whole record in not duplicated with different information
Go to the top of the page
 
GroverParkGeorge
post Aug 2 2018, 09:13 AM
Post#8


UA Admin
Posts: 33,260
Joined: 20-June 02
From: Newcastle, WA


You can do this without conditional highlighting. It's a fairly common requirement, in fact, and there are probably several examples here that show how to do it.

You can use the Before Update event of the control on this form to check for the existence of the BusNumber value typed into the control and either accept it or warn the user. Here's a potential way to do that,
CODE
Private Sub txtBusNumber_BeforeUpdate(Cancel As Integer)
Dim strNewBusNumber as string

strNewBusNumber = Me.txtBusNumber.Text 'Use the text property because the value hasn't yet been saved

If DCount("BusNumber", "tblYourTableNameGoesHere", "tblYourTableNameGoesHere.BusNumber = """ & strNewBusNumber & """") >0 Then

  If MsgBox(Prompt:="The BusNumber entered, " & strNewBusNumber & " has previously been used. Confirm that you want to use it again.", Buttons:=vbYesNo + vbQuestion, Title:="Accept Duplicate?") = vbNo Then
       Cancel = True
  End if
  
End If

End Sub

--------------------
Go to the top of the page
 
Quinto1
post Aug 2 2018, 09:57 AM
Post#9



Posts: 68
Joined: 23-April 16



This is exactly what I need, if only I could get to work.
I will load a copy of what I have but it is not working.

Attached File(s)
Attached File  BusNumber.pdf ( 123.38K )Number of downloads: 4
 
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    20th August 2018 - 12:08 AM