Web Development

Ask something
Discussion Locked

Viewing Duplicates on a form

0 helpful
7 replies

by djpinson

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a form in Access 2K. I would like to type in a Lname to check for duplicate records.

If there are any duplicates have access return a list that I can select from or at least show them on a list.

7 replies

by john_wills

It seems to me that what you need is a scrolling subform with child field set to the Lname on the subform, itself set to the Lname in the table, and the master field set to the input on the control. The subform should be a left-to-right list of the other names and identification code of the person. I have not tried anything very close to this, but I think it should work.

by shanghai sam

Try the following:

---Start Code---
Dim dbs as Database
Dim rst as recordset

set dbs = Currentdb

set rst = dbs.OpenRecordset("SELECT RecordID FROM tblNames WHERE LName = '" & txtLName & '")
if rst.RecordCount = 0 then
msgbox "No duplicates exist"
set rst = nothing
set dbs = nothing
exit sub
endif

-- comments
' If your code continues, then do something with the recordset that now contains your duplicates. I am putting their RecordIDs that I asked for in the SELECT statement) into a message box, but you could ask for other fields and put them into a list box or another form etc

--- code continues
Dim x as integer
Dim strDuplicates as string
x = 0

rst.movelast
rst.movefirst

strDuplicates = ""
strDuplicates = rst.fields!(RecordID)
rst.movenext
x = x + 1

while (x < rst.recordcount)
strDuplicates = ", " & rst.fields!(RecordID)
rst.movenext
x = x + 1
Wend

msgbox strDuplicates, vbokonly, "Duplicates Found"

set rst = nothing
set dbs = nothing

---End Code---

This is just off the top of my head, so check the syntax and while code etc

Hope it helps

by timfox

Perhaps I will have to add a sig to my posts...
I was answer 2, above.
cheers, all
TF

This discussion is closed to new replies.