.MoveNext not working

[chuckcoleman]

If I run the queries before the rest of the code starts, aren't the records "old", not "new"?

Honestly, this whole code block seems strange. Stuff is in the wrong place or the wrong order like the .movelast and .movefirst coming after you try to set the recordcount. You have detailed error handling but no On Error in the appropriate place. You have an If/Then code block checking a local boolean variable that isn't set or changed anywhere. You have a fairly detailed/complex block of code including conditional operators but don't know how to use the debugger? It all seems a bit strange.

First, I didn't copy the On Error line to what I posted. In my code I have it remarked out so if there is an error I could see where the error occurred. No errors. I'm always open to learning which is why I posed this asking for help. Again, the record count was just for me to use when testing to validate the correct number of records in the table. I'm sorry I appear to be strange. I try not to be.

 

[JMongi]

You don't appear to be strange! The code is strange. Also, it's a faux pas to post an edited version of your code. The fine folks here that are more skilled than I need to see every line of code in order to help. As soon as you start pruning before posting, you inevitably prune something important.

the record count was just for me to use when testing to validate the correct number of records in the table

I'm not sure what you are objecting to? Whatever your use for it, if it is not set correctly and you don't have a way in your code (since we are troubleshooting) to see if it is being set correctly, than how is it useful to you? You are using lngRSCount as a criteria in an If/Then statement so it plays a pretty key part in the flow of your code. Two users so far have highlighted that your setting of that variable may not be working as you intended.

I apologize if my comments sounded harsh. They were not intended to be that way.

The unused Dim's indicate to me that this is a code snippet you borrowed from someone/somewhere else and are modifying it to meet your needs. Nothing wrong with that, most of us do that from time to time, I know I do. Posting the full code with every line included including the Function header sounds like a good place to start. That guarantees that we are not missing anything important.

 

[Isaac]

You're doing this in a very weird way that makes it a mind-twisting exercise to look at.

Drop the With block and just use:

Do Until rs.eof=true
.....do things
rs.movenext
Loop

All the withs and end withs make it simply harder to look at then it needs to be, and if you're newer at this, that triples the impact.

I honestly have no idea why people complicate things with With blocks, when declaring and setting the lowest-level variable/objects possible accomplishes the same thing, without the weirdness to mentally digest

 

[gemma-the-husky]

FirstName = Nz(DLookup("FName", "Temp Email and Tel Table for Outlook"))
LastName = Nz(DLookup("LName", "Temp Email and Tel Table for Outlook"))
AddrStreet = Nz(DLookup("Address", "Temp Email and Tel Table for Outlook"))
AddrStreet2 = Nz(DLookup("Address2", "Temp Email and Tel Table for Outlook"))
AddrCity = Nz(DLookup("City", "Temp Email and Tel Table for Outlook"))
AddrState = Nz(DLookup("State", "Temp Email and Tel Table for Outlook"))
AddrZIP = Nz(DLookup("ZIP", "Temp Email and Tel Table for Outlook"))
CustomerTel = Nz(DLookup("Telephone", "Temp Email and Tel Table for Outlook"))
CustomerEmail = Nz(DLookup("Email", "Temp Email and Tel Table for Outlook"))
MsgBox ("2-The value of FName is: " & FirstName)

RIGHT. I am pretty sure

These dlookups will always return the same value, irrespective of how many records are in the temp object.

So your loop executes twice, but the code to generate the contact repeats the same reads/dlookups each time - and tries to add the same values each time. Maybe .save isn't the right method to add a new record. I doubt you will get duplicated contacts, so maybe .save just overwrites the existing data with the same data, hence no run time error.

This is the sort of thing that gets found by careful controlled analysis of the process, including stepping through - in this case to make sure that the record you are trying to insert carries the correct values.

 

[The_Doc_Man]

I believe .MoveNext only works if the next record is not a new one. In your case, if there are only 2 records, then if will not work.

While it is indeed possible that the .MoveNext didn't work, the reason you gave is wrong. The .MoveNext fails when the current record is also the last record of the set. Not because it is a new record.

However, if the next record is a new record that hasn't been saved yet, there is no way that a .MoveNext that will get to it.

I did notice a rather common "gotcha' that applies to Outlook. You are creating a new Outlook app object each time through the loop, but that is wrong. Last time I tried, Outlook didn't like the attempt to create TWO instances of Outlook on the same machine. It should error out on the 2nd attempt. The correct way to do that is to create ONE instance of Outlook outside the loop and release it outside the other end of the loop.

However, if Outlook is already open at the time you run this, even the FIRST iteration of the loop would fail at that point.

 

[chuckcoleman]

