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

[robojeff]

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:

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

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

 

[dhookom]

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

 

[robojeff]

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?

 

[dhookom]

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

 

[robojeff]

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:

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

 

[dhookom]

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:

    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

 

[robojeff]

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

 

[PHV]

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

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
FAQ181-2886

 

[robojeff]

Thanks PH-

I tried:

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:

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

 

[dhookom]

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

 

[PHV]

...
If IsNull(Me!Device) Then
  MsgBox "Please select an Assembly"
  Exit Sub
End If
strSQL = "SELECT Assembly,Description FROM M_tbl WHERE Assembly='" & Me!Device & "'"
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[robojeff]

Thank you Duane & PHV

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