Two Listboxes need to be Equal 1

[BradB]

I have two lists. List1 has employee names. List2 has ID's. When I click on a name in List1, I want the corresponding ID to also be highlighted. In toher words, I want the listindex of List1 to equal List2. How do I do it?

Right now, I have to click the name and then click the other listbox.

Brad B

 

[braindead2]

why do you have 2 list boxes? why not 1 listbox with 2 columns?

 

[Maquis]

Use a listbox with 2 columns instead of 2 listboxes. Maq B-)
<insert witty signature here>

 

[BradB]

I'm moving data from one listbox to the other. When I move the data from the List1 to List2, It will only carry either the Name OR the ID. I can't get it to move both. I swear I've tried everything. I thought for sure the Column property would solve my problem, but it hasn't. Also, when I move the name over to List 2, the name appears:
--Column Count 1
--Bound Column 1
Brad
Boydston

--Column Count 2
--Bound Column 1
Brad | Boydston

When I include spaces in List1 such as name: ([stf_ln] & &quot;, &quot; & [stf_fn]), List 2 doesn't show the stf_fn unless I use column count 2.

Anyway, I want List2 to show the employee name and save the employee number. If you want code I'll post it.

Brad B

 

[Maquis]

Hmmm, I'm still not picturing what you are trying to do.

Could you instead use just 1 listbox containing the employee's name and id (the id could be a hidden column if you prefer) and then just have a textbox which could contain the selected employee. Maq B-)
<insert witty signature here>

 

[BradB]

Available Selected Fields
LstQue LstStaff
____________ _____________
| | | |
| | -> | |
| | ->> | |
| | | |
| | <- | |
| | <<- | |
|__________| |___________|

Available has a hidden key of StaffID and diplays Staff Name.

Selected needs to have the StaffID transfered(hidden) and have the Staff Name displayed.

I want to store the StaffID in the Selected Listbox to the database. Right now, I can only get EITHER the StaffID transfered or the Staff Name not both.

Here's the code:

Private Sub MoveToLstStaff()
Dim AvailCounter As Integer, SelectedCounter As Integer
Dim AvailList As Integer, SelectedList As Integer
Dim Liststr As String, FoundInList As Integer
Dim X As Variant

AvailList = lstQue.ListCount - 1
SelectedList = lstStaff.ListCount - 1
For AvailCounter = 0 To AvailList
If lstQue.Selected(AvailCounter) = True Then
If IsNull(lstStaff.RowSource) Then
Liststr = lstQue.Column(0, AvailCounter) & &quot;;&quot;
lstStaff.RowSource = Liststr

Else
FoundInList = False
For SelectedCounter = 0 To SelectedList
If lstStaff.Column(0, SelectedCounter) = _
lstQue.Column(0, AvailCounter) Then
FoundInList = True
End If
Next SelectedCounter
If Not FoundInList Then
Liststr = lstStaff.RowSource & _
lstQue.Column(0, AvailCounter) & &quot;;&quot;
lstStaff.RowSource = &quot;&quot;
lstStaff.RowSource = Liststr
End If
End If
End If
Next AvailCounter
For X = 0 To lstStaff.ListCount - 1
lstStaff.Selected(X) = True
Next X
'lstStaff.Selected(0) = True
End Sub

Private Sub SaveToDB_Click()
Dim rstUpdate As DAO.Recordset
Dim dbsUpdate As DAO.Database
'Dim lstQue As ListBox
Dim varSelected As Variant
'Dim strSql As String

Set dbsUpdate = CurrentDb
Set rstUpdate = dbsUpdate.OpenRecordset(&quot;TestListBoxAdd&quot

With rstUpdate
For Each varSelected In Me.lstStaff.ItemsSelected

.AddNew
!StaffID = lstStaff.Column(0, varSelected)
!DateOfSAL = ActDateOfSAL.Value
!DateRec = actDateRec.Value
.Update
Next varSelected
End With

End Sub


Thanks,

Brad Boydston
boydston@bertnash.org

 

[Maquis]

Whew, you're right. This isn't easy if you've never coded it before. (which I haven't) But I like a challenge.

Ok, 1 hour later I finally got this piece of code to work.

If Not FoundInList Then
   Liststr = lstQue.Column(0, AvailCounter) & ";" & _
      lstQue.Column(1, AvailCounter)
   If SelectedList < 0 Then
      lstStaff.RowSource = lstStaff.RowSource & Liststr
   Else
      lstStaff.RowSource = lstStaff.RowSource & &quot;;&quot; & Liststr
   End If
