[Last Call] Learn how to a build a cloud-first strategyRegister Now


How to check database to see if there's a matching record...?

Posted on 2014-08-25
Medium Priority
Last Modified: 2014-09-11
I have an access front end with a sql backend. I have jobs that have unique ID's, but there might be two or more jobs that have the same job number. I want to display a list box that shows up only if there are matching jobs. What syntax do I use (I'm assuming on load) to check for duplicates?

(side question: will this slow it down much on a db with about 3k records?)

Question by:Kevin Smith
LVL 25

Expert Comment

by:Lee Savidge
ID: 40283152
Which part do you want the syntax for? The SQL Server code or VBScript?

If it's SQL you need to group on a count.

select count(myIDColumn) from myTable
group by myIDColumn
having count(myIDColumn) > 1

-- You could use the above to give you the list of IDs. If you wanted more from the table you could wrap it in another select

select * from myTable where myIDColumn in
select count(myIDColumn) from myTable
group by myIDColumn
having count(myIDColumn) > 1
order by myIDColumn

Open in new window

Again, this depends on where you want the code to look for duplicates.

Author Comment

by:Kevin Smith
ID: 40283280
The vb...I need to be on a form and for the list box to show the duplicate jobs in a list box based on the job number. It needs to list them 'cause they'll need to click in the list box to open the jobs if needed.
LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 40283472
The code for this would simply be to set the listbox's RowSource to the query that shows the duplicates, for example:

Me.MyListbox.RowSource = "SELECT Field1, Field2, Field3 FROM YourTable WHERE JobNumber='" & Me.JobNumber & "'"

You'd do this in the Current event of the form - that fires every time you move to a different/new record.

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question