swapping recordsets on a form

I am trying to change the recordset that my form uses, but when I
requery my form, all of the textboxes display #Name?

Here is what I am trying to do:
The user enters a search term in a textbox clicks a button. The
form's recordset is then searched and when a matching record is found
it is added into a second recordset.
Then I set the form's recordset to the second recordset and requery,
with the intention that the form will now display the records that were
determined to have mathed the search term.


This is how I have done it:

Dim myTerm As String
myTerm = Me.Search_Box

Dim myRecordCount As Long

Dim rs1 As DAO.Recordset
Set rs1 = CurrentDb.OpenRecordset("Select * from Case_List")
If rs1.EOF = False Then
rs1.MoveLast
End If

myRecordCount = rs1.recordCount
rs1.MoveFirst

Dim rs2 As ADODB.Recordset
Set rs2 = New ADODB.Recordset
'now create our relevant fields
rs2.Fields.Append "Case_ID", adInteger
rs2.Fields.Append "Case_or_Issue", adVarChar, 50
rs2.Fields.Append "Case_Number", adInteger
rs2.Fields.Append "Client", adInteger

If Me.Frame_1 = 1 Then 'then we are searching on Party Name
'now loop through rs1 and see if the instr() function gets a
match, if so, add to rs2
rs2.Open
For i = 0 To myRecordCount - 1
If InStr(rs1!Case_or_Issue, myTerm) > 0 Then 'we have a
match
rs2.AddNew Array("Case_ID", "Case_or_Issue",
"Case_Number", "Client"), Array(rs1!Case_ID, rs1!Case_or_Issue,
rs1!Case_Number, rs1!Client)
rs2.Update
End If

If rs1.EOF = False Then
rs1.MoveNext
End If

Next 'next record


ElseIf Me.Frame_1 = 2 Then 'then we are searching on case
number


ElseIf Me.Frame_1 = 3 Then 'then we are searching on case ID

End If

Set Me.Recordset = rs2
Me.Requery

rs2.Close
rs1.Close



rs1 has all of the same field names as rs2, but rs2 does not have all
of the same field names as rs1. Is that where my problem is? Can
someone tell me how to fix this so that the rs2 records display on my
form?

Thanks,

Jody Blau

re: swapping recordsets on a form

"jodyblau" <jodyblau@gmail.com> wrote in message
news:1140070807.730577.91480@g47g2000cwa.googlegro ups.com...[color=blue]
>I am trying to change the recordset that my form uses, but when I
> requery my form, all of the textboxes display #Name?
>
> Here is what I am trying to do:
> The user enters a search term in a textbox clicks a button. The
> form's recordset is then searched and when a matching record is found
> it is added into a second recordset.
> Then I set the form's recordset to the second recordset and requery,
> with the intention that the form will now display the records that were
> determined to have mathed the search term.
>
>
> This is how I have done it:
>
> Dim myTerm As String
> myTerm = Me.Search_Box
>
> Dim myRecordCount As Long
>
> Dim rs1 As DAO.Recordset
> Set rs1 = CurrentDb.OpenRecordset("Select * from Case_List")
> If rs1.EOF = False Then
> rs1.MoveLast
> End If
>
> myRecordCount = rs1.recordCount
> rs1.MoveFirst
>
> Dim rs2 As ADODB.Recordset
> Set rs2 = New ADODB.Recordset
> 'now create our relevant fields
> rs2.Fields.Append "Case_ID", adInteger
> rs2.Fields.Append "Case_or_Issue", adVarChar, 50
> rs2.Fields.Append "Case_Number", adInteger
> rs2.Fields.Append "Client", adInteger
>
> If Me.Frame_1 = 1 Then 'then we are searching on Party Name
> 'now loop through rs1 and see if the instr() function gets a
> match, if so, add to rs2
> rs2.Open
> For i = 0 To myRecordCount - 1
> If InStr(rs1!Case_or_Issue, myTerm) > 0 Then 'we have a
> match
> rs2.AddNew Array("Case_ID", "Case_or_Issue",
> "Case_Number", "Client"), Array(rs1!Case_ID, rs1!Case_or_Issue,
> rs1!Case_Number, rs1!Client)
> rs2.Update
> End If
>
> If rs1.EOF = False Then
> rs1.MoveNext
> End If
>
> Next 'next record
>
>
> ElseIf Me.Frame_1 = 2 Then 'then we are searching on case
> number
>
>
> ElseIf Me.Frame_1 = 3 Then 'then we are searching on case ID
>
> End If
>
> Set Me.Recordset = rs2
> Me.Requery
>
> rs2.Close
> rs1.Close
>
>
>
> rs1 has all of the same field names as rs2, but rs2 does not have all
> of the same field names as rs1. Is that where my problem is? Can
> someone tell me how to fix this so that the rs2 records display on my
> form?
>
> Thanks,
>
> Jody Blau[/color]




Wow!
Where did that idea come from? Surely not from any standard Access
textbook? From a suggestion here? Or did you just let your imagination run
wild? You are mixing DAO and ADO recordsets, building up a 'virtual'
recordset which is not bound to any of your tables.
A more standard approach would be simply to update the form's recordsource
(not recordset). So this means you build up the SQL and assign it to the
recordsource:
strSQL="Select * from Case_List WHERE Case_or_Issue Like ""*" & strSearch &
"*"""
Me.RecordSource=strSQL
This assumes Case_or_Issue Like is a text field which won't contain quotes.
If it might, then you need to add a bit more code to double them up.

re: swapping recordsets on a form

Thanks for the help,

I admit I am using a mix of examples and then spining them around in
my imagination.
I'm actually a lawyer by profession, rather than a programmer, so I
am learning this stuff for the first time, and on-the-fly.
So, I appreciate your help and I will definately incorporate your
suggestion!


Thanks,

Jody Blau