List Box Manipulation 1

[sliptdisk]

I need to allow the user to select a record in a list box, and, move it up or down, within the list box. In other words, select the record, then click on an up or down arrow to move that record above or below the adjacent record. Can you help me?

 

[PaulF]

The following code uses a Listbox and two command buttons, the name of the Listbox is lstA, which uses a Value List as the RowSourceType. The two command buttons are named cmdMoveDown and cmdMoveUp. To use this code, you select the item in the Listbox that you want to move, then click the appropriate command button. What the code does is rearrange the Value List, and then refreshes the form. To test it, create a form with the Listbox and two command buttons (using the provided names), set the Listbox RowSourceType to Value List and add "A";"B";"C";"D";"E" to the Listbox RowSource. Cut and Paste the code, ensuring that the On Click Event property for each command button shows [Event Procedure] and start selecting, and clicking.


Private Sub cmdMoveDown_Click()
Dim lngItem As Long, lngItem1 As Long, i As Long, varItem As Variant
Dim strSelectedItem As String, strNextItem As String, LBValue() As String
Dim strSource As String, lenStrSource As Long
Dim ctl As Control
Set ctl = lstA
i = ctl.ListCount
ReDim LBValue(i)
For Each varItem In lstA.ItemsSelected()
lngItem = varItem
If lngItem = i - 1 Then
MsgBox "You can't move this item to a lower position"
Exit Sub
End If
lngItem1 = lngItem + 1
strSelectedItem = lstA.ItemData(lngItem)
strNextItem = lstA.ItemData(lngItem1)
Next
i = 0
For i = 0 To ctl.ListCount - 1
LBValue(i) = lstA.Column(0, i)
Next i
LBValue(lngItem) = strNextItem
LBValue(lngItem1) = strSelectedItem
i = 0
For i = 0 To ctl.ListCount - 1
strSource = strSource & Chr(34) & LBValue(i) & Chr(34) & ";"
Next i
lenStrSource = Len(strSource)
strSource = Left(strSource, lenStrSource - 1)
lstA.RowSource = strSource
Me.Refresh
Exit Sub
End Sub


Private Sub cmdMoveUp_Click()
Dim lngItem As Long, lngItem1 As Long, i As Long, varItem As Variant
Dim strSelectedItem As String, strNextItem As String, LBValue() As String
Dim strSource As String, lenStrSource As Long
Dim ctl As Control
Set ctl = lstA
i = ctl.ListCount
ReDim LBValue(i)
For Each varItem In lstA.ItemsSelected()
lngItem = varItem
If lngItem = 0 Then
MsgBox "You can't move this to a higher location"
Exit Sub
End If
lngItem1 = lngItem - 1
strSelectedItem = lstA.ItemData(lngItem)
strNextItem = lstA.ItemData(lngItem1)
Next
i = 0
For i = 0 To ctl.ListCount - 1
LBValue(i) = lstA.Column(0, i)
Next i
LBValue(lngItem) = strNextItem
LBValue(lngItem1) = strSelectedItem
i = 0
For i = 0 To ctl.ListCount - 1
strSource = strSource & Chr(34) & LBValue(i) & Chr(34) & ";"
Next i
lenStrSource = Len(strSource)
strSource = Left(strSource, lenStrSource - 1)
lstA.RowSource = strSource
Me.Refresh
Exit Sub
End Sub


HTH

PaulF

 

[sliptdisk]

Wow. Thanks. That looks like an awesome piece of code. I don't understand why the if statement you've got after the declarations are followed by exit sub commands, <I> then</I> the end if statements, but, I'll give it a try and see how it works. Thanks, again.

 

[sliptdisk]

Unfortunately, I don't think I'll be able to take advantage of this code, because the row source type is set to table/query and if I switch it to value type, the list box won't be populated properly. I think ... I'm not sure. I'm still working on it.

 

[PaulF]

You can still use the code if you add this to the Form_Load( ) Event. Just change the query name and field name where appropriate.


