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 2 1827
"jodyblau" <jo******@gmail.com> wrote in message
news:11*********************@g47g2000cwa.googlegro ups.com... 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
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.
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Christopher Jeris |
last post by:
Please help me understand the differences, in semantics, browser
support and moral preferredness, between the following three methods
of swapping content in and out of a page via JavaScript. I...
|
by: James Cane |
last post by:
Here's an interesting problem that someone might have an answer to...
Some time ago, I wrote a set of utility classes which wrap up the
custom row source function needed to add arbitrary items to...
|
by: Randy Harris |
last post by:
I was inspired by the recent discussion of returning multiple recordsets to
ADO from a stored procedure. (Amazed is probably more accurate). I asked
about how to accomplish same with Oracle and...
|
by: Donald Grove |
last post by:
I want to populate an array with values from an ado recordset
(multiple rows)
I use the absolute position of the cursor in the recordset to define
the row of my array to be populated. I have a...
|
by: nachiket2k2 |
last post by:
Hi,
I am using VB 6.0, ADO 2.0 with MS Access 2000 Database.
in a vb form, i need to fatch data from 3 different tables (ABC.MDB).
it will be a tedious job if i create 3 connections with 3...
| |
by: rdemyan via AccessMonster.com |
last post by:
Can someone help me with creating code that will look for DAO recordsets in
modules and then check to see if the recordset is also closed in the module.
All of my recordsets are of the form rs*...
|
by: JonC |
last post by:
I am developong a database using acces 2003 and have got to a stage
where I have a form with two levels of nested subforms. Recordsets are
selected using a string of linked comboboxes and this is...
|
by: sphinney |
last post by:
As my Access form opens, I want it to find the names of the tables in the current Access database and populate a combobox with the table (recordset) names. Problem is, the CurrentDb.Recordsets...
|
by: convexcube |
last post by:
Hi Experts,
I have a unbound form which records details of complaints. It also records what products are affected in the complaint. These are stored in a table named ComplaintProducts that has...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |