[RESOLVED] Update record not working

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

Code:

---

    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

---

What is the value of "strConnection"

Quote:

---

Originally Posted by Mark Gambo

What is the value of "strConnection"

---

Sorry missed this reply!

Code:

---

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?

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

Hi aikidokid;

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

Code:

---

"Where Memb_ID = " & lngSelection

---

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

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?

Quote:

---

Originally Posted by LinXG

Hi aikidokid;

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

Code:

---

"Where Memb_ID = " & lngSelection

---

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

---

Good point.

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

Quote:

---

Originally Posted by LinXG

Hi aikidokid;

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

Code:

---

"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. :thumb:

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

Quote:

---

Originally Posted by 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.

---

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 :D

Quote:

---

Originally Posted by aikidokid

. . .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 :D

---

Sure take a look at this graphic:

Thanks for this Mark, I will take a look

:thumb:

No problems, I am glad that I could help