Search and open match report

[zionman4]

Hello everyone,

I currently have a form that has demographic info like ID, NAME and a search criteria like TASK_ID and LOCATION_ID.

I created a button that would open a matching report based on other participants information matching that on the form TASK_ID and LOCATION_ID. When compiling the code I get a member not found under DO/LOOP myrs.Participant_ID. Can anyone help me debug the following code:

Thanks!!!

Private Sub btnMatch_Click()
On Error Resume Next
Dim mydb As Database, myrs As Recordset
Set mydb = CurrentDb

idlist = ""
Loc_needed = Me![Location].Value
Task_needed = Me![TaskService].Value

If Loc_needed = "" Or IsNull(Loc_needed) Then
Set myrs = mydb.OpenRecordset("qselMatch", dbOpenDynaset)
criteria = "[Participant_ID] Is Not Null"
Else
Set myrs = mydb.OpenRecordset("qselMatch", dbOpenDynaset)
criteria = "[GAvail_Location_ID] = '" & Loc_needed & "'"
End If

myrs.FindFirst criteria
If myrs.NoMatch Then
MsgBox ("No Participants meet your location critera&quot
Exit Sub
End If

Do Until myrs.NoMatch
idlist = idlist & "'" & myrs.Participant_ID & "',"
myrs.FindNext criteria
Loop

Set myrs = mydb.OpenRecordset("qselMatch", DB_OPEN_DYNASET)

If Task_needed <> &quot;&quot; Then
criteria = &quot;[SAvail_Task_ID] = '&quot; & Task_needed & &quot;' and [Participant_ID] in (&quot; & idlist & &quot&quot;
myrs.FindFirst criteria
If myrs.EOF Then
MsgBox (&quot;No Participants meet your Task critera&quot
Exit Sub
End If
idlist = &quot;&quot;
Do Until myrs.NoMatch
idlist = idlist & &quot;'&quot; & myrs.Participant_ID & &quot;',&quot;
myrs.FindNext criteria
Loop

End If

doreport:
mysql = &quot;[Participant_ID] in (&quot; & idlist & &quot&quot;
DoCmd.OpenReport &quot;rptPotentialMatch&quot;, A_PREVIEW, , mysql

End Sub

 

[JoanneM]

Hello
you may have declared and defined your recordset but the myrs.participant_id or myrs!participant_id is how you refer to the field so you are referring to a member of an undeclared recordset by omitting the MYRS - so it can't find it
You may have the same problem with the else statement as well.

Additionally I believe the syntax for your 'not is null may cause you grief down the line

but let's get the member prob resolved first

regards

Jo

 

[zionman4]

Hi Jo,
Is there a way to simplify this code by using a sql recordset and getting the Loc_Needed and Task_Needed and then matching them using Do/Loop to find the match instead of going at individually?

Thanks!