使用DLookup [英] Using DLookup

问题描述

嘿伙计们......我以前从未使用过DLookup ......

我只是想知道如何使用它。我必须检查一个字段，看看之前是否输入过。该字段位于recSet6.Fields（DatabaseReferenceNumber）中，我必须检查它是否与特定的recSet1.Fields（DatabaseReferenceNumber）有任何匹配，它通过Do Until recSet1.EOF有点循环计算（从1到字段的结尾，但由于循环内有多个IF语句，我正在检查的recSet1.Fields（DatabaseReferenceNumber）并不总是一个接一个。对此有任何帮助将非常感激。

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("DatabaseReferenceNumber") which I have to check to see if it has any matches to a specific recSet1.Fields("DatabaseReferenceNumber") 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("DatabaseReferenceNumber") 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.

推荐答案

实际上我甚至不确定我是否正确使用DLookup功能，我只需要一个函数将返回true / false值，具体取决于是否在该字段中找到其他值。

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.

实际上我是''我甚至不确定我是否正确使用DLookup函数，我只需要一个函数，它将返回一个真/假值，具体取决于是否在该字段中找到另一个值。

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 :)

我希望你并不是说你认为我不会发布代码...你可以看一下在我以前关于那篇文章的一些帖子中...但是假设你并不是那个意思......确定这里有很多评论的整个功能，但我把评论放在了我在CAPITALS中特别关注的地方： ）



[PHP] Private Sub cmdAddToOutlook_Click（）

On Error GoTo Err_cmdAddToOutlook_Click


DoCmd.SetWarnings False

DoCmd.OpenQuery（" CleartblContractsTemp"）

Dim stDocName As String


stDocName =" FindVendorContracts"

DoCmd.OpenQuery stDocName，acNormal，acEdit




DoCmd。 OpenQuery（" ClearNotificationXTable"）

DoCmd.OpenQuery（" ClearNotEndedPastRenewalXTable"）

DoCmd.OpenQuer y（" ClearContractEndXTables"）

DoCmd.OpenQuery（" ClearExpiredXTables"）

''打开ADODB连接和记录集

Dim con1 As ADODB.Connection

Dim con2 As ADODB.Connection

Dim con3 As ADODB.Connection

Dim con4 As ADODB.Connection

Dim con5 As ADODB.Connection

Dim con6 As ADODB.Connection

Dim con7 As ADODB.Connection

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

设置con2 = CurrentProject.Connection

设置con3 = CurrentProject.Connection

设置con4 = CurrentProject。连接

设置con5 = CurrentProject.Connection

设置con6 = CurrentProject.Connection

设置con7 = CurrentProject.Connection

设置recSet1 =新ADODB.Recordset

设置recSet2 =新ADODB.Recordset

设置recSet3 =新ADODB.Recordset

设置recSet4 =新ADODB.Recordset

设置recSet5 =新ADODB.Recordset

设置recSet6 =新ADODB.Recordset

设置recSet7 =新ADODB.Recordset


''设置实际表的记录和连接（并允许编辑临时表的功能，以及检查约会是否已发送到Outlook但不是合同表的表） />
recSet1.Open" tblContractsTemp"，con1 ,, adLockOptimistic

recSet2.Open" NotEndedPastRenewalX"，con2，adOpenKeyset，adLockOptimistic

recSet3.Open NotificationX，con3，adOpenK eyset，adLockOptimistic

recSet4.Open" ContractEndX"，con4，adOpenKeyset，adLockOptimistic

recSet5.Open" ExpiredX"，con5，adOpenKeyset，adLockOptimistic

recSet6.Open" AddedToOutlook"，con6，adOpenKeyset，adLockOptimistic

recSet7.Open" tblContracts"，con7，adOpenKeyset，adLockReadOnly


Dim X As Long

Dim Y As Long

Y = 0

''在表单的上下文中，这将打开，询问用户合约结束或通知或不续约（或续订）前的通知天数

X = InputBox（输入天数：）


''声明直到完成，直到完成的天数和UntilCompletion2将是直到需要通知的天数

