sql statement INSERT and UPDATE withn one if - end if code

[NiMora]

and again. I know it´s late and I should go to bed but I want solve this part. So if you can take an eye on this.

as in some threads before I have the following code:

Option Compare
DatabaseOption Explicit


Private Sub Befehl17_Click()
' ##################### OutlookImport ##################################
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim outObject, Mapi, Inbox, InboxImported
Dim i As Integer
Dim strSQL As String


Set rs = CurrentDb.OpenRecordset("OutlookImport")
Set outObject = CreateObject("Outlook.Application")
Set Mapi = outObject.GetNamespace("MAPI")
Set Inbox = Mapi.GetDefaultFolder(olFolderInbox).Folders("import")
Set InboxImported = Mapi.GetDefaultFolder(olFolderInbox).Folders("imported")


    For Each Mail In Inbox.Items
    
    strSQL = "INSERT INTO OutlookImport (AbsenderMail, AbsenderName, SendTo, SendCC, Betreff, MailDatum, Nachricht, EntryID) VALUES ('" & Mail.SenderEmailAddress & "', '" & Mail.SenderName & "', '" & Mail.To & "', '" & Mail.CC & "', '" & Mail.Subject & "', '" & Mail.SentOn & "', '" & Mail.Body & "', '" & Mail.EntryID & "');"


        DoCmd.SetWarnings False
        On Error Resume Next
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
    
         If Not Err.Number <> 0 Then
            If Mail.Attachments.Count > 0 Then                
                AnzAttach = Mail.Attachments.Count
                strPath = "C:\Unterlagen\" & Replace(Mail.SenderName, " ", "") & "_" & Replace(Replace(Mail.SentOn, " ", "_"), ":", ".")
                Debug.Print strPath
                MkDir strPath
                
                For i = 1 To AnzAttach
                    Mail.Attachments.Item(i).SaveAs strPath & Mail.Attachments.Item(i).FileName
                Next i

                strSQLAtt = "UPDATE OutlookImport SET AttachFolder = '" & strPath & "' WHERE AbsenderMail = '" & Mail.SenderEmailAddress & "'"
                CurrentDb.Execute strSQLAtt

            End If                    
            Mail.Move InboxInBearbeitung 'Mails verschieben in Bearbeitung
         End If
    Next


    Forms![OutlookImport].Requery
End Sub

I want to import the path to the Attachments I import before or put into a folder
I add an UPDATE sql statement but it don´t work correctly. I get on every record the same string (the same folder).

what is wrong?

[ItsMe]

It is probably a syntax error. I would make sure everything is printing out as expected using something like the following ...

'Dim db As DAO.Database
'Dim rs As DAO.Recordset
Dim outObject, Mapi, Inbox, InboxImported
Dim i As Integer
Dim strSQL As String

'Set rs = CurrentDb.OpenRecordset("OutlookImport")
Set outObject = CreateObject("Outlook.Application")
Set Mapi = outObject.GetNamespace("MAPI")
Set Inbox = Mapi.GetDefaultFolder(olFolderInbox).Folders("import")
'Set InboxImported = Mapi.GetDefaultFolder(olFolderInbox).Folders("imported")

    For Each Mail In Inbox.Items
    
'    strSQL = "INSERT INTO OutlookImport (AbsenderMail, AbsenderName, SendTo, SendCC, Betreff, MailDatum, Nachricht, EntryID) VALUES ('" & Mail.SenderEmailAddress & "', '" & Mail.SenderName & "', '" & Mail.To & "', '" & Mail.CC & "', '" & Mail.Subject & "', '" & Mail.SentOn & "', '" & Mail.Body & "', '" & Mail.EntryID & "');"
'
'
'        DoCmd.SetWarnings False
        On Error Resume Next
'        DoCmd.RunSQL strSQL
'        DoCmd.SetWarnings True
    
         If Not Err.Number <> 0 Then
            If Mail.Attachments.Count > 0 Then
                AnzAttach = Mail.Attachments.Count
                strPath = "C:\Unterlagen\" & Replace(Mail.SenderName, " ", "") & "_" & Replace(Replace(Mail.SentOn, " ", "_"), ":", ".")
                Debug.Print "*******************************************"
                Debug.Print strPath
'                MkDir strPath
                
'                For i = 1 To AnzAttach
'                    Mail.Attachments.Item(i).SaveAs strPath & Mail.Attachments.Item(i).FileName
'                Next i
                strSQLAtt = "UPDATE OutlookImport SET AttachFolder = '" & strPath & "' WHERE AbsenderMail = '" & Mail.SenderName & "'"
                Debug.Print "**" & vbCrLf
                Debug.Print strSQLAtt
                Debug.Print "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^"
'                CurrentDb.Execute strSQLAtt
            End If
'            Mail.Move InboxInBearbeitung 'Mails verschieben in Bearbeitung
         End If
    Next

'    Forms![OutlookImport].Requery

[June7]

In your other thread I thought you changed the code to include another \ character and use SaveAsFile?

That UPDATE should work. Step debug. Make sure the WHERE clause is correct. Review link at bottom of my post for debugging guidelines.

[NiMora]

Hi ItsMe, thx for reply.

I have allreade do this. I check all variables and its content. actually it works tecnicaly, but I get allways the same content in this field. view moments ago I try to use MailDatum and Mail.SentOn instead of Mail.SenderEmailAddress. with these I get no content in the fields.

could you show me maybe another sollution, to put the path-string into the field "AttachFolder"? I´m a litte desperately

[June7]

Did you read post 3?

Did you try the Debug.Print code suggested by ItsMe?

[NiMora]

Nope, june,

Sorry for that, that was a copy paste Problem. I've changed the Code to SaveAsFile.

but it's 1 AM in Germany, I go to bed now and try my best tomorrow, to find a solution.

thx 4 your help

[NiMora]

hey, sorry for my late reply. I did not solve the problem, I put it all in the INSERT statement.

thx for help