View recordset on form

[gopher_by_fende]

Hi,
i'm pretty new to VBA so you'll have to bare with me. i'm currently making a search form for a new database, and ive got as far as getting the searching part done. what i can't figure out is how to get the results from the recordset in VBA onto a subform on the main search form.

the code for the search is -

Private Function cmdSearchSQL() As Variant


Dim strSearchtype As String
Dim strSearch As String
Dim strSQL As String
Dim db As Database
Dim rs As Recordset


strSearchtype = Form_frmJobIndex!txtSearchtype
strSearch = Form_frmJobIndex!txtSearch
strSQL = "SELECT * FROM jobs_query WHERE '" & strSearchtype & "'= '" & strSearch & "';"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)


Set db = Nothing
Set rs = Nothing

End Function

Obviously im missing something, buti dont know what. also, the code above is probably less than perfect, but as i said im new to VBA and im learning the code as i go!!

Thanks for any help

Phil

 

[gopher_by_fende]

Anybody?? this is driving me mad, ive tried all my books, forums, searching google, picked apart northwind, i just dont know

 

[checoturco]

before you set rs= nothing you have to get tha values...

Set rs = db.OpenRecordset("SELECT * FROM jobs_query WHERE '" & strSearchtype & "'= '" & strSearch & "';")
if rs.BOF and rs.EOF
' Nothing
else
rs.MoveFirst
Do Until rs.EOF
rs(<first_field>)----->gives you the first field of the table jobs_query
rs(<fsecond_field>)----->gives you the second field of the table jobs_query
.....
.....
rs.MoveNext
Loop
end if
rs.close
Set rs = Nothing

There is another way, you can put the daa into a temp table:

docmd.runsql " SELECT * into <temp_table> FROM jobs_query WHERE '" & strSearchtype & "'= '" & strSearch & "';

and put the source data of your subform to this table

checo