I'm back at this. With this code I've removed all if/then logic to make sure that wasn't causing a problem. I added a counter called TestMsg and I've confirmed the code is looping; it increments the counter each time it goes through the loop. However, for whatever reason, the code continues to pick the data from the first record. rs.MoveNext does not cause it to pick the next record. Someone said it won't work with only two records. I added a third record and the behavior is still the same.

Public Function AddOlContact()
    'Ref: https://docs.microsoft.com/en-us/office/vba/api/outlook.contactitem
    On Error GoTo Error_Handler
    
    Dim MyDB As Database, rs As Recordset
    Dim CustomerID As Long
    Dim rstFiltered As DAO.Recordset
    Dim FName As String
    Dim LName As String
    Dim Address As String
    Dim Address2 As String
    Dim City As String
    Dim State As String
    Dim ZIP As String
    Dim CustomerTel As String
    Dim CustomerEmail As String
    Dim TransferredTo As Boolean
    Dim lngRSCount As Long
    Dim TestMsg As Long
    
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "Delete Temp Email and Tel Table for Outlook"
    DoCmd.Close acQuery, "Delete Temp Email and Tel Table for Outlook"
    DoCmd.OpenQuery "Append FName to TempOutlook Table"
    DoCmd.Close acQuery, "Append FName to TempOutlook Table"
    DoCmd.OpenQuery "Append FName2 to TempOutlook Table"
    DoCmd.Close acQuery, "Append FName2 to TempOutlook Table"
    DoCmd.SetWarnings True
    
    Set MyDB = DBEngine.Workspaces(0).Databases(0)
    Set rs = MyDB.OpenRecordset("Temp Email and Tel Table for Outlook")
    lngRSCount = rs.RecordCount
    TestMsg = 0
     rs.MoveLast
     rs.MoveFirst
    With rs
     Do Until rs.EOF
      MsgBox ("At the beginning of Do Until, TestMsg is " & TestMsg)
      FirstName = Nz(DLookup("FName", "Temp Email and Tel Table for Outlook"))
      LastName = Nz(DLookup("LName", "Temp Email and Tel Table for Outlook"))
      AddrStreet = Nz(DLookup("Address", "Temp Email and Tel Table for Outlook"))
      AddrStreet2 = Nz(DLookup("Address2", "Temp Email and Tel Table for Outlook"))
      AddrCity = Nz(DLookup("City", "Temp Email and Tel Table for Outlook"))
      AddrState = Nz(DLookup("State", "Temp Email and Tel Table for Outlook"))
      AddrZIP = Nz(DLookup("ZIP", "Temp Email and Tel Table for Outlook"))
      CustomerTel = Nz(DLookup("Telephone", "Temp Email and Tel Table for Outlook"))
      CustomerEmail = Nz(DLookup("Email", "Temp Email and Tel Table for Outlook"))
      MsgBox ("123-The value of FName is: " & FirstName)
 
    #Const EarlyBind = False    'True  = Use Early Binding
                                'False = Use Late Binding
    #If EarlyBind = True Then
        'Early Binding Declarations
        'Requires Ref to Microsoft Outlook XX.X Object Library
        Dim oOutlook          As Outlook.Application
        Dim olContact         As Outlook.ContactItem
    #Else
        'Late Binding Declaration/Constants
        Dim olApp             As Object
        Dim olContact         As Object
        Const olContactItem = 2
    #End If
 
    Set olApp = CreateObject("Outlook.Application")
    Set olContact = olApp.CreateItem(olContactItem)

    With olContact
    MsgBox ("TestMsg within olContact is: " & TestMsg)
     .FirstName = FirstName
     .LastName = LastName
     .FullName = FirstName & ", " & LastName
     .FileAs = LastName & ", " & FirstName
     .JobTitle = JobTitles
     .CompanyName = CompName
     .HomeAddressStreet = AddrStreet
     .HomeAddressCity = AddrCity
     .HomeAddressState = AddrState
     .HomeAddressPostalCode = AddrZIP
     .BusinessTelephoneNumber = ContactTel
     .Email1Address = CustomerEmail
     .MobileTelephoneNumber = CustomerTel
     .Save
      ' .Display  'Uncomment if you wish the user to see the contact pop-up
      MsgBox ("Thank you, I have filed " & FirstName & " " & LastName & "'s contact information in Outlook.")
     End With
    TestMsg = TestMsg + 1
    MsgBox ("First TestMsg is: " & TestMsg)
    rs.MoveNext
    MsgBox ("Second TestMsg is: " & TestMsg)
    Loop
    End With

    rs.Close
    MyDB.Close
    Set rs = Nothing
    Set MyDB = Nothing
    Close

Error_Handler_Exit:
    On Error Resume Next
    If Not olContact Is Nothing Then Set olContact = Nothing
    If Not olApp Is Nothing Then Set olApp = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: AddOlContact" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

 

[Pat Hartman]

The domain functions will ALWAYS return a random record, usually the first record because you have no criteria.

Adding the recordset to read through the temp table should solve the problem but only if you get rid of the domain functions and just take the value in the record being read.

And finally, this is a lot of work when you can probably accomplish the task with an append query and no VBA loops at all.

 

[chuckcoleman]

Hi Pat. Can you please give me an example of using an append query to add a contact to Outlook? I believe I understand your point about Domain functions, but I’m not sure how to extract each record from the temp table and use that to send it to Outlook as a Contact.

 

[Pat Hartman]

Use the QBE when you don't know how to build a query.

Open the QBE
Select the From table
Select the columns you want
Change the query type to Append
Choose the To table
If the names don't autotmatically map correctly, you will have to do it manually.

 

[Gasman]

Ok, now I am confused, easily done I know, but someone always finds a new way.?

Why are you using Dlookup() to set the contact values?
The recordset would do that?

So you are moving next, just getting the SAME data each time with DLookUp()?

Obviously I have no idea what all those queries are doing, BUT if you were to explain your logic?, step by step, that might be quicker.?

EG: I would do this

Test to see if any records to make new contacts. If none end with message.
Open/Connect to outlook
Read first record
Populate Outlook contact and Save
Read next record until EOF
Close recordset and tidy up.

So in retrospect, the MoveNext was always working, your logic was just way way out?

 

[Minty]

There is a host of things not right with your code, but the reason for your failure as others have stated is the DLookup - it will only ever return the first value it gets to in the underlying record set.

I have moved your code around a bit (No point continually dimming the outlook stuff in the loop) and adjusted your code to pick the values up from the record set you are opening.
I have removed the close query statements they are unnecessary from what I remember of opening action queries.
I added another method for the action queries that negates the need to turn warnings off.

See if this works

Public Function AddOlContact()
    'Ref: https://docs.microsoft.com/en-us/office/vba/api/outlook.contactitem
    On Error GoTo Error_Handler
   
    Dim MyDB As Database, rs As Recordset
    Dim CustomerID As Long
    Dim rstFiltered As DAO.Recordset
    Dim FName As String
    Dim LName As String
    Dim Address As String
    Dim Address2 As String
    Dim City As String
    Dim State As String
    Dim ZIP As String
    Dim CustomerTel As String
    Dim CustomerEmail As String
    Dim TransferredTo As Boolean
    Dim lngRSCount As Long
    Dim TestMsg As Long
   
    #Const EarlyBind = False    'True  = Use Early Binding
    'False = Use Late Binding
#If EarlyBind = True Then
    'Early Binding Declarations
    'Requires Ref to Microsoft Outlook XX.X Object Library
    Dim oOutlook          As Outlook.Application
    Dim olContact         As Outlook.ContactItem
#Else
    'Late Binding Declaration/Constants
    Dim olApp             As Object
    Dim olContact         As Object
    Const olContactItem = 2
#End If
   
    Set MyDB = CurrentDb
   
    DoCmd.SetWarnings False
   
    MyDB.Execute "Delete Temp Email and Tel Table for Outlook", dbSeeChanges    ''' This method removes the need to set warnings off   
    DoCmd.OpenQuery "Append FName to TempOutlook Table"
    DoCmd.OpenQuery "Append FName2 to TempOutlook Table"

    DoCmd.SetWarnings True
   
    Set rs = MyDB.OpenRecordset("Temp Email and Tel Table for Outlook")
    rs.MoveLast
    rs.MoveFirst
    lngRSCount = rs.RecordCount
    TestMsg = 0

    Do Until rs.EOF
        MsgBox ("At the beginning of Do Until, TestMsg is " & TestMsg)
        FirstName = rs.Fields("FName")
        LastName = rs.Fields("LName")
        AddrStreet = rs.Fields("Address")
        AddrStreet2 = rs.Fields("Address2")
        AddrCity = rs.Fields("City")
        AddrState = rs.Fields("State")
        AddrZIP = rs.Fields("ZIP")
        CustomerTel = rs.Fields("Telephone")
        CustomerEmail = rs.Fields("Email")
        MsgBox ("123-The value of FName is: " & FirstName)

        Set olApp = CreateObject("Outlook.Application")
        Set olContact = olApp.CreateItem(olContactItem)

        With olContact
            MsgBox ("TestMsg within olContact is: " & TestMsg)
            .FirstName = FirstName
            .LastName = LastName
            .FullName = FirstName & ", " & LastName
            .FileAs = LastName & ", " & FirstName
            .JobTitle = JobTitles
            .CompanyName = CompName
            .HomeAddressStreet = AddrStreet
            .HomeAddressCity = AddrCity
            .HomeAddressState = AddrState
            .HomeAddressPostalCode = AddrZIP
            .BusinessTelephoneNumber = ContactTel
            .Email1Address = CustomerEmail
            .MobileTelephoneNumber = CustomerTel
            .Save
            ' .Display  'Uncomment if you wish the user to see the contact pop-up
            MsgBox ("Thank you, I have filed " & FirstName & " " & LastName & "'s contact information in Outlook.")
        End With
        TestMsg = TestMsg + 1
        MsgBox ("First TestMsg is: " & TestMsg)
        rs.MoveNext
        MsgBox ("Second TestMsg is: " & TestMsg)
       
        Set olApp = Nothing         ' Close the objects you are opening in the loop
        Set olContact = Nothing
   
    Loop

    rs.Close
    MyDB.Close
    Set rs = Nothing
    Set MyDB = Nothing
    Close

Error_Handler_Exit:
    On Error Resume Next
    If Not olContact Is Nothing Then Set olContact = Nothing
    If Not olApp Is Nothing Then Set olApp = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
        "Error Number: " & Err.Number & vbCrLf & _
        "Error Source: AddOlContact" & vbCrLf & _
        "Error Description: " & Err.Description & _
        Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
        , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

 

[Minty]

I have just noticed that quite a number of the fields you set in the outlook code aren't declared in the variable set up or in the recordset.

e.g.
.JobTitle = JobTitles
.CompanyName = CompName

Neither of these is specified as variables anywhere, so you will get an error if you compile the code, or try and run it.
Add Option Explicit to the top of all your code modules, and it will highlight these types of errors.

 

[chuckcoleman]

OK, I have mostly figured this out. After reading Pat Herman's reply about DLookup and no criteria, (and Minty's feedback which addressed the same issue), I added an Autonumber field to the temp table, and I used the DFirst function to find the first Autonumber value and then set the DLookup criteria to that value. Just before the loop, I added +1 to the DFirst value and that became the new criteria. It works.

However, I believe I've also learned from Minty's suggestion a cleaner way that doesn't use any Domain function. Thank you. I still have some things to figure out like if the contact person has two telephone numbers, (cell and landline), mapping that to the same contact. I'll get there.

Thank you all for pointing me in the right direction. My motto is, "I reserve the right to learn more each day."

 

[The_Doc_Man]

Just a word of warning. using DFirst works GREAT on queries that have an ORDER BY in them. Doesn't work well at all on a table, because raw tables are stored in chronological order of when each record was last updated, and directly naming a table doesn't allow you to use an ORDER BY clause. Use DMin (or DMax if the ORDER BY was DESC - descending).

 

[Minty]

The point with the recordset is that by stepping through it you don't really need to know how many records you have (as long as you have at least 1!) and you don't really need to know where you are in the recordset at any given time as you are only dealing with one record at a time.

 

[chuckcoleman]

Just a word of warning. using DFirst works GREAT on queries that have an ORDER BY in them. Doesn't work well at all on a table, because raw tables are stored in chronological order of when each record was last updated, and directly naming a table doesn't allow you to use an ORDER BY clause. Use DMin (or DMax if the ORDER BY was DESC - descending).

Got it. Thank you!

 

[Pat Hartman]

Since you are stepping back, step all the way back. It is rarely necessary to do what you are trying to do using a code loop. It is far easier and far more efficient to use an Action query.. Look back at my description of how to create one.

 

[chuckcoleman]

Since you are stepping back, step all the way back. It is rarely necessary to do what you are trying to do using a code loop. It is far easier and far more efficient to use an Action query.. Look back at my description of how to create one.

Hi Pat. I just want to make sure we’re on the same page. I want to be able from a customer form, (contains data for one customer name that may include one or more telephone numbers and probably just one email address), click on a command button and have the contact information sent to Outlook Contacts and create a new contact. I know how to export the data to a file and import it from within Outlook, but for my users that’s too many steps. I realize it won’t update an existing contact, but that’s ok. I interpret your feedback as this can be done with an Access action query. Maybe I’m wrong in understanding what you’re suggesting.

 

[Pat Hartman]

I haven't had to do this in years but try linking to the contacts table in Outlook. I think it is in Other data sources. You can use this linked table like any other linked table. You can even update it. However, that process is a little strange. If you open the table and modify one of the entries, the entry shows #deleted# after the update completes. so it looks like the process deletes the old record and adds a new record because if you close and open the recordset, you see the updated record. If all you are doing is appending records, that should be fine.

 

[chuckcoleman]

I haven't had to do this in years but try linking to the contacts table in Outlook. I think it is in Other data sources. You can use this linked table like any other linked table. You can even update it. However, that process is a little strange. If you open the table and modify one of the entries, the entry shows #deleted# after the update completes. so it looks like the process deletes the old record and adds a new record because if you close and open the recordset, you see the updated record. If all you are doing is appending records, that should be fine.

Thanks Pat. I always appreciate your feedback. Chuck