Help with Search function (1 Viewer)

[gguy]

I need a bit of help with lookup code

The following works well, it brings up a list box by application number

Private Sub Search__AfterUpdate()

Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[appl_no] = '" & Me![search#] & "'"
Me.Bookmark = rs.Bookmark

End Sub

The problem is say that it find 20 records with the same application number, the code will not define in what order the 20 records are shown. I want them shown in recieved date order or [rec_date]. Thanks, Greg

 

[vbaInet]

There's nowhere in your code that references a listbox.

If you want your records sorted by received date you do it in the row source of the listbox or form. It will need to be based on a query.

 

[gguy]

Thanks for your reply VBAInet but I am looking for someone that really understands VBA and this code specifically. I am not posting to get palaver from Egotistical Access hacks.

VBAInet, if you really want to help then do the leg work. Otherwise, don't waste my time and yours. G

 

[vbaInet]

This sort of attitude won't get you anywhere in this forum or any other forum to be honest. If there was something you didn't understand in my reply you could have simply asked for more explanation on how to approach it.

Perhaps someone will less "ego" will provide more insight to your problem.

 

[colenzo]

I may not be right here.. but here's my shot at it.

If you create a basic select query - which will bring up all the data from your table - and then use a date ascending / decending under the date field of your table within the query, this should filter (sort) it correctly.

If you base your form on the query instead of the table, then the records should filter accordingly.

Thats my best guess, and it what i used when i sorted my own search form.

 

[gguy]

VBAInet, sorry for my curt reply, and I do understand all that you we're saying in your initial post.
Look, if you understand the code presented than you would realize that your initial post was just as offensive as my reply.

Again, I am asking for help with this code and I have been around the block several times with the coding that you are suggesting. Use the code and you will understand that for which I am asking.

When I first got this code off this forum about four years ago, I had been through every in-the-box possibility and never came close what I wanted.

This code does what I want, it just needs a tweek.

 

[CBrighton]

I'd set the Filter and OrderBy properties of the form via VBA.

You could leave the query bound to the form as something with little to no criteria, then use the VBA behind the search button to construct the WHERE clause into a string to be used in the Filter. Add a button to show all (by setting the search textbox to null and the forms .FilterOn property to false) and the user can cancel the filter and search for something different.

OrderBy can be handled a few ways. Inputting a field name into the properties window will give you a set order. Adding a combobox tied to some simple VBA would let the user change which field it is sorted by on the fly. Putting some VBA behind the column header labels would allow your users to click on the column heading they want to order by.

I tend to go with the third option if it's a form/subform setup.

I have done both the string-based filter and all 3 ways of setting the OrderBy property. If you want further info on any of them then come back asking for more.



:edit:

TBH, it may be of more use to review the order by settings in the underlying query, the recordset clone should inherit the order by details.

 

[gguy]

Wow, I am embarrassed that it is that simple, some times I get so focused on a code change that I didn't see the obvious.

I wasn't using an underlying query I was just using a table. I created a query with a sort and a secondary sort. Works like a top.

My apologies VBAInet for getting irritated, now I feel like a heel.

 

[vbaInet]

Absolutely understood. You were problem stressed with the problem.

Happy to hear you've got it resolved.