End If

Make sure your 2nd listbox has 2 columns. You can hide the id column if you prefer by setting it's width = 0&quot;. Maq B-)
<insert witty signature here>

 

[BradB]

Excellent work Maquis!

Maquis, try this. Try selecting the first item in the first listbox, and multiselect three or four names. Then click to add them to the second list box. I get jumbled formatting. If I select the first one, add it to the second listbox, and then multiselect three or more names in the first box, it adds them correctly. Any ideas?

 

[Maquis]

The &quot;jumbled formatting&quot; is what took me most of the hour.

I didn't try doing a multi-select for my 1st select, but I understand what is causing the jumbling. Each column and each record is separated by a ';'. So, if you have a multi-column list box in this format:

Name Color
John Red
Bruce Blue
Jane Tan

It's stored in rowsource like this: John;Red;Bruce;Blue;Jane;Tan

Access knows where to put the carriage returns based on the # of columns defined. When I didn't have the &quot;If selectedlist < 0 then&quot; clause in my code I was putting a &quot;;&quot; at the beginning of rowsource and &quot;pushing&quot; all my data over 1 column.

You need to increment selectedlist each time you add a row to the listbox in order for it to maintain an accurate count of the rows, or create another boolean variable which will store whether the 2nd list is empty or not. Maq B-)
<insert witty signature here>

 

[BradB]

&quot;You need to increment selectedlist each time you add a row to the listbox in order for it to maintain an accurate count of the rows, or create another boolean variable which will store whether the 2nd list is empty or not.&quot;

I'm unsure of how to proceed. I don't want to take up anymore of your time, but I'm finished after I get this formatting issue solved. I've played around with it most of yesterday and today, but can't find the solution. Does anyone have and example of what Maquis is talking about?

Thanks,

BradB

 

[Maquis]

Brad, all you need to do is increment SelectedList as you add lines to your listbox. Just add this line of code after your &quot;lstStaff.rowsource = ...&quot; lines:

SelectedList = SelectedList + 1 Maq B-)
<insert witty signature here>

 

[BradB]

It worked! I definitely need to brush up on my VBA.

Can I do the same thing on remove it from a listbox and returning data? When I move the data over, eveything works great. When I remove someone from the second box everything is great. But when you Add someone again, the formatting reverts back to the jumbled mess.

I don't expect anymore help, but I thought I'd give it a try.

Private Sub MoveFromLstStaff()
Dim Liststr As String
Dim SelectedList As Integer, SelectedCounter As Integer
Dim AvailList As Integer

SelectedList = lstStaff.ListCount - 1
Liststr = &quot;&quot;
For SelectedCounter = 0 To SelectedList
If lstStaff.Selected(SelectedCounter) = False Then
Liststr = Liststr + lstStaff.Column(0, SelectedCounter) & &quot;;&quot; & _
lstStaff.Column(1, SelectedCounter) & &quot;;&quot;

End If
Next SelectedCounter
lstStaff.RowSource = &quot;&quot;
lstStaff.RowSource = Liststr

--BradB

 

[Maquis]

Sounds like you're probably leaving an extra ; in your rowsource when you remove records. I would suggest rewriting your remove routine to build the rowsource variable similiar to the add routine. Maq B-)
<insert witty signature here>

 

[BradB]

So freakin close. Now I get a blank line between the entries. It's formatted correctly...sort of. As it is now, the database won't accept null entries. is there a way to delete all the blank entries in the list? If I had more time, I could probably do this right. Here's an example of what it's doing now...

1111 Brad B
2222 Brad C

3333 Brad D
4444 Brad E
5555 Brad F

6666 Brad G


Private Sub MoveFromLstStaff()
Dim Liststr As String
Dim SelectedList As Integer, SelectedCounter As Integer
Dim AvailList As Integer

SelectedList = lstStaff.ListCount - 1
Liststr = &quot;&quot;
For SelectedCounter = 0 To SelectedList
If lstStaff.Selected(SelectedCounter) = False Then
Liststr = Liststr + lstStaff.Column(0, SelectedCounter) & &quot;;&quot; & _
lstStaff.Column(1, SelectedCounter) & &quot;;&quot;
SelectedList = SelectedList + 1
End If
Next SelectedCounter
lstStaff.RowSource = &quot;&quot;
lstStaff.RowSource = Liststr + &quot;;&quot;

 

[tamalou]

Could try a reference form (which is actually a subform) that looks up the name and lists corresponding ID - won't show other values/IDs though.