Access 2000 ListBox Multiselect Question

[BennyWong]

Hello All,
I have a data entry form with a listbox that is set to simple which allows for multiselect in the listbox. I understand that from a recent response that there are two commands that needs to be used to captures the items selected. They are:

'Create new record
DoCmd.GoToRecord acDataForm, "formname", acNewRec

'Save new record
DoCmd.RunCommand acCmdSaveRecord

I was told to put these two commands in a loop to save each items selected. My question is where do I create the loop and statement? I'm familiar with macros but no in visual basic. Any help or advice in this matter is greatly appreciated. Thanks for your time and patience.

 

[jebry]

Hi!

What are you trying to save and how? Are you trying to make a new record for each item selected in the list box? Is there other data on the form which need to be saved with each item selected? Does the list box have more than one column which contains data that needs to be saved?

Jeff Bridgham
bridgham@purdue.edu

 

[BennyWong]

Hello Jeff,
Thanks for responding. I have a data entry form in which the user tabs in the various fields until they reach this listbox that has the multiselect option in which the user can select multiple items in the listbox. When the user completes the data entry form it is suppose to save all the fields that has been entered including the the multiselect listbox entries. The listbox has only two columns but only one of the column will be used to save to the table. The listbox first column is an autonumber field (pk) and the second column is a textfield which the user selects to update the table on the many-side. I hope this clears up what you are asking and any help or advice is appreciated. Thanks for your time.

 

[jebry]

Hi!

If I understand this correctly you can try it like this(Put this code in the click event of a command button):

Dim varRow As Variant
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("YourTable", dbOpenDynaset)

For Each varRow In Me!YourListBox.ItemsSelected
rst.AddNew
rst.Field1 = Me!YourListBox.Column(1, varRow)
rst.ForiegnKey = Me!YourListBox.Column(0, varRow)
rst.Field2 = Me!YourTextBox.Value
rst.Fiels3 = Me!YourNextBox.Value
etc.
rst.Update
Next varRow

Set rst = Nothing

You will need to use your field, control and table names as they appear in you Db. This will add one record in the table for each item selected in the list box using the other fields that the user added in the form.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[BennyWong]

Hi Jeff,
Thanks for responding. Please understand my limited knowledge of VBA but am learning currently, what I don't understand is why would I need a command button to trigger the save each item in the listbox being selected. I thought it would be like select the multiple items and in event module such as afterupdate or before update or something of that sort to trigger the data to populate the destination table? Thanks for your response in advance.

 

[jebry]

Hi!

When you have set a list box for multiselect the value of the list box never changes(it remains null at all times). Since it never changes events that fire based on the change of value (Change, Before and After Update) do not get activated. That is why I recommend a command button. Usually the intent of the set up is pretty clear to the user.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[EarS]

If you wanted to you could use the on click event of the list box. Use the same code but this way it would get fired every time another is selected. We use this method to build strings which are passed. Not for sure if this will help but its a thought.

 

[BennyWong]

Hi Jeff,
I entered the code you supplied in the button on the on-click event and I received a compile error with the error as "user-defined type not defined.

Private Sub Command74_Click()
Dim varRow As Variant
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset(tblclient, dbOpenDynaset)
For Each varRow In Me!List72.ItemsSelected
rst.AddNew
rst.Field1 = Me!List72.Column(1, varRow)
rst.foreignkey = Me!List72.Column(0, varRow)
rst.Field2 = Me!List72.Value
rst.Update
Next varRow
Set rst = Nothing
End Sub

The error was on line Dim rst As DAO.Recordset

Thanks for your response.

 

[EarS]

Make sure that you have the "Microsoft DAO Object Library" selected in the reference window. That is what is causing the compile error.

 

[BennyWong]

Hello EarS,
Thanks for responding. Where do I find the "Microsoft DAO Object Library"? Is it in VBA? I can't seem to locate it. I'm a novice in VBA. Thanks for your time.

 

[jebry]

Hi!

In your VB window select Tools/References. In the window that pops up scroll down until you find Microsoft DAO 3.6 Object Library. Check mark the box beside it and you should be good to go.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[teenmonk]

I'm trying the above code out and am receiving the following error:

"Can't Assign To Read-Only Property"

The error occurs at the line:

rst.MODEL = Me!lstMODELS.Column(1, varRow)

I can't figure out why. Would someone please help. Thanks.