Unsort listbox

[NRK]

As to be expected, I always seem to need the opposite of what Microsoft thinks I need.

I have a listbox which filters a report (on Cmd button click). This listbox is populated from another listbox, which gets its contents based on a combo box selection.

When adding records from the first listbox to the second listbox (the one that runs the filter for the report), the records are automatically sorted alphabetically. Unfortunately, I do not want that. I want the records to be moved into the listbox in the order in which it happens.

Does anyone know how to define the sort for a listbox?

Any advice would be greatly appreciated.

 

[MichaelRed]

dest list box sorted property = true. Make it not.

NOT an MS 'Issue', there is a CHOICE.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[NRK]

Thank you for the feedback...I have looked in the properties of my listbox and in the help but I do not see the "list box sorted property."

Where is it located?

 

[MichaelRed]

hMMMMMMMMMMMMMM,

I think I blew it!

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[WildHare]

Doesn't the second list box get it's values from a "value list" ?? You should be able to simply rebuild the value list each time, on the order of:

ValueList = "First Choice"
Me.LB2.Refresh
...
(another selection made from 1st list box)
ValueList = ValueList & ";" & secondChoice
Me.LB2.Refresh
...

and so on.

I haven't actually coded this to test, but it seems like it should work this way.

Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[NRK]

Jim - thanks for the reply. I will try it when I get into the office later.

For further information:
My listboxes are based off of queries for a table. In order to move records between listbox #1 and #2, I have a checkbox that is True (if record in listbox #2) or False (if record is in listbox #1).

I want to keep listbox #1 sorted the way it is, but listbox #2 just needs to show records in the order in which I add them. The only thing I can think of is that since it (listbox #2) is based off a queried table (which is sorted), it is grabbing that sort as well.

As I move records b/w the 2 listboxes, the table's checkbox field is continually refreshed.

Don't know if this better defines the problem, but still would appreciate any feedback...

 

[B827]

Don't you need to build the items as a text string (as if you typed the values in) that way you can order them in the order they're added. The indices in your source table may be affecting the order items appear in the query. Sandy

 

[NRK]

Sandy - As I was typing my last message, that is what I was beginning to think. The issue with building the listbox contents as a text string is that the contents could be extremely long (in the hundreds of records).

My initial experience with builiding this form was creating a SQL string, but I quickly found the limits of listboxes within Access.

Can you think of any way in which I can remove or hold any indices related to my source table?

Thank you.

 

[MichaelRed]

Atter looking at the docs, I think the list box will always end up being sorted. This 'hunch' is based on the behaviour of the look up feature when typing an entry - which will 'Go To' the nearest record on an alpha sort, this implies an ordering of the records ...

IF the listbox would list the recods in the sorce order, you could build a temporary or disconnected recordset by adding the (selected) elements to an array and making an ADO recordset from the array - but then I doubt it will accept the recordset w/o ordering the elements - - - but then again, I've bee wrong before (just in case you didn't notice)


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[HowieG]

Consider this. Instead of a true/false field in the underlying table, use a number (say, long integer). Then in the code that 'moves' a record from one listbox to the other (which currently sets the true/false field to 'true') have it set the number to the next highest number. (you can use a dmax() lookup function to ensure you always get the next highest).

This way you can differentiate between the two listboxes by whether this field is zero(null?) or has a number greater than zero. Then in the listboxes sql, use a ORDER BY clause to sort by the numeric field.

 

[NRK]

HowieG - That is what I was brainstorming with my team last night.

I had a slight variation in that I would append a number that was auto-generated (DMAX function) when I moved from the first listbox to the second. So, if I moved record AC001, it would be placed on the second listbox as 1_AC001, but would be displayed as "AC001". Does that sound feasible?

A new monkeywrench in my project is that my boss wants to be able to insert records in the second listbox at any location (not just adding to the end of the set). I think I am getting close to maxing my knowledge of listboxes and, possibly, the limit of listboxes.

Any further advice would be greatly appreciated! Thanks to all for your help.

 

[jebry]

Hi!