Dim UntilCompletion As Long

Dim UntilCompletion2 As long


''循环播放直到EOF（直到TblContracts中EndDate的最后一条记录......所以

''其他人会声明recSet1.Open

''" tblWhateverYourTableNameIs"，con1表示

''connection1然后打开你输入的记录集中的字段

''recSet1.Fields（" fieldname"））

>
recSet1.MoveFirst

Do until recSet1.EOF

''结束日期必须在引号中或不起作用

UntilCompletion = DateDiff（" d"，Date，recSet1.Fields（" EndDate2"））

recSet1.Fields（" NotificationDate2"）=（recSet1.Fields（" EndDate2"） - recSet1 .Fields（" RequiredNotificationInDays2"））

UntilCompletion2 = DateDiff（" d"，Date，recSet1.Fields（" NotificationDate2"））



''用于调试目的...立即检查窗口

Debug.Print X

Debug.Print UntilCompletion

Debug.Print UntilCompletion2


如果UntilCompletion2> = 0且UntilCompletion2< = X那么

''必须在更新字段之前和之后说rs.AddNew和rs.Update

''捕获需要在接下来的x天内通知的合同

recSet3.AddNew

recSet3.Fields（" UntilNotification"）= UntilCompletion2

recSet3.Fields（" Vendor"）= recSet1.Fields（" Vendor2"）

recSet3.Fields（" NotificationAddress"）= recSet1.Fields（" NotificationAddress2"）

recSet3.Fields（" RequiredNotificationInDays"）= recSet1.Fields（" ; RequiredNotificationInDays2"）

recSet3.Fields（" DateofContract"）= recSet1.Fields（" DateofContract2"）

recSet3。字段（NotificationDate）= recSet1.Fields（" NotificationDate2"）

recSet3.Fields（" TermofContract"）= recSet1.Fields（" TermofContract2"）

recSet3.Fields（" EndDate"）= recSet1.Fields（" EndDate2"）

recSet3.Fields（" PaymentTerms / LateFees"）= recSet1.Fields（" PaymentTerms / LateFees2" ;）

recSet3.Fields（" AutomaticRenewal"）= recSet1.Fields（" AutomaticRenewal2"）

recSet3.Fields（" EarlyOutClause"）= recSet1.Fields （" EarlyOutClause2"）

recSet3.Fields（" OwnerName"）= recSet1.Fields（" OwnerName2"）

recSet3.Fields（" City"） = recSet1.Fields（" City2"）

recSet3.Fields（" Department"）= recSet1.Fields（" Department2"）

recSet3.Fields（" ; LicensedU se"）= recSet1.Fields（" LicensedUse2"）

recSet3.Update



''这就是我想要检查的地方在这个单独的表中已经有一个值recSet6.Fields（" DatabaseReferenceNumber"）

''检查是否确定我还没有将它添加到展望中

recSet6.AddNew

Dim outobj作为Outlook.Application

Dim outappt作为Outlook.AppointmentItem

设置outobj = CreateObject（" ; outlook.application"）

设置outappt = outobj.CreateItem（olAppointmentItem）

带outappt

.Start = recSet1.Fields（" NotificationDate2"）& " " &安培; recSet1.Fields（" ApptTime2"）

.Duration = 15

.Subject =" Contract Notification / End" &安培; " " &安培; recSet1.Fields（" DatabaseReferenceNumber2"）& " " &安培; recSet1.Fields（" Vendor2"）

.Body =" Contract Notification / End" &安培; " " &安培; recSet1.Fields（" DatabaseReferenceNumber2"）& " " &安培; recSet1.Fields（" Vendor2"）

.ReminderMinutesBeforeStart = recSet1.Fields（" ReminderMinutes2"）

.ReminderSet = True

。保存

结束

recSet6.Fields（" AddedToOutlook"）= True

recSet6.Fields（" DatabaseReferenceNumber"）= recSet1。字段（DatabaseReferenceNumber2）

设置outobj = Nothing

DoCmd.RunCommand acCmdSaveRecord

recSet6.Update

recSet6.MoveNext


结束如果


