Like criteria on a combo box ot working the way I want... Please help!

All,

Below is a the standard code I use in a combo box.

 Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[llListingID] = " & str(Nz(Me![cboTerritoryName], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

    Me.Form.AllowAdditions = False

The query used to find the data is...

SELECT qryLister.llListingID, qryLister.llTerritoryName
FROM qryLister
WHERE (((qryLister.llTerritoryName) Like "*"+[Enter any part of Territory 
Name:]+"*"));

All works fine for the first look-up.  But in order to refresh the combo box 
so it allows one to 'reuse' the Like criteria I am trying to put a Refresh 
or ReQuery.  I have tried the On_Exit (which I used before and it worked but 
not in Access 2003), the On_GotFocus and the On_Click event.  What I get is 
you type in say "we", get your selection but before it actually goes to the 
matching record it puts up the Like critieria again.  What I want is once 
you make your selection, go to that selection and then when you go to look 
up another selection it asks for the Like criteria.  If I leave out the 
Refresh or ReQuery I have to close the menu and start again.

Hope that makes sense.  Any ideas?

-- 
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors 
II 


0
Gina
11/12/2007 9:31:16 PM
access 16762 articles. 3 followers. Follow

4 Replies
815 Views

Similar Articles

[PageSpeed] 20

Gina Whipp wrote:
>Below is a the standard code I use in a combo box.
>
> Dim rs As Object
>
>    Set rs = Me.Recordset.Clone
>    rs.FindFirst "[llListingID] = " & str(Nz(Me![cboTerritoryName], 0))
>    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
>
>    Me.Form.AllowAdditions = False
>
>The query used to find the data is...
>
>SELECT qryLister.llListingID, qryLister.llTerritoryName
>FROM qryLister
>WHERE (((qryLister.llTerritoryName) Like "*"+[Enter any part of Territory 
>Name:]+"*"));
>
>All works fine for the first look-up.  But in order to refresh the combo box 
>so it allows one to 'reuse' the Like criteria I am trying to put a Refresh 
>or ReQuery.  I have tried the On_Exit (which I used before and it worked but 
>not in Access 2003), the On_GotFocus and the On_Click event.  What I get is 
>you type in say "we", get your selection but before it actually goes to the 
>matching record it puts up the Like critieria again.  What I want is once 
>you make your selection, go to that selection and then when you go to look 
>up another selection it asks for the Like criteria.  If I leave out the 
>Refresh or ReQuery I have to close the menu and start again.


That code looks like a mix of DAO and ADO.  To do that in
DAO, I use something like:

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[llListingID]=" & Nz(Me![cboTerritoryName], 0)
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark

FindFirst is a DAO method, but using BOF/EOF to check for a
match is ADO.

If you intended to use ADO, then you should say so in your
question so someone that uses it regularly can answer your
question.

From what you posted here, I have no idea how your query can
relate to the combo box or it's use to navigate to a record
on the form.

-- 
Marsh
MVP [MS Access]
0
Marshall
11/12/2007 10:52:26 PM
Marshall,

Sorry my explanation was so unclear...  Let me see if I can clear that up.

The query is used as the Row Source on the combo box.

1. Open form and click on combo box.  It prompts me "Enter any part of 
Territory Name:"
2. I put 'gi' in the box and it pulls up all territories with 'gi' in the 
name and I make a choice from the abbreviated list.
3. Once selected that record then queries the form to show me the record.

All is good up to item 3.

4. Before it actually shows me the record it prompts me again to "Enter any 
part of Territory Name:"

What I want is it not to prompt me again UNTIL I actually click on 
cboTerritoryName.

Hope that clears it up.

Oh, as for the ADO/DAO code, I left that to the wizard.  I should have known 
better!
-- 
Gina Whipp

