Use a record set as a recordsource

[RogerB]

I have created the recordset below
which I would like to make the recordsource for a form in the current database called "frmMain".

I would be very grateful if someone could give me a hint as to how to go about it.


strResult = "SELECT * FROM tblMain WHERE
(((tblMain.[AccNo])= " & "1111" & "));"

Set Rst = Dbs.OpenRecordset(strResult)

Thanks

Roger

 

[Liv Manto]

Put it on Open event

Private Sub Form_Open(Cancel As Integer)
Dim strsql As String
strsql = "SELECT Field1, Field2 where etc;"

Me.RecordSource = strsql

End Sub

 

[Jack Cowley]

I would create a query and make the query the Recordsource for the form. No code required and easy to see what you have done.

 

[The_Doc_Man]

I do this all the time in subforms. The catch is to make sure that either (a) you open the form, set its recordsource, and do a .REQUERY before setting focus in the form,
or (b) if the form is dinking with its own recordsource, that you do a me.REQUERY after you muck with your me.RECORDSOURCE

But if the query is well-formed, it should work just fine. Hint: Take extra care to get the quotes right if you generate a WHERE clause like "WHERE [X] = " and you concatenate a variable into the clause.

dim stWhere as string
dim stValue as string

stWhere = "WHERE [X] = """ & stValue & """"

But if [X] and the value are numbers, you don't need the extra quotes. Then,

stWhere = "WHERE [X] = " & stValue

is adequate.


[This message has been edited by The_Doc_Man (edited 08-22-2001).]