473,325 Members | 2,671 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Update Query from List Box selection

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
5 4648
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: David | last post by:
Consider this SQL Query: ----------------------------------------------------------------- SELECT c.CASE_NBR, DATEDIFF(d, c.CREATE_DT, GETDATE()) AS Age, c.AFFD_RCVD, c.PRV_CRD_ISS, x.RegE,...
1
by: John Doe | last post by:
I have a PHP page that generates a list box with several options in it. I would like to have a "view" link next to the list box. When the user changes the contents of the list box, I would like...
1
by: Ryan | last post by:
Hello, I have a quick question (I hope). I have a form with a combo box and a multi-selection list box. The list box is based on a query. Users can select values from the cmbobox to add to...
0
by: starace | last post by:
I have designed a form that has 5 different list boxes where the selections within each are used as criteria in building a dynamic query. Some boxes are set for multiple selections but these list...
11
by: Siv | last post by:
Hi, I seem to be having a problem with a DataAdapter against an Access database. My app deletes 3 records runs a da.update(dt) where dt is a data.Datatable. I then proceed to update a list to...
0
southoz
by: southoz | last post by:
Good ay all , I'm fairly new to access(a little over 5 weeks now). Since I'v started I have picked up a lot of useful information from forums such as this and in doing so will share that information...
13
by: shookim | last post by:
I don't care how one suggests I do it, but I've been searching for days on how to implement this concept. I'm trying to use some kind of grid control (doesn't have to be a grid control, whatever...
3
by: kenn0380 | last post by:
Ok I have two list boxes. One list box gets it values from a list I typed in. It is used in a query that populates the list in List box2. It works fine but only for one time. If I make another...
9
by: Brett_A | last post by:
I have a form where the first field is a dynamic drop-down that pulls from a db (Access). The fields associated with the query are task_id, task_name and task_rate. The field has the value of...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.