Dim db As DAO.Database, rst As DAO.Recordset, strValueList As String
Dim strListBox() As String, intCount As Integer, X As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset(&quot;YOUR QUERY NAME GOES HERE&quot

If rst.RecordCount > 0 Then
intCount = rst.RecordCount
End If
ReDim strListBox(1 To intCount)
X = 1
With rst
.MoveFirst
Do While Not .EOF
strListBox(X) = rst![FIELD NAME GOES HERE]
.MoveNext
X = X + 1
Loop
End With
X = 1
For X = 1 To intCount
strValueList = strValueList & Chr(34) & strListBox(X) & Chr(34) & &quot;;&quot;
Next X
strValueList = Left(strValueList, Len(strValueList) - 1)
lstA.RowSource = strValueList
Me.Refresh
Exit Sub
End Sub


The above code will populate an array with the data from &quot;ONE&quot; field, and use that as the Value List for the Listbox. The reason for the Exit Sub in the If statements, is to prevent errors from occuring. When you are at the first position in the Listbox and attempt to move UP, an error will occur unless you trap for it. Same when you are at the last position in the Listbox and attempt to move DOWN. I Exit the sub instead of allowing the errors to occur.

HTH

PaulF

 

[sliptdisk]

Thanks. I inserted the code you gave me, earlier, and, this chunk, too, now. I replaced the lstA name w/ the name of the list box, but, it's telling me it's not defined. I'm going to go back and put the query name, etc. into what you just gave me and see if that works. I sure appreciate the great help.

 

[dcuper1]

I am new to forms in Microsoft Access and I didn't know you could add such a list of code to it. I thought you could only add such code to Visual Basic. If you guys are adding this code to the forms in Microsoft Access, please tell me how you did that. Specifically, in the description of the code by PaulF he said to copy and paste it to the form. Where in the form should I copy and paste it?? I thought the code is great, congratulations.

Thanks,
Daniel

 

[PaulF]

sliptdisk

Don't know why you are getting the error, ensure you have replaced all of the lstA entries with your Listbox name (there are 6 in each command button's code, and 1 in the Form OnLoad code).

Daniel

You need to read up on Events and Event Properties Reference in the Help File. Each control (including Forms and Reports) have a series of Event Properties that you can assign code or Macros to. The code used in the steps above are assigned to the Form's OnLoad Event and to the OnClick Event for two Command Buttons, one with the name cmdMoveUp and one with the name cmdMoveDown. To see what Events are available for a specific control, click on the Events Tab in the Properties window for that control.


PaulF

 

[sliptdisk]

