如何将rst.FindFirst与rst.NoMatch结合使用? [英] How to use rst.FindFirst with rst.NoMatch?

问题描述

我的代码仅适用于这一行

My code works except for this one line

.FindFirst "[DONOR_CONTACT_ID] = strTemp2"

我希望我的代码检查是否有记录，因为存在多个具有相同DONOR_CONTACT_ID的记录，所以存在特定的DONOR_CONTACT_ID.如果该记录不存在，那么我想将该DONOR_CONTACT_ID和RECIPIENT_CONTACT_ID添加到RECIPIENT_1.如果该记录确实存在，我想为该特定的DONOR_CONTACT_ID将RECIPIENT_CONTACT_ID添加到RECIPIENT_2.为此，我使用.FindFirst查看是否有记录，然后使用.NoMatch.如果没有匹配项，我想添加一条新记录，但是如果有匹配项，我想检查一下是否必须将其放入RECIPIENT_2中.

I want my code to check if there is a record, where a specific DONOR_CONTACT_ID exists becaue there are multiple records with the same DONOR_CONTACT_ID. If that record does not exist, then I want to add that DONOR_CONTACT_ID and RECIPIENT_CONTACT_ID to RECIPIENT_1. If that record does exist, I want to add the RECIPIENT_CONTACT_ID to RECIPIENT_2 for that specific DONOR_CONTACT_ID. To do this, I used .FindFirst, to see if there was a record, then used .NoMatch. If there isn't a match, I want to add a new record, but if there is then I want to check to see if it has to go in RECIPIENT_2.

我得到的错误是无法将'strTemp2'识别为有效的字段名称或表达式".我想查看记录是否等于strTemp2，但是我认为我的语法是错误的.谢谢您的帮助！

The error I get is "does not recognize 'strTemp2' as a valid field name or expression". I want to see if the record is equal to strTemp2, but I think my syntax is wrong. Thanks for any help!!

这是我的代码:

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

推荐答案

将变量的 value (而不是变量 name )构建为您提供的字符串FindFirst查找.

Build the value of the variable, instead of the variable name, into the string you give FindFirst to find.

假设DONOR_CONTACT_ID是文本数据类型，还应在变量的值两边加上引号...

Assuming DONOR_CONTACT_ID is text data type, also include quotes around the variable's value ...

.FindFirst "[DONOR_CONTACT_ID] = '" & strTemp2 & "'"

但是如果是数字，则不需要那些引号...

But if it's numeric, you don't need those quotes ...

.FindFirst "[DONOR_CONTACT_ID] = " & strTemp2

这篇关于如何将rst.FindFirst与rst.NoMatch结合使用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！