customize sort of search form

  • Thread starter Thread starter Angi
  • Start date Start date
A

Angi

I have a search form for company listings. When it opens, it prompts
the user for a part of a company name. It works great, but the order
is wrong. Technically not wrong, but in a db with 1500 records,
scrolling for the right name is a pain.

ie: User puts in Brag.

Results are:

ABC Brag Co.
Brag Designs.

Is there anyway to bring up the ones that match the user's input first?
I've taken out all the Sort by's but then it defaults to sorting by
the customer # which is wrong too. I have to use the LIKE in case
someone enters the company differently, or wrong. We've run into the
problem where they couldn't find the name so they started a new company
file. Is this possible?

TIA!
Angi
 
Can you be more specific on what you mean by match?

A guess is that you mean if they put in "Brag", you want the ones that have Brag
at the start of the field to appear first. You might try adding another column.

Field: StartPos: Instr(1,[CompanyNameField],[What Company Prompt],1)
Order By: Ascending

That will find the start position of the parameter prompt and return the number.
So if you sort by this criteria and then by company name, you would return
records in the order

Brag Company
Brag Designs
Adbrag
D Braggart
ABC Brag

Is that what you want?
 
John,
Yes, that is what I meant. I changed the [companynamefield] to my
field name, and it works, but now I'm getting two prompts. I tried
taking out the Like statement, but then it gives me all the company
names. And, not in ascending order, I might add. I tried changing the
prompt in the StartPos field, but got an invalid syntax error, of
course. What am I doing wrong? Please be patient...I have the flu and
pretty much in a fog! Thanks for all your help! I really appreciate
it.

Ang
 
Angi, this will not halp you but maybe me,

I am tryng to build a serach form similirar to the one that you made, but i
cannot get around, would you plese help me, maybe sending a small example of
a search form or giving me the like statement you put on the query and how
to link the query with the input of the forms.

Thanks
 
Marci,
I started by using a blank form and used the query builder for the
control source. I picked the fields I wanted then designed the form.
Set the form default view to continuous forms. In the criteria line
for the field you want to prompt for, put in this line: (customize the
area in brackets to say what you want)

Like "*" & [Enter part of a customer's name:] & "*"

If the user presses enter and inputs nothing, it will return all
records.

I have my search form set to open the customer form with either a
double click or by pressing enter on the acct # (all other fields are
disabled).

You will need something similiar to this in your OnKeyPress or
OnDblClick event for the field(s) you choose to use. This is my
OnKeyPress event: Again, change the fields to match your db.

If KeyAscii = vbKeyReturn Then
DoCmd.OpenForm "YourFormName", acNormal, , "[MatchingField] ="
& [Forms]![YourSearchFormName]![MatchingField], acFormReadOnly,
acWindowNormal
DoCmd.Close acForm, "YourSearchFormName", acSaveNo
End If

Hope this helps! Good luck!
Angi
 
Ahhh!

Angi,

The dual-parameter-prompt problem may be caused by your closing your search
form. When you do that the query can no longer see the control in the form and
the value in the control - they are no longer loaded and don't exist anymore
(except potentially). One way to handle this is to set the Search form's
visible property to false instead of closing it. That will keep the form loaded
in memory. Then when you close the form you are calling you can use its close
event to also close the search form.

Another option, more complex, would be to build the record source for the called
form in vba based on your search form.
 
Normally when you get two prompts you have a spelling problem. Are the two
parameter references EXACTLY the same (no extra spaces at the beginning or end
or double spaces instead of single spaces)?

Another way this can happen is if you have executed the query and then filtered
or sorted it in the data view. If that is the case, you can usually clear that
problem by opening the query in datasheet view and removing the filter/sort and
then saving the query.

If you can't find the problem try posting the SQL text view of your query.
 
John,
I only get the dual parameter when trying to open the form, not close
it. I don't have any problems when it's closing. In fact this form
ran great til people got picky!

Ok, checked the sort issue. The only filter or sort is done by the
query, not the form. Just to clarify something, my form's control
source is the sql string. Not a saved query. I don't think that
matters, so I didn't mention it.

Here's the sql string:

SELECT CompMain.CoID, CompMain.CompanyName, CompMain.Phone,
CompMain!City & ", " & CompMain!State AS CityState,
InStr(1,CompMain!CompanyName,[What Company Prompt],1) AS StartPos
FROM CompMain
ORDER BY InStr(1,CompMain!CompanyName,[What Company Prompt],1);

I took out the Like statement since it was giving me two prompts, but
it was in the CompanyName column. Now, when I run this, it prompts me
but ignores what I put in and lists everything. Now what am I doing
wrong??

ang
 
I would think that something like the SQL below would work.


SELECT CompMain.CoID, CompMain.CompanyName, CompMain.Phone,
CompMain.City & ", " & CompMain.State AS CityState
FROM CompMain
WHERE CompMain.CompanyName Like [What Company Prompt] & "*"
ORDER BY InStr(1,CompMain.CompanyName,[What Company Prompt],1), CompanyName
 
John
That worked! I see I had to take the Instr out of of the SELECT line.
Thank you very much!

Angi
 
Back
Top