[/ PHP]

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_cmdAddToOutlook_Click

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

Dim stDocName 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 con1 As ADODB.Connection
Dim con2 As ADODB.Connection
Dim con3 As ADODB.Connection
Dim con4 As ADODB.Connection
Dim con5 As ADODB.Connection
Dim con6 As ADODB.Connection
Dim con7 As ADODB.Connection
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 "tblContractsTemp", con1, , adLockOptimistic
recSet2.Open "NotEndedPastRenewalX", con2, adOpenKeyset, adLockOptimistic
recSet3.Open "NotificationX", con3, adOpenKeyset, adLockOptimistic
recSet4.Open "ContractEndX", con4, adOpenKeyset, adLockOptimistic
recSet5.Open "ExpiredX", con5, adOpenKeyset, adLockOptimistic
recSet6.Open "AddedToOutlook", con6, adOpenKeyset, adLockOptimistic
recSet7.Open "tblContracts", 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 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 recSet1.Open
''"tblWhateverYourTableNameIs", con1 which means
''connection1 and then to open a field in that recordset you type
''recSet1.Fields("fieldname"))

recSet1.MoveFirst
Do Until recSet1.EOF
'' End Date must be in quotes or will not work
UntilCompletion = DateDiff("d", Date, recSet1.Fields("EndDate2"))
recSet1.Fields("NotificationDate2") = (recSet1.Fields("EndDate2") - recSet1.Fields("RequiredNotificationInDays2"))
UntilCompletion2 = DateDiff("d", Date, recSet1.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
recSet3.AddNew
recSet3.Fields("UntilNotification") = UntilCompletion2
recSet3.Fields("Vendor") = recSet1.Fields("Vendor2")
recSet3.Fields("NotificationAddress") = recSet1.Fields("NotificationAddress2")
recSet3.Fields("RequiredNotificationInDays") = recSet1.Fields("RequiredNotificationInDays2")
recSet3.Fields("DateofContract") = recSet1.Fields("DateofContract2")
recSet3.Fields("NotificationDate") = recSet1.Fields("NotificationDate2")
recSet3.Fields("TermofContract") = recSet1.Fields("TermofContract2")
recSet3.Fields("EndDate") = recSet1.Fields("EndDate2")
recSet3.Fields("PaymentTerms/LateFees") = recSet1.Fields("PaymentTerms/LateFees2")
recSet3.Fields("AutomaticRenewal") = recSet1.Fields("AutomaticRenewal2")
recSet3.Fields("EarlyOutClause") = recSet1.Fields("EarlyOutClause2")
recSet3.Fields("OwnerName") = recSet1.Fields("OwnerName2")
recSet3.Fields("City") = recSet1.Fields("City2")
recSet3.Fields("Department") = recSet1.Fields("Department2")
recSet3.Fields("LicensedUse") = recSet1.Fields("LicensedUse2")
recSet3.Update


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

recSet6.AddNew
Dim outobj As Outlook.Application
Dim outappt As Outlook.AppointmentItem
Set outobj = CreateObject("outlook.application")
Set outappt = outobj.CreateItem(olAppointmentItem)
With outappt
.Start = recSet1.Fields("NotificationDate2") & " " & recSet1.Fields("ApptTime2")
.Duration = 15
.Subject = "Contract Notification/End" & " " & recSet1.Fields("DatabaseReferenceNumber2") & " " & recSet1.Fields("Vendor2")
.Body = "Contract Notification/End" & " " & recSet1.Fields("DatabaseReferenceNumber2") & " " & recSet1.Fields("Vendor2")
.ReminderMinutesBeforeStart = recSet1.Fields("ReminderMinutes2")
.ReminderSet = True
.Save
End With
recSet6.Fields("AddedToOutlook") = True
recSet6.Fields("DatabaseReferenceNumber") = recSet1.Fields("DatabaseReferenceNumber2")
Set outobj = Nothing
DoCmd.RunCommand acCmdSaveRecord
recSet6.Update
recSet6.MoveNext

End If


[/PHP]

这篇关于使用DLookup的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！