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