How to remain original RecordSource

[ck.letterbox]

Hi Guys,
I'm another newby to MS Access.
Here is my story:
I have Form1 with sort button.
After clicking sort button, Form1 will send its RecordSource to Form2.
Form2 provides sorting criteria and returns the SQL statement.
Code below is in sort button in Form1:

Private Sub btnSort_Click()

Dim strSQL_SortSequence As String

'this gfnSort will call to Form2 and return SQL string
strSQL_SortSequence = gfnSort(Me!sbfData.Form.RecordSource)

Me!sbfData.Form.RecordSource = strSQL_SortSequence

End Sub

The gfnSort works fine. It returns SQL string I expect.
The problem is the RecordSource will change to the returned SQL string.
This means the second time I click sort button, program will not work correctly since RecordSource was changed.

Do you guys have any idea?
Any suggestion'd help me a lot and thank you for any reply.

 

[unclejoe]

Hi,

You didn't say much about why on changing the recordsource. It appears that you are only sorting the ascending or descending order.

You should use a combo box to sort the order instead.

Here's one form Allen Browne. Sort by combo

 

[ck.letterbox]

Hi Unclejoe,
Your idea is right. Thank you for the link.
But now I'm working on the existing project and just join the team.
Form2 that I refered is a global form which can be called from many modules/forms.
Form2 will manage to return SQL string, "SELECT column1, 2,... FROM RecordSource ORDER BY ...".
After running
Me!sbfData.Form.RecordSource = strSQL_SortSequence
the RecordSource changes to "SELECT ...".
Now, I can't do the sorting again unless I close Form1.

Any idea?

 

[ck.letterbox]

ps. The link you provided, I'm not sure if it's about sorting or searching?

 

[unclejoe]

Hi,

It's a search form, but you'll get the idea.

ps. The link you provided, I'm not sure if it's about sorting or searching?

You might want to start something like...

Select ...From Table Where...Order By Forms!YourFormName!SearchCombo

In the combo Row Source Type to "Value List" and in Row Source key in "Column1;Column2..." where column1 is the field of the sorting order.

From your code...
Me!sbfData.Form.RecordSource = strSQL_SortSequence

If string value is strSQL_SortSequence = "Select col1...n From Table"
Then you concatenate the string to something like....

Me!sbfData.Form.RecordSource = strSQL_SortSequence & " Order By " & Me.ComboName

or

Me!sbfData.Form.RecordSource = strSQL_SortSequence & " Order By " & Forms!YourFormName!ComboBoxName.

You get something like "Select col1... From Table Order By Col1"

Also set the combo default value when the form is open so that the form's recordsource has a value for the sort order.

 

[ck.letterbox]

Thanks so much Unclejoe!
I'll try that. Back to my question.
Are there any handy way to just bring back the original RecordSource?

If there's, please let me know.
Thank you very much.

 

[unclejoe]

Hi,

If you use named queries, then = "YourQueryName".

Another is on the form just below the option declaration..

Option Compare Database
Option Explicit

Dim YourSQLString As String ' or just put this into the open event

Somewhere in your open event of your form...

YourSQLString = Me.RecordSource or Forms!YourFormName!RecordSource

Then use "YourSQLString" as the original record source string.

Me.Recordsource = YourSQLString

Thanks so much Unclejoe!
I'll try that. Back to my question.
Are there any handy way to just bring back the original RecordSource?

If there's, please let me know.
Thank you very much.