How to change the currect recordset in a Form

[mybellame]

Hi,
How can I change the current recordset in a form to a new recordset using a query in vba?

i.e. To populate the recordset in my form I have a combo box that is used in a query to populate the recordset. The recordset is a subset of my table. In one of my sub modules I search my table for an ID # and I then want to populate the form with the record that matches the ID #. My problem is that ID # is not in current recordset. How can I change the recordset so that the contents of the entire table is in it?

Thanks

[debasisdas]

try to frame the recordset dynamically by passing / framing query at runtime .

[AV Manoharan]

Hi, How can I change the current recordset in a form to a new recordset using a query in vba? i.e. To populate the recordset in my form I have a combo box that is used in a query to populate the recordset. The recordset is a subset of my table. In one of my sub modules I search my table for an ID # and I then want to populate the form with the record that matches the ID #. My problem is that ID # is not in current recordset. How can I change the recordset so that the contents of the entire table is in it? Thanks

You may or may not declare another recordset to include ID #. You can open any number of recordset either in one table or joining multiple tables. Actually at declaration time it doesn't have any role to play exept binding it with your connection object. Even you can use the same recordset (variable name) any no.of times as long as the connection object remains same. Only thing, for each time you open it, you have to make sure that it has been preceeded by a close.
eg.

rs.open "seclect * from table2", con,....

do all those things with you rs.fields....

rs.close

rs.open "select table1.IDNO,table2.* from table1,table2",con,...etc.

do all those things with your new rs.fields which contains the ID# field of the table1 and all fields of the table2.

If you want any more clarification please let me know.
I do not know presently, what type connection you have used in your program.

AV Manoharan

[mybellame]

Thanks, I understand your explanantion, though I don't think that's what I'm trying to do (please bear with me I'm new at this and everything I know so far has been self taught!). Here's a sample of my code:

Dim rs As New ADODB.Recordset
Dim SQLStmt As String

' value has been populated in unshown code

SQLStmt = "SELECT ID, active, item FROM tbl WHERE item = '" & value & "';"
rs.Open = SQLStmt, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

If active = "YES" Then
Me.RecordsetClone.FindFirst "ID = " & rs!ID
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
rs.Close
Set rs = Nothing

If I'm understanding this correctly :) RecordsetClone has been populated by a combo box that the user selected, it contains a subset of tbl. The SELECT then creates rs.Recordset that contains a (possible) different subset of tbl. My problem is that I want to change the record that is displayed on the form to the newly found record. Nothing is happening on the form when the code is executed. I think it is because rs!ID is not a subset of RecordsetClone.

More help would be greatly appreciated! :)

[AV Manoharan]

RecordsetClone has been populated by a combo box that the user selected, it contains a subset of tbl. The SELECT then creates rs.Recordset that contains a (possible) different subset of tbl. My problem is that I want to change the record that is displayed on the form to the newly found record. Nothing is happening on the form when the code is executed. I think it is because rs!ID is not a subset of RecordsetClone.

Please pay attention to the underlined and this one:

Me.Bookmark = Me.RecordsetClone.Bookmark

there are two things.
one is a form refresh with the new record that may not have happened.
Second, the Bookmark property is not the record's position in the recordset. It only reflracts the relative order of records in the recordset. so the clone recordset's bookmark may be some times same, other times different - depends upon your two SELECT statements.

Again pay attention. you are assigning the clone's bookmark to your earlier bookmark by the above statment. so which recordset will be bookmarked? the earlier SELECT or the newly SELECT?

I think you understood and will solve yourself the problem. If not contact me again.

[mybellame]

Thank you for your input. I have solved my problem. On to the next problem!
Thanks