Full Version: Form Sorting
UtterAccess Forums > Microsoft® Access > Access Forms
asrodg
Hello there!
I have one form but I want to be able to sort it based upon a value in another table when the form opens.
Scenario:
Search options: Last Name, Parcel Num, Control Num (Each search option has a different form that prompts the user for search criteria.)
Functionality: When the user selects one of the above search options, a value unique to the selected search option is written to a temp table. If a search returns no records the application checks the temp table for the search type and automatically opens a new search form based on the search type stored in the temp table. If the search does find records, the results are display in a browse list form (same form is used for all three search types) on which the user may select/deselect records to include in the batch ouput.
Issue:
If the search finds records, the resulting browse list should be sorted in ascending order according to the search type stored in the temp table. I tried using OrderBy in combination with an If...then...else statement (code shown below) but its not working. I also tried adding Me.OrderByOn = True but it prompts me to enter a value for the first record returned. Any insight?
Private Sub Form_Open(Cancel As Integer) 'code OK ASR 20070814
Dim rs As New ADODB.Recordset
rs.Open "tbl_temp_search", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
Dim strSearchType As String
strSearchType = rs.Fields("temp_SearchType")
If strSearchType = "P" Then
Me.OrderBy = Me.Parcel_Num
If strSearchType = "C" Then
Me.OrderBy = Me.Control_Num
If strSearchType = "L" Then
Me.OrderBy = Me.LastName
End If
rs.Close
Set rs=Nothing
End Sub
JVanKirk
I'm not sure why you need to write to a temp table. If a a unique form is opening for each of the different search options, then on the "Find" button on your criteria form, you assign the new sort order for your original form.
think thought that you need to also have Me.OrderByOn = True after each of your OrderBy's though.
Jason
asrodg
Example:
Say I want to search the database by control number. I open the application and select the "Search Menu" option from the Main Menu. The Search Menu form appears (part of this subprocedure clears the temp table of all previous records). This menu displays an option group with radio button for each search option. I select the "Control Number" radio button. As soon as I select this radio button the letter "C" is written to the temp table for reference later in the process and the Search by Control Number form appears (this is different from the the Search by Last Name and Search by Parcel Number forms). I enter the number "11" as search criteria (which is written to the temp table). The Search by Control Number form closes and the MsgBox "No records meet criteria" is displayed. As soon as I click the OK button, all the search information on the temp table, except the search type, is cleared. I have VBA code that looks for the search type in the temp table - if the search type = "C" then the Search by Control Number form is displayed. Since I cleared the search values from the temp table the criteria I entered previously no longer appears. I hope this explains why I used the temp table. I'm sure it could've been done several other ways but with my limited knowledge of VBA, I chose to do it this way.
nyway...
HAs I said before, when I include the OrderByOn line in my code I am prompted to enter a parameter. For Example: If I'm searching by Control Number and the criteria is "*18*", an "Enter Parameter Value" dialog box appears displaying 0001800 (first number returned in the results) with a blank text box underneath for the user to enter a value.
So I'm not sure how to use the OrderBy and OrderByOn in VBA.
Let me know if furth explanation is required. Thanks!
Jack Cowley
PMFJI, but if this were my database I would use the method found in this article to build my search 'on the fly'...
My 3 cents worth...
Jack
JVanKirk
Very excellent Jack, and you've known me for some time...you never have to be pardoned for jumping in...it's always a pleasure to see you MAD face involved.
ow's sunny SF today? It's beautiful here if we'd just get some rain to bring up the water table.
Take care.
Asrodg, let us know if Jack link helps you out. Never fun to redesign, but if it makes life easier down the road, and makes the db work more efficiently, then it always worth the time to fix it now.
Jacks done that to me many times LOL...
J
Jack Cowley
Jason -
had heard that there was a lot of rain in PA. Are you in a dry part of the state?
You don't want to know about the weather here because this place is already too crowded! The word "perfect" might give you some indication...
My pappy always told me that good manners were essential so I have tried to remember that and not just barge in without so much as a by-your-leave...
Jack
asrodg
Here's the thing...I read the article that Jack suggested and, I'm so new to VBA, I wouldn't have the foggiest idea of how to adapt it to my needs. I'm kind of on a deadline and redesigning the database doesn't seem too feasible right now. Any help with my current project would be great. Thanks!
.S. It's hot and humid in my part of Pennsylvania.... : wink.gif
Jack Cowley
This should get you started. Change object names as necessary...
CODE
Dim db As DAO.Database
Dim QD As QueryDef
Dim where As Variant
Set db = CurrentDb()
On Error Resume Next
db.querydefs.Delete ("Dynamic_Query")
On Error GoTo 0
where = Null
where = where & " AND [Parcel_Num]= " + Me![ParcelNumber]
where = where & " AND [Control_Num]= " + Me![ControlNumber]
Set QD = db.CreateQueryDef("Dynamic_Query", _
"Select * from YourTableNameGoesHere " & (" where " + Mid(where, 6) & ";"))
DoCmd.OpenQuery "Dynamic_Query"

hth,
Jack
asrodg
I get a run-time error message - Syntax error (missing operator) in query expression '*tbl_LKMRC where [Parcel_Num]=PRMAP AND [Control_Num]=00018"
Thanks for your help!
Jack Cowley
Parcel_Num is Text so try:
CODE
where = where & " AND [Parcel_Num]= '" + Me![ParcelNumber] & "'"

hth,
Jack
asrodg
The error message no longer appears but...Maybe I'm "thick" but I don't see how this helps with the sorting problem.
asrodg
It's probably redundant but I fixed it using the following code when the form opens:
ublic Sub sortby()
Dim rs As New ADODB.Recordset
rs.Open "tbl_temp_search", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
Dim strSearchType As String
strSearchType = rs.Fields("temp_SearchType")
If strSearchType = "P" Then
[sortparcelnumber1]
ElseIf strSearchType = "C" Then
[sortcontrolnumber2]
ElseIf strSearchType = "L" Then
[sortownername3]
End If
End Sub
Public Sub sortparcelnumber1()
Dim strOrder As String
strOrder = "Parcel_Num"
Me.OrderBy = strOrder
Me.OrderByOn = True
End Sub
Public Sub sortcontrolnumber2()
Dim strOrder As String
strOrder = "Control_Num"
Me.OrderBy = strOrder
Me.OrderByOn = True
End Sub
Public Sub sortownername3()
Dim strOrder As String
strOrder = "OwnerName"
Me.OrderBy = strOrder
Me.OrderByOn = True
End Sub
I truly appreciate all your help and insight. As I get more familiar with VBA, I'm sure I'll start using more of the suggestions you gave me. Thanks again! thumbup.gif
ScottGem
I'll jump in here. Instead of using a Temp table, I would just hide the form you select The sort type from.
When using the ON Open event of the form like this:
SELECT CASE Forms!formname!controlname
CASE "C"
Me.OrderBy = "Control_Num"
etc.
END SELECT
Jack Cowley
Your approach does seem like the long way round, but you got it working, learned something in the process and that will stand you in good stead later. Continued success with your project....
ack
Jack Cowley
Slick idea Scott! It is still too early for me to have any 'slick' ideas... :(
ack
JVanKirk
Exactly Scott, that was my though with my original post here, but I don't even think you have to hide the form, you could just close it when you click the find button. Either way, he's got it working now. I guess the thought of a temp table just bothers me, never need temp tables in any of may databases, especially not for something like this. When I think of any kind of temp tables, I think of relationships getting messed up somewhere down the road...
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.