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]

Re: Table name and RecordSource


"Jenny Kurniawan" <jkurniawan@max xiscanada.com> wrote in message
news:QeVVa.1386 $j_5.6582@news2 0.bellglobal.co m...[color=blue]
> 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 =[/color]
1[color=blue]
> 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[/color]
property.[color=blue]
> ' How do I accomplish this?
>
> Me.RecordSource = "Price_2003 "
> ' I wish I could just handcode "Price_2003 " to my RecordSource but[/color]
I[color=blue]
> couldn't ....
> ' RecordSource will change according to the table, in fact it was
> determined by By_Default field
> ' in the table.
>
> Please help .....
>
>[/color]

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

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.DatabaseN ame = gFName

Data1.RecordSou rce = strSQL1 'set the record source to this query
Data1.Refresh 'execute it
' populate it
' if zero then no matching record
If (Data1.Recordse t.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.RecordSou rce = 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 :)