I need update query to run from combo box

In my application I have a form with a combo box. What I want to do is select
an item from the combo box and have that item removed without deleting it
from the table bound to the combo box. I inserted a check box in the table
and created an update query to remove the item from the combo box when the
check box = No.
If I run the query by going to the objects window and double clicking the
update query the item I select in the combo box is removed (and is still in
the table). What I want to do is have the query run all the time so the data
in the combo box is removed as soon as I select it.
How can I get this to happen? I want the query to run all the time. I did go
to the query in design view and clicked on the red exclamation point (!) but
the query still does not run and update when I close the form. If I need to
write code could you please tell me where the code should go and include the
code for me as I have tried writing code in several places and this did not
work.

Bill_De wrote:

In my application I have a form with a combo box. What I want to do is select
an item from the combo box and have that item removed without deleting it
from the table bound to the combo box. I inserted a check box in the table
and created an update query to remove the item from the combo box when the
check box = No.
If I run the query by going to the objects window and double clicking the
update query the item I select in the combo box is removed (and is still in
the table). What I want to do is have the query run all the time so the data
in the combo box is removed as soon as I select it.
How can I get this to happen? I want the query to run all the time. I did go
to the query in design view and clicked on the red exclamation point (!) but
the query still does not run and update when I close the form. If I need to
write code could you please tell me where the code should go and include the
code for me as I have tried writing code in several places and this did not
work.


The code you need is just:

Me.thecombobox.Requery

Where you put it depends on when you want the list to
reflect the selection. If it's immediately when the combo
box item is selected, then put it in the combo box's
AfterUpdate event procedure. If you want to wait until the
user does something else, then you can put it in the event
procedure that deals with whatever the users use to indicate
the additional action.

--
Marsh
MVP [MS Access]

Thanks Marshall for your help,
I didn't mean to send in so many posts I could not find the question I
posted so I thought there was something wrong with the way I sent in the post
I have never done this before.

One question though:
Where I put the code you said to write, you are talking about the event
section in the combo box is that correct where it lists items such as; on
enter, or on save?

Thanks again
Bill

"Marshall Barton" wrote:

Bill_De wrote:

In my application I have a form with a combo box. What I want to do is select
an item from the combo box and have that item removed without deleting it
from the table bound to the combo box. I inserted a check box in the table
and created an update query to remove the item from the combo box when the
check box = No.
If I run the query by going to the objects window and double clicking the
update query the item I select in the combo box is removed (and is still in
the table). What I want to do is have the query run all the time so the data
in the combo box is removed as soon as I select it.
How can I get this to happen? I want the query to run all the time. I did go
to the query in design view and clicked on the red exclamation point (!) but
the query still does not run and update when I close the form. If I need to
write code could you please tell me where the code should go and include the
code for me as I have tried writing code in several places and this did not
work.


The code you need is just:

Me.thecombobox.Requery

Where you put it depends on when you want the list to
reflect the selection. If it's immediately when the combo
box item is selected, then put it in the combo box's
AfterUpdate event procedure. If you want to wait until the
user does something else, then you can put it in the event
procedure that deals with whatever the users use to indicate
the additional action.

--
Marsh
MVP [MS Access]

I tried entering the code you suggested: me.thecombobox.requery and I got a
pop up which read: Microsoft Access can't find the Macro "me."
I entered the code in the combo box properties under events "on Exit".
If I need to create this macro also could you please let me know what steps
to take to create this macro as I don't know what a "me" macro is and am
still very new to programing and don't know to much about macros or VBA yet.
thanks
Bill

"Bill_De" wrote:

In my application I have a form with a combo box. What I want to do is select
an item from the combo box and have that item removed without deleting it
from the table bound to the combo box. I inserted a check box in the table
and created an update query to remove the item from the combo box when the
check box = No.
If I run the query by going to the objects window and double clicking the
update query the item I select in the combo box is removed (and is still in
the table). What I want to do is have the query run all the time so the data
in the combo box is removed as soon as I select it.
How can I get this to happen? I want the query to run all the time. I did go
to the query in design view and clicked on the red exclamation point (!) but
the query still does not run and update when I close the form. If I need to
write code could you please tell me where the code should go and include the
code for me as I have tried writing code in several places and this did not
work.

