Database become error when sending email

[davyre]

Hi,

I have a button which sends email. The problem is whenever I clicked the button, and it finishes, then the database become error in terms of a textbox in every form which has Date Picker, it stopped working. I cannot fill the textbox using the date picker anymore (either a click on a particular date or click on Today button).. But the textbox still can be filled manually.

Do you think this is because of the button? Maybe I should close something down because there is still a process going on from the button? (docmd.close or set x=nothing or anything with CDO because I am new using CDO)

anyway, heres the code of the button:

Private Sub EmailFollowUpButton_Click()

'this button will check every customer orders on TblCustOrder where Email1Sent is False and Email1DueDate is today or less than today.
'if the conditions are fulfilled, system will automatically send email as reminder to make a follow up call about the product.

Dim cdoConfig
Dim msgOne
Dim dbs As Database
Dim rst As DAO.Recordset
Dim notSentQry As String
Dim emailSentUpdateQry As String

'details about the order
Dim cust As String
Dim PONumber As String
Dim jobName As String
Dim dateSent As String
Dim dateToday As String

Dim unitIDRst As DAO.Recordset
Dim SN As String
Dim units As String
Dim selectUnitQry As String

Dim emailMessage As String


notSentQry = "SELECT CustOrderID, CustomerID, PONumber, JobName, DateSent, Email1DueDate FROM TblCustOrder WHERE Email1Sent=False AND Email1DueDate<=Now()"
dateToday = Format(Now(), "dd/mm/yyyy")

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset(notSentQry)
If rst.EOF And rst.BOF Then
    MsgBox ("No follow up should be made for today")
    Exit Sub
Else
    rst.MoveFirst
    Do While rst.EOF = False
        cust = DLookup("CustomerName", "TblCustomer", "CustomerID=" & rst("CustomerID"))
        PONumber = rst("PONumber")
        jobName = Nz(rst("JobName"), "")
        'dateSent = rst("DateSent")
    
        emailMessage = vbCrLf + emailMessage + cust + " PO Number: " + PONumber + ", Job Name: " + jobName + " " + vbCrLf
        selectUnitQry = "SELECT UnitID, SerialNumber FROM TblCustOrderUnit WHERE OrderID=" & rst("CustOrderID")
        Set unitIDRst = dbs.OpenRecordset(selectUnitQry)
        unitIDRst.MoveFirst
        Do While unitIDRst.EOF = False
            units = DLookup("BSUnitID", "TblUnits", "UnitID=" & unitIDRst("UnitID"))
            SN = unitIDRst("SerialNumber")
            emailMessage = emailMessage + "+++" + units + " Serial Number: " + SN + vbCrLf
            unitIDRst.MoveNext
        Loop
        
        
        emailSentUpdateQry = "UPDATE TblCustOrder SET Email1Sent=TRUE WHERE CustOrderID=" & rst("CustOrderID")
        
        DoCmd.SetWarnings False
        DoCmd.RunSQL emailSentUpdateQry
        DoCmd.SetWarnings True
        rst.MoveNext
    Loop

    Set cdoConfig = CreateObject("CDO.Configuration")
    With cdoConfig.Fields
        .Item(cdoSendUsingMethod) = cdoSendUsingPort
        .Item(cdoSMTPServerPort) = 25 ' (your port number) usually is 25
        .Item(cdoSMTPServer) = "smtp.internet.com" ' your SMTP server goes here
        '.Item(cdoSendUserName) = "My Username"
        '.Item(cdoSendPassword) = "myPassword"
        .Update
    End With

    Set msgOne = CreateObject("CDO.Message")
    Set msgOne.Configuration = cdoConfig
    msgOne.To = "mail@mail.com" 'recipient email address
    msgOne.From = "mail2@mail.com" 'sender email address
    msgOne.Subject = "Reminder for Follow Up Orders Due Today '" & dateToday & "'" 'email subject
    msgOne.TextBody = "This is an email reminder to follow up these orders: " & vbCrLf & vbCrLf & emailMessage 'email body
    msgOne.Send
    MsgBox ("Message Sent")
    
End If

Set rst = Nothing
Set unitIDRst = Nothing
Set dbs = Nothing

End Sub

 

[lameid]

I've not used CDO and I didn't take the time to read it and I am going to assume it is not driving data in the form... Generally when you see errors like that something has run afoul in terms of references in returning data, so I would ensure all references are appropriate (that is generally when your not doing something that is a legitimate error).

Beyond that I'd take the next two steps...

I would open the database with the /decompile command line switch to remove compiled code.
I would compact and repair the database.

If weird still happens, then a closer look is warranted.

 

[davyre]

Hi, usually the form/textbox acts fine..but whenever I clicked the button to send the email, the datepicker went wrong.
I can remedy this by closing the MS Access and re-open it, but it is not convenient. And if I click the button again, it goes wrong again.

I have tried to compact/repair database, but no avail.
I am curious on how to to open with /decompile command, what should I do? Would you tell me step by step?

 

[1DMF]

http://datagnostics.com/dtips/decompile.html

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads