Using DLookup

[Kosmos]

Hey guys...I've never used DLookup before...
I'm just simply wondering how to use it. I have to check a field to see if it has been entered before. The field is in recSet6.Fields( "DatabaseRefere nceNumber") which I have to check to see if it has any matches to a specific recSet1.Fields( "DatabaseRefere nceNumber") which is counted through a Do Until recSet1.EOF kinda loop (which goes from 1 until the end of the field, but the recSet1.Fields( "DatabaseRefere nceNumber") I am checking against is not always one after another due to multiple IF statements within the loop). Any help with this would be much appreciated.

[Kosmos]

actually I'm not even sure if I'm correct in using the DLookup function, I just need a function that will return a true/false value depending on whether or not the other value is found in that field.

[willakawill]

actually I'm not even sure if I'm correct in using the DLookup function, I just need a function that will return a true/false value depending on whether or not the other value is found in that field.

Hi. Help is in ample supply to those who are willing to post code :)

[Kosmos]

well I hope you do not mean that you think I don't ever post code...you can look at some of my previous posts with respect to that...but assuming you did not mean that...sure here's the whole function with many comments but I put the comment where I'm specifically looking at in CAPITALS :)



[PHP]Private Sub cmdAddToOutlook _Click()
On Error GoTo Err_cmdAddToOut look_Click

DoCmd.SetWarnin gs False
DoCmd.OpenQuery ("CleartblContr actsTemp")

Dim stDocName As String

stDocName = "FindVendorCont racts"
DoCmd.OpenQuery stDocName, acNormal, acEdit




DoCmd.OpenQuery ("ClearNotifica tionXTable")
DoCmd.OpenQuery ("ClearNotEnded PastRenewalXTab le")
DoCmd.OpenQuery ("ClearContract EndXTables")
DoCmd.OpenQuery ("ClearExpiredX Tables")

'Opening ADODB connections and record sets
Dim con1 As ADODB.Connectio n
Dim con2 As ADODB.Connectio n
Dim con3 As ADODB.Connectio n
Dim con4 As ADODB.Connectio n
Dim con5 As ADODB.Connectio n
Dim con6 As ADODB.Connectio n
Dim con7 As ADODB.Connectio n
Dim recSet1 As ADODB.Recordset
Dim recSet2 As ADODB.Recordset
Dim recSet3 As ADODB.Recordset
Dim recSet4 As ADODB.Recordset
Dim recSet5 As ADODB.Recordset
Dim recSet6 As ADODB.Recordset
Dim recSet7 As ADODB.Recordset
Set con1 = CurrentProject. Connection
Set con2 = CurrentProject. Connection
Set con3 = CurrentProject. Connection
Set con4 = CurrentProject. Connection
Set con5 = CurrentProject. Connection
Set con6 = CurrentProject. Connection
Set con7 = CurrentProject. Connection
Set recSet1 = New ADODB.Recordset
Set recSet2 = New ADODB.Recordset
Set recSet3 = New ADODB.Recordset
Set recSet4 = New ADODB.Recordset
Set recSet5 = New ADODB.Recordset
Set recSet6 = New ADODB.Recordset
Set recSet7 = New ADODB.Recordset

'setting records and connections to actual tables (and allowing editing capabilities to temporary tables and the table that checks if the appointment has already been sent to Outlook, but not the Contracts table)
recSet1.Open "tblContractsTe mp", con1, , adLockOptimisti c
recSet2.Open "NotEndedPastRe newalX", con2, adOpenKeyset, adLockOptimisti c
recSet3.Open "NotificationX" , con3, adOpenKeyset, adLockOptimisti c
recSet4.Open "ContractEn dX", con4, adOpenKeyset, adLockOptimisti c
recSet5.Open "ExpiredX", con5, adOpenKeyset, adLockOptimisti c
recSet6.Open "AddedToOutlook ", con6, adOpenKeyset, adLockOptimisti c
recSet7.Open "tblContrac ts", con7, adOpenKeyset, adLockReadOnly

Dim X As Long
Dim Y As Long
Y = 0
'In context of the form this will open in, asking user for the number of days notification before contract end or notification or non-renewal (or renewal)
X = InputBox("Enter the number of days:")

'Declaring UntilCompletion as the amount of days until completion and UntilCompletion 2 will be number of days until required notification
Dim UntilCompletion As Long
Dim UntilCompletion 2 As Long

'Looping until EOF (until the last record for EndDate in tblContracts... so
'someone else would have declared recSet1.Open
'"tblWhateverYo urTableNameIs", con1 which means
'connection1 and then to open a field in that recordset you type
'recSet1.Fields ("fieldname" ))

recSet1.MoveFir st
Do Until recSet1.EOF
' End Date must be in quotes or will not work
UntilCompletion = DateDiff("d", Date, recSet1.Fields( "EndDate2") )
recSet1.Fields( "NotificationDa te2") = (recSet1.Fields ("EndDate2") - recSet1.Fields( "RequiredNotifi cationInDays2") )
UntilCompletion 2 = DateDiff("d", Date, recSet1.Fields( "NotificationDa te2"))


' For Debugging purposes...chec k in immediate window
Debug.Print X
Debug.Print UntilCompletion
Debug.Print UntilCompletion 2

If UntilCompletion 2 >= 0 And UntilCompletion 2 <= X Then
'Must say rs.AddNew and rs.Update before and after updating fields
'Captures contracts that require notification within the next x number of days
recSet3.AddNew
recSet3.Fields( "UntilNotificat ion") = UntilCompletion 2
recSet3.Fields( "Vendor") = recSet1.Fields( "Vendor2")
recSet3.Fields( "NotificationAd dress") = recSet1.Fields( "NotificationAd dress2")
recSet3.Fields( "RequiredNotifi cationInDays") = recSet1.Fields( "RequiredNotifi cationInDays2")
recSet3.Fields( "DateofContract ") = recSet1.Fields( "DateofContract 2")
recSet3.Fields( "NotificationDa te") = recSet1.Fields( "NotificationDa te2")
recSet3.Fields( "TermofContract ") = recSet1.Fields( "TermofContract 2")
recSet3.Fields( "EndDate") = recSet1.Fields( "EndDate2")
recSet3.Fields( "PaymentTer ms/LateFees") = recSet1.Fields( "PaymentTer ms/LateFees2")
recSet3.Fields( "AutomaticRenew al") = recSet1.Fields( "AutomaticRenew al2")
recSet3.Fields( "EarlyOutClause ") = recSet1.Fields( "EarlyOutClause 2")
recSet3.Fields( "OwnerName" ) = recSet1.Fields( "OwnerName2 ")
recSet3.Fields( "City") = recSet1.Fields( "City2")
recSet3.Fields( "Department ") = recSet1.Fields( "Department 2")
recSet3.Fields( "LicensedUs e") = recSet1.Fields( "LicensedUs e2")
recSet3.Update


'THIS IS WHERE I WANT TO CHECK IF THERE IS A VALUE ALREADY IN THIS SEPARATE TABLE recSet6.Fields( "DatabaseRefere nceNumber")
'IT IS A CHECK TO MAKE SURE I HAVE NOT ALREADY ADDED IT TO OUTLOOK

recSet6.AddNew
Dim outobj As Outlook.Applica tion
Dim outappt As Outlook.Appoint mentItem
Set outobj = CreateObject("o utlook.applicat ion")
Set outappt = outobj.CreateIt em(olAppointmen tItem)
With outappt
.Start = recSet1.Fields( "NotificationDa te2") & " " & recSet1.Fields( "ApptTime2" )
.Duration = 15
.Subject = "Contract Notification/End" & " " & recSet1.Fields( "DatabaseRefere nceNumber2") & " " & recSet1.Fields( "Vendor2")
.Body = "Contract Notification/End" & " " & recSet1.Fields( "DatabaseRefere nceNumber2") & " " & recSet1.Fields( "Vendor2")
.ReminderMinute sBeforeStart = recSet1.Fields( "ReminderMinute s2")
.ReminderSet = True
.Save
End With
recSet6.Fields( "AddedToOutlook ") = True
recSet6.Fields( "DatabaseRefere nceNumber") = recSet1.Fields( "DatabaseRefere nceNumber2")
Set outobj = Nothing
DoCmd.RunComman d acCmdSaveRecord
recSet6.Update
recSet6.MoveNex t

End If


[/PHP]

[Kosmos]

Function continued since was too long (in case anyone wanted to follow) [I have posted a similar function to this before, however, that is easier to follow but doesn't have this extra adding appointments to outlook section in there]:
[PHP]

End If
If UntilCompletion >= 0 And UntilCompletion <= X Then
'Captures contracts that end within the next x number of days
recSet4.AddNew
recSet4.Fields( "UntilContractE nd") = UntilCompletion
recSet4.Fields( "Vendor") = recSet1.Fields( "Vendor2")
recSet4.Fields( "NotificationAd dress") = recSet1.Fields( "NotificationAd dress2")
recSet4.Fields( "RequiredNotifi cationInDays") = recSet1.Fields( "RequiredNotifi cationInDays2")
recSet4.Fields( "DateofContract ") = recSet1.Fields( "DateofContract 2")
recSet4.Fields( "NotificationDa te") = recSet1.Fields( "NotificationDa te2")
recSet4.Fields( "TermofContract ") = recSet1.Fields( "TermofContract 2")
recSet4.Fields( "EndDate") = recSet1.Fields( "EndDate2")
recSet4.Fields( "PaymentTer ms/LateFees") = recSet1.Fields( "PaymentTer ms/LateFees2")
recSet4.Fields( "AutomaticRenew al") = recSet1.Fields( "AutomaticRenew al2")
recSet4.Fields( "EarlyOutClause ") = recSet1.Fields( "EarlyOutClause 2")
recSet4.Fields( "OwnerName" ) = recSet1.Fields( "OwnerName2 ")
recSet4.Fields( "City") = recSet1.Fields( "City2")
recSet4.Fields( "Department ") = recSet1.Fields( "Department 2")
recSet4.Fields( "LicensedUs e") = recSet1.Fields( "LicensedUs e2")
recSet4.Update
If UntilCompletion 2 <= UntilCompletion And UntilCompletion 2 < 0 Then
'Captures contracts that end within the next x number of days but have expired
UntilCompletion 2 = (UntilCompletio n2 * (-1))
recSet2.AddNew
recSet2.Fields( "PastRenewalDat e") = UntilCompletion 2
recSet2.Fields( "Vendor") = recSet1.Fields( "Vendor2")
recSet2.Fields( "NotificationAd dress") = recSet1.Fields( "NotificationAd dress2")
recSet2.Fields( "RequiredNotifi cationInDays") = recSet1.Fields( "RequiredNotifi cationInDays2")
recSet2.Fields( "DateofContract ") = recSet1.Fields( "DateofContract 2")
recSet2.Fields( "NotificationDa te") = recSet1.Fields( "NotificationDa te2")
recSet2.Fields( "TermofContract ") = recSet1.Fields( "TermofContract 2")
recSet2.Fields( "EndDate") = recSet1.Fields( "EndDate2")
recSet2.Fields( "PaymentTer ms/LateFees") = recSet1.Fields( "PaymentTer ms/LateFees2")
recSet2.Fields( "AutomaticRenew al") = recSet1.Fields( "AutomaticRenew al2")
recSet2.Fields( "EarlyOutClause ") = recSet1.Fields( "EarlyOutClause 2")
recSet2.Fields( "OwnerName" ) = recSet1.Fields( "OwnerName2 ")
recSet2.Fields( "City") = recSet1.Fields( "City2")
recSet2.Fields( "Department ") = recSet1.Fields( "Department 2")
recSet2.Fields( "LicensedUs e") = recSet1.Fields( "LicensedUs e2")
recSet2.Update
End If
ElseIf UntilCompletion < 0 Then
'Captures contracts that have expired and are presented in a table which the user can use in order to delete information they no longer need if they have proper access
UntilCompletion = (UntilCompletio n * (-1))
recSet5.AddNew
recSet5.Fields( "PastExpiration ") = UntilCompletion
recSet5.Fields( "Vendor") = recSet1.Fields( "Vendor2")
recSet5.Fields( "NotificationAd dress") = recSet1.Fields( "NotificationAd dress2")
recSet5.Fields( "RequiredNotifi cationInDays") = recSet1.Fields( "RequiredNotifi cationInDays2")
recSet5.Fields( "DateofContract ") = recSet1.Fields( "DateofContract 2")
recSet5.Fields( "NotificationDa te") = recSet1.Fields( "NotificationDa te2")
recSet5.Fields( "TermofContract ") = recSet1.Fields( "TermofContract 2")
recSet5.Fields( "EndDate") = recSet1.Fields( "EndDate2")
recSet5.Fields( "PaymentTer ms/LateFees") = recSet1.Fields( "PaymentTer ms/LateFees2")
recSet5.Fields( "AutomaticRenew al") = recSet1.Fields( "AutomaticRenew al2")
recSet5.Fields( "EarlyOutClause ") = recSet1.Fields( "EarlyOutClause 2")
recSet5.Fields( "OwnerName" ) = recSet1.Fields( "OwnerName2 ")
recSet5.Fields( "City") = recSet1.Fields( "City2")
recSet5.Fields( "Department ") = recSet1.Fields( "Department 2")
recSet5.Fields( "LicensedUs e") = recSet1.Fields( "LicensedUs e2")
recSet5.Update

End If

recSet1.MoveNex t
Loop

'Closing connections and clearing record sets
recSet1.Close
recSet2.Close
recSet3.Close
recSet4.Close
recSet5.Close
recSet6.Close
recSet7.Close
con1.Close
con2.Close
con3.Close
con4.Close
con5.Close
con6.Close
con7.Close
Set con1 = Nothing
Set con2 = Nothing
Set con3 = Nothing
Set con4 = Nothing
Set con5 = Nothing
Set con6 = Nothing
Set con7 = Nothing
Set recSet1 = Nothing
Set recSet2 = Nothing
Set recSet3 = Nothing
Set recSet4 = Nothing
Set recSet5 = Nothing
Set recSet6 = Nothing
Set recSet7 = Nothing
Y = 0

DoCmd.TransferS preadsheet acExport, acSpreadsheetTy peExcel9, "NotificationX" , "C:\ContractRep orts\Personaliz edContractRepor t_ImportantDate s", True
DoCmd.TransferS preadsheet acExport, acSpreadsheetTy peExcel9, "ContractEn dX", "C:\ContractRep orts\Personaliz edContractRepor t_ImportantDate s", True
DoCmd.TransferS preadsheet acExport, acSpreadsheetTy peExcel9, "NotEndedPastRe newalX", "C:\ContractRep orts\Personaliz edContractRepor t_ImportantDate s", True
DoCmd.TransferS preadsheet acExport, acSpreadsheetTy peExcel9, "ExpiredX", "C:\ContractRep orts\Personaliz edContractRepor t_ImportantDate s", True

DoCmd.OpenQuery ("ClearNotifica tionXTable")
DoCmd.OpenQuery ("ClearNotEnded PastRenewalXTab le")
DoCmd.OpenQuery ("ClearContract EndXTables")
DoCmd.OpenQuery ("ClearExpiredX Tables")
DoCmd.OpenQuery ("CleartblContr actsTemp")




'CommandBars("M enu Bar"). _
'Controls("Tool s"). _
'Controls("Data base utilities"). _
'Controls("Comp act and repair database..."). _
'accDoDefaultAc tion

DoCmd.SetWarnin gs True




Exit_cmdAddToOu tlook_Click:
Exit Sub

Err_cmdAddToOut look_Click:
MsgBox Err.Description
Resume Exit_cmdAddToOu tlook_Click
End Sub[/PHP]

[Kosmos]

I basically know what I want to do: It's an If statement that would ask:

IsNull(Dlookup( of recSet6.Fields" DatabaseReferen ceNumber" where is equal to recSet1.Fields( "DatabaseRefere nceNumber") which will be a specific number at that time)

I'm just not sure how to format what's inside of the DLookup when using integers.
The actual table names for recSet1 and recSet6 are 'tblContractsTe mp' for recSet1 and 'AddedToOutlook ' for recSet6

[Kosmos]

If IsNull(DLookup( "[DatabaseReferen ceNumber]", "AddedToOutlook ", ("[DatabaseReferen ceNumber]" = recSet1.Fields( "DatabaseRefere nceNumber2")))) Then

that's what I'm using...I'm getting no errors but It's coming up with a null every time which it shouldn't be...so it keeps on adding the three rows I currently have in the table so there is definetely a 1,2 and 3 in there and it's not finding it for some reason...am I setting the criteria in the wrong way?

[willakawill]

If IsNull(DLookup( "[DatabaseReferen ceNumber]", "AddedToOutlook ", ("[DatabaseReferen ceNumber]" = recSet1.Fields( "DatabaseRefere nceNumber2")))) Then

that's what I'm using...I'm getting no errors but It's coming up with a null every time which it shouldn't be...so it keeps on adding the three rows I currently have in the table so there is definetely a 1,2 and 3 in there and it's not finding it for some reason...am I setting the criteria in the wrong way?

You can try this. It is different in quite a few ways from your original code

Private Sub cmdAddToOutlook_Click()
    On Error GoTo Err_cmdAddToOutlook_Click

    DoCmd.SetWarnings False
    DoCmd.OpenQuery ("CleartblContractsTemp")

    Dim stDocName As String
    Dim stSQL As String

    stDocName = "FindVendorContracts"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    



    DoCmd.OpenQuery ("ClearNotificationXTable")
    DoCmd.OpenQuery ("ClearNotEndedPastRenewalXTable")
    DoCmd.OpenQuery ("ClearContractEndXTables")
    DoCmd.OpenQuery ("ClearExpiredXTables")

    'Opening ADODB connections and record sets
    Dim con As ADODB.Connection
    
    Dim rsContTemp As ADODB.Recordset
    Dim rsPastRen As ADODB.Recordset
    Dim rsNotification As ADODB.Recordset
    Dim rsContEnd As ADODB.Recordset
    Dim rsExp As ADODB.Recordset
    Dim rsOutlook As ADODB.Recordset
    Dim rsContracts As ADODB.Recordset
    
    Set con = CurrentProject.Connection
    
    Set rsContTemp = New ADODB.Recordset
    Set rsPastRen = New ADODB.Recordset
    Set rsNotification = New ADODB.Recordset
    Set rsContEnd = New ADODB.Recordset
    Set rsExp = New ADODB.Recordset
    Set rsOutlook = New ADODB.Recordset
    Set rsContracts = New ADODB.Recordset
    
    'setting records and connections to actual tables (and allowing editing
    'capabilities to temporary tables and the table that checks if the
    'appointment has already been sent to Outlook, but not the Contracts table)
    rsContTemp.Open "tblContractsTemp", con, , adLockOptimistic
    rsPastRen.Open "NotEndedPastRenewalX", con, adOpenKeyset, adLockOptimistic
    rsNotification.Open "NotificationX", con, adOpenKeyset, adLockOptimistic
    rsContEnd.Open "ContractEndX", con, adOpenKeyset, adLockOptimistic
    rsExp.Open "SELECT * FROM ExpiredX WHERE PastExpiration = -1", con, adOpenKeyset, adLockOptimistic
    rsContracts.Open "tblContracts", con, adOpenKeyset, adLockReadOnly
    
    Dim X As Long
    Dim Y As Long
    Y = 0
    'In context of the form this will open in, asking
    'user for the number of days notification before
    'contract end or notification or non-renewal (or renewal)
    X = InputBox("Enter the number of days:")
    
    'Declaring UntilCompletion as the amount of days
    'until completion and UntilCompletion2 will be
    'number of days until required notification
    Dim UntilCompletion As Long
    Dim UntilCompletion2 As Long

    'Looping until EOF (until the last record for EndDate in tblContracts...so
    'someone else would have declared rsContTemp.Open
    '"tblWhateverYourTableNameIs", con1 which means
    'connection1 and then to open a field in that recordset you type
    'rsContTemp.Fields("fieldname"))
    
    rsContTemp.MoveFirst
    Do Until rsContTemp.EOF
      ' End Date must be in quotes or will not work
      UntilCompletion = DateDiff("d", Date, rsContTemp.Fields("EndDate2"))
      rsContTemp.Fields("NotificationDate2") = (rsContTemp.Fields("EndDate2") - rsContTemp.Fields("RequiredNotificationInDays2"))
      UntilCompletion2 = DateDiff("d", Date, rsContTemp.Fields("NotificationDate2"))

      
      ' For Debugging purposes...check in immediate window
      Debug.Print X
      Debug.Print UntilCompletion
      Debug.Print UntilCompletion2
    
      If UntilCompletion2 >= 0 And UntilCompletion2 <= X Then
            'Must say rs.AddNew and rs.Update before and after updating fields
            'Captures contracts that require notification within the next x number of days
            With rsNotification
                .AddNew
                .Fields("UntilNotification") = UntilCompletion2
                .Fields("Vendor") = rsContTemp.Fields("Vendor2")
                .Fields("NotificationAddress") = rsContTemp.Fields("NotificationAddress2")
                .Fields("RequiredNotificationInDays") = rsContTemp.Fields("RequiredNotificationInDays2")
                .Fields("DateofContract") = rsContTemp.Fields("DateofContract2")
                .Fields("NotificationDate") = rsContTemp.Fields("NotificationDate2")
                .Fields("TermofContract") = rsContTemp.Fields("TermofContract2")
                .Fields("EndDate") = rsContTemp.Fields("EndDate2")
                .Fields("PaymentTerms/LateFees") = rsContTemp.Fields("PaymentTerms/LateFees2")
                .Fields("AutomaticRenewal") = rsContTemp.Fields("AutomaticRenewal2")
                .Fields("EarlyOutClause") = rsContTemp.Fields("EarlyOutClause2")
                .Fields("OwnerName") = rsContTemp.Fields("OwnerName2")
                .Fields("City") = rsContTemp.Fields("City2")
                .Fields("Department") = rsContTemp.Fields("Department2")
                .Fields("LicensedUse") = rsContTemp.Fields("LicensedUse2")
                .Update
            End With

[willakawill]

'THIS IS WHERE I WANT TO CHECK IF THERE IS A VALUE ALREADY IN THIS SEPARATE TABLE rsOutlook.Fields("DatabaseReferenceNumber")
'IT IS A CHECK TO MAKE SURE I HAVE NOT ALREADY ADDED IT TO OUTLOOK

                    stSQL = "SELECT * FROM AddedToOutlook WHERE DatabaseReferenceNumber = " _
                            & rsContTemp("DatabaseReferenceNumber2")
                            
                    rsOutlook.Open stSQL, con, adOpenKeyset, adLockOptimistic
                    If rsOutlook.EOF Then
                        rsOutlook.AddNew
                        Dim outobj As Outlook.Application
                        Dim outappt As Outlook.AppointmentItem
                        Set outobj = CreateObject("outlook.application")
                        Set outappt = outobj.CreateItem(olAppointmentItem)
                            With outappt
                                .Start = rsContTemp.Fields("NotificationDate2") _
                                    & " " & rsContTemp.Fields("ApptTime2")
                                .Duration = 15
                                .Subject = "Contract Notification/End" & " " _
                                    & rsContTemp.Fields("DatabaseReferenceNumber2") _
                                    & " " & rsContTemp.Fields("Vendor2")
                                .Body = "Contract Notification/End" & " " _
                                    & rsContTemp.Fields("DatabaseReferenceNumber2") _
                                    & " " & rsContTemp.Fields("Vendor2")
                                .ReminderMinutesBeforeStart = rsContTemp.Fields("ReminderMinutes2")
                                .ReminderSet = True
                                .Save
                            End With
                        rsOutlook.Fields("AddedToOutlook") = True
                        rsOutlook.Fields("DatabaseReferenceNumber") = rsContTemp.Fields("DatabaseReferenceNumber2")
                        Set outobj = Nothing
                        'DoCmd.RunCommand acCmdSaveRecord
                        rsOutlook.Update
                    End If
                    rsOutlook.Close
                End If
             End If
      If UntilCompletion >= 0 And UntilCompletion <= X Then
            'Captures contracts that end within the next x number of days
            With rsContEnd
                .AddNew
                .Fields("UntilContractEnd") = UntilCompletion
                .Fields("Vendor") = rsContTemp.Fields("Vendor2")
                .Fields("NotificationAddress") = rsContTemp.Fields("NotificationAddress2")
                .Fields("RequiredNotificationInDays") = rsContTemp.Fields("RequiredNotificationInDays2")
                .Fields("DateofContract") = rsContTemp.Fields("DateofContract2")
                .Fields("NotificationDate") = rsContTemp.Fields("NotificationDate2")
                .Fields("TermofContract") = rsContTemp.Fields("TermofContract2")
                .Fields("EndDate") = rsContTemp.Fields("EndDate2")
                .Fields("PaymentTerms/LateFees") = rsContTemp.Fields("PaymentTerms/LateFees2")
                .Fields("AutomaticRenewal") = rsContTemp.Fields("AutomaticRenewal2")
                .Fields("EarlyOutClause") = rsContTemp.Fields("EarlyOutClause2")
                .Fields("OwnerName") = rsContTemp.Fields("OwnerName2")
                .Fields("City") = rsContTemp.Fields("City2")
                .Fields("Department") = rsContTemp.Fields("Department2")
                .Fields("LicensedUse") = rsContTemp.Fields("LicensedUse2")
                .Update
            End With
            
            If UntilCompletion2 <= UntilCompletion And UntilCompletion2 < 0 Then
                'Captures contracts that end within the next x number of days but have expired
                UntilCompletion2 = (UntilCompletion2 * (-1))
                With rsPastRen
                    .AddNew
                    .Fields("PastRenewalDate") = UntilCompletion2
                    .Fields("Vendor") = rsContTemp.Fields("Vendor2")
                    .Fields("NotificationAddress") = rsContTemp.Fields("NotificationAddress2")
                    .Fields("RequiredNotificationInDays") = rsContTemp.Fields("RequiredNotificationInDays2")
                    .Fields("DateofContract") = rsContTemp.Fields("DateofContract2")
                    .Fields("NotificationDate") = rsContTemp.Fields("NotificationDate2")
                    .Fields("TermofContract") = rsContTemp.Fields("TermofContract2")
                    .Fields("EndDate") = rsContTemp.Fields("EndDate2")
                    .Fields("PaymentTerms/LateFees") = rsContTemp.Fields("PaymentTerms/LateFees2")
                    .Fields("AutomaticRenewal") = rsContTemp.Fields("AutomaticRenewal2")
                    .Fields("EarlyOutClause") = rsContTemp.Fields("EarlyOutClause2")
                    .Fields("OwnerName") = rsContTemp.Fields("OwnerName2")
                    .Fields("City") = rsContTemp.Fields("City2")
                    .Fields("Department") = rsContTemp.Fields("Department2")
                    .Fields("LicensedUse") = rsContTemp.Fields("LicensedUse2")
                    .Update
                End With
            End If
            
        Else 'UntilCompletion < 0
            'Captures contracts that have expired and are presented
            'in a table which the user can use in order to delete
            'information they no longer need if they have proper access
            UntilCompletion = (UntilCompletion * (-1))
            With rsExp
                .AddNew
                .Fields("PastExpiration") = UntilCompletion
                .Fields("Vendor") = rsContTemp.Fields("Vendor2")
                .Fields("NotificationAddress") = rsContTemp.Fields("NotificationAddress2")
                .Fields("RequiredNotificationInDays") = rsContTemp.Fields("RequiredNotificationInDays2")
                .Fields("DateofContract") = rsContTemp.Fields("DateofContract2")
                .Fields("NotificationDate") = rsContTemp.Fields("NotificationDate2")
                .Fields("TermofContract") = rsContTemp.Fields("TermofContract2")
                .Fields("EndDate") = rsContTemp.Fields("EndDate2")
                .Fields("PaymentTerms/LateFees") = rsContTemp.Fields("PaymentTerms/LateFees2")
                .Fields("AutomaticRenewal") = rsContTemp.Fields("AutomaticRenewal2")
                .Fields("EarlyOutClause") = rsContTemp.Fields("EarlyOutClause2")
                .Fields("OwnerName") = rsContTemp.Fields("OwnerName2")
                .Fields("City") = rsContTemp.Fields("City2")
                .Fields("Department") = rsContTemp.Fields("Department2")
                .Fields("LicensedUse") = rsContTemp.Fields("LicensedUse2")
                .Update
            End With
      End If
      
      rsContTemp.MoveNext
    Loop
      
    'Closing connection and clearing record sets
    rsContTemp.Close
    rsPastRen.Close
    rsNotification.Close
    rsContEnd.Close
    rsExp.Close
    rsContracts.Close
    con.Close
    
    Set con = Nothing
    
    Set rsContTemp = Nothing
    Set rsPastRen = Nothing
    Set rsNotification = Nothing
    Set rsContEnd = Nothing
    Set rsExp = Nothing
    Set rsOutlook = Nothing
    Set rsContracts = Nothing
    Y = 0
    
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "NotificationX", _
        "C:\ContractReports\PersonalizedContractReport_Impo  rtantDates", True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "ContractEndX", _
        "C:\ContractReports\PersonalizedContractReport_Impo  rtantDates", True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "NotEndedPastRenewalX", _
        "C:\ContractReports\PersonalizedContractReport_Impo  rtantDates", True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "ExpiredX", _
        "C:\ContractReports\PersonalizedContractReport_Impo  rtantDates", True

  DoCmd.OpenQuery ("ClearNotificationXTable")
  DoCmd.OpenQuery ("ClearNotEndedPastRenewalXTable")
  DoCmd.OpenQuery ("ClearContractEndXTables")
  DoCmd.OpenQuery ("ClearExpiredXTables")
  DoCmd.OpenQuery ("CleartblContractsTemp")
    
  DoCmd.SetWarnings True

Exit_cmdAddToOutlook_Click:
    Exit Sub

Err_cmdAddToOutlook_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddToOutlook_Click

End Sub

[Kosmos]

oh my...you've gone mad lol Thanks...I think you did a looot more than you needed to. I know I use more code than I need to but I think it's good practice to keep all my options in mind since I am new to this. I will let you know if it works in a little bit.

[Kosmos]

stSQL = "SELECT * FROM AddedToOutlook WHERE DatabaseReferen ceNumber = " _
& rsContTemp("Dat abaseReferenceN umber2")

rsOutlook.Open stSQL, con, adOpenKeyset, adLockOptimisti c
If rsOutlook.EOF Then
rsOutlook.AddNe w
Dim outobj As Outlook.Applica tion
Dim outappt As Outlook.Appoint mentItem
Set outobj = CreateObject("o utlook.applicat ion")
Set outappt = outobj.CreateIt em(olAppointmen tItem)
With outappt
.Start = rsContTemp.Fiel ds("Notificatio nDate2") _
& " " & rsContTemp.Fiel ds("ApptTime2" )
.Duration = 15
.Subject = "Contract Notification/End" & " " _
& rsContTemp.Fiel ds("DatabaseRef erenceNumber2") _
& " " & rsContTemp.Fiel ds("Vendor2")
.Body = "Contract Notification/End" & " " _
& rsContTemp.Fiel ds("DatabaseRef erenceNumber2") _
& " " & rsContTemp.Fiel ds("Vendor2")
.ReminderMinute sBeforeStart = rsContTemp.Fiel ds("ReminderMin utes2")
.ReminderSet = True
.Save
End With
rsOutlook.Field s("AddedToOutlo ok") = True
rsOutlook.Field s("DatabaseRefe renceNumber") = rsContTemp.Fiel ds("DatabaseRef erenceNumber2")
Set outobj = Nothing
'DoCmd.RunComma nd acCmdSaveRecord
rsOutlook.Updat e
End If
rsOutlook.Close

Hey, I'm just trying to figure out what you are doing by opening rsOutlook as stSQL ...I don't see how it knows that rsOutlook is the table "AddedToOutlook " now if you open rsOutlook as stSQL - sorry...again, I'm relatively new so please forgive me :)

[willakawill]

Hey, I'm just trying to figure out what you are doing by opening rsOutlook as stSQL ...I don't see how it knows that rsOutlook is the table "AddedToOutlook " now if you open rsOutlook as stSQL - sorry...again, I'm relatively new so please forgive me :)

"AddedToOutlook " is a string and so is stSQL

stSQL = "SELECT * FROM AddedToOutlook WHERE DatabaseReferenceNumber = " _
& rsContTemp("DatabaseReferenceNumber2")

I have changed the string to return no records if there is no match

[Kosmos]

yes but I guess what I'm asking is where do you add the contents of that string to AddedToOutlook? because it says rsOutlook.field s("etc..") = etc... but how does that add to AddedToOutlook if you open the connection as stSQL? Is there something I missed?

[willakawill]

yes but I guess what I'm asking is where do you add the contents of that string to AddedToOutlook? because it says rsOutlook.field s("etc..") = etc... but how does that add to AddedToOutlook if you open the connection as stSQL?

You are always using an SQL statement. When you use only the table name, "AddedToOutlook ", it is an abbreviation for "SELECT * FROM AddedToOutlook" . ADO works this out for you. I have added a WHERE clause to this statement so that we can test for an existing record.

The part of the sql statement, "SELECT *" means that we have all of the fields in the recordset so we can add a new record to this recordset using all or some of the fields. When you ask the recordset to update, it will update the AddedToOutlook table because that is the only table that we have queried here.

[Kosmos]

You are always using an SQL statement. When you use only the table name, "AddedToOutlook ", it is an abbreviation for "SELECT * FROM AddedToOutlook" . ADO works this out for you. I have added a WHERE clause to this statement so that we can test for an existing record.

The part of the sql statement, "SELECT *" means that we have all of the fields in the recordset so we can add a new record to this recordset using all or some of the fields. When you ask the recordset to update, it will update the AddedToOutlook table because that is the only table that we have queried here.

Ahh...glorious. ..I understand now. Thank you.