Listbox Rowsource 08-02-07

I want to use a table as a listbox rowsource.
But here is the catch.  I want to add the record "ALL" to the listbox 
without adding  "ALL" as a record in the table.

Thanks
DS
0
DS
8/2/2007 9:11:43 PM
access.formscoding 7493 articles. 0 followers. Follow

11 Replies
768 Views

Similar Articles

[PageSpeed] 28

DS <bootybox@optonline.net> wrote in
news:DSqsi.43$ck6.18@newsfe12.lga: 

> I want to use a table as a listbox rowsource.
> But here is the catch.  I want to add the record "ALL" to the
> listbox without adding  "ALL" as a record in the table.
> 
> Thanks
> DS
> 
Create a union query.
SELECT "<< ALL >>" AS field1 from mytable
UNION SELECT fieldname from mytable. 

Use that as the recordsource for the listbox.
If you need multiple fields, use "" as field2, etc.
The number of columns must agree in both select statements.
The "from mytable" in the first select will return 1 row even if 
the table has many, and is required.

-- 
Bob Quintal

PA is y I've altered my email address.

-- 
Posted via a free Usenet account from http://www.teranews.com

0
Bob
8/2/2007 7:59:54 PM
On Thu, 02 Aug 2007 16:11:43 -0500, DS wrote:

> I want to use a table as a listbox rowsource.
> But here is the catch.  I want to add the record "ALL" to the listbox 
> without adding  "ALL" as a record in the table.
> 
> Thanks
> DS


Without knowing your actual situation, or the field names you wish to
include, you can adapt the following.
Change the table and field names to your actual table and field names.

Create a Union query.

Select YourTable.SomeField from YourTable
Union select "<All>" from YourTable
Order by [SomeField];

Note that the symbols <> surround the word "All". That is to have the
list sort with the word <All> at the top of the list before any other
"A" listing.

Make this query the Rowsource of the List box.
-- 
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
0
fredg
8/2/2007 8:32:01 PM
DS <bootybox@optonline.net> wrote in
news:83ssi.66$b72.31@newsfe12.lga: 

> OK all of the other listboxes work except this one which has a
> criteria. 
> 
> SELECT tblItemTypes.ItemTypeID,tblItemTypes.ItemTypeName
> FROM tblItemTypes UNION SELECT 0,"All Types" FROM tblItemTypes
> WHERE tblItemTypes.ItemTypeID Not Like 5
> ORDER BY ItemTypeName;
> 
> it's not excluding the one record.
> 
> Thanks
> DS
> 
Try
SELECT 
tblItemTypes.ItemTypeID,
tblItemTypes.ItemTypeName
FROM tblItemTypes 
WHERE tblItemTypes.ItemTypeID Not Like 5
UNION SELECT 
0 as ItemTypeID,
"All Types" as ItemTypeName 
FROM tblItemTypes
ORDER BY ItemTypeName;

-- 
Bob Quintal

PA is y I've altered my email address.

-- 
Posted via a free Usenet account from http://www.teranews.com

0
Bob
8/2/2007 9:16:36 PM
DS <bootybox@optonline.net> wrote in
news:hMrsi.37$6m7.4@newsfe12.lga: 

> Bob Quintal wrote:
> 
>> DS <bootybox@optonline.net> wrote in
>> news:DSqsi.43$ck6.18@newsfe12.lga: 
>> 
>> 
>>>I want to use a table as a listbox rowsource.
>>>But here is the catch.  I want to add the record "ALL" to the
>>>listbox without adding  "ALL" as a record in the table.
>>>
>>>Thanks
>>>DS
>>>
>> 
>> Create a union query.
>> SELECT "<< ALL >>" AS field1 from mytable
>> UNION SELECT fieldname from mytable. 
>> 
>> Use that as the recordsource for the listbox.
>> If you need multiple fields, use "" as field2, etc.
>> The number of columns must agree in both select statements.
>> The "from mytable" in the first select will return 1 row even
>> if the table has many, and is required.
>> 
> Works Great Thanks
> PS I did have to add another field so your advice came in
> handy! DS

It's a lesson I learned the hard way, a long long time ago.

-- 
Bob Quintal

PA is y I've altered my email address.

-- 
Posted via a free Usenet account from http://www.teranews.com

0
Bob
8/2/2007 9:17:59 PM
fredg wrote:

> On Thu, 02 Aug 2007 16:11:43 -0500, DS wrote:
> 
> 
>>I want to use a table as a listbox rowsource.
>>But here is the catch.  I want to add the record "ALL" to the listbox 
>>without adding  "ALL" as a record in the table.
>>
>>Thanks
>>DS
> 
> 
> 
> Without knowing your actual situation, or the field names you wish to
> include, you can adapt the following.
> Change the table and field names to your actual table and field names.
> 
> Create a Union query.
> 
> Select YourTable.SomeField from YourTable
> Union select "<All>" from YourTable
> Order by [SomeField];
> 
> Note that the symbols <> surround the word "All". That is to have the
> list sort with the word <All> at the top of the list before any other
> "A" listing.
> 
> Make this query the Rowsource of the List box.
Works great!
Thanks
DS
0
DS
8/2/2007 10:12:21 PM
Bob Quintal wrote:

> DS <bootybox@optonline.net> wrote in
> news:DSqsi.43$ck6.18@newsfe12.lga: 
> 
> 
>>I want to use a table as a listbox rowsource.
>>But here is the catch.  I want to add the record "ALL" to the
>>listbox without adding  "ALL" as a record in the table.
>>
>>Thanks
>>DS
>>
> 
> Create a union query.
> SELECT "<< ALL >>" AS field1 from mytable
> UNION SELECT fieldname from mytable. 
> 
> Use that as the recordsource for the listbox.
> If you need multiple fields, use "" as field2, etc.
> The number of columns must agree in both select statements.
> The "from mytable" in the first select will return 1 row even if 
> the table has many, and is required.
> 
Works Great Thanks
PS I did have to add another field so your advice came in handy!
DS
0
DS
8/2/2007 10:13:13 PM
OK all of the other listboxes work except this one which has a criteria.

SELECT tblItemTypes.ItemTypeID,tblItemTypes.ItemTypeName
FROM tblItemTypes UNION SELECT 0,"All Types" FROM tblItemTypes
WHERE tblItemTypes.ItemTypeID Not Like 5
ORDER BY ItemTypeName;

it's not excluding the one record.

Thanks
DS
0
DS
8/2/2007 10:33:20 PM
On Thu, 02 Aug 2007 17:33:20 -0500, DS <bootybox@optonline.net> wrote:

>WHERE tblItemTypes.ItemTypeID Not Like 5

You don't need to use the LIKE operator here - it should be used only when
you're using wildcards on string fields. If you want to exclude typeID 5 just
use

WHERE tblItemTypes.ItemTypeID <> 5

             John W. Vinson [MVP]
0
John
8/2/2007 10:52:53 PM
On Thu, 02 Aug 2007 17:33:20 -0500, DS wrote:

> OK all of the other listboxes work except this one which has a criteria.
> 
> SELECT tblItemTypes.ItemTypeID,tblItemTypes.ItemTypeName
> FROM tblItemTypes UNION SELECT 0,"All Types" FROM tblItemTypes
> WHERE tblItemTypes.ItemTypeID Not Like 5
> ORDER BY ItemTypeName;
> 
> it's not excluding the one record.
> 
> Thanks
> DS

What's the datatype of the ItemTypeID field? Is it a Number or Text?

If it is a Text datatype, you can use the Like keyword with a wildcard
to exclude any value in the field that starts with, contains anywhere
in the field, or ends with, that 5 value. Note, because it is Text
datatype, the value must be encloses within quotes "5".

WHERE tblItemTypes.ItemTypeID Not Like "5*"

will exclude records that begin with the 5, i.e. 5, 53, 569, etc.

If the value to exclude is just "5", then there is no need for a wild
card nor the Like keyword.

WHERE tblItemTypes.ItemTypeID Not "5"

If the datatype is a Number datatype, then do not use the Like keyword
nor the quotes..

WHERE tblItemTypes.ItemTypeID Not 5