What I suggested was a VBA statement that must be in the
event **procedure**, not the corresponding event property.
(I do not recommend using a macro for much of anything.)

If you want the list requeried immediately, I would suggest
the AfterUpdate or the Exit event procedure. They will
behave a little differently so you'll have to decide which
one you prefer.

To create an event procedure and put you into the procedure
to enter the VBA statement, click on the [...] button in the
right margin of the event **property**.
--
Marsh
MVP [MS Access]


Bill_De wrote:
I tried entering the code you suggested: me.thecombobox.requery and I got a
pop up which read: Microsoft Access can't find the Macro "me."
I entered the code in the combo box properties under events "on Exit".
If I need to create this macro also could you please let me know what steps
to take to create this macro as I don't know what a "me" macro is and am
still very new to programing and don't know to much about macros or VBA yet.


"Bill_De" wrote:
In my application I have a form with a combo box. What I want to do is select
an item from the combo box and have that item removed without deleting it
from the table bound to the combo box. I inserted a check box in the table
and created an update query to remove the item from the combo box when the
check box = No.
If I run the query by going to the objects window and double clicking the
update query the item I select in the combo box is removed (and is still in
the table). What I want to do is have the query run all the time so the data
in the combo box is removed as soon as I select it.
How can I get this to happen? I want the query to run all the time. I did go
to the query in design view and clicked on the red exclamation point (!) but
the query still does not run and update when I close the form. If I need to
write code could you please tell me where the code should go and include the
code for me as I have tried writing code in several places and this did not
work.

I tried the code you told me to enter and The combo box is still not
updateing. Here is what I did.
The combo box is named: Combo54

I went to the Event tab
Opened the Event Porcedure for On Exit
Clicked on the [...]
Opened Code Builder
Code window was as follows after I entered the code:
Private sub Combo54_Exit(cancel as Interger)
me.combo54.requery
End Sub

It seems like it is not refering back to the update query named "Reserve
Room".
Any suggestions on what to do
Thanks
Bill

"Bill_De" wrote:

In my application I have a form with a combo box. What I want to do is select
an item from the combo box and have that item removed without deleting it
from the table bound to the combo box. I inserted a check box in the table
and created an update query to remove the item from the combo box when the
check box = No.
If I run the query by going to the objects window and double clicking the
update query the item I select in the combo box is removed (and is still in
the table). What I want to do is have the query run all the time so the data
in the combo box is removed as soon as I select it.
How can I get this to happen? I want the query to run all the time. I did go
to the query in design view and clicked on the red exclamation point (!) but
the query still does not run and update when I close the form. If I need to
write code could you please tell me where the code should go and include the
code for me as I have tried writing code in several places and this did not
work.

You must be doing something wrong somewhere, this kind of
situation works fine for me.

Are you sure that your UPDATE query has executed before the
combo box's Exit event is triggered?
--
Marsh
MVP [MS Access]


Bill_De wrote:
I tried the code you told me to enter and The combo box is still not
updateing. Here is what I did.
The combo box is named: Combo54

I went to the Event tab
Opened the Event Porcedure for On Exit
Clicked on the [...]
Opened Code Builder
Code window was as follows after I entered the code:
Private sub Combo54_Exit(cancel as Interger)
me.combo54.requery
End Sub

It seems like it is not refering back to the update query named "Reserve
Room".
Any suggestions on what to do


