By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,960 Members | 1,563 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,960 IT Pros & Developers. It's quick & easy.

Is it possible to create a search form and have the results appear in a specific orde

P: 12
I have a database that utilizes a form that we use to search records in the table. The user can place their cursor in any of the most relevant fields, and hit the search button which will produce a "find" dialog box. When the user inputs the information they wish to search by, and hits enter, the form will then find a record matching that one field and populate the rest of the controls with that records information. Hitting enter again will move to another record that fills this criteria, and it will continue to do so until no more records exist that fit the search information, then presenting a dialog box letting the user know.

It works fine, but what I was wondering is if there was any way to make it search in a specific fashion. For instance, most users will search by requisition number. If I search for requisition 237, it will begin the process as stated above, however there appears to be no rhyme or reason to what order it likes to produce the results in, though whatever ordering system its using, it does appear to be consistent. What I would like for it to do, is make it so that the first result that populates for requisition 237 is the oldest 237 on file. In other words, I wish for it to grab the oldest one using the "date assigned" field as its point of reference. The oldest 237 on record is currently 06/26/2013. This is the first one I want to populate. Hitting enter again should produce the next oldest one, and so on. However, the order they come up is:
06/26/2013
10/01/2014
12/22/2014
08/15/2014
08/20/2014
12/23/2014
02/04/2015
10/03/2014
09/15/2014
As you can see, they're not in order. Is there any way for the search results to populate in order of date if I'm searching like this?
Apr 13 '15 #1

✓ answered by zmbd

Not how I would normally do this; however, open the form in design mode
Ribbon>FormDesignTools>Tools>PropertySheet
Dialog>Dropdown>Form
Dialog>tab=Data
[Order By] = [date assigned]
[Order By On Load] = Yes
save your form.
Please run as normal and post back results.

See if that works.

Share this Question
Share on Google+
12 Replies


Rabbit
Expert Mod 10K+
P: 12,430
Since you didn't post your code that does the search, it's hard to offer any specific advice.

But when you coded the form, you probably built a dynamic SQL string to do your search. Just change that dynamic SQL string to include an order by clause.

Also, there is a rhyme and reason to the order that it is returning results. It's the order in which the rows are stored in the database file or it's the order in the index that is being used, if you're using an index that is.
Apr 13 '15 #2

P: 12
The on click code has the following macro coding seen in this picture.

Apr 13 '15 #3

Rabbit
Expert Mod 10K+
P: 12,430
Can't see your attachment, it's blocked by our firewall. It's better if you attach it to the thread itself. It's best if you convert the macro to VBA and post the code here between code tags
Apr 13 '15 #4

P: 12
Sorry about that, I didn't see that we had the ability to do this.

Attached Images
File Type: jpg Capture2.jpg (7.2 KB, 343 views)
Apr 13 '15 #5

Rabbit
Expert Mod 10K+
P: 12,430
The screenshot is too small, I can't see the text. Please convert the macro to VBA and post the code here.
Apr 13 '15 #6

zmbd
Expert Mod 5K+
P: 5,397
If you are using ACC2010 or newer,
Open your macro in design view
<ctrl><A> to select all
In this thread... click on the [CODE/] button in the post formatting toolbar
Place your cursor between the code tags and <ctrl><v>
This will paste the AccMacroScript in the proper forum format.
-Z
Apr 13 '15 #7

P: 12
I really don't know if this is what you want... this looks like html coding but I did as you asked.


Expand|Select|Wrap|Line Numbers
  1. <?xml version="1.0" encoding="UTF-16" standalone="no"?>
  2. <UserInterfaceMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application">
  3.    <UserInterfaceMacro 
  4.       For="Search" 
  5.       Event="OnClick">
  6.       <Statements>
  7.          <Action Name="OnError"/>
  8.          <Action Name="GoToControl">
  9.             <Argument Name="ControlName">=[Screen].[PreviousControl].[Name]
  10.             </Argument>
  11.          </Action>
  12.          <Action Name="ClearMacroError"/>
  13.          <Action Name="RunMenuCommand">
  14.             <Argument Name="Command">Find
  15.             </Argument>
  16.          </Action>
  17.          <ConditionalBlock>
  18.             <If>
  19.                <Condition>
  20.                   [MacroError&lt;&gt;0
  21.                </Condition>
  22.                <Statements>
  23.                   <Action Name="MessageBox">
  24.                      <Argument Name="Message">=[MacroError].[Description]
  25.                      </Argument>
  26.                   </Action>
  27.                </Statements>
  28.             </If>
  29.          </ConditionalBlock>
  30.       </Statements>
  31.    </UserInterfaceMacro>
  32. </UserInterfaceMacros>
Apr 14 '15 #8

zmbd
Expert Mod 5K+
P: 5,397
The code you provided here just covers the find action. It doesn't cover the actual record set.

Next question is how is your form setup?
Are you using a query or the table directly?
I suspect that you are bound directly to the table.
What I would do in that case is setup a query that sorts your table in the correct order.
Then open the form in design view.
In the form design tools context ribbon, design tab, tools, property sheet
In the property sheet there is a dropdown list, select [form]
Data tab
Record source... select your sorted query
Recordset type... Dynaset
Leave all else alone

Now, look at your form... if you used the EXACT same names in your query (which is default behavior) you should be good to go... save your form and run as you normally would. If you did not use the same names then you may have to re-bind your controls to the correct fields in the query.

Please update us with what happens.

:)

-Z
Apr 14 '15 #9

P: 12
I'm using a form, which searches information in a table. I'm not interested in creating a query, I just wanted to know if it was possible for this form to perform the way I described with some programming or tweaking of the form or table itself.
Apr 15 '15 #10

zmbd
Expert Mod 5K+
P: 5,397
Not how I would normally do this; however, open the form in design mode
Ribbon>FormDesignTools>Tools>PropertySheet
Dialog>Dropdown>Form
Dialog>tab=Data
[Order By] = [date assigned]
[Order By On Load] = Yes
save your form.
Please run as normal and post back results.

See if that works.
Apr 15 '15 #11

P: 12
It works! Except its yielding in reverse order! How can I get it to yield the most recent assigned date? It starts with the oldest as of now.
Apr 28 '15 #12

P: 12
Never mind! I figured it out! For anyone who finds this thread useful in the future, if the column I'm sorting by is titled "Date Assigned" then you follow zmbd's instructions and enter "Date Assigned" just as he mentioned above but then place a space and type DESC.
Apr 28 '15 #13

Post your reply

Sign in to post your reply or Sign up for a free account.