Default at the top of the list

D

ddrew

seasoned user
Local time
Today, 20:14
Joined
Jan 26, 2003
Messages
911
In my form I have a Listbox that can be added to by the user. The list gets sorted into alphabetical order. I have one permenant item that cannot be deleted, "Default". Whwn the user opens the database I would like "Default" to always be at the top of the list even though the rest are in alphabetical order.

I saw an earlier post on similar lines talking about placing the word "DEfault" in [] and it made reference to 'Devs Site', but there was no link. Any ideas anyone? Thanks
 
This is probably the link to Dev's site mentioned. Adding "All" to a listbox or combobox

Basically the parens sort out before any alpha character in an ASCII sort so beginning your word with an open paren makes it come out first. Not so with the brackets!
 
OK that worked a treat, thanks, just one more thing, when I open the database I want the combobox to always read [Deafault] at startup. What the user then selects from the combbox will determin what happens after that. At the minute it retains the info that the last person used. i.e if the last time it was used it said "Brother" then that is what will be there the next time until it is changed. I tried:

On_Open

Me.Exercisename =[Default]

but that just produced an error, any ideas? Thanks
 
Default

Hi, it was my message that referenced the Deafult at top of the list. I am glad that at least someone else has the problem (though sorry that you do obviously!!) As no-one replied (which I find SO disheartening) I assumed I was being incredibly dumb. The link IS the one I referred to tho it suggests brackets and neither those nor parentheses work. Sorry I have no idea why but am about to give up on the forum and walk across campus to IT services so if I get an answer I will let you know asap.

Ali
 
Try this, as Rural Guy says put [] around the word, so for mine it reads [Deafault], for yours it reads [All]. That way it will always be at the top of the list, unless someone creates a word with [] around it!

Remember the list needs to be sorted alphabetically for it to work!

Incidentally dont give up on the forum, these guys do a great job and do their best to answer all the questions. I'd be lost without them, cheers guys!:D
 
Last edited:
Hi Drew- it does put it at the top of the list (or lists as I have 4 of them) but the query then doesn't work! Just returns nothing. If I leave the "All" languishing in mid list then it works fine. Tried parentheses and brackets and wildcards etc etc ad nauseum. As for no replies, I know there are many people who know loads out there and really kindly share their knowledge which is why i get disheartened! Glad yours is working tho!
 
ddrew,
have you tried: Me.Exercisename = "[Default]"
 
SnowPatrol,
There is nothing magic about the "All". You need to watch for it in the BeforeUpdate event and make whatever adjustment are needed to the query to return "All". Dev's code just shows you how to get it in the list so you can watch for it.
 
Excuse my ignorance on this subject. I can understand the logic behind the 'ALL' SQL Union statement but for the life of me I do not understand how putting square brackets [] round an option works. I have tried it with one of my applications but the option is then left with the [] characters round it which is not what I want.

I have used a sort order field in tables used for combo and list box lookups in the past but this is somewhat cumbersome.

Can somebody explain the logic behind the [] method?

Ta :D
 
I'm going to take an educated guess here. If you bring up your character map you will see that the left bracket "[" comes before the lowercase alpha characters in the Ascii chart. Since Access is basically case *insensitive* I suspect that all Ascii is converted to lowercase internally and therefore any Ascii sort will put a word that begins with the left bracket before any word that begins with a letter.
 
Hi RuralGuy. Thanks for the speedy reply. However, maybe I didn't explain myself very well, I understand why anything that starts with [ is at the top. I just assumed it was something "magic" in Access that showed it at the top but did not display the [].

Maybe I have just had a very long day and should now go home and have a good bottle of something red and fruity!! :p
 
Hi RG- thanks for the reply. However, dumb fool that I am, I CANNOT understand why the "All" works within the query when it is just in quote marks and therefore not at the top of the list, but when I put it in "[All]" it appears at the top of the list but then the query doesn't work!

Is it because my SQL is in the RowSource of the listbox properties rather than as code??? (as below)

SELECT [tblYears].[Year] FROM tblYears UNION select "All" from tblyears;

Thanks, Ali
 
The answer to your question is NO. What code do you have in the AfterUpdate event of the ComboBox?
 
Hi, I have none! I must be truly working with aircode!! Is this an oversight??? The query works fine with no "after update" coding involved. Its just the aesthetics that are the problem.

Thanks for your continual help..do you never sleep!!??

A
 
A couple of questions. Are we talking about a ComboBox or a ListBox?
...but when I put it in "[All]" it appears at the top of the list but then the query doesn't work!
Click to expand...
What query doesn't work? Have you posted the SQL for that query yet? If not, would you post it please?
 
Hi RG- it looks rather unwieldy even to my eyes.....

They are all listboxes

lboname
lboyear
lboapparatus
lboparish

SELECT tblStJamesAgric.harvest_Year AS Expr1, tblStJamesAgric.Owner_Title AS Expr2, tblStJamesAgric.Forename AS Expr3, tblStJamesAgric.Plantation_Name AS Expr4, tblStJamesAgric.Apparatus AS Expr5, tblStJamesAgric.crop_yield_in_hogs_heads AS Expr6, tblStJamesAgric.Lastname AS Expr7, tblStJamesAgric.ID, tblStJamesAgric.Parish, tblStJamesAgric.Post_Office, tblStJamesAgric.crop_yield_in_lbs
FROM tblYears INNER JOIN (tblApparatus INNER JOIN (tblParishes INNER JOIN tblStJamesAgric ON tblParishes.Parish = tblStJamesAgric.Parish) ON tblApparatus.Apparatus = tblStJamesAgric.Apparatus) ON tblYears.Year = tblStJamesAgric.harvest_Year
WHERE (((tblStJamesAgric.crop_yield_in_hogs_heads) Between [Forms]![Harvestform]![lbocrop] And [Forms]![Harvestform]![lbocrop1]) AND ((IIf([Forms]![Harvestform]![Lboparish]="All",True,[tblparishes].[Parish]=[Forms]![Harvestform]![Lboparish]))=True) AND ((IIf([Forms]![Harvestform]![Lboname]="All",True,[tblStJamesAgric].[lastname]=[Forms]![Harvestform]![Lboname]))=True) AND ((IIf([Forms]![Harvestform]![Lboyear]="All",True,[tblyears].[Year]=[Forms]![Harvestform]![Lboyear]))=True) AND ((IIf([Forms]![Harvestform]![Lboapparatus]="All",True,[tblapparatus].[apparatus]=[Forms]![Harvestform]![Lboapparatus]))=True))
ORDER BY tblStJamesAgric.harvest_Year;

Thanks again.
 
When you change to [All] rather than "All" do you also change the 4 lines of the SQL with "All" in them to "[All]"?
 
THANK YOU SO MUCH!!! I could not see the wood for the trees (I could barely see the trees either!1) You have made a tired girl very happy!
 
You must log in or register to reply here.

Similar threads

S
Interesting puzzler
2 3
Replies
52
Views
1,427
Gasman
Gasman
M
Move to record closest to today's date on continuos form
Replies
10
Views
518
mafhobb
M
Dick7Access
Word 365 Win 11
Replies
4
Views
388
Dick7Access
Dick7Access
J
Create a form with dynamic number of tab?
Replies
6
Views
330
MajP
MajP
oxicottin
Solved Move selected to top of listbox
Replies
11
Views
1,067
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom