After updating listbox, populate another 2

[NRK]

I am trying to create a form (Query by form) that will allow users to first select one or many records from listbox (Category field). Then, on After Updated of that listbox, I want my second listbox to populate with all the records that are associated with the selected Category or Categories.

My logic is that I should create a data string (from the first listbox) and then plug that string into the criteria for the query that the second listbox references.

I am unsure if I have built my code wrong or if this is not possible. As yet, I have been unable to find much help on this topic. I hope that you will be able to help...

 

[papparuff]

If you are going to use Multiselect then you will need a button to run the procedure in. Each time you select an item in a listbox, the After Update event is triggered; so the After Update event cannot be used for a multiselect listbox.

Here's your code

Private Sub cmdProcess_Click()
Dim ctl As Control
Dim varItm As Variant
Dim intI As Integer
Dim strRcd As String
Dim lngRcd As Long
Dim intCount As Integer

Set ctl = List0
For Each varItm In ctl.ItemsSelected
intCount = intCount + 1
For intI = 0 To ctl.ColumnCount - 1
If intI = 0 Then
lngRcd = ctl.Column(intI, varItm)
Else
strRcd = ctl.Column(intI, varItm)
End If
Next intI
List2.AddItem Item:=lngRcd & ";" & strRcd ', Index:=intCount - 1
Debug.Print
Next varItm

End Sub
John Ruff - The Eternal Optimist

 

[NRK]

John,
Thanks for the code...I copied it into my form, but it is being stopped on the "List2.AddItem Item" line. It is a Compile Error stating that the "method or data member not found." I am unsure why this is occurring and wondered if you had any advice.

Also, why are you leaving the "Index:=intCount - 1" line as a comment?

I really appreciate your advice and code. Thanks so much!

 

[papparuff]

Hmmm,

The code works perfectly in my test form. Change the line to this,

List2.AddItem lngRcd & ";" & strRcd

I remarked out ', Index:=intCount - 1 because I decided I did not want to tell the program where to place the value being moved in List2. You can un-remark it and see how it works.
John Ruff - The Eternal Optimist

 

[NRK]

John,
Again, thank you. I apologize for continuing to bother you, but it isn't working.

Would it be possible that you are using a Reference that I am not.

When I write "List2." and click Ctrl-J, the statement "AddItem" is not a choice. I assume this is the problem, but am unsure why.

Hope you can help...

 

[papparuff]

The only references I have are:
Visual Basic for Applications
Microsoft Access 10.0 Object Library

Your Microsoft Access x.x Object Library may be different, but it doesn't matter which version is checked.

There is one thing I did forget. Let's make sure that some properties of List2 are set properly.

Row Source Type - Value List
Column Count - 2
Column Widths - 0;1
Bound Column - 1

I may sound condescending but I'm not trying to be...make sure List2 is a listbox. John Ruff - The Eternal Optimist

 

[Craig0201]

AddItem isn't a method on Access list controls (at least not in 2k or earlier) so that method is out. You can do this and examples are in the Access Developer's Handbook.

Craig

 

[NRK]

Craig,
Thanks for the information. Would you be kind enough to point me in the right direction regarding this issue?

I do not have the Access Developer's Handbook.

 

[papparuff]

I beg to differ with Craig0201, but Access 2K does have the method AddItem. Even though I'm using AccessXP, I'm saving all my programs in Access2K. If AddItem was not available I would get compile errors. If you are using Access97, then the AddItem is not available, you must go another route to populate an Access97 Listbox.

Go to Microsoft's Knowledgebase

http://support.microsoft.com/default.aspx?ln=EN-US&pr=kbinfo&

(watch any wordwrapping)

and select Article ID then search for Q124344, "How to Fill a List Box with Database Object Names". This will give you the basics for filling an Access97 listbox. You will need to modify it to meet your criteria.
John Ruff - The Eternal Optimist

 

[NRK]

John,
Thanks for sticking with me on this...