Also the Where clause goes after the Select, not after the Union
Select...

I'll guess that the 5 is a number, not text.

 SELECT tblItemTypes.ItemTypeID,tblItemTypes.ItemTypeName
 FROM tblItemTypes 
 WHERE tblItemTypes.ItemTypeID Not 5
 UNION SELECT 0,"All Types" FROM tblItemTypes
 ORDER BY ItemTypeName;
 
 Note: if one of the values sorts before "All Types", for example,
"alabaster", your "All Types" will NOT be first in the list. 
To sort first, you could use "<All Types" or " All Types", etc.

-- 
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
0
fredg
8/2/2007 11:01:01 PM
John W. Vinson wrote:

> On Thu, 02 Aug 2007 17:33:20 -0500, DS <bootybox@optonline.net> wrote:
> 
> 
>>WHERE tblItemTypes.ItemTypeID Not Like 5
> 
> 
> You don't need to use the LIKE operator here - it should be used only when
> you're using wildcards on string fields. If you want to exclude typeID 5 just
> use
> 
> WHERE tblItemTypes.ItemTypeID <> 5
> 
>              John W. Vinson [MVP]
Thanks John, I also needed to move the where clause to an earlier 
position. It works now.
Thanks
DS
0
DS
8/3/2007 12:29:08 AM
fredg wrote:

> On Thu, 02 Aug 2007 17:33:20 -0500, DS wrote:
> 
> 
>>OK all of the other listboxes work except this one which has a criteria.
>>
>>SELECT tblItemTypes.ItemTypeID,tblItemTypes.ItemTypeName
>>FROM tblItemTypes UNION SELECT 0,"All Types" FROM tblItemTypes
>>WHERE tblItemTypes.ItemTypeID Not Like 5
>>ORDER BY ItemTypeName;
>>
>>it's not excluding the one record.
>>
>>Thanks
>>DS
> 
> 
> What's the datatype of the ItemTypeID field? Is it a Number or Text?
> 
> If it is a Text datatype, you can use the Like keyword with a wildcard
> to exclude any value in the field that starts with, contains anywhere
> in the field, or ends with, that 5 value. Note, because it is Text
> datatype, the value must be encloses within quotes "5".
> 
> WHERE tblItemTypes.ItemTypeID Not Like "5*"
> 
> will exclude records that begin with the 5, i.e. 5, 53, 569, etc.
> 
> If the value to exclude is just "5", then there is no need for a wild
> card nor the Like keyword.
> 
> WHERE tblItemTypes.ItemTypeID Not "5"
> 
> If the datatype is a Number datatype, then do not use the Like keyword
> nor the quotes..
> 
> WHERE tblItemTypes.ItemTypeID Not 5
> 
> Also the Where clause goes after the Select, not after the Union
> Select...
> 
> I'll guess that the 5 is a number, not text.
> 
>  SELECT tblItemTypes.ItemTypeID,tblItemTypes.ItemTypeName
>  FROM tblItemTypes 
>  WHERE tblItemTypes.ItemTypeID Not 5
>  UNION SELECT 0,"All Types" FROM tblItemTypes
>  ORDER BY ItemTypeName;
>  
>  Note: if one of the values sorts before "All Types", for example,
> "alabaster", your "All Types" will NOT be first in the list. 
> To sort first, you could use "<All Types" or " All Types", etc.
> 
Thanks Fred,
All of that was very informative.  I appreciate it.
Thanks
DS
0
DS
8/3/2007 12:30:48 AM
Reply:

Similar Artilces:

Tracking Email in CRM 07-07-06
Im getting error messages on user pc's when they try and select Track in CRM for email. The error is "error has occured" Thats it. I was told to search the updates and hotfixes page but it isnt listed unless im being thick! If anyone knows the hotfix number then i can go straight to MS with it Can anyone help as this now is really ticking me off...... ...

Automatically Resizing Listboxes
I've written an option pricing model that is constantly updating valuations in real-time based on a datafeed. Because of this, I leave the sheet on autorecalc. On a few of the sheets in the workbook, I have listboxes allowing me to select the tenor of the series I'm trying to price. Occasionally excel goes into a 'death spiral' and begins to resize these boxes automatically until they are almost non-existent and then crashes. The problem is not consistent - many times I can close and reopen excel and the problem is solved. I figure there must be a conflict somewhere, but I...

