I need update query to run from combo box

[Guest]

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 Barton]

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.

 

[Guest]

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

 

[Guest]

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

 

[Marshall Barton]

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**.

 

[Guest]

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

 

[Marshall Barton]

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]

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

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.

 

[Guest]

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

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]

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

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 Barton]

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]

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

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?

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


:
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.

 

[Guest]

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

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]

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

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


:
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 Barton]

I forgot the part about the parameter prompt in your
question. That requires different code to resolve. Try
this instead:

Dim db As DAO.Database
Dim qdf As QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs("Reserve Room")
qdf.Parameters(0).Value = Eval(qdf.Parameters(0).Name)
qdf.Execute dbFailOnError
Me.combo54.requery
--
Marsh
MVP [MS Access]

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.

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

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

:
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


:
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.

 

[Guest]

Entered the below code in the event procedure for combo54 under private Sub.
when I clicked save to exit the form I got the following pop up:

Compile Error
User-defined type not Defined

The area in the code that was highlighted was: db As DAO.Database

Bill

I forgot the part about the parameter prompt in your
question. That requires different code to resolve. Try
this instead:

Dim db As DAO.Database
Dim qdf As QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs("Reserve Room")
qdf.Parameters(0).Value = Eval(qdf.Parameters(0).Name)
qdf.Execute dbFailOnError
Me.combo54.requery
--
Marsh
MVP [MS Access]

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.

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


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

:
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


:
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 Barton]

Sheesh, that implies that you have not set your References
(VBE window - Tools - References menu) to include the DAO
library. If you are not intending to use ADO, you should
also uncheck the ActiveX Data Objects library.

Please remember that the code I am posting is just a general
suggestion, you really need to check any part of it that you
do not understand in Help and make sure that the names are
correct. You will also need to add your own error handling
and any other bits and pieces pecular to your application.
--
Marsh
MVP [MS Access]

Entered the below code in the event procedure for combo54 under private Sub.
when I clicked save to exit the form I got the following pop up:

Compile Error
User-defined type not Defined

The area in the code that was highlighted was: db As DAO.Database

I forgot the part about the parameter prompt in your
question. That requires different code to resolve. Try
this instead:

Dim db As DAO.Database
Dim qdf As QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs("Reserve Room")
qdf.Parameters(0).Value = Eval(qdf.Parameters(0).Name)
qdf.Execute dbFailOnError
Me.combo54.requery

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.


:
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


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

:
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


:
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.

 

[Guest]

Well that got rid of the pop up and I don't get any more error messages but
it is still not executing the Query. when I select a room click save and
close the form then reopen the form the room still shows up in the combo box.

The query just won't run with the criteria As I said it runs fine when I
remove the criteria but then unchecks every room in the table.

the only thing I have not tried so far is putting code to run the query
someplace else perhaps within the command button to save the form.

Also here is what is checked in the references:
Visual Basic for Application
Microsoft Access 9.0 Object Library
OLE Automation
Microsoft Calendar control 9.0
Microsfot DAO 3.6 Object Library

also I did hit the help button but it didn't give me any information that I
was able to use like I said I am new to this and VBA is still very difficult
for me like brain surgery. Sorry

Sheesh, that implies that you have not set your References
(VBE window - Tools - References menu) to include the DAO
library. If you are not intending to use ADO, you should
also uncheck the ActiveX Data Objects library.

Please remember that the code I am posting is just a general
suggestion, you really need to check any part of it that you
do not understand in Help and make sure that the names are
correct. You will also need to add your own error handling
and any other bits and pieces pecular to your application.
--
Marsh
MVP [MS Access]

Entered the below code in the event procedure for combo54 under private Sub.
when I clicked save to exit the form I got the following pop up:

Compile Error
User-defined type not Defined

The area in the code that was highlighted was: db As DAO.Database

I forgot the part about the parameter prompt in your
question. That requires different code to resolve. Try
this instead:

Dim db As DAO.Database
Dim qdf As QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs("Reserve Room")
qdf.Parameters(0).Value = Eval(qdf.Parameters(0).Name)
qdf.Execute dbFailOnError
Me.combo54.requery


Bill_De wrote:
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.


:
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


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

:
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


:
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.

 

[Guest]

Marshall I don't know what I did just yet but I was moving stuff around and
retypeing entries and all of a sudden everything started working just the way
it is suppose so the problem is solved. I will have to sit down tonight or
tomorrow and figure out what I did.

I do have one last question for you before I leave you. As you can tell I am
very new to VBA and really don't know much at all especially with all the
abreviations and stuff. Is there a reference book that you could recommend
for someone trying to learn how to do VBA. I am really interested in it and
would like to learn more but there are so many books out there and I just
don't want to get one that is over my head starting out. Any suggestions you
might have would be greatly appreciated.

Thanks again for all the hard work you put into this for me I am sorry I was
such a pain. You guys are the greatest.
Thanks again and I hope you have a suggestion for me

Bill

Well that got rid of the pop up and I don't get any more error messages but
it is still not executing the Query. when I select a room click save and
close the form then reopen the form the room still shows up in the combo box.

The query just won't run with the criteria As I said it runs fine when I
remove the criteria but then unchecks every room in the table.

the only thing I have not tried so far is putting code to run the query
someplace else perhaps within the command button to save the form.

Also here is what is checked in the references:
Visual Basic for Application
Microsoft Access 9.0 Object Library
OLE Automation
Microsoft Calendar control 9.0
Microsfot DAO 3.6 Object Library

also I did hit the help button but it didn't give me any information that I
was able to use like I said I am new to this and VBA is still very difficult
for me like brain surgery. Sorry

Sheesh, that implies that you have not set your References
(VBE window - Tools - References menu) to include the DAO
library. If you are not intending to use ADO, you should
also uncheck the ActiveX Data Objects library.

Please remember that the code I am posting is just a general
suggestion, you really need to check any part of it that you
do not understand in Help and make sure that the names are
correct. You will also need to add your own error handling
and any other bits and pieces pecular to your application.
--
Marsh
MVP [MS Access]

Entered the below code in the event procedure for combo54 under private Sub.
when I clicked save to exit the form I got the following pop up:

Compile Error
User-defined type not Defined

The area in the code that was highlighted was: db As DAO.Database


:
I forgot the part about the parameter prompt in your
question. That requires different code to resolve. Try
this instead:

Dim db As DAO.Database
Dim qdf As QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs("Reserve Room")
qdf.Parameters(0).Value = Eval(qdf.Parameters(0).Name)
qdf.Execute dbFailOnError
Me.combo54.requery


Bill_De wrote:
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.


:
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


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

:
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


:
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 Barton]

Marshall I don't know what I did just yet but I was moving stuff around and
retypeing entries and all of a sudden everything started working just the way
it is suppose so the problem is solved. I will have to sit down tonight or
tomorrow and figure out what I did.

I do have one last question for you before I leave you. As you can tell I am
very new to VBA and really don't know much at all especially with all the
abreviations and stuff. Is there a reference book that you could recommend
for someone trying to learn how to do VBA. I am really interested in it and
would like to learn more but there are so many books out there and I just
don't want to get one that is over my head starting out. Any suggestions you
might have would be greatly appreciated.

Thanks again for all the hard work you put into this for me I am sorry I was
such a pain. You guys are the greatest.
Thanks again and I hope you have a suggestion for me

It's great to hear that it's working now!

However it would also be instructive to know why it wasn't
working before and what you did to fix it. Maybe you have a
heretofore unknown talent that is just starting to manifest
itself ;-)

As for a beginner's book on Access VBA, I have not had to
actually use one so I'm not qualified to recommend one.
I'm sure that if you post that request to a new thread
someone else will offer a suggestion or two.