"Marshall Barton" <marshbarton@wowway.com> wrote in message 
news:mflhj31gjrqdloqknvlrv5td00di4pufab@4ax.com...
> Gina Whipp wrote:
>>Below is a the standard code I use in a combo box.
>>
>> Dim rs As Object
>>
>>    Set rs = Me.Recordset.Clone
>>    rs.FindFirst "[llListingID] = " & str(Nz(Me![cboTerritoryName], 0))
>>    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
>>
>>    Me.Form.AllowAdditions = False
>>
>>The query used to find the data is...
>>
>>SELECT qryLister.llListingID, qryLister.llTerritoryName
>>FROM qryLister
>>WHERE (((qryLister.llTerritoryName) Like "*"+[Enter any part of Territory
>>Name:]+"*"));
>>
>>All works fine for the first look-up.  But in order to refresh the combo 
>>box
>>so it allows one to 'reuse' the Like criteria I am trying to put a Refresh
>>or ReQuery.  I have tried the On_Exit (which I used before and it worked 
>>but
>>not in Access 2003), the On_GotFocus and the On_Click event.  What I get 
>>is
>>you type in say "we", get your selection but before it actually goes to 
>>the
>>matching record it puts up the Like critieria again.  What I want is once
>>you make your selection, go to that selection and then when you go to look
>>up another selection it asks for the Like criteria.  If I leave out the
>>Refresh or ReQuery I have to close the menu and start again.
>
>
> That code looks like a mix of DAO and ADO.  To do that in
> DAO, I use something like:
>
> Dim rs As DAO.Recordset
> Set rs = Me.RecordsetClone
> rs.FindFirst "[llListingID]=" & Nz(Me![cboTerritoryName], 0)
> If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark
>
> FindFirst is a DAO method, but using BOF/EOF to check for a
> match is ADO.
>
> If you intended to use ADO, then you should say so in your
> question so someone that uses it regularly can answer your
> question.
>
> From what you posted here, I have no idea how your query can
> relate to the combo box or it's use to navigate to a record
> on the form.
>
> -- 
> Marsh
> MVP [MS Access] 


0
Gina
11/12/2007 11:06:37 PM
Gina Whipp wrote:
>The query is used as the Row Source on the combo box.
>
>1. Open form and click on combo box.  It prompts me "Enter any part of 
>Territory Name:"
>2. I put 'gi' in the box and it pulls up all territories with 'gi' in the 
>name and I make a choice from the abbreviated list.
>3. Once selected that record then queries the form to show me the record.
>
>All is good up to item 3.
>
>4. Before it actually shows me the record it prompts me again to "Enter any 
>part of Territory Name:"
>
>What I want is it not to prompt me again UNTIL I actually click on 
>cboTerritoryName.
>
>Oh, as for the ADO/DAO code, I left that to the wizard.  I should have known 
>better!


I seriously doubt that you can control prompts from a query
used as a row source.  There are all kind of things that can
cause Access to reconstruct the combo box's list.

You should use a text box on the form to enter the partial
territory name and modify the query's criteria to refer to
the text box instead of using a prompt string.  Then the
text box's AfterUpdate event can requery the combo box to
construct a new list.

It's OK to use a wizard if you don't know enough to do it
yourself, at least as a learning aid.  BUT each wizard's
result needs to be checked as they are are not always
correct or if legal, not always the best code.  Even if a
wizard does those adequately, you can run into limits and
have to complete it yourself.  Eventually, you will learn
which wizards are to be trusted or how to do the job without
a wizard getting in the way ;-)

-- 
Marsh
MVP [MS Access]
0
Marshall
11/13/2007 3:46:57 AM
Marshall,

Thaks for the info about the combo box.  i kinda figured that even though I 
actually got this to work in Access 97 but things change.

As for the 'happy little wizard', I was just being lazy... Lesson LEARNED!

-- 
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors 
II
"Marshall Barton" <marshbarton@wowway.com> wrote in message 
news:6h6ij398shegj762ac4qdnvnabn4k7kir1@4ax.com...
> Gina Whipp wrote:
>>The query is used as the Row Source on the combo box.
>>
>>1. Open form and click on combo box.  It prompts me "Enter any part of
>>Territory Name:"
>>2. I put 'gi' in the box and it pulls up all territories with 'gi' in the
>>name and I make a choice from the abbreviated list.
>>3. Once selected that record then queries the form to show me the record.
>>
>>All is good up to item 3.
>>
>>4. Before it actually shows me the record it prompts me again to "Enter 
>>any
>>part of Territory Name:"
>>
>>What I want is it not to prompt me again UNTIL I actually click on
>>cboTerritoryName.
>>
>>Oh, as for the ADO/DAO code, I left that to the wizard.  I should have 
>>known
>>better!
>
>
> I seriously doubt that you can control prompts from a query
> used as a row source.  There are all kind of things that can
> cause Access to reconstruct the combo box's list.
>
> You should use a text box on the form to enter the partial
> territory name and modify the query's criteria to refer to
> the text box instead of using a prompt string.  Then the
> text box's AfterUpdate event can requery the combo box to
> construct a new list.
>
> It's OK to use a wizard if you don't know enough to do it
> yourself, at least as a learning aid.  BUT each wizard's
> result needs to be checked as they are are not always
> correct or if legal, not always the best code.  Even if a
> wizard does those adequately, you can run into limits and
> have to complete it yourself.  Eventually, you will learn
> which wizards are to be trusted or how to do the job without
> a wizard getting in the way ;-)
>
> -- 
> Marsh
> MVP [MS Access] 


