How to use rst.FindFirst with rst.NoMatch?

dao, ms-access, vba

Solution

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

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

Problem

My code works except for this one line ``` .FindFirst "[DONOR_CONTACT_ID] = strTemp2" ``` 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. 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!! Here is my code: ``` 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 ```

Original source