asked on

transition from combobox to listbox

I used to have a combobox in the attached form image that had a list of all the students in a combobox. However, with the need to select more than one student, I had to move to a feature that allows multiple selections. Hence, I chose a listbox. Do you have any better suggestions?

As a result of using the listbox, I need to figure out how to capture the data and send it to the database. Here is the code below I use to add a person from the previous combobox. How do I now save the multiple records for each student in a listbox?

        esql = "select * from tblOrgHours"
        rec.Open (esql), conn, adOpenDynamic, adLockOptimistic
        rec.AddNew
        
        rec!Agency = Combo1
        rec!AgencyID = AgencyID
        rec!Program = Combo4
        rec!ActivityID = ActivityID
        rec!RegID = Combo3
        rec!ActivityDate = DTPicker1.Value
        rec!Hours = Text2
        rec!Notes = Text1
        rec!Fiscal = Fiscal
        rec!EntryTime = ServerTime
        
        If Not rec.EOF Then rec.MoveNext
        rec.Close

Open in new window

example.JPG

Dale Fye

Listboxes have an ItemsSelected property, which identifies which items in the list have been selected. To loop through that collection, you would need to use code similar to:

    Dim varItem as variant

    esql = "select * from tblOrgHours"
    rec.Open (esql), conn, adOpenDynamic, adLockOptimistic

    For Each varItem In Me.yourListName.ItemsSelected

        rec.AddNew
        rec!Agency = Combo1
        rec!AgencyID = AgencyID
        rec!Program = Combo4
        rec!ActivityID = ActivityID
        rec!RegID = yourListName.Column(0, varItem)  'not necessarily the correct line for this.
        rec!ActivityDate = DTPicker1.Value
        rec!Hours = Text2
        rec!Notes = Text1
        rec!Fiscal = Fiscal
        rec!EntryTime = ServerTime
    
    Next varItem

    rec.Close

Open in new window

Somewhere in there you would probably have a reference to one or more columns from the list. Remember, the list is zero based

al4629740

ASKER

For line 3
For Each varItem In Me.List1.ItemsSelected

ItemsSelected has an error of Method or data member not found

al4629740

ASKER

Any ideas what it could be?

Dale Fye

My bad, I thought this was an Access question. What version of Visual Basic are you using?

I'm assuming your list is called "List1"

You should probably institute some form of naming convention so that your controls have more meaning, Combo1, Combo4, Text1, Text2 have no meaning. There are several common naming conventions, I generally use something similar to what is described here.

al4629740

ASKER

Thank you for the suggestion.

I am using vb6

Dale Fye

if you don't get any feedback this evening from any other experts, you might want to request attention from the moderators. You might also want to add a tag to your post (don't know whether you can do that at this point) to indicate you are working with VB6.

al4629740

ASKER

Ok thanks

aikimark

Starting with Dale's code, the VB6 version should look something like this:

    Dim lngLoop as Long

    esql = "select * from tblOrgHours"
    rec.Open (esql), conn, adOpenDynamic, adLockOptimistic

    For lngLoop = 0 to Me.yourListName.ListCount - 1
        If Me.yourListName(lngLoop).Selected Then
		rec.AddNew
		        rec!Agency = Combo1
		        rec!AgencyID = AgencyID
			rec!Program = Combo4
			rec!ActivityID = ActivityID
			rec!RegID = yourListName(lngLoop)
			rec!ActivityDate = DTPicker1.Value
			rec!Hours = Text2
			rec!Notes = Text1
			rec!Fiscal = Fiscal
			rec!EntryTime = ServerTime
		rec.Update
        End If
    Next

    rec.Close

Open in new window

al4629740

ASKER

On Line7 I get an error

Compile error
Wrong number of arguments or invalid property assignment

al4629740

ASKER

    Dim lngLoop as Long

    esql = "select * from tblOrgHours"
    rec.Open (esql), conn, adOpenDynamic, adLockOptimistic

    For lngLoop = 0 to Me.yourListName.ListCount - 1
        If Me.yourListName.Selected(lngLoop)  Then
		rec.AddNew
		        rec!Agency = Combo1
		        rec!AgencyID = AgencyID
			rec!Program = Combo4
			rec!ActivityID = ActivityID
			rec!RegID = yourListName.List(lngLoop)
			rec!ActivityDate = DTPicker1.Value
			rec!Hours = Text2
			rec!Notes = Text1
			rec!Fiscal = Fiscal
			rec!EntryTime = ServerTime
		rec.Update
        End If
    Next

    rec.Close

Open in new window

ASKER CERTIFIED SOLUTION

aikimark

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

al4629740

ASKER

I corrected line 7 and 13 on my solution. Thanks for giving me some guidance.