Help - Populate Data from List Box to Form

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

Guest

I'm trying to set up a form with a list box to have a user select a few lines
of data. Once all of the selections are made, I'd like to append this data
to a table, which feeds a "data entry" form. Does anyone have any ideas?

The other issue is that all selections need to be appended to one row of
data in the table. For example, selection 1 goes to Field 1, selection 2
goes to Field 2.

Thank you.
 
To answer the first Question:
Private Sub Button_Click()
Dim prm_MyCtl As Control
Dim VarItm
Dim DBS As Database
Dim rst As Recordset

Set prm_MyCtl = Me![MyList]

If prm_MyCtl.ItemsSelected.Count = 0 Then
Beep
MsgBox "No Item Selected", 48
Exit Sub
End If
Set DBS = CodeDb
Set rst = DBS.openrecordset("Select * From MyTable")

' run through all selected items
For Each VarItm In prm_MyCtl.ItemsSelected
rst.addnew
rst!FieldName = prm_MyCtl.Column(0) ' just choose the location of the
field in the list start with 0
rst.update
Next

End Sub
====================================
To answer the second Question:
Private Sub Button_Click()
Dim prm_MyCtl As Control
Dim VarItm, MyCount as integer
Dim DBS As Database
Dim rst As Recordset

Set prm_MyCtl = Me![MyList]
MyCount = 1
If prm_MyCtl.ItemsSelected.Count = 0 Then
Beep
MsgBox "No Item Selected", 48
Exit Sub
End If

Set DBS = CodeDb
Set rst = DBS.openrecordset("Select * From MyTable")
rst.addnew
' run through all selected items
For Each VarItm In prm_MyCtl.ItemsSelected

rst("FieldName" & MyCount) = prm_MyCtl.Column(0) ' just choose the
location of the field in the list start with 0
MyCount =MyCount +1 ' To get the next field num
Next
rst.update
End Sub
 
The first part is not difficult, but the second gives me pause.
It's not usually wise to have a data structure with the same kind of data in
multiple fields. What happens if the user selects the wrong number of items
in the listbox?
 
Thanks so much! I was on the right track but was missing a few simple
things. This really helped. However, I am having one issue:

When I select multiple records to append to a table, it is only appending
the data in the last record for each instance. So, if the data I'm selecting
is "Data1","Data2","Data3" in my list box, "Data3" is being appended to the
table 3 times. Any idea why? The code I'm using is pasted below:

Private Sub Command4_Click()
Dim prm_MyCtl As Control
Dim VarItm As Variant
Dim DB As Database
Dim rs As Recordset
Dim strDelSQL As String

Set DB = CurrentDb

'Unload the records in the Temp table
strDelSQL = " DELETE tblTemp.Trng_ID FROM tblTemp"
DB.Execute strDelSQL

Set prm_MyCtl = Me!List0

If prm_MyCtl.ItemsSelected.Count = 0 Then
Beep
MsgBox "No Item Selected", 48
Exit Sub
ElseIf prm_MyCtl.ItemsSelected.Count > 5 Then
Beep
MsgBox "You may only select up to 5 sessions!! Please try again.", 48
Exit Sub
End If

Set rs = DB.OpenRecordset("Select * From tblTemp")

For Each VarItm In prm_MyCtl.ItemsSelected
rs.AddNew
rs!Trng_ID = prm_MyCtl.Column(3)
rs.Update
Next

End Sub

Ofer said:
To answer the first Question:
Private Sub Button_Click()
Dim prm_MyCtl As Control
Dim VarItm
Dim DBS As Database
Dim rst As Recordset

Set prm_MyCtl = Me![MyList]

If prm_MyCtl.ItemsSelected.Count = 0 Then
Beep
MsgBox "No Item Selected", 48
Exit Sub
End If
Set DBS = CodeDb
Set rst = DBS.openrecordset("Select * From MyTable")

' run through all selected items
For Each VarItm In prm_MyCtl.ItemsSelected
rst.addnew
rst!FieldName = prm_MyCtl.Column(0) ' just choose the location of the
field in the list start with 0
rst.update
Next

End Sub
====================================
To answer the second Question:
Private Sub Button_Click()
Dim prm_MyCtl As Control
Dim VarItm, MyCount as integer
Dim DBS As Database
Dim rst As Recordset

