Error trapping

[stealth black]

Trying to prevent duplicates from being entered into database.... How do I have the app search the db for a username AND date match, so I can yell at the user through an error message? LOL

Thanks in advance.......

[dglienna]

Before you add it, use a SELECT statement to search for what you are about to add. If it's found, then shout away.

[Pradeep1210]

Here is a sample code:

Private Sub cmdCheckDuplicateUser_Click()
    Dim rs As adodb.Recordset
    
    '--Replace with appropriate field names and table name --
    Set rs = cnn.Execute("SELECT * FROM TableName WHERE UserName='" & txtUserName & "' AND dDate=#" & txtDate & "#")
    If Not rs.EOF Then
        MsgBox "User already exists!", vbExclamation
    Else
        'Unique user
        rs.AddNew
        rs!UserName = txtUserName
        rs!dDate = txtDate
        rs.Update
    End If
    rs.Close
    Set rs = Nothing
End Sub

Pradeep

[stealth black]

I'm still using the antique DAO code.....

What I meant was that I would like the error to pop up when the username and date combination matches the same combo in the db. There is a set number of reps, and I got the app automatically pulling their name from the machine. There should never be two entries with the same date for a given user....

[StevenHickerson]

Pradeep are you sure that code works correctly? I recently experienced some issues with my database code where the EOF was true when nothing was returned in the recordset. In fact I had to trap the situation by testing if both BOF and EOF were true at the same time, if they were that meant the recordset was empty.

I may have missed something as I'm still rather new with databases and recordsets, but that is the only way I have gotten it to work.

[Pradeep1210]

Pradeep are you sure that code works correctly? I recently experienced some issues with my database code where the EOF was true when nothing was returned in the recordset. In fact I had to trap the situation by testing if both BOF and EOF were true at the same time, if they were that meant the recordset was empty.

I may have missed something as I'm still rather new with databases and recordsets, but that is the only way I have gotten it to work.

Whenever a recordset is opened, it is on the first record by default. So when you have just opened a recordset (and haven't moved yet) and you get the EOF=True, it mean's that there are no records in the recordset. Checking for BOF is useless in such a case.

EDIT: Ohh.. sorry. I have edited the code. There was a typo and it was doing the opposite. (I missed the NOT !)

Pradeep

[stealth black]

Before you add it, use a SELECT statement to search for what you are about to add. If it's found, then shout away.

David, how do you suggest I do this???

[kfcSmitty]

David, how do you suggest I do this???

check pradeep's post #3

[stealth black]

check pradeep's post #3

Isn't that adodb? I'm in DAO

[kfcSmitty]

yes, but the select statement would be the same, would it not?

[szlamany]

Whenever a recordset is opened, it is on the first record by default. So when you have just opened a recordset (and haven't moved yet) and you get the EOF=True, it mean's that there are no records in the recordset. Checking for BOF is useless in such a case.

Just to add to that - BOF checking is not required after you load a RS from a database - EOF checking is enough. But if there is a chance that the SQL is bad, and no recordset is returned (as opposed to an empty recordset), then you should check the .STATE of the RECORDSET first.

[Pradeep1210]

Here's the DAO version:

Private Sub cmdCheckDuplicateUser_Click()
    Dim rs As Recordset
    
    '--Replace with appropriate field names and table name --
    Set rs = cnn.OpenRecordset("SELECT * FROM TableName WHERE UserName='" & txtUserName & "' AND dDate=#" & txtDate & "#")
    If Not rs.EOF Then
        MsgBox "User already exists!", vbExclamation
    Else
        'Unique user
        rs.AddNew
        rs.Fields("UserName") = txtUserName
        rs.Fields("dDate") = txtDate
        rs.Update
    End If
    rs.Close
    Set rs = Nothing
End Sub

[Pradeep1210]

Just to add to that - BOF checking is not required after you load a RS from a database - EOF checking is enough. But if there is a chance that the SQL is bad, and no recordset is returned (as opposed to an empty recordset), then you should check the .STATE of the RECORDSET first.

Won't it raise an error in such a case??

[dglienna]

Here is a simple DAO example. They don't check for dups, but show how to find fields.

[szlamany]

Won't it raise an error in such a case??

I guess my post was really directed at StevenHickerson - I just wanted to shed some more light on how BOF and EOF work...