Union query and The search key was not found in any record. (Error 3709)

[lina2401]

I'm facing a problem and I really appreciate eny help with this.
In my continuous form I have this query in the record source

SELECT DISTINCT TBL_Institutions.* FROM TBL_Institutions INNER JOIN Tbl_FieldInstitJctTbl ON TBL_Institutions.InstitCode = Tbl_FieldInstitJctTbl.InstitCode WHERE M<>"" AND MajorCode='NUTR' AND ELI_CoopClass='ELS' AND M<>"H" AND G_CLA=True AND Country="USA" AND TBL_Institutions.City in ('Boone','Thousand Oaks','Clemson','Cleveland','Chico','Washington','Teaneck','Melbourne','Erie','Terre Haute','Harrisonburg','Ruston','Milwaukee','West Long Branch','Murfreesboro','Billings','Bozeman','Las Cruces','Socorro','Dekalb','Oklahoma City','Rapid City','Edwardsville','Philadelphia','Marietta','Cincinnati','Colorado Spring','Denver','Dayton','Houston','North Dartmouth','St Louis','Missoula','Grand Forks','West Haven','St Paul','Laramie','Wichita') UNION SELECT DISTINCT TBL_Institutions.* FROM TBL_Institutions INNER JOIN Tbl_FieldInstitJctTbl ON TBL_Institutions.InstitCode = Tbl_FieldInstitJctTbl.InstitCode WHERE M<>"" AND MajorCode="CSCI" AND ELI_CoopClass="ELS" AND M<>"H" AND G_CLA=True
AND Country="USA" AND TBL_Institutions.City in ('Chico','Cleveland','Pocatello','Terre Haute','Ruston','Dekalb','Cincinnati','West Haven','Rock Hill')

The form is working fine and I'm able to view the results. The issue is that I need to store the output (query values) in another table so I added a button that will go thru all the records in the record source

Private Sub Command2_Click()
Dim db As Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Debug.Print Me.RecordSource
Set rs = db.OpenRecordset(Me.RecordSource, dbOpenDynaset)
If rs.EOF Then
Else
Do While Not (rs.EOF)
Debug.Print "Instit= " & rs("InstitCode")
rs.MoveNext
Loop
End If
rs.Close
db.Close
End Sub

Whenever I run the above code I got the following error in this line
Debug.Print "Instit= " & rs("InstitCode")
The search key was not found in any record. (Error 3709)

So can anybody explain the cause of such an error.

Thanks in advance.
Lina

 

[jdraw]

I don't see anything obvious in your code. But it may have an issue trying to determine which "InstitCode'" you are referring to.

Also in this section of your code

If rs.EOF Then
Else
Do While Not (rs.EOF)
Debug.Print "Instit= " & rs("InstitCode")
rs.MoveNext
Loop
End If

You don't need the if statement -- you could have

Do While Not (rs.EOF)
Debug.Print "Instit= " & rs("InstitCode")
rs.MoveNext
Loop

but I don't think it has anything to do with the 3709.

The 3709 seems to have a few causes based on some googling. The link below has info that may help you.

http://en.allexperts.com/q/Using-MS-Access-1440/Error-3709-Access.htm

 

[lina2401]

jdraw, thanks for your response.
Do you think that the query itself has some problems I mean since it is a union query??
You have also mentioned that determinings which "InstitCode'" might cause this problem! what do you mean by that?

Thanks again.

 

[jdraw]

I don't think this is your issue 3709.
But, there are at least 2 InstitCode's in your rs.
TBL_Institutions.InstitCode = Tbl_FieldInstitJctTbl.InstitCode

Access, and parsers generally, may want to know explicitly which one is being referenced.

 

[vbaInet]

But, there are at least 2 InstitCode's in your rs.
TBL_Institutions.InstitCode = Tbl_FieldInstitJctTbl.InstitCode

Access, and parsers generally, may want to know explicitly which one is being referenced.

For example:

Debug.Print "Instit= " & rs("[COLOR=Red]TBL_Institutions.[/COLOR]InstitCode")

If you create a query based on your SELECT statement, then use the query in the OpenRecordset command, you won't need to explicitly define which table it's coming from because there will only be one InstitCode.

 

[lina2401]

Thanks all for your help.
I tried this and I still got the same error!!!!!!

Debug.Print "Instit= " & rs("TBL_Institutions.InstitCode")

Any idea???

 

[vbaInet]

Let's have a quick look at your db. Don't forget to indicate where to find the code.