Table name and RecordSource

[Jenny Kurniawan]

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 .....

[Raoul Watson]

"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 :)