0
Gina
11/13/2007 4:06:33 AM
Reply:

Similar Artilces:

Vb code working one time
Hi, I have a form for adding data to a table. In this form , there is a command button for adding those data and it has the following code: Private Sub Command22_Click() On Error GoTo Err_Command22_Click If IsNull(Me.Combo28) = True Then MsgBox "You Must Select a S.O.Processor First.", vbCritical, "No Processor Selected..." Me.Combo28.SetFocus ElseIf IsNull(Me.CONTRACT_NO) = True Then MsgBox "You Must Input a Contract No. First.", vbCritical, "No Contract No. Input..." Me.CONTRACT_NO.SetFocus ElseIf IsNull(Me.CONTRACTOR) = True Then ...

Size of drop down boxes
I have a number of drop down select boxes, through the dta/validatio method, i want all the options to be displayed instead of having t scroll up and down, is this possible Thanks Rober -- Message posted from http://www.ExcelForum.com Hi AFAIK this is not possible with data validation listboxes -- Regards Frank Kabel Frankfurt, Germany > I have a number of drop down select boxes, through the dta/validation > method, i want all the options to be displayed instead of having to > scroll up and down, is this possible > Thanks > Robert > > > --- > Message posted fro...

Need help with moving Public folder from exch 2000 to 2003
I am trying to upgrade my server from Exch 2000 to 2003. I moved all the users mailbox without problem but can't seem to move the public folder. I try setting up replication but doesn't seem to work. any white paper or KB that give the procedure? I tried searching the site for days already but never seem to find an answer for this. There are lots of article on how to move from 5.5 to 2000 but none from 2000 to 2003 This kb might be useful as it discusses how to troubleshoot replication issues http://support.microsoft.com/default.aspx?scid=kb;en-us;842273 Regards Gordon ...

Exchange 2000 box freezes and needs reboot DAILY!
Hello, I'm having a problem with my mail server. The box freezes (Move mouse NOTHING hit keyboad NOTHING) and seems to shut it's self down (At the time it hung). Fun Eh? My first thoughts were screen saver settings. The next thing was the video card drivers, changed them and same thing. So tried new vid card to no avail. Changed to PCI vid card still the same effect. Now starting to think it's the motherboard or some combination of settings. Does anyone have any idea of what may be causing my problem? Looking into the event viewer there is nothing extraordinary about the...

Distinct Counts with Multiple Criteria
Each month we produce a complex report in Excel using thousands of rows of data. We've just been asked to add in a new table which needs to use distinct counts based on multiple criteria. I've included an example of the data below. How can I create a formula to complete the results table as I have manually entered below? A B C D 1 NAME DEPT LOCATION TYPE 2 Mary Finance A Primary 3 Sally Finance B Primary 4 Sally Finance B Primary 5 Mary IT A Primary 6 John Finance B Secondary 7 John Finance B Secondary 8 David Admin A Primary 9 John IT B Secondary 10 Da...

Want to send email from Draft folder
how do I do this or what is the alternative? You double click on it and then click Send. -- Bruce Hagen MS-MVP [Mail] Imperial Beach, CA "goldfish" <goldfish@discussions.microsoft.com> wrote in message news:007C88E2-D34B-476F-AA0B-DF168AE1F648@microsoft.com... > how do I do this or what is the alternative? Open the message(required), then click Send to place the message in the = Outbox folder - Click Send/Receive to send the message (required unless you have WLM = configured to send automatically) -- ....winston ms-m...

Want to know if there is a canned project summary format that I can use/customize #2
Hi Debra, Thanks for the tip. I found some very interesting templates there However, I didn't find any that suits my needs. I will continue t search for that -- ssaiba ----------------------------------------------------------------------- ssaibal's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1493 View this thread: http://www.excelforum.com/showthread.php?threadid=26567 ...

