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

  • Thread starter Thread starter Gina Whipp
  • Start date Start date
G

Gina Whipp

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 said:
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.
Click to expand...


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.
 
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 said:
Gina said:
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.
Click to expand...


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.
Click to expand...
 
Gina said:
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!
Click to expand...


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 ;-)
 
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!
 
Back
Top