Updating Problem

Hey all, I've been workin on this all afternoon and I can't get it working for the life of me. I have tried it too many ways to count so I will post the few where I think I am the closest. I am simply trying to update a record (in access database) using my visual basic form. Here is the code and associated errors:
Connection
Set Rs = New ADODB.Recordset
Rs.Open "Update T_Questions_Static Set IM_Number = " & txtIM & " AND Meeting_Number = " & txtMeeting & " AND Questions_Static = " & txtStatic & "", Cn, adOpenDynamic, adLockBatchOptimistic, cmdAddText
Rs.AddNew
Rs("Meeting_Number") = txtMeeting.Text
Rs("IM_Number") = txtIM.Text
Rs("Questions_Static") = txtStatic.Text
Rs.Update
Rs.UpdateBatch
Rs.MoveNext
Rs.Close
Set Rs = Nothing

Error: No value given for one or more required parameters

Next Try:
Dim Cm As ADODB.Command
Dim Rs As ADODB.Recordset

Set Cm = New ADODB.Command
With Cm
.CommandType = adCmdText
.ActiveConnection = Cn
.CommandText = "Select * From T_Questions_Static"
End With
'Create record set
Set Rs = New ADODB.Recordset
Rs.Open Cm, , adOpenStatic, adLockOptimistic
'Update values for row 1
Rs.AddNew
Rs!Meeting_Number = txtMeeting
Rs!IM_Number = txtIM
Rs!Questions_Static = txtStatic

Call Rs.Update
Set Rs = Nothing
Set adoCommand = Nothing
Exit Sub

Error: THe changes you requested to the table were not successful becayse they would create duplicate values in the index, primary key, etc....

'Updates to T_Questions_Static Table
Connection
Set Rs = New ADODB.Recordset
Rs.Open "Select * From T_Questions_Static", Cn, 1, 3, AddcmdText
With DeMeeting.rsQuestions_Static
Rs("Meeting_Number") = txtMeeting.Text
Rs("IM_Number") = txtIM.Text
Rs("Questions_Static") = txtStatic.Text
DeMeeting.rsQuestions_Static.Update
End With
Set Rs = Nothing

This method kind of works, but it updates the first record in the record set, not the record that it is suppose to update.

Any help is welcome, or suggestions to try something else would also be great. I was thinking it might possibly be the settings in my database that are causing the errors, but I am not sure.

Quote:

---

Originally posted by ABCDEFGHIJKL
1)

VB Code:

Set Rs = New ADODB.Recordset
        Rs.Open "[b]Update[/b] T_Questions_Static Set IM_Number = " & txtIM & " AND Meeting_Number = " & txtMeeting & " AND Questions_Static = " & txtStatic & "", Cn, adOpenDynamic, adLockBatchOptimistic, cmdAddText
        Rs.AddNew
        Rs("Meeting_Number") = txtMeeting.Text
        Rs("IM_Number") = txtIM.Text
        Rs("Questions_Static") = txtStatic.Text
        Rs.Update
        Rs.UpdateBatch
        Rs.MoveNext
        Rs.Close
        Set Rs = Nothing

2)

VB Code:

Dim Cm As ADODB.Command
    Dim Rs As ADODB.Recordset
 
    Set Cm = New ADODB.Command
    With Cm
        .CommandType = adCmdText
        .ActiveConnection = Cn
        .CommandText = "Select * From T_Questions_Static"
    End With
    'Create record set
    Set Rs = New ADODB.Recordset
[b]    Rs.Open Cm, , adOpenStatic, adLockOptimistic[/b]
    'Update values for row 1
    Rs.AddNew
    Rs!Meeting_Number = txtMeeting
    Rs!IM_Number = txtIM
    Rs!Questions_Static = txtStatic
     
    Call Rs.Update
     Set Rs = Nothing
    Set adoCommand = Nothing
    Exit Sub

3)

VB Code:

'Updates to T_Questions_Static Table
    Connection
        Set Rs = New ADODB.Recordset
[b]        Rs.Open "Select * From T_Questions_Static", Cn, 1, 3, AddcmdText[/b]
[color=red]        With DeMeeting.rsQuestions_Static[/color]
            Rs("Meeting_Number") = txtMeeting.Text
            Rs("IM_Number") = txtIM.Text
            Rs("Questions_Static") = txtStatic.Text
            DeMeeting.rsQuestions_Static.Update
[color=red]        End With[/color]
        Set Rs = Nothing

---

1)
Update??? Don't you mean Select?
Error suggests that you have specified a value but its not recognising it, so you'd need to debug.print the sql statement before opening it.

2)
Bits in bold...
rs.open strSql,connection, adOpenStatic, adLockOptimistic,adCmdText
open static - not updateable - its a snapshot - you need dynamic to open and edit/update. static for recordcount/view only.
use rs("fieldname") .. nicer than rs![fieldname]

3)
Rs.Open "Select * From T_Questions_Static", Cn, 2, 3, 1
Bits in red are not needed
This should work...
And will if you put rs.Addnew before trying to add a new record ;) lol


Vince

Thanks for the help buddy. I got it working, although I had to add a little extra to get it working. Here is the code incase anyone out there in the future is doing something similar to me.

'Updates to T_Questions_Static Table
Connection
Set Rs = New ADODB.Recordset
Rs.Open "Select * From T_Questions_Static Where IM_Number = " & txtMeeting & " and Meeting_Number = " & txtMeeting & "", Cn, 2, 3, 1
'Rs("Meeting_Number") = txtMeeting.Text
'Rs("IM_Number") = txtIM.Text
Rs("Questions_Static") = txtStatic.Text
Rs.Update
Rs.MoveFirst
Set Rs = Nothing

Thanks again Ecniv, you the MAANNNNN!!!!!!!!!!