I made sure that I had, indeed, replaced all of the incidences of lstA with ReqItemList (I didn't design this beast, or name the controls). I had accidentally pasted the code into the from load event of the main form, instead of the one it needed to be in. I've gotten that corrected, but, I'm still having problems. I don't know what to enter as the query name - when I pull it up through the properties box, it shows a SQL expression builder, but, I don't know what the name is.

 

[sliptdisk]

Actually, I just pulled it up and did an abourted SaveAs, and it just read Query3, so, I'm going to plug that in and see what happens. Sometimes the obvious solution can elude us.

 

[sliptdisk]

Ok. Now, I'm getting a Parameter request that's associated with what I put in the form load module. Hmmm...

 

[sliptdisk]

And, also, this chunk of code was already in the form load module, some of which might be causing a conflict.

Set db = CurrentDb
Set rsRO = db.OpenRecordset(&quot;ReqOrder&quot;, dbOpenTable)
'Call cmdNew_Click
Set rsItemOrdered = db.OpenRecordset(&quot;ItemOrdered&quot;, dbOpenTable)
rsItemOrdered.Index = &quot;PrimaryKey&quot;
Call InitItem
Call ItemEditColor
Call ItemDisable

I stuck what you gave me in between that and:

Exit_Form_Load:
Exit Sub

Err_Form_Load:
MsgBox Err.Description
Resume Exit_Form_Load

 

[sliptdisk]

Ok. Well, thanks for trying to help me. My boss looked under the hood and deleted all of that code I had pasted in, saying that he wanted to do it a much simpler way, and, now, I have to try and figure out how to do it. Arrrgh.

 

[PaulF]

ok, let's step back for a moment........what is selected as the RowSourceType for the Listbox in your form, and what is selected for the RowSource???

BTW you should make a new form with a listbox and two command buttons to test this out and see if you get what you are looking for........ This code returns you to the original RowSource everytime the form reopens..... the sequencing only lasts as long as the form is opened.... so what are you planning to do with the data after you have moved it Up and down??????


PaulF

 

[sliptdisk]

The Rowsource Type is Table/Query and the Row Source is SELECT DISTINCTROW ItemOrdered.ItemOrderedNum, ItemOrdered.ItemNum, ItemOrdered.ItemName, ItemOrdered.Description, ItemOrdered.Price, ItemOrdered.Qty, ItemOrdered.Unit, ItemOrdered.SortOrder, ItemOrdered.CostCodeNum FROM ItemOrdered WHERE (((ItemOrdered.ROSysID)=[Forms]![ReqOrder]![SysID])) ORDER BY ItemOrdered.SortOrder;

What we're doing with the list box is manipulating the items contained within, in preparation for a report. This is a program which tracks purchases made by a government organ. After loading the main form, the user selects &quot;Requisition Order&quot; cmd button(hence the &quot;RO&quot, and the requisition order form loads. After we took out the code you gave us, the errors went away, by the way. Anyway, the user selects a vendor from the SysID combo box, and the list box is populated with purchases that the client has made.

What I need to do, now, is attempt to follow these instructions, which the boss laid out via comments, in the code module, as follows:

'Get itemorderedID and Sort Order from current listindex of list box


'Open record set with only items from this SYSID


'Find item with this current sort order and the previous one; then swap them


'close record set


'Requery list box


If I can manage this and a few other small changes, I should be home free. Sorry about the confusion, and, thanks again for your generous help.

 

[sliptdisk]

In other words, the user wants to be able to highlight an entry and move it up or down, so that they can group items with the same commodity code together, before, printing the report. The boss added another field, an order #, to the table, to help make the code simpler, and, I have to update the table, in addition to the list box, after it's been changed.

 

[PaulF]

Well, don't know if what you are attempting to do is the proper way to go about this... But first, the problem with the form is that you needed to change the RowSourceType to &quot;ValueList&quot; and leave the RowSource blank, and the change the Column Count to 1. Then instead of using docmd.openquery(YOURQUERYNAME) you'd need to change it to something like this (cut and paste the Select Statement from the RowSource):

dim MySQL as string
MySQL = &quot;SELECT DISTINCTROW ItemOrdered.ItemOrderedNum, ItemOrdered.ItemNum, ItemOrdered.ItemName, ItemOrdered.Description, ItemOrdered.Price, ItemOrdered.Qty, ItemOrdered.Unit, ItemOrdered.SortOrder,
ItemOrdered.CostCodeNum FROM ItemOrdered WHERE (((ItemOrdered.ROSysID)=[Forms]![ReqOrder]![SysID])) ORDER BY ItemOrdered.SortOrder;&quot;

docmd.openquery (MySQL)


Now..... This should allow the Listbox to open, however it will only populate the Listbox with data from one field (depending on which one you chose), unless you change the line of code from

strListBox(X) = rst![club name]

to something like

strListBox(X) = rst![ItemOrderedNum] & &quot;, &quot; & rst![ItemNum] & &quot;, &quot; & rst![ItemName]



Now if the Listbox does populate (and it should), it will only do it the first time you open the form...... Unless you move the code from the Form's OnLoad Event to another sub or function, and call it from the Forms OnLoad Event and the AfterUpdate Event for ROSysID ComboBox. Once you've moved the data, you need to capture it and process the data accordingly.... The problem you've been having is based on the stuff mentioned above.


PaulF

 

[Junior1544]

It took a little doing, but I got this code to work for me! This place is Great! junior1544@jmjpc.net
Life is change. To deny change is to deny life.