Cursor disappears 02-18-10
I'm using Word 2003. While typing or during a pause, the cursor disappears and I end up typing blanks. This is a new problem after being infected by a virus. If your computer has really been infected by a virus, you should obviously deal with that first. However, note that there is a known issue with Word 2003 that affects the size of the insertion point. To fix it (temporarily), change the zoom to 500% and then back to your preference. -- Stefan Blom Microsoft Word MVP "Mr Ron" wrote: > I'm using Word 2003. While typing or during a pause, ...

Automatically upload EFT file to bank? 02-17-10
Does anyone know of any communications software that enables a payables EFT file to automatically upload to the bank? Please disregard this post, it is a duplicate. Sorry about that. If anyone knows how to delete posts, feel free to let me know. Thank you. "MichelleKay" wrote: > Does anyone know of any communications software that enables a payables EFT > file to automatically upload to the bank? ...

SE:49999 08-31-07
not sure if this is the right group but... I installed a game and it came up with that after it autopatched. what does SE:49999 mean? > not sure if this is the right group but... Nope. Microsoft.Public.Access.Anything is still a newsgroup for questions about Microsoft Access databases. I'd suggest finding a newsgroup or forum with your game's name in it, or with the name of your game's manufacturer if it isn't Microsoft. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials. Blog...

Replication 12-17-07
Can anyone tell me what the easiest way is to stop a database from being able to be replicated!? I have a database that while I was gone over the weekend, someone had changed something between the two, and has added a bunch more data, so I have a bit over 7,000 conflicts. I would like to stop the replica, and create a new one, so I still have all the data that was created over the weekend. How would I do this!? Goobz <charlestwaters@gmail.com> wrote in news:418eda5a-8a0e-4823-92e7-d52134966cba@t1g2000pra.googlegroups.com : > Can anyone tell me what the easiest way is to stop a da...

Crystal Reports 02-24-05
What version of Crystal Reports do I need to use for CRM v1.2.3297.0 customizable reports? I had downloaded a free version of Crystal 9 and could not determine how to plug CRM into it. Please help! Leanne Leanne: Hello again! You need Crystal 9.2.2 for CRM 1.2.x You can contact Business Objects to get the upgrade from version 9 or 9.2 You will also want to make sure you install Crystal Reports on a machine that is not the CRM server, and then install the Crystal Enhancements for CRM that is on a disc that came with your CRM discs. Good luck, Matt Wittemann http://icu-mscrm.blogspo...

Help with a query 04-21-07
First of all thank you for taking the time to read my question. I am having a problem running a query on a simple table. Trying to run a query on a table with a field called ENCOUNTERS and a field called CODES. What I'm trying to ask get out of the query is this: Every time an ENCOUNTERS field has a CODES value of 69210, I want it to return all other CODES results for that ENCOUNTERS number. On 20 Apr 2007 18:39:20 -0700, "scottpconnors@yahoo.com" <scottpconnors@yahoo.com> wrote: >First of all thank you for taking the time to read my question. I am >having a proble...

Subform problem 03-18-08
Hi, I've a problem with my subform... When i open the main form i click on the button "New" to start logging data,same time, a form with a timer start working,so when i put the ID that links between the main form and the subform,the subform does not appear because of the timer... Anyone suggest a solution? ...

CRM Modifications 08-12-04
Name" field sorts on first name?? Can you see if that can be changed. Also,When an old quote is brought up to be printed, it uses the current date instead of the create or Modified date. Can that be fixed? Are you talking about the web client quote or the Outlook client quote? If you are using the Outlook client, the quote is simply a Word document which is mailmerged into the data from CRM. It can be edited to meet your exact requirements - the particular change that you require would be changing the date field (which always the current date) to a createdate or a savedate. To c...

