By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,144 Members | 1,056 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,144 IT Pros & Developers. It's quick & easy.

Update Query from List Box selection

P: n/a
I've got a list box that selects a record on a subform, and in that
subform are a few text fiels and a button that runs an update query.
How do I have the update query button run and only update the record
that is selected in the list box? The data updates right, but I can't
get the update query to do anything but update all of the records.

Thanks,
Andrew
Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
And why are you selecting the record in a separate listbox, instead of
directly on the subform?

"Andrew" <sp****@gnt.net> wrote in message
news:d8**************************@posting.google.c om...
I've got a list box that selects a record on a subform, and in that
subform are a few text fiels and a button that runs an update query.
How do I have the update query button run and only update the record
that is selected in the list box? The data updates right, but I can't
get the update query to do anything but update all of the records.

Thanks,
Andrew

Nov 13 '05 #2

P: n/a
Andrew wrote:
I've got a list box that selects a record on a subform, and in that
subform are a few text fiels and a button that runs an update query.
How do I have the update query button run and only update the record
that is selected in the list box? The data updates right, but I can't
get the update query to do anything but update all of the records.


The update query must have a correct WHERE clause that pinpoints the
record to update. The primary key is a splendid candidate--you include
the fields from the primary key with the update query.

I'd like to see the answer to Turtle's question as well.
Nov 13 '05 #3

P: n/a
What happens is the list box chooses a client name, and the subform
enters services for that client. There are many services to one
client. The update query basically checks a box in the data to
'discharge' the client, so they won't show up in the list box anymore.
This button is clicked only after services have been entered and the
client leaves the program. I had the button on the main form, but got
the same result.

As far as writing a where clause, I don't know what to set the where
equal to.

Thanks,
Andrew
Bas Cost Budde <b.*********@heuvelqop.nl> wrote in message news:<cn**********@news2.solcon.nl>...
Andrew wrote:
I've got a list box that selects a record on a subform, and in that
subform are a few text fiels and a button that runs an update query.
How do I have the update query button run and only update the record
that is selected in the list box? The data updates right, but I can't
get the update query to do anything but update all of the records.


The update query must have a correct WHERE clause that pinpoints the
record to update. The primary key is a splendid candidate--you include
the fields from the primary key with the update query.

I'd like to see the answer to Turtle's question as well.

Nov 13 '05 #4

P: n/a
Andrew wrote:
What happens is the list box chooses a client name, and the subform
enters services for that client. There are many services to one
client. The update query basically checks a box in the data to
'discharge' the client, so they won't show up in the list box anymore.
This button is clicked only after services have been entered and the
client leaves the program. I had the button on the main form, but got
the same result.

As far as writing a where clause, I don't know what to set the where
equal to.


Since the listbox identifies the client, you should include its value in
the query. I suggest you create a parameter in the query, and fill it
from code; I found that safest, maybe others disagree.

(( The other way is to put a reference to the listbox in the query's
WHERE (the criteria line in design view): forms!yourform!yourlistbox ))

To have a parameter in a query, enter a name within [sqare brackets] in
the Criteria line (and make sure it is a name that is not equal to one
of the fields in the query). Access will now ask a value for this name
when you open the query.

To fill the parameter from code, use this:

dim qd as querydef
set qd=currentdb.querydefs("thatQuery")
qd.parameters(0) = me!listboxname.value
qd.execute
set qd=nothing

I assume two things here: (1) there is only one parameter in the query,
and (2) the listbox is on the form with this code. Not in a subform or
superform.

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea
Nov 13 '05 #5

P: n/a
sp****@gnt.net (Andrew) wrote in message news:<d8**************************@posting.google. com>...
What happens is the list box chooses a client name, and the subform
enters services for that client. There are many services to one
client. The update query basically checks a box in the data to
'discharge' the client, so they won't show up in the list box anymore.
This button is clicked only after services have been entered and the
client leaves the program. I had the button on the main form, but got
the same result.

As far as writing a where clause, I don't know what to set the where
equal to.

Thanks,
Andrew


I would vote for making your life easy. Since you can only select one
item at a time, why not use a combobox instead? If you want to use
the Multiselect property of a listbox, you have to use code to access
all the selected elements... not pretty if you can't code.
Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.