I think that HowieG's idea will work more efficiently. Fortunately you can also use his idea to insert the record anywhere in the list. What you will need is two buttons, one for appending and one for inserting. The append button will use the DMax function to add the record to the bottom of the list(you can also allow this to occur using a double click in the first list box. The insert button will require one record from each list box to be selected and you can insert the record from the first list box into the second list box by incrementing the value in the number field by one for each record in the second list box that is equal to or greater than the one selected and putting the numerical value from the selected record into the record selected in the first list box.

I hope you caught all of that. If you need some specific code let us know.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[NRK]

Jeff,
I did catch all of that. Thanks for the idea. Although it makes sense, I will probably need help with the code. I will let you know...would you like the code I am currently using? If so, let me know and I will e-mail it to you.

Again, my thanks!

 

[jebry]

Hi!

You are certainly welcome to email your code to me.

Jeff Bridgham
bridgham@purdue.edu

 

[NRK]

I have made some progress on this topic. I seem to be able to auto generate an ID number when I move records from listbox #1 to listbox #2.

My problem is that I cannot seem to clear these numbers when I am done (unless I manually take them out of the table). Since the table's columns (RptID, RptList, and PrintRpt) are only populated during the usage of this search form, I need to be able to reset all of the values.

Here is what I am using to create the ID:
IDval = Nz(DMax("RptID", "1_tbltcMaster&quot + 1, 1)

I, then, use an UPDATE query to remove values from the aforementioned fields. The code is:
strSQL = "UPDATE 1_tbltcMaster SET RptList = False, PrintRpt = False " & _
"WHERE RptList = True"
DoCmd.RunSQL strSQL

This is where I am trying to insert something to zero the RptID field, but to no avail.

Probably something simple that I am overlooking, but would appreciate any advice that you may have.

Thanks!

 

[HowieG]

I take it that RptID is the numeric sort order for items in listbox #2?

What is the RptID field defined as? Is it indexed? Allows duplicates?

What commands have you tried to zero out the RptID field?

I would guess that something like this would work:

strSQL = "UPDATE 1_tbltcMaster SET RptID = 0 WHERE RptList = True"
DoCmd.RunSQL strSQL

If not, what error message does it give you?

 

[MichaelRed]

If "Auto Generate" implies the use of an AutoNumber, you can only re-set this when the recordset is empty and then only by "compacting" the db. On the other hand, it does NOT need to be reset as long as you clear the table between uses, as only the current recordset values would be there to sort anyway. Just delete all the records between uses of the list box.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[NRK]

HowieG -
The RptID field is just a number field to create the sort order for listbox #2. It is indexed, with no duplicates. I was trying to null the values by things like:
SET RptID = ""
SET RptID = vbNullString

I tried your code - it entered 0 for the first value (1) but did not do anything to the others. I could add to WHERE statements - "WHERE RptList = True AND RptID Is Not Null" - and this may address the issue.

Although I like what your code did, I would prefer to not have any values when I reset that field, which is why I was trying to set the field to "" or Null.

MichaelRed -
"Auto Generate" does not imply AutoNumber. I am, as I believe you are, not a fan of the AutoNumber feature. This project will be deployed both locally on a server and on an intranet, so the use of AutoNumber would just cause problems.

 

[NRK]

Follow-up - believe I fixed this issue. As mentioned, I created a double WHERE statement and set RptID = Null. Here is the code:
strSQL = "UPDATE 1_tbltcMaster SET RptID = Null, RptList = False, PrintRpt = False " & _
"WHERE RptList = True AND RptID Is Not Null"
DoCmd.RunSQL strSQL

In case anyone is following this project:
My next step in this process is to modify listbox #2 to be two columns, so that when it sorts it sorts by the column with the RptID. Finally, I will probably need some code advice from Jeff (jebry) about how to dynamically add or insert records from listbox #1 to listbox #2.

As always, cannot thank you enough for your patience, support, and advice!

 

[HowieG]

If the field is set to "indexed, with no duplicates" then you wouldn't be able to set all its values to Zero because that would cause duplicates. However, setting to Null as you've done is probably okay, because you probably have 'Required' set to No.

You don't actually need two columns in the list box. Just make sure the SQL string in the control's 'rowsource' property has a ORDER BY clause in it (it can order by a field in the queried tables, and does not have to actually return the field being sorted on).

For example:

SELECT RptName 
FROM 1_tblcMaster 
WHERE RptList = True 
ORDER BY RptID

(You'll have to change this a bit I'm sure)