Using a Public Function 12-08-07
I found a great function to allow me to display my decimals as fractions, but I don't know how to use it? I create reports which then become either work tickets or finished product labels. Where do I put the code. I understand the idea that it should be public so I can use it in any part of my application, but how do I refer to it? Thanks so much. =?Utf-8?B?TGVMZQ==?= <LeLe@discussions.microsoft.com> wrote in news:4FE3E937-4282-4067-92D8-921F44BDBC13@microsoft.com: > I found a great function to allow me to display my decimals as > fractions, but I don't know how ...

Re: OE won's send email, insufficient memory 02-26-10
"dada" <ali-mehrvar22@yahoo.com> wrote in message news:... > > "dada" <ali-mehrvar22@yahoo.com> wrote in message news:... >> >> "BlueMagoo" <BlueMagoo@discussions.microsoft.com> wrote in message >> news:0CE239C6-E6D5-483C-BEA2-A8B98548C13F@microsoft.com... >>> Suddenly OE will not send email but gives a "not enough memory" message. >>> My >>> sent mail folder is suddenly empty as well. Also, if I try to move a >>> message >>> into the now empty sent mal...

Slow Query 02-29-08
I have a log file with about 180K records. I'm looking to create a ranking of most frequently visited pages. This is the query I'm currently using: Select top 10 * from ( SELECT HitPage, URL, count(HitPage) as f1, PageTitle from tblSession where LogDate >=39142 GROUP BY HitPage, url, PageTitle ) order by f1 desc I have tried indexes on: LogDate, HitPage, URL, PageTItle HitPage, URL, PageTitle, LogDate When the LogDate is less than a month ago, its very quick, but when looking at a year's worth of data is very slow Any advice would be appreciated! I'm not sure if yo...

Customize views 07-25-03
Hi, How can i customize views to put some new columns visible ? Thanks, Hugo In CRM goto home,settings,system customization then click on one of the object types such as lead to see the customization screens "Hugo Fonseca" <hugo.fonseca@maeil.pt> wrote in message news:uLP#vMtUDHA.1748@TK2MSFTNGP12.phx.gbl... > Hi, > > How can i customize views to put some new columns visible ? > > Thanks, > Hugo > > thanks, yes, i remember now I had seen it somewhere "John O'Donnell" <csharpconsulting@hotmail.com> wrote in message news:#6G...

outlook express 07-06-07
When send mails in outlook express it remails in the outbox and it does not go further "Aruna Sharma" <ArunaSharma@discussions.microsoft.com> wrote in message news:B07F9F1B-1DEA-478B-A9FC-791052C904DE@microsoft.com... > When send mails in outlook express it remails in the outbox and it does > not > go further Try one of the Outlook Express newsgroups. Your question may be better answered in a newsgroup appropriate to your problem. This newsgroup is dedicated to the Microsoft Access database product. The Microsoft website is not all that clear and may have mis...

Custom Error Message 01-30-08
I'm trying to display a custom error message if the person misses entry in ANY of the Fields. Is there another way I can have the Error message display BEFORE the record saves and moves onto the next form ? It seems the code works up until a point. It displays what items require entry but when I click OK (on the error message to go back and fill-in the required fields) the form closes and moves onto the next form. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200801/1 Musa via AccessMonster.com wrote: >I'm trying to dis...

Decimal Places 02-10-10
Why does access put 4 decimal places on a calculation? I have it set to currency and zero or 2 decimal places but it still does it. I have tried fixed also. The calculation is basically the same as if I did it in excel but for some reason it always puts the 4 decimal places. I need to explain it to my boss and don't know why it does it. So far it hasn't effected our data. On Wed, 10 Feb 2010 12:48:01 -0800, Glenna <Glenna@discussions.microsoft.com> wrote: >Why does access put 4 decimal places on a calculation? I have it set to >currency and zero or 2...

INTERNET ACCESS 03-27-07
Each time I try to go into the internet I get a box up saying can't access whilst working off line. This locks the screen so I have to do Control Alt and delete to clear it. How do I clear this. Sorry, this newsgroup is for questions about Access, the database product that's part of Office Professional.You'll need to repost your question to a more appropriate group, likely one related to Internet Explorer-- Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele(no e-mails, please!)"David Reynolds" <dandsreynolds@tiscali.co.uk> wrote in message news:OX%23U8QKcHHA...

