Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old September 13th, 2007, 06:52 AM
Friend of Wrox
Points: 912, Level: 11
Points: 912, Level: 11 Points: 912, Level: 11 Points: 912, Level: 11
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2005
Location: Dublin, , Ireland.
Posts: 181
Thanks: 0
Thanked 0 Times in 0 Posts
Default Moving Items up and down in a listbox

Hi All
I need to be able to move items up and down in a listbox
Thanks

Brendan Bartley
__________________
Brendan Bartley
  #2 (permalink)  
Old September 13th, 2007, 07:34 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

If the rowsource of the list box is a table, you could conceivably create queries that sort it differently, then make the different queries the rowsource when you want the sort order to be different for each situation.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
  #3 (permalink)  
Old September 13th, 2007, 08:06 AM
Friend of Wrox
Points: 912, Level: 11
Points: 912, Level: 11 Points: 912, Level: 11 Points: 912, Level: 11
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2005
Location: Dublin, , Ireland.
Posts: 181
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I think you may have Misinterpret what I am looking for.
I need to move items in a listbox up and down as required by arrow keys
Thanks

Brendan Bartley
  #4 (permalink)  
Old September 13th, 2007, 10:11 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

That might be easier. If you select an item and then click an arrow key, you can take a snapshot of the listbox in an array, then take the selectedIndex item and subtract 1 or add 1 and rebuild the array and post it back to the listbox. Do you have code so far?

I am not sure if that is how top Access experts would do it. Perhaps one will post.


mmcdonal
  #5 (permalink)  
Old September 13th, 2007, 10:13 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Oh, I should mention that you could put the results in a table, and then change the sort order or sort values and requery the listbox as SerranoG suggests. He is a top Access expert. =)

mmcdonal
  #6 (permalink)  
Old September 13th, 2007, 10:34 AM
Friend of Wrox
Points: 912, Level: 11
Points: 912, Level: 11 Points: 912, Level: 11 Points: 912, Level: 11
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2005
Location: Dublin, , Ireland.
Posts: 181
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The Listbox will have items added to them each hour and I need to priorities these depending on their importance importance I have no code as yet to move them up and down the listbox

Brendan Bartley
  #7 (permalink)  
Old September 13th, 2007, 10:50 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

It sounds like they will have a ranking based on importance. Is that correct? If you tie that ranking to a listbox index schema, then that could work. You rank them once, and then re-rank them using the same mechanism. This gets back to the table solution. Have two columns: RankID, ItemName. RankID would be a number field, sort on RankID. Your processes would be to change the RankID and cascade that change through the rest of the table.

Ex:

RankID ItemName
0 Sam
1 Dave
2 Debbie

The process of adding Joe and making him top rank, would involve adding Joe to the table, settig his rank to 0, and then adding 1 to every record where RanikID >= 0, without acting on Joe.

Then sort the list box by the RankID, and hide that column. It will correspond to the list box index, so you can use the listbox index where needed.

Does that give you any ideas?


mmcdonal
  #8 (permalink)  
Old September 14th, 2007, 06:57 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Quote:
quote:Originally posted by mmcdonal
 ... as SerranoG suggests. He is a top Access expert. =)


Oh, heck no! But thanks for your vote of confidence!

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
  #9 (permalink)  
Old September 14th, 2007, 07:57 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Hiya again,

I created a table with two columns, ListID and ListItem. ListID is a number field, indexed but duplicates are allowed. I used this as the source of a list box, which holds Column(0) = ListID, Column(1) = ListItem, Column(0) hidden.

I added a button to add items to the table so:

'This adds an item to the list table with the next PK

Dim sSQL As String
Dim iIndex As Integer
Dim sString As String

'Take the new text value to show up in the list.
If IsNull(Me.Text2) Or Me.Text2 = "" Then
    MsgBox "Please type something first.", vbCritical
    Exit Sub
Else
    sString = Me.Text2
End If

