copy multiple lstbx selections to another lstbox etc

  • Thread starter Thread starter perryclisbee via AccessMonster.com
  • Start date Start date
P

perryclisbee via AccessMonster.com

(see code below as a reference)
I have a list of facilities in a listbox [lstFacAdd] on a form
[frm_credreqdetails], pulling from a source table [tbl_cr_facilities]. I
currently have it set up so that when I double click on one of those
facilities, it adds that facility to another listbox on another form [Forms!
frm_CR_ViewFacilities!lstFacilities].

What I want to do is allow multiple selections to be hightlighted from the
[lstFacAdd] listbox, and then be simultaneously copied over to the other
listbox. maybe a coded button that will take the hightlighted selections and
copy them all over at the same time.

Is there any way to do these two things?

thanks,

Perry

CODE:


Dim db As Database
Dim rs As Recordset


Private Sub lstFacAdd_DblClick(Cancel As Integer)
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70

Set db = CurrentDb
Set rs = db.OpenRecordset("TBL_CR_FACILITIES")

rs.AddNew
rs("FK_CR_ID") = Forms!frm_Credreqdetails!ID.Value
rs("FAC_NO") = lstFacAdd
rs.Update

rs.Close
db.Close

Set rs = Nothing
Set db = Nothing
Forms!frm_CR_ViewFacilities!lstFacilities.Requery
End Sub
 
perryclisbee via AccessMonster.com said:
(see code below as a reference)
I have a list of facilities in a listbox [lstFacAdd] on a form
[frm_credreqdetails], pulling from a source table [tbl_cr_facilities]. I
currently have it set up so that when I double click on one of those
facilities, it adds that facility to another listbox on another form
[Forms!
frm_CR_ViewFacilities!lstFacilities].

What I want to do is allow multiple selections to be hightlighted from the
[lstFacAdd] listbox, and then be simultaneously copied over to the other
listbox. maybe a coded button that will take the hightlighted selections
and
copy them all over at the same time.

Is there any way to do these two things?

thanks,

Perry

CODE:


Dim db As Database
Dim rs As Recordset


Private Sub lstFacAdd_DblClick(Cancel As Integer)
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70

Set db = CurrentDb
Set rs = db.OpenRecordset("TBL_CR_FACILITIES")

rs.AddNew
rs("FK_CR_ID") = Forms!frm_Credreqdetails!ID.Value
rs("FAC_NO") = lstFacAdd
rs.Update

rs.Close
db.Close

Set rs = Nothing
Set db = Nothing
Forms!frm_CR_ViewFacilities!lstFacilities.Requery
End Sub
Click to expand...

Sure is. First set the Multiselect property of the listbox control to either
Simple or Extended, depending on how you want your users to make multiple
selections. Then, in the Click event of the button, use the ItemsSelected
property of the listbox to loop through the selected items:

Dim db As Database
Dim rs As Recordset
Dim vnt as Variant
Set db = CurrentDb
Set rs = db.OpenRecordset("TBL_CR_FACILITIES")
For Each vnt in lstFacAdd.ItemsSelected
rs.AddNew
rs("FK_CR_ID") = Forms!frm_Credreqdetails!ID.Value
rs("FAC_NO") = lstFacAdd.ItemData(vnt)
rs.Update
Next
rs.Close
Set rs = Nothing
Set db = Nothing
Forms!frm_CR_ViewFacilities!lstFacilities.Requery

Carl Rapson
 
It works perfectly! Thanks for the help.

Perry

Carl said:
(see code below as a reference)
I have a list of facilities in a listbox [lstFacAdd] on a form
Click to expand...
[quoted text clipped - 40 lines]
Forms!frm_CR_ViewFacilities!lstFacilities.Requery
End Sub
Click to expand...

Sure is. First set the Multiselect property of the listbox control to either
Simple or Extended, depending on how you want your users to make multiple
selections. Then, in the Click event of the button, use the ItemsSelected
property of the listbox to loop through the selected items:

Dim db As Database
Dim rs As Recordset
Dim vnt as Variant
Set db = CurrentDb
Set rs = db.OpenRecordset("TBL_CR_FACILITIES")
For Each vnt in lstFacAdd.ItemsSelected
rs.AddNew
rs("FK_CR_ID") = Forms!frm_Credreqdetails!ID.Value
rs("FAC_NO") = lstFacAdd.ItemData(vnt)
rs.Update
Next
rs.Close
Set rs = Nothing
Set db = Nothing
Forms!frm_CR_ViewFacilities!lstFacilities.Requery

Carl Rapson
Click to expand...
 
I am trying to use your code and having issues
I have a table called tblECR and form frmECR.
I have an unbound listbox called ECRNumber pulling the ECR numbers from
tblECR.
I have a listbox called ECRsSelected on frmECN. The commandbutton to move
the multiselected items doesn't work. Here is the code:


Private Sub ListAdd_Click()

Dim db As Database
Dim rs As Recordset
Dim vnt As Variant
Set db = CurrentDb
Set rs = db.OpenRecordset("tblECR")
For Each vnt In ECRNumber.ItemsSelected
rs.AddNew
rs("ECRNumber") = Forms!frmECR!ECRNumber.Value
rs("ECRNumber") = ECRNumber.ItemData(vnt)
rs.Update
Next
rs.Close
Set rs = Nothing
Set db = Nothing
Forms!frmECN!ECRsSelected.Requery

End Sub

Where is the problem? Urgent please.
 
Hi,

What errors messages are you having?

1. Are you trying to insert a same record? Inserting same records into the
same Table is a bad idea.
2. Are you trying to populate the "ECRsSelected" listbox with "ECRNumber"?
not inserting records?
 
It's just not working. I am getting error messages that something is wrong
with the code.

You see my code below. Let me try to clarify the fields.

On frmECN I have an unbound listbox called ECRNumbers that pulls the data
from tblECR fieldname ECRNo. On frmECN I have a listbox called ECRsSelected.
I want the commandbutton command below to take the items I select from the
unbound listbox and "move" them to the new listbox ECRsSelected and store it
in that fieldname.

Hope that makes sense.

Ben
 
Hi,

If you just want to populate the list box than, try this

Watch for word wrap!

Private Sub ListAdd_Click()

Dim strList As String

For Each vnt In ECRNumber.ItemSelected ‘assume this for loop works

strList = strList & ECRNumber.Column(0) & “,†‘assume 0 as the column
ECRNumber

Next vnt

strList = Left(strList, Len(strList) - 1) ‘remove the last comma

Me.ECRsSelected.RowSource = “SELECT yourlistoffieldnamesHere FROM tblECR
WHERE ECRNumber IN (“ & strList &â€)â€

End Sub
 
Ha. It doesn't really work. It actually takes the SELECT command in the
code and populates the table with that. It isn't taking the ECRs listed in
the unbound listbox and copying multiple values to the ECRsSelected field.
 
Hi,

That's what it's doing. It does not populate the table (tblECR), which is not
the correct thing to do. By populating the same records into the same table,
you are breaking the database normalization rules.

If you want to fill records from tblECR to another table somewhere, what is
that table name?

What the code does is to take the selected rows in the listbox "ECRNumber"
and populate the listbox "ECRsSelected". It doesn't fill the selected records
into the same table.

Bound listbox only fill the data into the Form's RowSource property. It
doesn't fill selected records into the same table.

Hope you'll understood the purpose of listbox.
 
Back
Top