Updating Selected Records on a Form

[SonOfZeus]

Is there a way of using something such as multiselect on a for that contains all the details of an invoice. What the user wishes is to pick records in the detail area and then mass change a flag on just the selected ones.

 

[FoxProProgrammer]

Yes, I'd suggest using a multiselect list box for that purpose. You can loop through the collection, and check the Selected property to see if an item is selected. If it is selected, you would set the flag, which I presume is a field in the table? The Listcount property contains the number of items in the list, so you would loop from 0 to listcount - 1

Example:

Assume that your list box is named lstExample, the table is named tblExample, and the field in the table that contains the flag is named fldFlag.

Dim i as Integer
Dim db as DAO.Database
Dim rs as DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset ("tblExample", dbOpenDynaset, dbReadOnly)

For i = 0 To lstExample.Listcount - 1
If lstExample.Selected(i) = True Then
rs.FindFirst <expression that identifies the record to look for>
If rs.NoMatch = &quot;False&quot; Then
rs.Edit
rs!fldFlag = True
rs.Update
Else
MsgBox &quot;Record not found.&quot; ' We shouldn't get here.
End If
End If
Next

rs.close
db.close


dz

 

[SonOfZeus]

I'm looking for a way besides a listbox, this is due to the fact it's not simply a list in the form, but all the details for a project timeline. Want to be able to select multiple details and set the group flag for these to the same

 

[FoxProProgrammer]

Perhaps I don't understand what you want to do. A list box can have multiple columns, so you could display multiple details in a list box, and still make it multiselect. This works ok unless there are so many columns in the list box that it scrolls off the screen, but even that is possible because Access will place scroll bars on the list. A list box is the easiest way that I know of to select multiple rows because the functionality is built into the control, and there are properties to assist you with determining which rows are selected.

 

[SonOfZeus]

By putting the detail record in a list box, I would lose the ability to adjust some of the other fields in the record if they are found inaccurate.

i.e. Detail Record such as

Item# #Purchased #Received Cost/Item GLGroup

The user may need to change one of the other fields, in addition to the fact that they may be building a variety of items and thus the GLGroup isn't fixed based on the Item#. Each detail could be put in any GLGroup, dependent on what the use of the product is. Thus they wish to have the ability to mass change the gl group and adjust parts of the detail record.

Sorry is so confusing

 

[FoxProProgrammer]

Perhaps I don't understand what you are trying to do, but it sounds like you might be trying to do too much with a single form or control. You might consider using a list box to allow the user to select multiple items, and provide a button that updates the flag. You could also provide an Edit button that brings up another form where the user can edit the fields for the records that they select instead of editing them directly in the list box. To do this, you could place the records from the list box into a temporary table by way of a make table query, and use that table as the control source for the Edit form. Then the user could cycle through the records that they want to edit, and press a save button after the change each record. This is just another idea that you might not have considered.

dz