Looking for an Easy way to Achive a Result
Dear All, I've decided to keep track of phone numbers dialed and the phone provider I'm with allows viewing of the bill, but it only displays numbers... So I copied and pasted it into Excel, now I'm looking for a way to automate a column creation so that is checks the number in the number column and puts a name in another column... Is it easy to acheive? Regards Paul. Hi Paul, Check this tutorial: http://www.contextures.com/xlFunctions02.html -- Kind regards, Niek Otten Microsoft MVP - Excel "Kardon Coup´┐Ż" <prefer.to@readon.newsgroups> wrote in mess...

Notify me of replies doesn't work?
Everytime I check the box below "Notify me of replies" before posting but not once I got the notification. I am new to this forum. I use Windows Live Mail on Windows 7 platform. Email address is same for Windows Live Mail as well as this Forum. I don't quite understand why I don't receive notification through mail so that I can immediately check for the answer. Any possible reason? mkr presented the following explanation : > Everytime I check the box below "Notify me of replies" before posting but not > once I got the notification. I am new to thi...

Need help hiding 0.00 value in datasheet view
Hello all I am having a lil problem to display my data in datasheet view. I know you can not have more than 3 Conditioanl Formating. So I am trying a way around to eliminate one of my current CF so I can add another one but at the same time keep the other CF. My question: How do I HIDE (I dont want it to display) a field in datasheet view if the value is = 0.00 without using conditional formating, in other words using VBA code. Thanks in advance... Thanks...works fine :-) ...

Outlook Express wants to load when Outlook 2003 starts
Hi, Everytime I load Outlook 2003, the "Manage Identities" box shows up for Outlook Express within two seconds. Anyone know why OE is trying to start when I load up Outlook 2003? Thanks Which application is set as the Default (in Internet Explorer, Tools>Internet Options>Programs tab)? I'm wondering if a shared resource isn't being touched and you have some sort of registration issue. You might try toggling the default, click Apply and then restart Outlook 2003. I'll check further with some solution notes when I get into the office. Let me know what ha...

Need help for getting output
have information from different people and want to consolidate it in one single sheet using pivot tabel (without using macro) sample- Data - 1 Name Date Org Status Tiff # Anita 14-Apr-10 it processed 123 Anita 14-Apr-10 it pending 126 Anita 15-Apr-10 datex pending 128 Data 2 Name Date Org Status Tiff Rahul 14-Apr-10 it processed 131 Rahul 15-Apr-10 datex pending 134 Rahul 15-Apr-10 datex processed 135 it is a continues process day by day both data are from different excel sheet want it in one sheet Hi Without using a macro you can only do a manual cop...

No e-mail entry under MY Work
No e-mail entry under MY Work Solved the problem. For some reason some of my Sales users were set for Service. Changed from Service to Sales in the Workspace Options. "Yoram" wrote: > No e-mail entry under MY Work ...

Outlook hangs with attachments - detect and repair temporarily works
HI all, If I open outlook and then try to open an email with an attachment in - outlook locks and I need to re-boot in order to get back into outlook. When I re-boot - I can do a detect and repair and it fixes the problem - but only temporarily as when I close outlook and re load it - the problem with viewing attachments comes back? Setup: Windows XP Pro SP2 Office XP Pro with Frontpage SP3 Detect and repair does not permanently fix the problem. Reinstalling Outlook does not fix the problem. Has anyone ever run into this? Any suggestions? Thanks in advance! Chris ...

Open files while login, Pls help
Hello Everybody, I have many files that open while login to the the system using Start-All programs-Startup. However, I have noticed that whatever files that has been saved at the end of day are not reflect the same(whatever data i have saved) when i login to the system a very next morning. Could you pls help on this issue Thanks and Regards Ashfaq Noorie. PS: i'm using version 2007 excel. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree&...

Help please
I have an Excel 2003 spreadsheet that I keep golf scores and with some formulas I maintain my golf handicap. I would like to have an interface, form, etc that will allow me to do the following: 1) Enter data for a golf round (name, date, course, rating1, rating2, score) 2) Display data for a golfer 3) Perform functions on the data in the spreadsheet (modify/delete data, compute handicaps, etc) I'm not sure if there is something within Excel (VBA?) that will allow this or if there is some shareware or freeware that is specifically designed for this but any help would be gre...

