Edit specific row in table (1 Viewer)

[FreonIceMan]

I have a report that I run which has PT_Ins_ID in it, I am trying to edit that row with the following code. I have been searching how to do this and I haven't had any luck. If I am doing something wrong or if someone know of a way to do this with vba it would be appreciated.
Thanks,

Freon
:banghead:

PT_Ins_ID is the primary key of dbo_Patient_Insurance

Private Sub cmd_Deact_Click()
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("dbo_Patient_Insurance")
    If rs.RecordCount <> 0 Then
        With rs
            .FindFirst "PT_Ins_ID" = Me.txt_PT_Ins_ID
            If Not .NoMatch Then
                .Edit
                    .Fields("PT_Ins_Active").Value = 0
                    .Fields("PT_Ins_Inactive_Date") = Now()
                    .Fields("PT_Ins_Deactivating_User") = (Environ$("Username"))
                .Update
            End If
        End With
    End If


End Sub

I've also tried this.

Dim rs_PI As DAO.Recordset
Set rs_PI = CurrentDb.OpenRecordset("dbo_Patient_Insurance")

DoWhile ("PT_Ins_ID") <> Me.txt_PT_Ins_ID
    rs_PI.MoveNext
Loop

rs_PI.Edit
rs_PI("PT_Ins_Active").Value = 0
rs_PI("PT_Ins_Inactive_Date") = Now()
rs_PI("PT_Ins_Deactivating_User") = (Environ$("Username"))
rs_PI.Update

 

[CJ_London]

try

.FindFirst "PT_Ins_ID = " & Me.txt_PT_Ins_ID

 

[FreonIceMan]

Hi CJ_London,

I tried your suggestion I am getting a "Compile error: Invalid or unqualified reference" the txt_PT_Ins_ID is highlighted.

your code as I have implimented it:

Private Sub cmd_Deactivate_Click()

Dim rs_PI As DAO.Recordset
Set rs_PI = CurrentDb.OpenRecordset("dbo_Patient_Insurance")

.FindFirst "PT_Ins_ID = " & Me.txt_PT_Ins_ID

rs_PI.Edit
rs_PI("PT_Ins_Active").Value = 0
rs_PI("PT_Ins_Inactive_Date") = Now()
rs_PI("PT_Ins_Deactivating_User") = (Environ$("Username"))
rs_PI.Update

End Sub

What are your thoughts?

Thanks,

Freon

 

[jdraw]

Did you try

rs_PI.FindFirst "PT_Ins_ID = " & Me.txt_PT_Ins_ID

 

[FreonIceMan]

I tried it and i recieved "Run-time error '3251': Operation is not supported for this type of object"

I searched for that error and found out that I needed to add ", dbOpenDynaset" to the code.

Private Sub cmd_Deactivate_Click()

Dim rs_PI As DAO.Recordset
Set rs_PI = CurrentDb.OpenRecordset("dbo_Patient_Insurance"[B][COLOR=Red], dbOpenDynaset[/COLOR][/B])

rs_PI.FindFirst "PT_Ins_ID = " & Me.txt_PT_Ins_ID

rs_PI.Edit
rs_PI("PT_Ins_Active").Value = 0
rs_PI("PT_Ins_Inactive_Date") = Now()
rs_PI("PT_Ins_Deactivating_User") = (Environ$("Username"))
rs_PI.Update

End Sub

The code is now working.

Thanks all for your help I appreciate it.

 

[CJ_London]

It would help if you used the code I provided in the context in which you supplied it originally - you removed the with rs loop.

 

[FreonIceMan]

Hi CJ_London,

I just tried it with your fix and I got the "Run-time error 3251". Once I added the dbOpenDynaset it fixed it.

Private Sub Command144_Click()
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("dbo_Patient_Insurance")
    If rs.RecordCount <> 0 Then
        With rs
            .FindFirst "PT_Ins_ID = " & Me.txt_PT_Ins_ID
            If Not .NoMatch Then
                .Edit
                    .Fields("PT_Ins_Active").Value = 0
                    .Fields("PT_Ins_Inactive_Date") = Now()
                    .Fields("PT_Ins_Deactivating_User") = (Environ$("Username"))
                .Update
            End If
        End With
    End If
End Sub