Set prm_MyCtl = Me![MyList]
MyCount = 1
If prm_MyCtl.ItemsSelected.Count = 0 Then
Beep
MsgBox "No Item Selected", 48
Exit Sub
End If

Set DBS = CodeDb
Set rst = DBS.openrecordset("Select * From MyTable")
rst.addnew
' run through all selected items
For Each VarItm In prm_MyCtl.ItemsSelected

rst("FieldName" & MyCount) = prm_MyCtl.Column(0) ' just choose the
location of the field in the list start with 0
MyCount =MyCount +1 ' To get the next field num
Next
rst.update
End Sub



JW said:
I'm trying to set up a form with a list box to have a user select a few lines
of data. Once all of the selections are made, I'd like to append this data
to a table, which feeds a "data entry" form. Does anyone have any ideas?

The other issue is that all selections need to be appended to one row of
data in the table. For example, selection 1 goes to Field 1, selection 2
goes to Field 2.

Thank you.
Click to expand...
Click to expand...
 
I put in some error trapping that says :

If prm_MyCtl.ItemsSelected.Count = 0 Then
Beep
MsgBox "No Item Selected", 48
Exit Sub
ElseIf prm_MyCtl.ItemsSelected.Count > 5 Then
Beep
MsgBox "You may only select up to 5 sessions!! Please try again.", 48
Exit Sub
End If

So, if you select more than 5 sessions to load to the form, the code will
error out and force the user to enter the correct number of sessions. I
believe this should work out OK. Do you have any other ideas?
 
If you still waiting for the answer then try that
For Each VarItm In prm_MyCtl.ItemsSelected
rs.AddNew
rs!Trng_ID = prm_MyCtl.Column(3, VarItm )
rs.Update
Next


JW said:
Thanks so much! I was on the right track but was missing a few simple
things. This really helped. However, I am having one issue:

When I select multiple records to append to a table, it is only appending
the data in the last record for each instance. So, if the data I'm selecting
is "Data1","Data2","Data3" in my list box, "Data3" is being appended to the
table 3 times. Any idea why? The code I'm using is pasted below:

Private Sub Command4_Click()
Dim prm_MyCtl As Control
Dim VarItm As Variant
Dim DB As Database
Dim rs As Recordset
Dim strDelSQL As String

Set DB = CurrentDb

'Unload the records in the Temp table
strDelSQL = " DELETE tblTemp.Trng_ID FROM tblTemp"
DB.Execute strDelSQL

Set prm_MyCtl = Me!List0

If prm_MyCtl.ItemsSelected.Count = 0 Then
Beep
MsgBox "No Item Selected", 48
Exit Sub
ElseIf prm_MyCtl.ItemsSelected.Count > 5 Then
Beep
MsgBox "You may only select up to 5 sessions!! Please try again.", 48
Exit Sub
End If

Set rs = DB.OpenRecordset("Select * From tblTemp")

For Each VarItm In prm_MyCtl.ItemsSelected
rs.AddNew
rs!Trng_ID = prm_MyCtl.Column(3)
rs.Update
Next

End Sub

Ofer said:
To answer the first Question:
Private Sub Button_Click()
Dim prm_MyCtl As Control
Dim VarItm
Dim DBS As Database
Dim rst As Recordset

Set prm_MyCtl = Me![MyList]

If prm_MyCtl.ItemsSelected.Count = 0 Then
Beep
MsgBox "No Item Selected", 48
Exit Sub
End If
Set DBS = CodeDb
Set rst = DBS.openrecordset("Select * From MyTable")

' run through all selected items
For Each VarItm In prm_MyCtl.ItemsSelected
rst.addnew
rst!FieldName = prm_MyCtl.Column(0) ' just choose the location of the
field in the list start with 0
rst.update
Next

End Sub
====================================
To answer the second Question:
Private Sub Button_Click()
Dim prm_MyCtl As Control
Dim VarItm, MyCount as integer
Dim DBS As Database
Dim rst As Recordset

Set prm_MyCtl = Me![MyList]
MyCount = 1
If prm_MyCtl.ItemsSelected.Count = 0 Then
Beep
MsgBox "No Item Selected", 48
Exit Sub
End If

