Update a record in MS ACCESS

[zeidhaddadin]

Hi all, I have this sub that should add new record to the MS Access database Which I have the following fields: url (Primary key), page, indexed

If the url was found there then just update the record, else add a new record with the new url you have.

This is my code, BUT this still giving me (Duplicate error) when entering a url that is in the database.

Private Sub cmd_save_Click()
    Dim sql As String
    sql = "SELECT url FROM Table1 WHERE url LIKE '%" & wb1.LocationURL & "&'"
    Set dbrs = New ADODB.Recordset
    dbrs.Open sql, dbconn, adOpenKeyset, adLockPessimistic, adCmdText
    
    If dbrs.EOF Then
        Set dbrs2 = New ADODB.Recordset
        dbrs2.Open "Table1", dbconn, adOpenKeyset, adLockPessimistic, adCmdTable
        With dbrs2
            .AddNew
            .Fields("url") = wb1.LocationURL
            .Fields("page") = txt_page.Text
            .Fields("indexed") = Now()
            .Update
        End With
    Else
        Set dbrs2 = New ADODB.Recordset
        dbrs2.Open "Table1", dbconn, adOpenKeyset, adLockPessimistic, adCmdTable
        With dbrs2
            .Fields("url") = wb1.LocationURL
            .Fields("page") = txt_page.Text
            .Fields("indexed") = Now()
            .Update
        End With
    End If

    MsgBox "MAIN PAGE HAS BEEN ADDED"
End Sub

I need someone to fix it for me,
Thanks very much,
zeid

[si_the_geek]

Moved to Database Development forum - which is where you should always post database related questions.

Erm... what is the point of dbrs2?

If there are no matching records, you can still add a new record to the original recordset.

If a matching record is found, you already have it loaded into dbrs - so you should be updating that.

[Tony Hine]

.
I had a similar problem recently, I was saving user preferences within an MS Access database. The table could hold, username, preference, and setting. For example :-

Username : Tony Hine
Preference: comment box Font
Setting: font size 12

When the user made a comment in a text box named "comment box" and used the increase/decrease buttons to change the font size, they could also press a "save settings" button which would save the font size in the preference table.

The first time this preference was saved, all well and good, all three items of data were saved in a new record. However if the user changed the font size setting again, then the code would try and save another new record, this failed because the fields username, and preference, were indexed to only accept unique values.

So I took advantage of this error message to call code to update the existing record.

in other words:
Save a new record --- (always does this)
If save fails because a record already exists, then
Abort Save
Call code to:
find that record and update it...

[si_the_geek]

While that solution works for you, it is far more common (and 'correct') to find out if there are matching records first - as in zeidhaddadin's code.

The problems with the code all come from using a second recordset - removing it (and changing the "With" blocks to work with the original one) will correct the issues.

[Tony Hine]

>>>(and 'correct') <<<

Interesting, I'm interested in how you define the difference between the two solutions, how can one be more correct than the other?

I ask this, because I strive to improve my coding so that it meets the highest standards, therefore it is important for me to understand why one particular procedure is considered more correct than another. This way I will learn and improve.

[si_the_geek]

That's the best way to be!

There are two main reasons for my thinking.. one is that errors (even when dealt with properly - which in your case would mean checking for the specific error number/message) should be avoided, mainly because it breaks the flow of the code (but also because it sets the Err object, which can make debugging harder). This is a bit debatable, and basically does come down to preference (or yourself, or your team if you work in a group).

The other reason is that you are assuming the error will happen if a duplicate exists.. while that is the case at the moment, it only takes a minor change in settings (of the table, or the database system, etc) for that error to not occur any more. While the settings shouldn't change, it is better (in my opinion at least) to not take the risk.

[Tony Hine]

>>>while that is the case at the moment, it only takes a minor change in settings<<<

Ah! now I understand, and I actually have had firsthand experience of a minor change affecting a similar routine.

Thanks for the information.

Cheers Tony

[Tony Hine]

I recently wrote a class module that simplifies calling a calendar entry form from text boxes, combo boxes sub forms etc.

However I am unhappy with one small part of the code because I have had to resort to using the occurrence of an error to make the code work properly.

I wondered if you could have a look at the code which is in this thread here, I thought a solution may occur to you, or you may be able to suggest a different approach.

If you prefer I can send you the code directly by e-mail.

Cheers Tony

[si_the_geek]

I'm not a member of that site, and I'm afraid I don't give out any of my email addresses... could you post/attach the code here instead?