Preventing Duplicate Records

[Lamb2087]

I have a query that finds duplicate records and the number of each duplicate there is, listed below
SELECT dbo_INV_INFSVC.CS_NUM, Count(*) AS Expr1
FROM dbo_INV_INFSVC
GROUP BY dbo_INV_INFSVC.CS_NUM
HAVING (((Count(*))>1));


What I need is to let the user know if there is a record in the table with the number they have enetered as a id. It would be great to have a messgae box come up after they tab off the txt box alerting them.

Field name is CS_NUM.

 

[charityg]

in the lostFocus event of the textbox try something similar to this code

Sub Textbox_LostFocus()
dim db as database
dim rst as recordset
set db=CurrentDB
Set rst=db.openrecordset("Select * from YourTable where ID=" & textbox)
if rst.eof=false then
msgbox "Duplicate ID!"
end if

end sub

 

[Lamb2087]

Here is what I put as you suggested.

Private Sub txtCS_NUM_LostFocus()

Dim db As Database
Dim rst As Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("Select * from dbo_INV_INFSVC where CS_NUM=" & txtCS_NUM)
If rst.EOF = False Then
MsgBox "Duplicate ID!"
End If
End Sub

DB is dbo_INV_INFSVC
Txt Box is txtCS_NUM
Field is CS_NUM

I tried this and it did not work. I believe I must have something wrong in the code.

 

[charityg]

Can you explain what didn't work? Did you get an error? Did you try stepping through the code?


Try:
Private Sub txtCS_NUM_LostFocus()

Dim db As Database
Dim rst As DAO.Recordset
if me.dirty then
Set db = CurrentDb
Set rst = db.OpenRecordset("Select * from dbo_INV_INFSVC where CS_NUM=" & txtCS_NUM)
If rst.EOF = False Then
MsgBox "Duplicate ID!"
End If
end if
End Sub

Is CS_NUM a numeric field?

 

[Lamb2087]

CS_NUM is a text field!

 

[charityg]

That's why it's not working.

Use this:
Private Sub txtCS_NUM_LostFocus()

Dim db As Database
Dim rst As Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("Select * from dbo_INV_INFSVC where CS_NUM='" & txtCS_NUM & "'")
If rst.EOF = False Then
MsgBox "Duplicate ID!"
End If
End Sub

 

[Lamb2087]

What you sent works but when you scroll back or forward between records the dialog box with duplicate id pops up. it comes up all the time instead of just when a new record is to be added.

 

[charityg]

oops. I left out the dirty part.

Dim db As Database
Dim rst As Recordset
if me.dirty then
Set db = CurrentDb
Set rst = db.OpenRecordset("Select * from dbo_INV_INFSVC where CS_NUM='" & txtCS_NUM & "'")
If rst.EOF = False Then
MsgBox "Duplicate ID!"
End If
end if
End Sub

 

[Lamb2087]

Thank You
Charity

It works like it should. I really appreciate your help!!

 

[Lamb2087]

I have another question for you. Are you pretty go with queires/reports?

I have a query that returns all the records sequentially some what. It will list the records like 1, 10 1000, 1001
It will jump like 1200, 12, 1201, 1202

instead of 1,2,3,4,5,6,7,8,9,10

It will list 1, 10, 1000

Any ideas?

Here is the query to list all records. I have gon intot he report for sorting and that did not make it work either.

SELECT dbo_INV_INFSVC.CS_NUM, dbo_INV_INFSVC.NON_TECH_DES, dbo_INV_INFSVC.USER_OPR, dbo_INV_INFSVC.SERIAL_NUM, dbo_INV_INFSVC.NMMI_NUM, dbo_INV_INFSVC.ROOM_NUM, dbo_INV_INFSVC.BUILDING, dbo_INV_INFSVC.LOCATION, dbo_INV_INFSVC.CHECKED, dbo_INV_INFSVC.DEPARTMENT
FROM dbo_INV_INFSVC;