Set DBS = CodeDb
Set rst = DBS.openrecordset("Select * From MyTable")
rst.addnew
' run through all selected items
For Each VarItm In prm_MyCtl.ItemsSelected

rst("FieldName" & MyCount) = prm_MyCtl.Column(0) ' just choose the
location of the field in the list start with 0
MyCount =MyCount +1 ' To get the next field num
Next
rst.update
End Sub



JW said:
I'm trying to set up a form with a list box to have a user select a few lines
of data. Once all of the selections are made, I'd like to append this data
to a table, which feeds a "data entry" form. Does anyone have any ideas?

The other issue is that all selections need to be appended to one row of
data in the table. For example, selection 1 goes to Field 1, selection 2
goes to Field 2.

Thank you.
Click to expand...
Click to expand...
Click to expand...
 
Never mind. I got it. All I did was add the row reference to the
prm_myCtl.Column piece. So, the code looks like the following when the
recordset is written:

For Each VarItm In prm_MyCtl.ItemsSelected
rs.AddNew
rs!Trng_ID = prm_MyCtl.Column(3, VarItm)
rs.Update
Next VarItm

This seems to be working. Thanks for all your help.



JW said:
Thanks so much! I was on the right track but was missing a few simple
things. This really helped. However, I am having one issue:

When I select multiple records to append to a table, it is only appending
the data in the last record for each instance. So, if the data I'm selecting
is "Data1","Data2","Data3" in my list box, "Data3" is being appended to the
table 3 times. Any idea why? The code I'm using is pasted below:

Private Sub Command4_Click()
Dim prm_MyCtl As Control
Dim VarItm As Variant
Dim DB As Database
Dim rs As Recordset
Dim strDelSQL As String

Set DB = CurrentDb

'Unload the records in the Temp table
strDelSQL = " DELETE tblTemp.Trng_ID FROM tblTemp"
DB.Execute strDelSQL

Set prm_MyCtl = Me!List0

If prm_MyCtl.ItemsSelected.Count = 0 Then
Beep
MsgBox "No Item Selected", 48
Exit Sub
ElseIf prm_MyCtl.ItemsSelected.Count > 5 Then
Beep
MsgBox "You may only select up to 5 sessions!! Please try again.", 48
Exit Sub
End If

Set rs = DB.OpenRecordset("Select * From tblTemp")

For Each VarItm In prm_MyCtl.ItemsSelected
rs.AddNew
rs!Trng_ID = prm_MyCtl.Column(3)
rs.Update
Next

End Sub

Ofer said:
To answer the first Question:
Private Sub Button_Click()
Dim prm_MyCtl As Control
Dim VarItm
Dim DBS As Database
Dim rst As Recordset

Set prm_MyCtl = Me![MyList]

If prm_MyCtl.ItemsSelected.Count = 0 Then
Beep
MsgBox "No Item Selected", 48
Exit Sub
End If
Set DBS = CodeDb
Set rst = DBS.openrecordset("Select * From MyTable")

' run through all selected items
For Each VarItm In prm_MyCtl.ItemsSelected
rst.addnew
rst!FieldName = prm_MyCtl.Column(0) ' just choose the location of the
field in the list start with 0
rst.update
Next

End Sub
====================================
To answer the second Question:
Private Sub Button_Click()
Dim prm_MyCtl As Control
Dim VarItm, MyCount as integer
Dim DBS As Database
Dim rst As Recordset

Set prm_MyCtl = Me![MyList]
MyCount = 1
If prm_MyCtl.ItemsSelected.Count = 0 Then
Beep
MsgBox "No Item Selected", 48
Exit Sub
End If

Set DBS = CodeDb
Set rst = DBS.openrecordset("Select * From MyTable")
rst.addnew
' run through all selected items
For Each VarItm In prm_MyCtl.ItemsSelected

rst("FieldName" & MyCount) = prm_MyCtl.Column(0) ' just choose the
location of the field in the list start with 0
MyCount =MyCount +1 ' To get the next field num
Next
rst.update
End Sub



JW said:
I'm trying to set up a form with a list box to have a user select a few lines
of data. Once all of the selections are made, I'd like to append this data
to a table, which feeds a "data entry" form. Does anyone have any ideas?

The other issue is that all selections need to be appended to one row of
data in the table. For example, selection 1 goes to Field 1, selection 2
goes to Field 2.

Thank you.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top