我的代码除了这一行以外都能正常工作
.FindFirst "[DONOR_CONTACT_ID] = strTemp2"
我希望我的代码能够检查是否存在一条记录,其中包含特定的DONOR_CONTACT_ID,因为有多条记录具有相同的DONOR_CONTACT_ID。如果该记录不存在,则我想将该DONOR_CONTACT_ID和RECIPIENT_CONTACT_ID添加到RECIPIENT_1中。如果该记录存在,则我想将RECIPIENT_CONTACT_ID添加到该特定DONOR_CONTACT_ID的RECIPIENT_2中。为此,我使用了.FindFirst来查看是否有记录,然后使用了.NoMatch。如果没有匹配项,我想添加一个新记录,但如果有匹配项,则要检查它是否必须放在RECIPIENT_2中。
我遇到的错误是“不认识'strTemp2'作为有效的字段名或表达式”。我想查看记录是否等于strTemp2,但我认为我的语法有误。感谢任何帮助!!
以下是我的代码:
Option Compare Database
Option Explicit
Function UsingTemps()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim rstOutput As DAO.Recordset
'Defines DAO objects
Dim strTemp1 As String
Dim strTemp2 As String
Dim strVal As String
Dim strRecip As String
DoCmd.SetWarnings False
DoCmd.OpenQuery ("Q_RECIPIENT_SORT")
DoCmd.OpenQuery ("Q_DELETE_T_OUTPUT")
DoCmd.SetWarnings True
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("T_RECIPIENT_SORT", dbOpenDynaset)
'rst refers to the table T_RECIPIENT_SORT
Set rstOutput = dbs.OpenRecordset("T_OUTPUT", dbOpenDynaset)
'rstOutput refers to the table T_OUTPUT
rst.MoveFirst
'first record
strTemp1 = rst!DONOR_CONTACT_ID
'sets strTemp1 to the first record of the DONOR_CONTACT_ID
rst.MoveNext
'moves to the next record
Do While Not rst.EOF
'Loop while it's not the end of the file
strTemp2 = rst!DONOR_CONTACT_ID
'strTemp2 = DONOR_CONTACT_ID from T_RECIPIENT_SORT
If strTemp1 = strTemp2 Then
'Runs if temps have same DONOR_CONTACT ID
strRecip = rst!RECIPIENT_CONTACT_ID
'Sets strRecip = RECIPIENT_CONTACT_ID FROM T_RECIPIENT_SORT
With rstOutput
'Uses T_OUTPUT table
If .RecordCount > 0 Then
'If table has records then you can check
.FindFirst "[DONOR_CONTACT_ID] = strTemp2"
If .NoMatch Then
.AddNew
!DONOR_CONTACT_ID = strTemp1
!RECIPIENT_1 = strRecip
.Update
Else
If !DONOR_CONTACT_ID = strTemp2 Then
If IsNull(!RECIPIENT_2) And Not (IsNull(!RECIPIENT_1)) Then
.Edit
!RECIPIENT_2 = strRecip
.Update
End If
.AddNew
!DONOR_CONTACT_ID = strTemp2
!RECIPIENT_1 = strRecip
.Update
End If
End If
Else
.AddNew
!DONOR_CONTACT_ID = strTemp2
!RECIPIENT_1 = strRecip
.Update
End If
End With
End If
strTemp1 = strTemp2
rst.MoveNext
Loop
Set dbs = Nothing
End Function