| 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.
| 
