Update records from a listbox populated by a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Firstly I hope I am posting this in correct forum ...... please advise if I
am not.

I have a table with 6 fields. I have a query which outputs records from that
table where one of the fields has a null value. I have a form with a listbox
populated by the records from the query, a drop down box populated with
values from another table and a command button. The listbox has "multi
select" set to extended. I want to code the command button so that it will
populate the record(s) in the query which have the null value with the value
selected from the drop-down box. The record(s) in question being those
selected in multi-select listbox. I am very familiar with the concepts of vba
but hopelessly lost as to how to apply that knowlwedge to the structure of
the objects I'm trying to work with in Access. Any help or guidance would be
appreciated

Regards

Michael Bond
 
Michael,

In the aerocode below,, I'll assume:

Table name: MyTable
Field to be updated in MyTable, name: FieldX
Listbox name: MyList, bound column is PK in the table
Combo box name: MyCombo

The code would look something like:

Dim db as DAO.Database
Dim rst as DAO.Recordset
If Me.MyList.ItemsSelected.Count = 0 Then
MsgBox "No items selected. Update operation aborted."
Exit Sub
End If
Set db = CurrentDb
set rst = db.OpenRecordset("MyTable")
rst.Index = "PrimaryKey"
For Each itm in Me.MyList.ItemsSelected
rst.Seek "=", Me.MyList.ItemData(itm)
rst.Edit
rst.FieldX = Me.MyCombo
rst.Update
Next
rst.Close
Set rst = Nothing
Set db = Nothing

Note: To run this code, an appropriate DAO Object Library reference is
required. While in the VB editor window, go to menu item Tools >
References; check if a Microsoft DAO reference is present among the ones
checked at the top of the list. If not, scroll down to find the
appropriate Microsoft DAO X.X Object Library reference and check it. The
appropriate reference is DAO 3.51 for A97, DAO 3.6 for A2K or later.

HTH,
Nikos
 
Nikos

thanks very much for this ...... it's just what I needed to help me
understand the objects I'm working with. I've used vba in Excel, VB6 and
VB.NET but this was my first attempt to enter the world of the mysteries of
database objects

Appreciate the help

Regards

Michael Bond
 
Back
Top