[RESOLVED] Update record not working

[aikidokid]

I have the following code which, when I debug.print the strSQL, I get the updated data.
The problem is, it doesn't save the changes and it doesn't thow an error, so I can't tell you what the error message could be.

Can anybody see what is wrong with the code?
Or could it be something wrong with the database set up, although there are plenty of records successfully saved into it!

Thanks

    Set cn = New ADODB.Connection
    cn.ConnectionString = strConnection & App.Path & "\Membership.mdb"
    cn.Open
    
    strSQL = "UPDATE tbl_Membership SET " & _
        "First_Name = '" & Replace(txtMembership(0).Text, "'", "''") & "', " & _
        "Middle_Name = '" & Replace(txtMembership(1).Text, "'", "''") & "', " & _
        "Surname = '" & Replace(txtMembership(2).Text, "'", "''") & "', " & _
        "Street = '" & Replace(txtMembership(3).Text, "'", "''") & "', " & _
        "Town = '" & Replace(txtMembership(4).Text, "'", "''") & "', " & _
        "City = '" & Replace(txtMembership(5).Text, "'", "''") & "', " & _
        "County = '" & Replace(txtMembership(6).Text, "'", "''") & "', " & _
        "Post_Code = '" & Replace(txtMembership(7).Text, "'", "''") & "', " & _
        "Home_Tel = '" & Replace(txtMembership(8).Text, "'", "''") & "', " & _
        "Mobile_Tel = '" & Replace(txtMembership(9).Text, "'", "''") & "', " & _
        "Email = '" & Replace(txtMembership(10).Text, "'", "''") & "', " & _
        "DOB = '" & Replace(txtMembership(11).Text, "'", "''") & "', " & _
        "Date_Joined = '" & Replace(txtMembership(12).Text, "'", "''") & "', " & _
        "Insurance_Due = '" & Replace(txtMembership(13).Text, "'", "''") & "', " & _
        "Grade = '" & Replace(txtMembership(14).Text, "'", "''") & "', " & _
        "Last_Graded = '" & Replace(txtMembership(15).Text, "'", "''") & "', " & _
        "Membership_Number = '" & Replace(txtMembership(16).Text, "'", "''") & "', " & _
        "Occupation = '" & Replace(txtMembership(17).Text, "'", "''") & "', " & _
        "Notes = '" & Replace(txtMembership(18).Text, "'", "''") & "' " & _
        "Where Memb_ID = " & lngSelection
              
    cn.Execute strSQL
    cn.Close
    Set cn = Nothing

[Mark Gambo]

What is the value of "strConnection"

[aikidokid]

What is the value of "strConnection"

Sorry missed this reply!

Public Const strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = "

It's definately not this as it works every time i make a call to the database.

Anything else jump out?

[techgnome]

1) how do you *know* it's not saving the changes?
2) I highly suggest you parameterize that query and use that instead. Many times a parameterized query is far easier to debug.
3) display the SQL in a message box before running it... see if anything jumps out at you.
4) Even though you thinkj you aren't getting errors, check the Errors Collection in the Connection object.... there might be something in there.

-tg

[LinXG]

Hi aikidokid;

Your query seems to be Ok. Have you checked...

"Where Memb_ID = " & lngSelection

it's possible you are trying to update a non-existent record.

[Mark Gambo]

Have you tried looking at the value of strSQL before it is executed? output the value of strSQL into the immediate window and then copy and paste it into an Access Query and see if it updates. Can you post the value of strSQL?

[Mark Gambo]

Hi aikidokid;

Your query seems to be Ok. Have you checked...

"Where Memb_ID = " & lngSelection

it's possible you are trying to update a non-existent record.

Good point.

[techgnome]

If this Access... the query may still not be right.... DBO - is this a date field? I know it's stupposed to be a date going in, but is it stored as a date type field... or a string type field? If i'ts stored as a date, then dump the ' around it, and suround your date value with # instead:
"DOB = #" & txtMembership(11).Text & "#, " & _


-tg

[aikidokid]

Hi aikidokid;

Your query seems to be Ok. Have you checked...

"Where Memb_ID = " & lngSelection

it's possible you are trying to update a non-existent record.

Thanks everybody who took the time to look at this for me.

I have to admit that this was the problem.
I have another variable with a very similar name (good lesson here).

Have you tried looking at the value of strSQL before it is executed? output the value of strSQL into the immediate window and then copy and paste it into an Access Query and see if it updates.

Could you explain a bit more about how to use the Access Query and what to expect from it.

A big thanks also to techgnome

[Mark Gambo]

. . .Could you explain a bit more about how to use the Access Query and what to expect from it.

A big thanks also to techgnome

Sure take a look at this graphic:

[aikidokid]

Thanks for this Mark, I will take a look

[Mark Gambo]

No problems, I am glad that I could help