×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

How do I select a record displayed in a listbox?
2

thread700-1687900

How do I select a record displayed in a listbox?

How do I select a record displayed in a listbox?

(OP)


I have a list box (Device) that is populated by a table (M_tbl)
and I would like to have the User click a button to select the
record of the table that is shown in the list box so that a
field of that record can be modified.

My list box row source looks like this:

CODE --> 

SELECT D.Assembly, D.Description, FROM M_tbl AS D
ORDER BY D.Assembly;

I tried to set up the code to do this but I am not sure how to
select the correct record as it is displayed in the list box...

CODE --> 

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb


    strSQL = "SELECT M_tbl.Assembly, M_tbl.Description, FROM M_tbl; "
    Set rst = dbs.OpenRecordset(strSQL)
    
    If rst.EOF = False Or rst.BOF = False Then
        
      With rst
     
           .Edit
               !Description = "Obsolete"
          .Update

        End With
        rst.close    ' Close the recordset.

Any suggestions?

Thanks

RE: How do I select a record displayed in a listbox?

I would probably display the record to be edited in a subform. Then use the link master/child properties of the subform to link the Listbox value (Master) and the appropriate field on the subform (Child).

Duane
Hook'D on Access
MS Access MVP

RE: How do I select a record displayed in a listbox?

(OP)

Thanks Duane-

Could I also accomplish the same thing by linking the list box that is on the main form instead of making a subform?

Just not sure how to link the List box value so that when I do the dbs.OpenRecordset that it is pointing to the correct table index but I am guessing this would be the same on a sub form as it is on a main form?

Is there some sort of .Move command that I can use in the With rst section to select the correct table item that is displayed in the listbox?




RE: How do I select a record displayed in a listbox?

You could use the control wizard to add a combo box that finds a record on the form. Once the wizard has completed, change the combo box to a list box.

Duane
Hook'D on Access
MS Access MVP

RE: How do I select a record displayed in a listbox?

(OP)

Sorry Duane, I must not be getting it...

I tried to use the control wizard to add a combo box that finds a record on the form but the control wizard only allows data to come from a table, a query, or both...

So I created a combo box with the control wizard based on the data in the M_tble and changed it to a list box but don't see too much of a difference between that and what I had posted in my original post.

The row source for this new list box is:

CODE --> 

SELECT M_tbl.Assembly, M_tbl.Description
FROM M_tbl
ORDER BY M_tbl.Assembly;

and I'm still not sure how to select the record displayed in that list box in my With rst code after the when the button is pressed...

I apologize for not understanding this yet...

RE: How do I select a record displayed in a listbox?

Looking back at your original post, I am confused whether you want to automatically update the data or just find it.

I think you only need to run an update query based on the record found in the list box. I would make sure the listbox bound column corresponds specifically to the record(s) to be updated. I would not create a recordset. Your code to update the selected record might look like:

CODE

Dim dbs As DAO.Database
    Set dbs = CurrentDb
    Dim strSQL as String
    
    strSQL = "UPDATE M_tbl SET [Description] = 'Obsolete' WHERE [SomeField] = " & Me.NameOfListbox
    dbs.Execute strSQL, dbFailOnError
    Set dbs = nothing

Duane
Hook'D on Access
MS Access MVP

RE: How do I select a record displayed in a listbox?

(OP)

Thanks Duane-

Sorry if I have been unclear about this...
I actually want to be able to grab an element of the table and
check it and based upon a certain condition I would want to change it.

For example, I would like to pull the description from the table of the record displayed in the list box and modify it by either
changing it to "obsolete" or something else.

This is why I was creating a recordset so that could grab a field of a selected record from a table and modify it.

This may also evolve where I may need to modify other fields as well and what I am looking for is a way to grab a specific field and modify it.

The condition of how this gets modified might get too convoluted to cleanly handle with a query as I may need to add fields with numbers that require calculations...


RE: How do I select a record displayed in a listbox?

how to select the correct record
Replace this:
strSQL = "SELECT M_tbl.Assembly, M_tbl.Description, FROM M_tbl; "
with this:

CODE

strSQL = "SELECT Assembly,Description FROM M_tbl WHERE Assembly='" & Me!NameOfListBox & "'"
Note, if Assembly is defined as numeric then get rid of the single quotes.

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: How do I select a record displayed in a listbox?

(OP)


Thanks PH-

I tried:

CODE --> 

strSQL = "SELECT Assembly,Description FROM M_tbl WHERE Assembly='" & Me!Device & "'"

But it may need a little tweaking...

EOF & BOF are both true because the listbox (Me!Device) is NULL

As a check, I did modify the SQL statement with a value for Assembly from the table and that worked but I really want that value to be set by what is displayed in the list box... Looks like it is getting close.

The rowsource for this list box is:

CODE --> 

SELECT D.Assembly, D.Description
FROM M_tbl AS D
ORDER BY D.Assembly;

RE: How do I select a record displayed in a listbox?

If Me.Device is null it suggests you haven't selected anything or the bound column is null.

Duane
Hook'D on Access
MS Access MVP

RE: How do I select a record displayed in a listbox?

(OP)

Thank you Duane & PHV

I appreciate your help in setting me straight on this...

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close