Reset Combo Box

G

Guest

I am using a combo box in a subform to allow users to enter their name by
selecting from the list. The combo box is based on a query, and on selecting
the user sees the criteria selection box from the query asking them to enter
the first initial of their surname, this then presents a subset of the full
names list from which they can select.
This works fine but if the user then wishes to move to the next record and
select a different name the query box does not appear and they are presented
with the sme subset of names.
The only way to prevent this appears to be to close the form and reopen it
which is slow.
Is there any method of ensuring that the query re-runs every time the cursor
moves to a new record?

Any help gratefully recieved.
 
G

Guest

Douglas
Thanks
I have tried using Requery and DoCmd. Requery but neither works. What
command should I use to reset the combo box?
 
G

Guest

Doug
I tried the following, on the current event of the subform and the got focus
event of the combo box .
DoCmd.Requery "Player 1"
Player 1 is the name of the control.
I get the same error message from both
Run Time error "2488"
You cannot use the ApplyFilter action on this window.
 
G

Guest

Doug
I put DoCmd.Requery Me.Controls("Player 1") on the current event of the
subform and got a different error message
"Run Time error 2498"
An expression you entered is the wrong data type for one of the arguments
The data is text
--
Dave Hawks


Douglas J. Steele said:
DoCmd.Requery Me![Player 1]

or

DoCmd.Requery Me.Controls("Player 1")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)
Click to expand...
 
D

Douglas J. Steele

Sorry, I wasn't thinking. You want:

Me.Controls("Player 1").Requery

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Dave Hawks said:
Doug
I put DoCmd.Requery Me.Controls("Player 1") on the current event of the
subform and got a different error message
"Run Time error 2498"
An expression you entered is the wrong data type for one of the arguments
The data is text
--
Dave Hawks


Douglas J. Steele said:
DoCmd.Requery Me![Player 1]

or

DoCmd.Requery Me.Controls("Player 1")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Dave Hawks said:
Doug
I tried the following, on the current event of the subform and the got
focus
event of the combo box .
DoCmd.Requery "Player 1"
Player 1 is the name of the control.
I get the same error message from both
Run Time error "2488"
You cannot use the ApplyFilter action on this window.

--
Dave Hawks


:

What are you requerying: the form or the combo box itself?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Douglas
Thanks
I have tried using Requery and DoCmd. Requery but neither works.
What
command should I use to reset the combo box?
--
Dave Hawks


:

Put code in the form's Current event to reset the combo box.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I am using a combo box in a subform to allow users to enter their
name
by
selecting from the list. The combo box is based on a query, and
on
selecting
the user sees the criteria selection box from the query asking
them
to
enter
the first initial of their surname, this then presents a subset
of
the
full
names list from which they can select.
This works fine but if the user then wishes to move to the next
record
and
select a different name the query box does not appear and they
are
presented
with the sme subset of names.
The only way to prevent this appears to be to close the form and
reopen
it
which is slow.
Is there any method of ensuring that the query re-runs every time
the
cursor
moves to a new record?

Any help gratefully recieved.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Doug
That appears to work when attached to the on current event of the subform.
However I have a total of 4 Combo boxes per record and when I tried to apply
it to them all as
Private Sub Form_Current()
Me.Controls("Player 1").Requery
Me.Controls("Player 2").Requery
Me.Controls("Player 3").Requery
Me.Controls("Player 4").Requery
The performance of each control varied in that for the popup query box comes
up once for player 1, twice for player 2 etc before it becomes active and
allows you to type in a letter.
I similarly tried attaching to the on Focus event of each control but if the
user then cancels the query rather than typing in the criteria it produces an
error message.
Run time error 2147417848(80010108)
Method Requery of Object Combobox failed.
Is there some event, specific to each control that could be used to prevent
this happening.

--
Dave Hawks


Douglas J. Steele said:
Sorry, I wasn't thinking. You want:

Me.Controls("Player 1").Requery

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Dave Hawks said:
Doug
I put DoCmd.Requery Me.Controls("Player 1") on the current event of the
subform and got a different error message
"Run Time error 2498"
An expression you entered is the wrong data type for one of the arguments
The data is text
--
Dave Hawks


Douglas J. Steele said:
DoCmd.Requery Me![Player 1]

or

DoCmd.Requery Me.Controls("Player 1")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug
I tried the following, on the current event of the subform and the got
focus
event of the combo box .
DoCmd.Requery "Player 1"
Player 1 is the name of the control.
I get the same error message from both
Run Time error "2488"
You cannot use the ApplyFilter action on this window.

--
Dave Hawks


:

What are you requerying: the form or the combo box itself?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Douglas
Thanks
I have tried using Requery and DoCmd. Requery but neither works.
What
command should I use to reset the combo box?
--
Dave Hawks


:

Put code in the form's Current event to reset the combo box.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I am using a combo box in a subform to allow users to enter their
name
by
selecting from the list. The combo box is based on a query, and
on
selecting
the user sees the criteria selection box from the query asking
them
to
enter
the first initial of their surname, this then presents a subset
of
the
full
names list from which they can select.
This works fine but if the user then wishes to move to the next
record
and
select a different name the query box does not appear and they
are
presented
with the sme subset of names.
The only way to prevent this appears to be to close the form and
reopen
it
which is slow.
Is there any method of ensuring that the query re-runs every time
the
cursor
moves to a new record?

Any help gratefully recieved.
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

What have you got as the row sources for these combos? Is is a query that
points to a control (or controls) on the form, is it straight SQL, etc?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Dave Hawks said:
Doug
That appears to work when attached to the on current event of the subform.
However I have a total of 4 Combo boxes per record and when I tried to
apply
it to them all as
Private Sub Form_Current()
Me.Controls("Player 1").Requery
Me.Controls("Player 2").Requery
Me.Controls("Player 3").Requery
Me.Controls("Player 4").Requery
The performance of each control varied in that for the popup query box
comes
up once for player 1, twice for player 2 etc before it becomes active and
allows you to type in a letter.
I similarly tried attaching to the on Focus event of each control but if
the
user then cancels the query rather than typing in the criteria it produces
an
error message.
Run time error 2147417848(80010108)
Method Requery of Object Combobox failed.
Is there some event, specific to each control that could be used to
prevent
this happening.

--
Dave Hawks


Douglas J. Steele said:
Sorry, I wasn't thinking. You want:

Me.Controls("Player 1").Requery

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Dave Hawks said:
Doug
I put DoCmd.Requery Me.Controls("Player 1") on the current event of the
subform and got a different error message
"Run Time error 2498"
An expression you entered is the wrong data type for one of the
arguments
The data is text
--
Dave Hawks


:

DoCmd.Requery Me![Player 1]

or

DoCmd.Requery Me.Controls("Player 1")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug
I tried the following, on the current event of the subform and the
got
focus
event of the combo box .
DoCmd.Requery "Player 1"
Player 1 is the name of the control.
I get the same error message from both
Run Time error "2488"
You cannot use the ApplyFilter action on this window.

--
Dave Hawks


:

What are you requerying: the form or the combo box itself?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Douglas
Thanks
I have tried using Requery and DoCmd. Requery but neither works.
What
command should I use to reset the combo box?
--
Dave Hawks


:

Put code in the form's Current event to reset the combo box.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
I am using a combo box in a subform to allow users to enter
their
name
by
selecting from the list. The combo box is based on a query,
and
on
selecting
the user sees the criteria selection box from the query asking
them
to
enter
the first initial of their surname, this then presents a
subset
of
the
full
names list from which they can select.
This works fine but if the user then wishes to move to the
next
record
and
select a different name the query box does not appear and they
are
presented
with the sme subset of names.
The only way to prevent this appears to be to close the form
and
reopen
it
which is slow.
Is there any method of ensuring that the query re-runs every
time
the
cursor
moves to a new record?

Any help gratefully recieved.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Doug
All four combo boxes use the same source which is a query run on a table of
members names.
One column in the query is the first initial of the surname and this is the
criteria that is requested in the popup criteria box everytime someone clicks
on the combo box.
After inputing the first initial the query /combo box shows a subset of the
members names from which the required name is selected, and this is then
stored against a particular day and time, and 4 players are allowed for each
time.
After entering Player 1 at time 9:00 the user may then wish to enter another
Player 1 at 9:09 and this is where the popup fails.

--
Dave Hawks


Douglas J. Steele said:
What have you got as the row sources for these combos? Is is a query that
points to a control (or controls) on the form, is it straight SQL, etc?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Dave Hawks said:
Doug
That appears to work when attached to the on current event of the subform.
However I have a total of 4 Combo boxes per record and when I tried to
apply
it to them all as
Private Sub Form_Current()
Me.Controls("Player 1").Requery
Me.Controls("Player 2").Requery
Me.Controls("Player 3").Requery
Me.Controls("Player 4").Requery
The performance of each control varied in that for the popup query box
comes
up once for player 1, twice for player 2 etc before it becomes active and
allows you to type in a letter.
I similarly tried attaching to the on Focus event of each control but if
the
user then cancels the query rather than typing in the criteria it produces
an
error message.
Run time error 2147417848(80010108)
Method Requery of Object Combobox failed.
Is there some event, specific to each control that could be used to
prevent
this happening.

--
Dave Hawks


Douglas J. Steele said:
Sorry, I wasn't thinking. You want:

Me.Controls("Player 1").Requery

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Doug
I put DoCmd.Requery Me.Controls("Player 1") on the current event of the
subform and got a different error message
"Run Time error 2498"
An expression you entered is the wrong data type for one of the
arguments
The data is text
--
Dave Hawks