I am using Access 2000, but I cannot find the AddItem method.

This is what I found in the Visual Basic help file:
AddItem Method Example

This example adds a combo box control to a command bar. Two items are added to the control, and the number of line items and the width of the combo box are set.

Set myBar = CommandBars("Custom&quot
Set myControl = myBar.Controls.Add(Type:=msoControlComboBox, Id:=1)
With myControl
.AddItem "First Item", 1
.AddItem "Second Item", 2
.DropDownLines = 3
.DropDownWidth = 75
.ListHeaderCount = 0
End With


I understand that by setting the listbox (list2) to a Control, you should be able to reference "AddItem" but it doesn't seem to be working.

I appreciate your continued assistance and hope you can provide some additional advice.

 

[papparuff]

To Craig0201 - I owe you a huge apology. AddItem is NOT a method of an Access2k listbox. I was basing my previous post to information I found in Sams VBA for Microsoft Office 2000 Unleashed and MSDN's October 2001 Library.

So Craig0201, please accept my apology.

But NRK, good news. I found exactly what you are looking for in Access2K's own help file. If you open help and in the answer wizard type selected property and go to the examples, you will find the following code. (I have modified it to suite your specific needs)

Sub cmdCopyItem_Click()

CopySelected Me

End Sub

Function CopySelected(frm As Form) As Integer
Dim ctlSource As Control
Dim ctlDest As Control
Dim strItems As String
Dim intCurrentRow As Integer
Dim intCol As Integer

Set ctlSource = frm!lstSource
Set ctlDest = frm!lstDestination

For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
For intCol = 0 To 1
strItems = strItems & ctlSource.Column(intCol, _
intCurrentRow) & ";"
Next intCol
End If
Next intCurrentRow
' Reset destination control's RowSource property.
ctlDest.RowSource = ""
ctlDest.RowSource = strItems
End Function

To make it easy, change your command button name to cmdCopyItem and after renaming it make sure the Click event has [Event Procedure] as it's choice. Next change the name of List0 to lstSource and List2 to lstDestination.

This will put you in business.

Whew!
John Ruff - The Eternal Optimist

 

[NRK]

John - that was perfect. Thank you so much! I would never have thought to check for "Selected Property" as my solution.

You have done so much for me today, but I must impose one more time...

The results need to be cumulative in the second listbox (ctlDest), I have commented out the ctlDest.RowSource = "" in hopes that this wouldn't reset the control/listbox, but this is not the case. Do you have any ideas?

Secondly, there is at least one carriage return being inserted after each record I move from ctlSource to ctlDest. I have gone through the code, but see no place in which it seems to indicate that the listbox should add a row entry added.

Example:
TC-AC001

TC-AC003


Again, I cannot thank you enough!

 

[NRK]

OK, fixed second issue so disregard that.

For anyone interested, my solution is to create a statement that deletes the last ";" in the string. My code was:
strItems = Left$(strItems, Len(strItems) - 1)

Still haven't solved the first issue from my previous post.

 

[papparuff]

This will do it.

Function CopySelected(frm As Form) As Integer
Dim ctlSource As Control
Dim ctlDest As Control
Dim strItems As String
Dim intCurrentRow As Integer
Dim intCol As Integer

Set ctlSource = frm!lstSource
Set ctlDest = frm!lstDestination

' Copy all the data in the lstDest listbox to the
' variable strItems so that they are not overwritten
For intCurrentRow = 0 To ctlDest.ListCount - 1
For intCol = 0 To 1
' Add items to the strItems variable
If (ctlDest.Column(intCol, intCurrentRow)) > 0 Then
strItems = strItems & ctlDest.Column(intCol, intCurrentRow) & ";"
End If
Next intCol
Next intCurrentRow

For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
' Determine if the value from the lstSource is not
' already in the lstDest listbox
If InStr(strItems, ctlSource.Column(0, intCurrentRow)) = 0 _
Or strItems = "" Then
For intCol = 0 To 1
strItems = strItems & ctlSource.Column(intCol, _
intCurrentRow) & ";"
Next intCol
End If
End If
Next intCurrentRow
' Reset destination control's RowSource property.
' ctlDest.RowSource = ""
ctlDest.RowSource = strItems
End Function John Ruff - The Eternal Optimist

 

[NRK]

John,
Thank you so much! You truly are "The Eternal Optimist"

In my book, you have earned a star. I really appreciate everything that you have done.

 

[NRK]

John,
As I mentioned the other day, this is fabulous code! My only issue is that if I select more than one record in a row from the listbox, I get a blank record inserted into the second listbox.

For example, if I select the following records in Listbox1:
TC-AC001
TC-AC002
TC-AC003

and click Copy cmd

The records are displayed in Listbox 2 as:
TC-AC001

TC-AC002

TC-AC003

If I look at their strVar value, created by the Function CopySelected(frm As Form) As Integer, I get:
TC-AC001;;TC-AC002;;TC-AC003;

As you can see, it is inserting a blank record and I cannot figure out how to avoid this...

If possible, I would appreciate your assistance on this issue.

 

[papparuff]

please post all of your code. The code I sent you works perfectly on my machine. John Ruff - The Eternal Optimist

 

[NRK]

Function CopySelected(frm As Form) As Integer
Dim ctlSource As Control
Dim ctlDest As Control
Dim strItems As String
Dim intCurrentRow As Integer
Dim intCol As Integer

Set ctlSource = frm!listID
Set ctlDest = frm!listFilter

' Copy all the data in the lstDest listbox to the
' variable strItems so that they are not overwritten
For intCurrentRow = 0 To ctlDest.ListCount - 1
For intCol = 0 To 1
' Add items to the strItems variable
If (ctlDest.Column(intCol, intCurrentRow)) > 0 Then
strItems = strItems & ctlDest.Column(intCol, intCurrentRow) & ";"
End If
Next intCol
Next intCurrentRow

For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
' Determine if the value from the lstSource is not
' already in the lstDest listbox
If InStr(strItems, ctlSource.Column(0, intCurrentRow)) = 0 Or strItems = "" Then
For intCol = 0 To 1
strItems = strItems & ctlSource.Column(intCol, intCurrentRow) & ";"
Next intCol
End If
End If
Next intCurrentRow
' Reset destination control's RowSource property.
' ctlDest.RowSource = ""
strItems = Left$(strItems, Len(strItems) - 1)
ctlDest.RowSource = strItems
End Function[/color]

The only part of the code I amended was the third line from the bottom ("strItems = Left$...&quot because it had additional semicolons at the end of the string which needed to be removed.

As mentioned, the problem does not occur unless I select multiple contiguous records (ie. TC-AC001 to TC-AC003).

 

[papparuff]

Ok,

Found the problem. You are storing only 1 column in both your listboxes (TC-AC001) while my sample is storing two columns (1, TC-AC001). So, there are two things you need to do

Listbox listFilter - Go to the properties sheet of the listbox and make sure the Column Count = 1 and that the Column Width = 1"

Next,

Change the two lines of code from

For intCol = 0 To 1

To

For intCol = 0 To ctlSource.ColumnCount - 1

This will solve the problem. John Ruff - The Eternal Optimist

 

[NRK]

OK, that makes sense and it did fix the problem.

Don't want you to fix this (unless you really want to), but would appreciate advice.

I can seem to only select about 100 records from the listbox listFilter and then I get run-time error 2167 "Setting for property is too long"

As you could probably see, I am not yet a master of VB and so am unsure why this error is being generated. Have you run into anything similar or do you have a clue to get me started? FYI - I started a thread on this topic (SQL string is too long...stuck!!) under the VBA Coding Forum for Access.

I do not want to push my luck, but I thought I would check. Regardless, you have been an enormous help and I cannot thank you enough!