list box for each coding

[Cynthia]

I have a list box that I am allowing multiple selections on. I need to capture
columns 0 and 4 from the list of what is selected. Does any one know how to
do that. On the code below it errors out after running through one selection,
I get invalid use of null?
Column 4 is the order number they are placed in and I am trying to
move them up by lowering this number by one and upping the
number below to its value.


For Each varItem In Me.listCircList.ItemsSelected
id = Me.listCircList.Column(0)
num = Me.listCircList.Column(4)
newnum = num - 1
If num = 1 Then
MsgBox "Cannot move up already at Top of List"
Else
DoCmd.RunSQL "Update EleCircRoute set routenum = " & num & "
where CircID = " & CircID & " and routenum = " & newnum & ""
DoCmd.RunSQL "Update EleCircRoute set routenum = " & newnum
& " where ID = " & id & ""
Me.Refresh
End If
Next varItem

 

[Roger Carlson]

I've done something similar, but used DAO recordsets instead. On my website
(www.rogersaccesslibrary.com), is a small Access database sample called
"MoveUpDownList.mdb" which illustrates this. Perhaps it would prove useful
to you.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Cynthia]

First I want to thank you for the help
I looked over the code and have a few questions about the
DAO.Database and DAO.recordset. I am linked to SQL and have been using
ADODB to get records sets. I do not really understand what the differences
are.
Can I use your program as is, or do I replace the DOA with the ADODB?

This is how I am currently getting my record sets.
Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset
Set cn = Application.CurrentProject.Connection

If I must use ADODB I'm not sure what to do with the following.
Set rs = db.OpenRecordset("qryListSort", dbOpenDynaset)

 

[Roger Carlson]

I used DAO because I am comfortable with it and it works as well or better
than ADO (in an Access-Only setting). You can use DAO (and the code just
the way it is) by adding a Reference to "Microsoft DAO 3.6 Object Library".

However, if you are already using ADO, it can also be done just as easily in
that. There are a few differences. You'll have to create your recordset
differently.
Change:
Set rs = db.OpenRecordset("qryListSort", dbOpenDynaset)
to
rs.Open "qryListSort", cn, adOpenKeyset, adLockOptimistic

You'll have to change "FindFirst" to "Find". Lastly, you'll have to delete
the "rs.Edit" line altogether. It should look something like this:

'======================
Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim temp As Long

Set cn = Application.CurrentProject.Connection

rs.Open "qryListSort", cn, adOpenKeyset, adLockOptimistic
rs.Find "[FruitID] =" & List0
temp = rs!FruitOrder

rs!FruitOrder = temp - 1
rs.Update
rs.MovePrevious

rs!FruitOrder = temp
rs.Update
Me.List0.Requery
'======================

You'll have to make similar changes to the Move Down button.
--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Cynthia]

I got to looking at your code at it does not allow multiple selects. I have
already got it to work for a single selection using code below, my problem is
getting it
to work when multple items are selected. Also like to find out the best way
to
find the top item selected.

If IsNull(Me.listCircList.Column(0)) Then
MsgBox "Must Select RaceWay to Move"
Else
id = Me.listCircList.Column(0)
circ = Me.listCircList.Column(1)
route = Me.listCircList.Column(2)
num = Me.listCircList.Column(3)
newnum = num - 1
If num = 1 Then
MsgBox "Cannot move up already at Top of List"
Else
DoCmd.RunSQL "Update EleCircuitRoute set routenum = " & num & " where
circuit = '" & circ & "' and routenum = " & newnum & ""
DoCmd.RunSQL "Update EleCircuitRoute set routenum = " & newnum & " where
ID = " & id & ""
Me.Refresh
End If

 

[Cynthia]

Thank you for explaining this. I did not understand the difference.
Where in my database do I add the reference to Microsoft DAO 3.6 Object
Library?

I am having a hard time getting help on multiple sections.
I think I can work it through, but may be the long way around.

I used DAO because I am comfortable with it and it works as well or better
than ADO (in an Access-Only setting). You can use DAO (and the code just
the way it is) by adding a Reference to "Microsoft DAO 3.6 Object Library".

However, if you are already using ADO, it can also be done just as easily in
that. There are a few differences. You'll have to create your recordset
differently.
Change:
Set rs = db.OpenRecordset("qryListSort", dbOpenDynaset)
to
rs.Open "qryListSort", cn, adOpenKeyset, adLockOptimistic

You'll have to change "FindFirst" to "Find". Lastly, you'll have to delete
the "rs.Edit" line altogether. It should look something like this:

'======================
Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim temp As Long

Set cn = Application.CurrentProject.Connection

rs.Open "qryListSort", cn, adOpenKeyset, adLockOptimistic
rs.Find "[FruitID] =" & List0
temp = rs!FruitOrder

rs!FruitOrder = temp - 1
rs.Update
rs.MovePrevious

rs!FruitOrder = temp
rs.Update
Me.List0.Requery
'======================

You'll have to make similar changes to the Move Down button.
--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

First I want to thank you for the help
I looked over the code and have a few questions about the
DAO.Database and DAO.recordset. I am linked to SQL and have been using
ADODB to get records sets. I do not really understand what the differences
are.
Can I use your program as is, or do I replace the DOA with the ADODB?

This is how I am currently getting my record sets.
Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset
Set cn = Application.CurrentProject.Connection

If I must use ADODB I'm not sure what to do with the following.
Set rs = db.OpenRecordset("qryListSort", dbOpenDynaset)

 

[Roger Carlson]

I'm terribly sorry. I missed that part of the question altogether. This
is a MUCH more difficult problem. Neither my code NOR YOUR CODE can be
easily modified to do what you want.

The main difficulty is that once you set the Multi-Select property of the
listbox, the listbox *no longer has a value*. That is the Value property is
NULL and will stay NULL. That's why you're getting the NULL error. In
order to get at the selected values, you have to use code to step through
the ItemsSelected Collection (or through the Selected Property) to see which
are selected. Then you would have to change all of the values selected
while changing all the unselected values in the opposite direction. If you
can select non-contiguous values, the problem becomes even more complex.

I don't have a sample that does this, but I DO have a couple of samples that
show how to get values from a Multi-Select listbox. Take a look at these:
"MultiSelect.mdb", "CreateQueries2.mdb" (shows how to create a query with
values selected in a multi-select listbox), and "MoveList2.mdb". None of
these do exactly what you want, but they will give you a better
understanding of how Multi-Select listboxes work.

Best I can do. Sorry.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Douglas J Steele]

Try

For Each varItem In Me.listCircList.ItemsSelected
id = Me.listCircList.Column(0, varItem)
num = Me.listCircList.Column(4, varItem)

 

[Cynthia]

I'll give that a try, if it works I will be much closer to getting what I need.
I did notice that the for each pulls items in order they show in the list box.
To get the order backwards could I change the query for the list box
before doing the for each?
I will need to get it backwards to do a move down.

 

[Douglas J. Steele]

Dim intLoop As Integer

For intLoop = (Me.listCircList.ItemsSelected.Count - 1) To 0 Step -1
varItem = Me.listCircList.ItemsSelected(intLoop)
id = Me.listCircList.Column(0, varItem)
num = Me.listCircList.Column(4, varItem)

 

[Cynthia]

Thank you so much. You were a great help in getting this up and running.