"Jenny Kurniawan" <jk********@maxxiscanada.com> wrote in message
news:Qe*****************@news20.bellglobal.com...
I have a procedure that goes like this:
Private Sub Form_Current()
Dim strSQL1 As String
strSQL1 = "Select Price_Year_Name from Price_Year where Category_ID =
1 And By_Default <> 0"
' I know that the result of the above query is 'Price_2003'
' I want to use the result of this query in RECORDSource
property. ' How do I accomplish this?
Me.RecordSource = "Price_2003"
' I wish I could just handcode "Price_2003" to my RecordSource but
I couldn't ....
' RecordSource will change according to the table, in fact it was
determined by By_Default field
' in the table.
Please help .....
I may not be understanding you completely, but this is what I would do to
change the "record set" assuming you have a data control "data1"
' I would save the current record set
Dim varBookmark As Variant
Dim RecSrcSave As String
Dim strSQL1 As String
'-------------------------------
' first save our position in db
'-------------------------------
varBookmark = Data1.Recordset.Bookmark
' save the record source
RecSrcSave = Data1.RecordSource
strSQL1 = "Select Price_Year_Name from Price_Year where Category_ID = 1 And
By_Default <> 0"
' If needed you can change the db file
' Data1.DatabaseName = gFName
Data1.RecordSource = strSQL1 'set the record source to this query
Data1.Refresh 'execute it
' populate it
' if zero then no matching record
If (Data1.Recordset.BOF = True And Data1.Recordset.EOF = True) Then
MsgBox ("No records meet the criteria.")
End If
' else total records matching your query
' will be in Data1.Recordset.RecordCount
' On the way out restore bookmark
' Now restore things
' restore the record source
Data1.RecordSource = RecSrcSave
Data1.Refresh
' populate it
Data1.Recordset.MoveLast
Data1.Recordset.MoveFirst 'ensure populated
Data1.Recordset.Bookmark = varBookmark
Again, not sure if that's what you looking for. By the way, I knew a girl a
long time ago in Indonesia with the same name. E-mail me if you're from
there :)