Unable to update the check box on the form from VBA (1 Viewer)

iworkonline

Registered User.
Local time
Yesterday, 23:04
Joined
May 25, 2010
Messages
44
I am trying to update a check box on the form from VBA code and it is not happennig. The code is as follows:

Code:
DoCmd.RunCommand acCmdRefresh
DoCmd.SetWarnings False
Dim rs As New ADODB.recordSet
Dim strSql As String
Dim cmd As ADODB.Command
Dim Userid As String
Dim NewUser As String
Dim count As Integer
Dim currentDate As String
Dim clientMessage As String
Dim clientSubject As String
Dim cm1 As String
Dim cm2 As String
Dim cm3 As String
Dim invheader As String
Dim e As String
Dim flag As String
Dim con As ADODB.Connection
Set con = New ADODB.Connection
con = CurrentProject.Connection
e = Chr$(13)
flag = True
 
currentDate = CStr(Date)
Userid = [Forms]![FRM_Shippers_Past_Due]![Userid]
 
    strSql = "SELECT [TBL_E-Mail_Shippers].E_Mail_Sent, [TBL_E-Mail_Shippers].Select,
             "TBL_Select_Shippers.* " & _
            "FROM [TBL_E-Mail_Shippers] " & _
            "INNER JOIN TBL_Select_Shippers ON [TBL_E-Mail_Shippers].CTC_SYS_NR = TBL_Select_Shippers.CTC_SYS_NR " & _
            "WHERE ((([TBL_E-Mail_Shippers].[Select])=-1) AND (([TBL_E-Mail_Shippers].Network_ID)= " & "'" & Userid & "'" & "));"
            '"WHERE ((([TBL_E-Mail_Shippers].E_Mail_Sent)=0) AND (([TBL_E-Mail_Shippers].[Select])=-1) AND (([TBL_E-Mail_Shippers].Network_ID)=[Forms]![FRM_Shippers_Past_Due]![Userid]));"
 
    count = 0
    rs.Open strSql, CurrentProject.Connection
 
    Do While Not rs.EOF
 
 'string extraction
 
        If rs!E_Mail_Sent = 0 Then
 
            Call FnTestSafeSendEmail(collectorEmail, EmailSubject, clientMessage, supervisorEmail, BCCEmail)
            [Forms]![FRM_Shippers_Past_Due]![E_Mail_Sent] = -1
            rs.MoveNext
            count = count + 1
            Else
            rs.MoveNext
       End If
 
    Loop
        MsgBox CStr(count) & " records emailed"
    rs.Close
    Set rs = Nothing   
End Sub

I am trying to update the field [Forms]![FRM_Shippers_Past_Due]![E_Mail_Sent] = -1 once the email is sent.

[Forms]![FRM_Shippers_Past_Due]![E_Mail_Sent] updates [TBL_E-Mail_Shippers].E_Mail_Sent

[TBL_E-Mail_Shippers].[Select] gets the input from the form.

User updates this field form the from to select the records to be emailed.

Right now its only updating only one record and I want to update multiple records. Thanks
 
Last edited:

iworkonline

Registered User.
Local time
Yesterday, 23:04
Joined
May 25, 2010
Messages
44
Here's how I am doing it
Code:
Dim rs As recordSet
strSql = "SOME SQL"
 
Set rs = CurrentDb.OpenRecordset(strSql) ', dbOpenDynaset)

rs.MoveFirst
Do While Not rs.EOF
 
 If rs!E_Mail_Sent = 0 Then
            
            Call FnTestSafeSendEmail()
            rs.Edit
            rs!E_Mail_Sent = -1
            rs.Update
            rs.MoveNext
            count = count + 1
            Else
            rs.MoveNext
       End If
      
    Loop
        MsgBox CStr(count) & " records emailed"
    rs.Close
    Set rs = Nothing

On line rs.Edit
I am getting error messsage: Cannot update. Database or object is read only. run time error 3027

Thanks
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 23:04
Joined
Aug 30, 2003
Messages
36,118
I would assume that the SQL being used results in a read-only recordset:

http://allenbrowne.com/ser-61.html

If you're stuck with that SQL, is there a key value available in the recordset that could be used with update SQL to update the underlying value?
 

iworkonline

Registered User.
Local time
Yesterday, 23:04
Joined
May 25, 2010
Messages
44
Thanks for the help guys

I used a different solution, I wrote the update query and ran the query as
DoCmd.OpenQuery ("My_Qry")

After running this query, I refresh the form and get a Write Conflict Box, stating
This record has been changed by another user since you started editing it..... then three choices, Save Record, Copy to clipboard and drop changes.

How do I remove this message from appearing?

My SQL statement have some stuff that is mentioned in th following link
http://allenbrowne.com/ser-61.html
and that's why my earlier code is not working.

Thanks
But
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 23:04
Joined
Aug 30, 2003
Messages
36,118
I suspect your update query is updating the same table the form is bound to, which can cause that error. Typically you either want to deal with the data via the form or via code, not both. You might be able to get around the error by explicitly saving the form record before running the update query. Either:

DoCmd.RunCommand acCmdSaveRecord

or

If Me.Dirty Then Me.Dirty = False
 

iworkonline

Registered User.
Local time
Yesterday, 23:04
Joined
May 25, 2010
Messages
44
Hi
I have the code as follow:
DoCmd.OpenQuery ("Qry_Update_E-Mail_Shippers_Ma")
DoCmd.RunCommand acCmdSaveRecord
and it is prompting me to click on the options on Write Conflict Wndow.

I started out by updating the field from the form wiht in the code. but it is only doing it for one record.
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 23:04
Joined
Aug 30, 2003
Messages
36,118
I mentioned trying it before the update query.
 

iworkonline

Registered User.
Local time
Yesterday, 23:04
Joined
May 25, 2010
Messages
44
I moved the statement as following and the program works fine.
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenQuery ("Qry_Update_E-Mail_Shippers_Ma")

Now I am going to improve my sql and use the following code:
Code:
Set rs = CurrentDb.OpenRecordset(strSql) ', dbOpenDynaset)

rs.MoveFirst
Do While Not rs.EOF
 
 If rs!E_Mail_Sent = 0 Then
            
            Call FnTestSafeSendEmail()
            rs.Edit
            rs!E_Mail_Sent = -1
            rs.Update
            rs.MoveNext
            count = count + 1
            Else
            rs.MoveNext
       End If
      
    Loop
        MsgBox CStr(count) & " records emailed"
    rs.Close
    Set rs = Nothing
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 23:04
Joined
Aug 30, 2003
Messages
36,118
No problem. Glad we got it sorted out.
 

Users who are viewing this thread

Top Bottom