:

DoCmd.Requery Me![Player 1]

or

DoCmd.Requery Me.Controls("Player 1")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug
I tried the following, on the current event of the subform and the
got
focus
event of the combo box .
DoCmd.Requery "Player 1"
Player 1 is the name of the control.
I get the same error message from both
Run Time error "2488"
You cannot use the ApplyFilter action on this window.

--
Dave Hawks


:

What are you requerying: the form or the combo box itself?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Douglas
Thanks
I have tried using Requery and DoCmd. Requery but neither works.
What
command should I use to reset the combo box?
--
Dave Hawks


:

Put code in the form's Current event to reset the combo box.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
I am using a combo box in a subform to allow users to enter
their
name
by
selecting from the list. The combo box is based on a query,
and
on
selecting
the user sees the criteria selection box from the query asking
them
to
enter
the first initial of their surname, this then presents a
subset
of
the
full
names list from which they can select.
This works fine but if the user then wishes to move to the
next
record
and
select a different name the query box does not appear and they
are
presented
with the sme subset of names.
The only way to prevent this appears to be to close the form
and
reopen
it
which is slow.
Is there any method of ensuring that the query re-runs every
time
the
cursor
moves to a new record?

Any help gratefully recieved.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Doug
After playing around with various combinations I eventually settled on
Me.ActiveControl.Requery on the Got Focus event of each control, which works
perfectly.
Thanks for your help.
--
Dave Hawks


Dave Hawks said:
Doug
All four combo boxes use the same source which is a query run on a table of
members names.
One column in the query is the first initial of the surname and this is the
criteria that is requested in the popup criteria box everytime someone clicks
on the combo box.
After inputing the first initial the query /combo box shows a subset of the
members names from which the required name is selected, and this is then
stored against a particular day and time, and 4 players are allowed for each
time.
After entering Player 1 at time 9:00 the user may then wish to enter another
Player 1 at 9:09 and this is where the popup fails.

--
Dave Hawks


Douglas J. Steele said:
What have you got as the row sources for these combos? Is is a query that
points to a control (or controls) on the form, is it straight SQL, etc?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Dave Hawks said:
Doug
That appears to work when attached to the on current event of the subform.
However I have a total of 4 Combo boxes per record and when I tried to
apply
it to them all as
Private Sub Form_Current()
Me.Controls("Player 1").Requery
Me.Controls("Player 2").Requery
Me.Controls("Player 3").Requery
Me.Controls("Player 4").Requery
The performance of each control varied in that for the popup query box
comes
up once for player 1, twice for player 2 etc before it becomes active and
allows you to type in a letter.
I similarly tried attaching to the on Focus event of each control but if
the
user then cancels the query rather than typing in the criteria it produces
an
error message.
Run time error 2147417848(80010108)
Method Requery of Object Combobox failed.
Is there some event, specific to each control that could be used to
prevent
this happening.

--
Dave Hawks


:

Sorry, I wasn't thinking. You want:

Me.Controls("Player 1").Requery

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Doug
I put DoCmd.Requery Me.Controls("Player 1") on the current event of the
subform and got a different error message
"Run Time error 2498"
An expression you entered is the wrong data type for one of the
arguments
The data is text
--
Dave Hawks


:

DoCmd.Requery Me![Player 1]

or

DoCmd.Requery Me.Controls("Player 1")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug
I tried the following, on the current event of the subform and the
got
focus
event of the combo box .
DoCmd.Requery "Player 1"
Player 1 is the name of the control.
I get the same error message from both
Run Time error "2488"
You cannot use the ApplyFilter action on this window.

--
Dave Hawks


:

What are you requerying: the form or the combo box itself?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Douglas
Thanks
I have tried using Requery and DoCmd. Requery but neither works.
What
command should I use to reset the combo box?
--
Dave Hawks


:

Put code in the form's Current event to reset the combo box.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
I am using a combo box in a subform to allow users to enter
their
name
by
selecting from the list. The combo box is based on a query,
and
on
selecting
the user sees the criteria selection box from the query asking
them
to
enter
the first initial of their surname, this then presents a
subset
of
the
full
names list from which they can select.
This works fine but if the user then wishes to move to the
next
record
and
select a different name the query box does not appear and they
are
presented
with the sme subset of names.
The only way to prevent this appears to be to close the form
and
reopen
it
which is slow.
Is there any method of ensuring that the query re-runs every
time
the
cursor
moves to a new record?

Any help gratefully recieved.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Requery on Combo Box 2
Search combo box 2
Combo Box Dropdown 4
combo box selection runs query to sum values in table not gettingparameter from combo box 2
Combo Box linking 4
Show only queries w/table source combo box 1
Combo box problem 3
Reset combo box on unbound form 5

Top