Counting occurences on multiple criteria
I have a sheet of business data. There are 3 locations of service and I want to count the numbers o sales in each location over a given time period. Column C has the dates of sales Column H has the location I tried to get count for sales for the current month of June 2004 a location A =SUMPRODUCT((C5:C309>"5/31/2004")*(H5:H309="A")) and I get 0 (which is wrong.) I took out the quotes around the date value =SUMPRODUCT((C5:C309>5/31/2004)*(H5:H309="A")) and I get a number which is much too high and is obviously incorrect. What am I doing wrong -- Mess...

PowerShell Query Help, WMI, Select Statement
Here is where I have started: #Get Physical Memory function getwmiinfo ($svr) { gwmi -query "select * from Win32_PhysicalMemory" -computername $svr | select [$svr], DeviceLocator } $Servers = get-content -path "C:\test.txt" foreach($Servers in $Servers) { getwmiinfo $Servers } I get this: [risk] DeviceLocator ------ ------------- DIMM0 DIMM1 What I want is this: ServerName DeviceLocator ---------- ...

Access 2007 Switchboard buttons stop working
Environments tested: Windows7, WindowsXP, Access 2007, Access 2003 Access data project (.adp) run in development mode in all tests. SQL Server 2005 back-end database. Trusted locations set up for all tests. The trusted location is the folder where the .adp resides, sub-folders included. Default file format: Access 2002-2003. I have a .adp application with a standard switchboard form as the main menu navigation. The buttons on the main switchboard form are used to launch forms, stored procedures, macros, and other child switchboard forms. The switchboard navigation buttons a...

Outlook is not working when I lock the computer (Win2k)
Dear Gurus, I can't receive any messages when I lock my computer (Win2k). How to overcome this? Thanks in advance. Warm Regards, Sathish. ...

Help to track down 5.7.1 errors
Users intermittently get 5.7.1 unable to relay errors, you do not have permission. I tracked this down to receiving incorrect DNS lookups, where instead of getting the MX record, Exchange is instead getting back the DNS Server's IP of the recipients external domain. What seems to fix this is if I go to a dos prompt and do a manual NSLOOKUP, then SET TYPE=MX, and then the name of the ext domain that originally failed. Sometimes it will timeout on the first try in dos. If I retry the lookup it succeeds, and then am able to send mail to the external domain no problem. Both Windows Clients...

How to rip Blu-ray movies to other HD formats for enjoyment anywhere you want
[b]How to rip Blu-ray movies to other HD formats for enjoyment anywhere you want[/b] Most portable devices holders want to enjoy high-definition movies on their players anywhere. Blu-ray is the new resource of the HD movie world. Most of us may concern about how to back up Blu-ray disks and play them on our computers or portable devices. It is convenient to enjoy popular Blu-ray movies on our iPod, BlackBerry, iPhone, PSP and other players when we are on the move. Because the price of Blu-ray players and Blu-ray disks are higher than DVDs', most people try to copy fragile and expe...

[SBS 2008] Sharepoint only works viaLocalhost.
I've just installed WSS 3.0 on SBS2008. (Well, when I say "install," I really mean that I ran the hands-off configuration utility in SBS2008.) My goal with this Sharepoint server is to allow myself access from the Internet. Right now, however, I can only raise the Sharepoint site by pointing a browser on the server itself to: https://localhost:987/ There are some external hostnames that, I believe, were automatically setup by the wizard, but when I attempt to use that external hostname on the server, I am prompted for credentials three times then taken to a bla...

VBA Help #2
In a user form I have 6 radio buttons, 3 inside one frame, and 3 indentical inside another frame. I want to be able to make it so that the radio button selected in one frame cannot be the same in the other frame? Hopefully this can be solved! Thanks ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Software! Free Support at http://www.ozgrid.com/forum/ ** If I understand you correctly: Assume Options Buttons1-3 in Frame1, OptionButtons4-6 in Frame2: Private Sub OptionButton1_Click() OptionButton2.Enabled = Not OptionButton5.Value OptionButt...

help with RAND
I have the following formula: =RAND()*(100-50)+50 How can I force it to return a whole number? I'm creating worksheets for kids and I'm getting: 85+31=115 I have the cell attributes set to no decimals but I'm guessing the SUM formula is still grabbing the full random numbers and rounding up or down. While I'm on the subject, are there worksheets already created and available for download somewhere. I'm teaching grandkids math skills. thanks! "shank" <shank@tampabay.rr.com> wrote in message news:ufAFM#G1DHA.3496@TK2MSFTNGP11.phx.gbl... > I have the fo...