"Bill_De" wrote:
In my application I have a form with a combo box. What I want to do is select
an item from the combo box and have that item removed without deleting it
from the table bound to the combo box. I inserted a check box in the table
and created an update query to remove the item from the combo box when the
check box = No.
If I run the query by going to the objects window and double clicking the
update query the item I select in the combo box is removed (and is still in
the table). What I want to do is have the query run all the time so the data
in the combo box is removed as soon as I select it.
How can I get this to happen? I want the query to run all the time. I did go
to the query in design view and clicked on the red exclamation point (!) but
the query still does not run and update when I close the form. If I need to
write code could you please tell me where the code should go and include the
code for me as I have tried writing code in several places and this did not
work.

I really don't know if the query is executing or not. If I double click the
query in the query window it runs and I get 3 pop ups: 1- you are about to
run an update query that will modify data in your table. I click yes. 2-
Enter Parameter value (Forms!frmRegistration!combo54). I don't enter a
parameter and click ok. 3- you are about to update 0 rows.
When I click the run query in design view the red ! I first get the Enter
Parameter value which I leave blank and then I get the box You are about to
update 0 rows.
When I remove the criteria from the query and then execute the query I get
the box You are about to run an update query..... I click yes then I get a
box that says you are about to update 246 rows.

If the problem is the query is not executing because I don't enter a
parameter could you tell me if it is possible remove that requirement and if
that can be removed how do I do that?
Thanks

"Marshall Barton" wrote:

You must be doing something wrong somewhere, this kind of
situation works fine for me.

Are you sure that your UPDATE query has executed before the
combo box's Exit event is triggered?
--
Marsh
MVP [MS Access]


Bill_De wrote:
I tried the code you told me to enter and The combo box is still not
updateing. Here is what I did.
The combo box is named: Combo54

I went to the Event tab
Opened the Event Porcedure for On Exit
Clicked on the [...]
Opened Code Builder
Code window was as follows after I entered the code:
Private sub Combo54_Exit(cancel as Interger)
me.combo54.requery
End Sub

It seems like it is not refering back to the update query named "Reserve
Room".
Any suggestions on what to do


"Bill_De" wrote:
In my application I have a form with a combo box. What I want to do is select
an item from the combo box and have that item removed without deleting it
from the table bound to the combo box. I inserted a check box in the table
and created an update query to remove the item from the combo box when the
check box = No.
If I run the query by going to the objects window and double clicking the
update query the item I select in the combo box is removed (and is still in
the table). What I want to do is have the query run all the time so the data
in the combo box is removed as soon as I select it.
How can I get this to happen? I want the query to run all the time. I did go
to the query in design view and clicked on the red exclamation point (!) but
the query still does not run and update when I close the form. If I need to
write code could you please tell me where the code should go and include the
code for me as I have tried writing code in several places and this did not
work.

It sounds more like you have not done anything to execute
the query. If that's the case, then add this to the code:

CurrentDb.Execute "yourqueryname", dbFailOnError
Me.combo54.Requery
--
Marsh
MVP [MS Access]


Bill_De wrote:
I really don't know if the query is executing or not. If I double click the
query in the query window it runs and I get 3 pop ups: 1- you are about to
run an update query that will modify data in your table. I click yes. 2-
Enter Parameter value (Forms!frmRegistration!combo54). I don't enter a
parameter and click ok. 3- you are about to update 0 rows.
When I click the run query in design view the red ! I first get the Enter
Parameter value which I leave blank and then I get the box You are about to
update 0 rows.
When I remove the criteria from the query and then execute the query I get
the box You are about to run an update query..... I click yes then I get a
box that says you are about to update 246 rows.

If the problem is the query is not executing because I don't enter a
parameter could you tell me if it is possible remove that requirement and if
that can be removed how do I do that?
Thanks

"Marshall Barton" wrote:
You must be doing something wrong somewhere, this kind of
situation works fine for me.

Are you sure that your UPDATE query has executed before the
combo box's Exit event is triggered?

Bill_De wrote:
I tried the code you told me to enter and The combo box is still not
updateing. Here is what I did.
The combo box is named: Combo54

I went to the Event tab
Opened the Event Porcedure for On Exit
Clicked on the [...]
Opened Code Builder
Code window was as follows after I entered the code:
Private sub Combo54_Exit(cancel as Interger)
me.combo54.requery
End Sub

It seems like it is not refering back to the update query named "Reserve
Room".
Any suggestions on what to do


"Bill_De" wrote:
In my application I have a form with a combo box. What I want to do is select
an item from the combo box and have that item removed without deleting it
from the table bound to the combo box. I inserted a check box in the table
and created an update query to remove the item from the combo box when the
check box = No.
If I run the query by going to the objects window and double clicking the
update query the item I select in the combo box is removed (and is still in
the table). What I want to do is have the query run all the time so the data
in the combo box is removed as soon as I select it.
How can I get this to happen? I want the query to run all the time. I did go
to the query in design view and clicked on the red exclamation point (!) but
the query still does not run and update when I close the form. If I need to
write code could you please tell me where the code should go and include the
code for me as I have tried writing code in several places and this did not
work.

Marshall,
I entered the below code so in the VBA window I had as follows:

Private Sub Combo54_Exit(Cancel as Integer)
CurrentDb.Execute "Reserve Room", dbfailonerror
Me.combo54.requery

When I clicked the save/exit button I received a Run-Time Error 3061
Too few Parameters.Expected1.

When I did the debug the code you told me to enter Current....... was
highlighted in Yellow.

Just to let you know the Combo box selects the rooms and then I have a
command button save the form then exit out if finished. I don't know if that
means something or not.
Please advise on next step as to what was missing in the code.
thanks
Bill

"Marshall Barton" wrote:

It sounds more like you have not done anything to execute
the query. If that's the case, then add this to the code:

CurrentDb.Execute "yourqueryname", dbFailOnError
Me.combo54.Requery
--
Marsh
MVP [MS Access]


Bill_De wrote:
I really don't know if the query is executing or not. If I double click the
query in the query window it runs and I get 3 pop ups: 1- you are about to
run an update query that will modify data in your table. I click yes. 2-
Enter Parameter value (Forms!frmRegistration!combo54). I don't enter a
parameter and click ok. 3- you are about to update 0 rows.
When I click the run query in design view the red ! I first get the Enter
Parameter value which I leave blank and then I get the box You are about to
update 0 rows.
When I remove the criteria from the query and then execute the query I get
the box You are about to run an update query..... I click yes then I get a
box that says you are about to update 246 rows.

If the problem is the query is not executing because I don't enter a
parameter could you tell me if it is possible remove that requirement and if
that can be removed how do I do that?
Thanks

"Marshall Barton" wrote:
You must be doing something wrong somewhere, this kind of
situation works fine for me.

Are you sure that your UPDATE query has executed before the
combo box's Exit event is triggered?

Bill_De wrote:
I tried the code you told me to enter and The combo box is still not
updateing. Here is what I did.
The combo box is named: Combo54

I went to the Event tab
Opened the Event Porcedure for On Exit
Clicked on the [...]
Opened Code Builder
Code window was as follows after I entered the code:
Private sub Combo54_Exit(cancel as Interger)
me.combo54.requery
End Sub

It seems like it is not refering back to the update query named "Reserve
Room".
Any suggestions on what to do


"Bill_De" wrote:
In my application I have a form with a combo box. What I want to do is select
an item from the combo box and have that item removed without deleting it
from the table bound to the combo box. I inserted a check box in the table
and created an update query to remove the item from the combo box when the
check box = No.
If I run the query by going to the objects window and double clicking the
update query the item I select in the combo box is removed (and is still in
the table). What I want to do is have the query run all the time so the data
in the combo box is removed as soon as I select it.
How can I get this to happen? I want the query to run all the time. I did go
to the query in design view and clicked on the red exclamation point (!) but
the query still does not run and update when I close the form. If I need to
write code could you please tell me where the code should go and include the
code for me as I have tried writing code in several places and this did not
work.