'Take the next number
iIndex = Me.List0.ListCount

'Build the SQL string to insert the new value
sSQL = "INSERT INTO ListSource(ListID, ListItem) " & _
        "VALUES(" & iIndex & ", '" & sString & "')"

'Turn off Access Query wanrings, insert and turn back on
DoCmd.SetWarnings False
DoCmd.RunSQL (sSQL)
DoCmd.SetWarnings True

'Requery the list so the new value shows up.
Me.List0.Requery


Then I created a button to move items up in the list so:

Dim iItem As Integer
Dim iUp As Integer
Dim iListID As Integer
Dim sListItem As String
Dim sSQL As String
Dim rs As ADODB.Recordset

'This is the ListID number from the table as well
'This will be reduced by one
iItem = Me.List0.Value
sListItem = Me.List0.Column(1)

'This is the item's new number in the list table
iUp = iItem - 1

sSQL = "SELECT * FROM ListSource"
Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

'Find iItem and iUp and change
rs.MoveFirst
Do Until rs.EOF
    If rs("ListID") = iUp Then
        rs("ListID") = iItem
        rs.Update
    End If
rs.MoveNext
Loop

rs.MoveFirst
Do Until rs.EOF
    If rs("ListItem") = sListItem Then
        rs("ListID") = iUp
        rs.Update
    End If
rs.MoveNext
Loop

'Requery the list so the new value shows up.
Me.List0.Requery
Me.Command4.SetFocus

This code works. I guess you can figure out how to make the items go down in the list. This only moves them up a row at a time.

Did that help?

Sorry I used the INSERT statement. As most Access experts will tell you, you should always select all the records in the table just to insert a single record. (Not a dig at you Greg, at another poster who recommends selecting all 10000000 records just to insert a single record - recommended by top Access experts.)
:D

mmcdonal
  #10 (permalink)  
Old September 14th, 2007, 10:04 AM
Registered User
 
Join Date: Oct 2006
Location: Leigh, Lancashire, United Kingdom.
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Well I am amazed. I didn't realize that we had Top Access Experts that are 5 years old. My grandson who is 7 doesn't act like mmcdonell when he can't get his own way. Obviously mmcdonell, whilst being very clever and extremely well versed in Access development obvoiusly cannot take criticism. It is a shame when someone who obviously has such intelligance has to continually make reference to this in his psotings.

My suggestion for that particular instance was to help a fellow forum member who wanted a quick easy way to build an insert into an new table just created. He appeared to a be a realtively new user of access and wanted him to see the the usefulness of recordsets, whether they be in DAO or ADO. I said at the start of that suggestion that it was "In my opinion". Even our 5 year old superstar admits himself that he cannot use DAO only ADO.

The main reason I posted that comment to you mmcdonnel, was the way in which you dismissed by effort, as if your a god, well let me tell you, your not. Many of your suggestions are, in your own words, ugly. Perhaps if you has suggested an alternative without making me as the poster of the comment feel small, we would not being having this disagreement in the forum.

I suggest you take time to read other peoples suggestions before dismissing them and learn how to respond.

I apologize to all you guys out there in the forum that has had to witness this small spat and I would also like to say, that I have learnt many new tips and tricks from - - - mmcondell and hopefully will continue to do so. I will also conitnue to post replies, but please mmcondell, grow up, its old hat now.




Similar Threads
Thread Thread Starter Forum Replies Last Post
Getting all listbox items Lucy_H85 C# 2005 2 December 1st, 2009 05:36 AM
unselecting items in a listbox Durkee VB.NET 2002/2003 Basics 9 October 8th, 2007 03:01 PM
UNSelect Items in ListBox mmcdonal Access VBA 7 June 21st, 2007 08:09 AM
Moving Selected Items from a list box to excel AlanAtMars SQL Server 2000 2 August 19th, 2005 02:02 PM
Saving Listbox Items antonducabre VB.NET 2002/2003 Basics 1 July 16th, 2003 12:46 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.