Sorting ListBox
Hi I need to control sorting in a CListBox control. I fount that to do this, I have to make it Owner Draw. So I did override the following methods, virtual int CompareItem( LPCOMPAREITEMSTRUCT lpCIS ); virtual void DrawItem(LPDRAWITEMSTRUCT lpDIS); virtual void MeasureItem(LPMEASUREITEMSTRUCT lpMIS); I create it with m_list.Create(LBS_OWNERDRAWFIXED|LBS_SORT|LBS_HASSTRINGS|LBS_NOTIFY|LBS_NOIN TEGRALHEIGHT|WS_HSCROLL|WS_VSCROLL|WS_CHILD|WS_VISIBLE,CRect(0,0,100,100),th is,100); But, in this case the CompareItem doesn't get called. I gets called if I don't use LBS_HASSTRINGS style...

Why do excell documents open faster in office 02 thant in Office 0
Why do excell documents open faster in Office 2002 thant in Office 2003? There is a big differnece in performance. It takes an additional 60 seconds for it to open in 2003. BTW - there are alot of Calcs in this spread sheet too. Thanks in Advance, Nick Hi if you have saved the file in Excel 2002 then Excel 2003 may re-calculate the entire sheet the first time you open it with this version. does saving the file in Excel 2003 and re-opening it help? -- Regards Frank Kabel Frankfurt, Germany "Nick Alesci" <Nick Alesci@discussions.microsoft.com> schrieb im Newsbeitrag ...

ListBox/ComboBox creation/deletion using VBA.
I wish to offer users the facility to select data from a list that is populated using the results of a search facility - a ListBox or a ComboBox is the solution of choice. I know how to use VBA to populate the list and to determine the response and process accordingly. I have already done this with pre-existing boxes that have been created on a spreadsheet. But, search as I may, I cannot find out how to Create and Delete list/combo boxes using VBA. Can sks please point me in the right direction. TIA Chris ...

Not Like 08-29-07
I have a number field in my query and I need the criteria to be... Not Like 6 Not Like 9 Not Like 10 Not Like 11 It can't be like any of the above...but the 6 is throwing me off. How can I do this? Thanks DS The Like expression is normally used for setting criteria for text fields, usually in combination with (a) wildcard character(s). To exclude any of the (integer) values 6, 9, 10, or 11, you can simply set a criterion of: Not In (6, 9, 10, 11) HTH Rob "DS" <bootybox@optonline.net> wrote in message news:Fn2Bi.29$vl.4@newsfe12.lga... >I have a number field...

CRM Access 02-23-05
I have a remote user accessing CRM via internet explorer. She says that any time she tries to open a window, a prompt comes up asking her if she would like to install crystal smart viewer. Keep in mind, she is not trying to view reports, just contacts and opportunities. Why would it be doing this and how can she get it to stop? Please Advise. This is fine. Tell her to accept it. -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "Dave" <dkoleno@cancerconsultantsinc.com> wrote in message news:188e01c519d9$cf140300$a501280a@phx.gbl... > I have a remo...

Conditional Formatting Access 07
I can use the sorting feature on my date columns in a continuious form but when I add a Conditional Format equal to a simple date value to one of them, I get a message stating "You have entered an expression that has an invalid reference to the property page - The property may not exist or may not apply to the object you specified" This is true on all of my fields but when I remove the formating, the message does not appear. How do I stop this? What are you entering into the Conditional Formatting? -- Bob Larson Access World Forums Super Moderator Utter Access VIP Tutorials at...

crm 2.0 10-08-04
Where can i find the upgrades that we are expecting in CRM 2.0? There hasn't been an official list of features published yet. Matt Parks MVP - Microsoft CRM ---------------------------------------- ---------------------------------------- On Fri, 8 Oct 2004 09:25:02 -0700, Gautam <gautam@fivepoints.net> wrote: Where can i find the upgrades that we are expecting in CRM 2.0? Hi, has an official list of features been published for 2.0 yet? Where can I find it? Thank you! Voni "Matt Parks" wrote: > There hasn't been an official list of features published yet....