Update recordset

[TryingMyBest]

What's wrong with this code please. I have an appointment in outlook and I want to use the following code to update the access tables with the custom fields in outlook. The variables are showing the correct data as is in the outlook form, however it is picking up only the first record in the table so the while statement is returned as false. What is the correct code to cycle through the records until the match is made?

Public Sub APTInterface()

Set ins = Application.ActiveInspector
Set itm = ins.CurrentItem
Set con = itm
      
    Set appAccess = CreateObject("Access.Application")
    strAccessPath = "U:\APT\Release 3\"
   
    Set dbe = CreateObject("DAO.DBEngine.36")
    strDBName = "audit_planning_Tool.mdb"
    strDBNameAndPath = strAccessPath & strDBName
   
    Set wks = dbe.Workspaces(0)
    Set dbs = wks.OpenDatabase(strDBNameAndPath)
   
    Set rst = dbs.OpenRecordset("tblauditdata")
    rst.Edit
      
    While con.UserProperties("AuditNumber") = rst!AuditNumber
    con.UserProperties("AuditDate") = rst!AuditDate
    Wend
    
    rst.Update
    rst.Close
    dbs.Close
    Set itm = Nothing
    Set wks = Nothing
    Set dbe = Nothing
    Set appAccess = Nothing
            
End Sub

Thanks
Jo

 

[modest]

I think a while loop will continue until you have a condition to escape the loop. So if you don't change rst!AuditNumber in:
con.UserProperties("AuditNumber") = rst!AuditNumber

The loop won't exit and you'll only be looking at one record.

Try:

Dim obj

For Each obj in Con.UserProperties
    Select Case obj.Name
        Case "AuditNumber"
            If obj = rst!AuditNumber Then
                con.UserProperties("AuditDate") = rst!AuditDate
            End If
    End Select
Next

I don't know if this would work, mainly because I dont know if you could call obj.Name the way I did, and I'm not sure if obj = rst!AuditNumber would look like that either, but this should help you out in looking at all the records.

 

[TryingMyBest]

Thanks for your response modest....here's what's happened now:

For Each obj In con.UserProperties
    Select Case obj.Name
        Case "AuditNumber"
            If obj = rst!AuditNumber Then
                con.UserProperties("AuditDate") = rst!AuditDate
            End If
    End Select
Next

The code loops through all user defined controls on my outlook form. It finds the control with the audit number and correctly identifies that the audit number in outlook is 24, however rst!auditnumber is always the first one in the table which is 1 so the if statement exits.

It looks like this if you replace the variables with the data that they represent:

For Each obj In con.UserProperties
    Select Case obj.Name
        Case "AuditNumber"
            If 24 = 1 Then
                con.UserProperties("AuditDate") = rst!AuditDate
            End If
    End Select
Next

I need to loop through all entries in my table until it gets to auditnumber = obj.

OK I'm going to try a few things and see if I can build on your work.

Jo

 

[TryingMyBest]

I've done it!!!!!!!!

Yippeee.....I figured it out...thank you so much for your help modest.

Here's the code if you're interested.

Option Explicit

Private ins As Outlook.Inspector
Private itm As Object
Private con As Outlook.AppointmentItem
Private appAccess As Access.Application
Private fso As Scripting.FileSystemObject
Private fld As Scripting.Folder
Private strAccessPath As String
Private dbe As DAO.DBEngine
Private strDBName As String
Private strDBNameAndPath As String
Private wks As DAO.Workspace
Private dbs As DAO.Database
Private rst As DAO.Recordset
Dim obj

Public Sub APTInterface()

Set ins = Application.ActiveInspector
Set itm = ins.CurrentItem
Set con = itm
      
    Set appAccess = CreateObject("Access.Application")
    strAccessPath = "U:\APT\Release 3\"
   
    Set dbe = CreateObject("DAO.DBEngine.36")
    strDBName = "audit_planning_Tool.mdb"
    strDBNameAndPath = strAccessPath & strDBName
   
    Set wks = dbe.Workspaces(0)
    Set dbs = wks.OpenDatabase(strDBNameAndPath)
   
    Set rst = dbs.OpenRecordset("tblauditdata")
    
[COLOR=Red]rst.MoveFirst
Do While Not rst.EOF
rst.Edit
For Each obj In con.UserProperties
    Select Case obj.Name
        Case "AuditNumber"
            If obj = rst!AuditNumber Then
                rst!AuditDate = con.UserProperties("AuditDate")
            rst.Update
            End If
    End Select
Next
rst.MoveNext

Loop[/COLOR]
 
    rst.Close
    dbs.Close
    Set itm = Nothing
    Set wks = Nothing
    Set dbe = Nothing
    Set appAccess = Nothing
            
End Sub