Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2011
    Posts
    33

    Unanswered: Pulling data from outlook

    Hello,

    Here is my situation:

    Firstly I have imported outlook contacts into access (roughly 60,000), access gave the name 'all users'.

    Next, I have a form (frmCSOrequest) with 4 text boxes COSid, CSOname, CSOLocation, CSOemail that are all bound to a table (tblMain).

    In my mind this is what I would like to do:

    Have a button that once clicked will bring up an input box, the user will then enter the CSOid which will lookup the linked outlook table and populate the 4 text boxes with the relevant data (Id, name, location, email).

    I was hoping someone could point me in the right direction in what to search for, as I have no idea (what I've searched for so far has given me zippo)

    Or am I even on the right track in my thinking of what I want, if there is a better method please let me know.

    Many thanks!
    Jeremy.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If the form is bound, all you have to do consists in searching for the wanted row in the data set:
    Code:
    Dim strCSOid As Long
    Dim rst As DAO.Recordset
    
    strCSOid = Inputbox("Please enter an Id:")
    If Len(strCSOid) > 0 Then
        Set rst = Me.RecordsetClone
        rst.FindFirst "CSOid = " & strCSOid
        If rst.Nomatch = True Then
            Msgbox "The Id " & strCSOid & " was not found.", vbInformation
        Else
            Me.Bookmark = rst.Bookmark
        End If
        rst.Close
    End If
    Set rst = Nothing
    Note: If COSid is not numeric, use:
    Code:
    rst.FindFirst "CSOid = '" & strCSOid & "'"
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    when ive done this sort of thing in the past Ive moved the data directly into the db from Outlook. Last time I did it I think I ran an overnight batch job which extracted all data from outlook / exchange server into the db. then moved the email from that holding tank within the email system into another folder / directory / container so the batch job wouldn't attempt to reprocess the job.

    the reasons
    well your data is now controlled by your db, NOT the email system. so you are not going to get problems from users deleting data or the email system archiving stuff off. Although given Sarbone Oxley its unlikely that email data is going to be deleted prematurely. however it does mean that there is reduced information to process.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Sep 2011
    Posts
    33
    Hi,

    Thanks for the replies.

    I think I'm in well over my head with this one.

    Shinndho thanks for the code you supplied, although I don't understand it (not saying much). I don't see any reference to the linked outlook table, so how does it look up the data? Or am I missing something? I get datatype mismatch when I put in a user ID.

    Healdem, thanks for the tip although I don't have the knowledge to do what you suggested. Although it does make sense, I will have to do some research on it. However I like the idea of a linked table, cause then the data will be live (if a new starter begins, the details will be there) or do I have that wrong?

    Thanks
    Jeremy.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Sorry, I thought the form was bound to the Outlook linked table.

    Here's the modified code:
    Code:
    Dim strCSOid As Long
    Dim rst As DAO.Recordset
    
    strCSOid = Inputbox("Please enter an Id:")
    If Len(strCSOid) > 0 Then
        Set rst = CurrentDb.OpenRecordset("all users", dbOpenSnapshot)
        With rst
            .FindFirst "CSOid = " & strCSOid
            If .Nomatch = True Then
                Msgbox "The Id " & strCSOid & " was not found.", vbInformation
            Else
                Me.COSid.Value = !CSOid
                Me.CSOname.Value = !CSOname
                Me.CSOLocation.Value = !CSOLocation
                Me.CSOemail.Value = !CSOemail 
            End If
            .Close
        End With
    End If
    Set rst = Nothing
    This supposes that the names of the columns in the linked Outlook table are COSid, CSOname, CSOLocation, CSOemail. If not, change the code accordingly.
    Have a nice day!

  6. #6
    Join Date
    Sep 2011
    Posts
    33
    Thanks Sinndho, much appreciated.

    And Healdem, I've had to take your advice as the search would sometimes take up to 10 mins to complete via the linked table. So I've imported the contacts into the DB and it works much faster.

    Jeremy.

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •