Popualting a List Box from an Array

[rzielins]

In my form, I have 4 key controls, one text box to allow the user to add a Product description, one command button to kick off some validation specific VBA, a list box that is initially empty and a batch update button.

The user has the option of creating 99 Product descriptions before hitting a Batch update button to commit the data.

I wish to incremently populate a list box to show the User the text they just entered before they enter the text for the next Product. So if 5 Product descriptions were entered, the list box would start with 1 entry and then grow to 5 entries. If the user was happy with the entries, they would then hit the Batch update button to commit the data to the database.

I have tried using the help in MS Access 97 on RowSourceType Property but am experiencing difficulties deciphering the on-line help instructions.

Conceptually, my objective is straightforward, however practically it is proving far less so. Any help would be appreciated.

 

[charityg]

Okay, here's how you do it.

On the form that contains the all of your controls

Dim txtSource as string

in the declarations section of the code (at the top, under Option Explicit)

Then set your Listbox.RowSourceType Property to ValueList

In the AfterUpdate event of your textbox where the description will be entered code this:

Private Sub Text2_AfterUpdate()

txtsource = txtsource & Text2 & "; "
lstName.RowSource = txtsource
lsnName.Requery
End Sub

(remember to change names).

In the onClick event of the Batch button, you will cycle through the list and update a recordset with the descriptions with

Dim varPosition As Variant
Dim rst As Recordset
Dim db As Database
Set db = CurrentDb
Set rst = db.OpenRecordset("tblProducts")
For varPosition = 0 To lstName.ListCount - 2
With rst
.AddNew
!Description = lstName.ItemData(varPosition)
.Update

End With
Next varPosition
txtsource = ""
lstName.requery


The last two lines will "reset" the listbox.

Hope this works for you, let me know if you have any problems.

~Charity

 

[rzielins]

Thanks, all is working fine.