Listbox OrderBy
Jun 20, 2006
Hi,
I have a list box based on a quite a complex query. I would like give the user the option to dynamically sort the list box.
How can I refer to my listbox's SQL rowsource and add a sort order using VB?
i.e.
Private Sub CmdButtonSortByName_Click
Dim strSQL as String
strSQL = me.listbox.rowsource & "ORDER BY FieldName;"
me.listbox.rowsource = strSQL
I am aware of how I could do this if my SQL was in VB i.e.:
Dim strSQL as String
Dim strSortOrder as String
Dim optSort as Integer
Select Case optSort.Value
Case 1
strSortOrder = "ORDER BY LastName;"
Case 2
strSortOrder = "ORDER BY FirstName;"
End Select
strSQL = "SELECT FirstName, LastName FROM TblName " & strSortOrder
Me.ListBox.Rowsource = strSQL
....but my query is far more complex than this and I dont want to have to keep calling the code each time I need to requery my form.
Thanks
BF
View Replies
ADVERTISEMENT
Apr 26, 2006
Hallo everyone,
I have a table with one of its column value as "B" or "D" or "E".
Now i am in need to display the rows using the select statement with the following conditions.
1. First display the rows with value "D"
2. then display the rows with value "E"
3. then display the rows with value "B"
Is it possible to specify the above condition in a orderby clause.
thanks,
aravind.s
View 1 Replies
View Related
Oct 13, 2006
I am trying to get the OrderBy to recognize wildcards (or to that effect). A scenario would be I would have more than one name in a field, EX: Name2, 4, 6. How do I use wildcards in the OrderBy and they are recognized.
ORDER BY (Table.Field)="Name1", (Table.Field)="Name2", (Table.Field)="Name3", (Table.Field)="Name4", (Table.Field)="Name5", (Table.Field)="Name6", (Table.Field)="Name7", (Table.Field)="Name8";
View 2 Replies
View Related
Nov 2, 2004
I want to basically perform a "Sort by ascending" Function.
I presume I have to grab the focus of the Item I want to order by.
So :
me.(Field).Setfocus
But then what is the code for running the Sort by ascending/Descending?
My data is currently in a list of text boxes. When I normally 'Sort by ascending' the function works fine.. so I just need to activate that....
View 4 Replies
View Related
Apr 15, 2005
In my db I have a report that I would like to sort by different fields. I tried coding this using option buttons, a command button, and the "Case" statement. While putting in the OrderBy portion of the code I keep getting errors in relation to the syntax. I'm not sure how long I worked with it and got absolutely nowhere. Can someone clue me in please? Here's my code:
Private Sub Command14_Click()
Select Case Me!Frame5.Value
Case 1
'Sort by Military Occupational Specialty (MOS) ascending
DoCmd.OpenReport ("Report1")
OrderBy MOS
Case 2
'Sort by Military Occupational Specialty (MOS) descending
DoCmd.OpenReport ("Report1")
OrderBy MOS, DESC
Case Else
MsgBox ("Please select an option before attempting to open the report.")
End Select
End Sub
View 6 Replies
View Related
Feb 21, 2006
Hi all,
Found a weird problem when editing records in continuous form view (and datasheet view) when a particular memo field is being used by OrderBy to sort the data. When i have just created a new record and tab to the next record, the record i was just in jumps to the top of the records, but in its place is an exact copy of the record above. Very confusing, and looks like a redraw bug but it happens on all PCs.
What happens is ... (letters relate to records going down the screen)
A
B
C
D ....new record being entered then <TAB> and ...
D ....has jumped up here
A
B
C
C ....duplicate of C - but duplicate record never created in the table
E ....new record
When i quit the form and come back in, everything is ok
As far as i know, OrderBy only takes effect if you refresh the form (which i'm not doing). It works fine if i take off OrderBy, or if i use another field with OrderBy. So is there some problem using OrderBy with a memo field, and how is this making my records jump about?
help!
Pete
View 1 Replies
View Related
Nov 15, 2006
I have a form where the recordsource is a stored procedure.
This forms shows a list. When the user clicks on a column title, the column is sorted by using OrderBy in VBA:
f.i.:
Private Sub lblCode_Click()
Me.OrderBy = "strCode"
End Sub
As a result, in the properties of the form, for the property Order By, the fieldname is filled in (f.i. strCode).
This works fine, but when the form is closed, access saves the property Order By.
Next time the form is opened, it gives an error: The recordsource <storedprocedurename> specified on this form does not exist.
When I remove the fieldname in the Order By property of the form, the form opens without an error.
I have tried to clear the Order By property by using Me.OrderBy = "" in the Close event of the form, but this does not work.
Any solution ?
View 2 Replies
View Related
Nov 29, 2013
I'm trying to have a form sorted in a particular order when it's opened.
When using the "order by" option in the properties sheet, it is sorted the way I want the first time I open the form.
However, if I change the order afterwards while using the form (for instance, by right clicking and selecting another sort order), well the next time the form is opened, it is sorted with this new sort order, and not the default one.
So I tried to add the following code :
Code:
Private Sub Form_Open()
Me.OrderBy "employee_name ASC"
Me.OrderByOn = True
End Sub
Which failed epically.
View 6 Replies
View Related
Oct 17, 2006
I have a subform within a form that displays filtered data based on dynamic selection criteria which is assigned to the RecordSource/OrderBy of the subform. On this form, I have a "Print Results" button -- OnClick, I would like the RecordSource/OrderBy of the subform to be copied to the Report, then run the report. What I have that doesn't work is:
Reports!rpt_frmTaskMain.RecordSource = Forms!frmTaskMain.frmTaskSub1.RecordSource
Reports!rpt_frmTaskMain.OrderBy = Forms!frmTaskMain.frm.TaskSub1.OrderBy
On execution I get error: "Object doesn't support this property or method"
I am thinking this can be done, and likely have the references wrong. Help?
View 1 Replies
View Related
May 14, 2013
I have a tabular form that is based on a select statement (Query) in the source property. It filters & sorts from this select statement. permanently
I have just added a
Me.Filter = "[Form1].[InDate] = #" & MyValue & "# "
Me.OrderBy = "Indate DESC"
This all works fine, filters & sorts on the existing results.However this new filter & sort seems to remain no matter what I do.I have used every variation one me.filter ="" with Me.FilterOn = True, False & every combination.
Me.Filter="" & Me.FilterOn = False does initially seem to clear the lsit to show all records but as soon as type anything that filters the Form on the original, existing underlying select statement the extra Filter Property & Sort properties are occupied with the last filter/sort.
I have manually deleted any remaining Filter Or Sort properties direct from design view, saved, compacted & saved again.Once I use the new me.filter it will always returned as soon an any filter on the form is applied.I definately am not setting it again anywhere. Is there something better than me.filter ="", seen clearallfilters mentioned but not sure if this would apply just to the current form but reluctant to use a blanket clear as I do have quite a lot of other things going on.
Not sure when but many months (If not years) ago my form source stopped being a query and started using the query (sql) direct from the source property.I have left it this way as not sure if any consequence of moving back but don't remember how or how it happened. What are the pros cons of using a separate query.
View 4 Replies
View Related
Aug 16, 2014
I am able to filter a data on a continuous form using drop downs and then the following code attached to a cmdbutton to create a report of the filtered data.
Code:
Private Sub Command30_Click()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If Me.FilterOn Then strWhere = Me.Filter
DoCmd.OpenReport "rptconveyorerrors", acViewReport, , strWhere
End Sub
On the same form where I filter the data i can sort it by clicking the headings aswell, however when i generate the report using the above VBA it doesn't take the sort with it and just generates it without the sort.
I am using the following VBA to sort my form
Code:
Me.OrderByOn = True
If Me.OrderBy = "[empname] DESC" Then
Me.OrderBy = "[empname] ASC"
Else
Me.OrderBy = "[empname] DESC"
End If
Me.Refresh
I thought it may be possible to use the following sort of VBA to pass the sort however i cant get it to work:
Code:
Private Sub Command30_Click()
Dim strOrder As String
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If Me.FilterOn Then strWhere = Me.Filter
If Me.OrderByOn Then strOrder=Me.OrderByOn
View 1 Replies
View Related
Apr 21, 2006
Hi, I'm new here, so I hope I'm posting this in the correct place. I've searched the forum to see if there are any existing threads that might help me, but I've not found anything that does...
(I think this thread ( http://www.access-programmers.co.uk/forums/showthread.php?t=93444&highlight=Copying+data )may be trying to achieve something similar to me, but I'm a beginner and don't really understand it)
I shall stop waffling! I'm not entirely sure that what I'm trying to achieve is possible, I expect it probably is!
Right, I have a form (frmGroupRegister, which contains exactly the same fields as the table it comes from, tblGroupRegister), which consists of three things:
-GroupDate - The date a group took place on. It is my primary key, as no more than one group occurs on a specific date.
-ParentList (A listbox which contains a query showing the ID number, forename and surname of everyone in a table, tblParentDetails)
-ParentsAttending (A blank listbox)
I would like to place buttons in between the ParentList and ParentsAttending, which would allow users to conduct a 'register' of attendance by copying individual/multiple details from ParentList into ParentsAttending (much like you get when choosing which fields to include in a form when using a wizard for example). I would also like them to be able to remove people from ParentsAttending by using a button in case of accidentally adding the wrong person into the ParentsAttending box.
I'm aware that another, probably simpler way of achieving this would be to use a tick-box system, but I feel that visually, the first method would both look better and demonstrate who is present more clearly.
Any help would be much appreciated, but my Access skills are quite basic and things will probably need to be spelled out for me.
I'm using Access 2000 and Windows XP.
Thanks for your help,
Alice :)
View 1 Replies
View Related
Jun 10, 2015
I am using the selections made of the form to generate a query for the user.
I have a CITIES listbox that is populated with values from a stored query.
I would like to make it multi-select and populate a LOCATIONS list box and a NAMES list box based upon the CITIES that are selected.
I have the locations currently populated from a stored query that reads the City selection from the Form. It looks like this
Code:
SELECT DISTINCT (t_location.LOCATION) AS Expr1
FROM t_location INNER JOIN t_asset_master ON t_location.LOCATION_PHY_ID = t_asset_master.LOCATION
WHERE (((t_location.CITY)=[Forms]![MasterQueryGenerator]![CityList]));
I also want multi-select so that is you can un-select all and get the results for all cities.
Here is my half thought approach.
Code:
Private Sub CityList_AfterUpdate()
'Dim LocQryStr As String
'Dim r As Integer
'Dim ctl9 As Control
'LocQryStr = "SELECT DISTINCT (t_location.LOCATION) " & _
[Code] ...
I intended to have the variable LocQryStr as the row source but I abandoned the idea of having multi-select when I saw that .Selected(I) never returned true. Its like the values aren't read in this subroutine.
View 5 Replies
View Related
Sep 9, 2013
I have a list box called "product list box" based on a query called "searchqry", i also have another listbox called "type list box" , how do i get the type list box to only show "types" based on the section in products list box?
View 1 Replies
View Related
Dec 14, 2014
Using a popup form
1. On my main form, I have a listbox, I would like to edit the values of the listbox.
To do this, I have a popup form with 2 listboxes, one to have the values of the listbox on the main form, and the other listbox with option values for the 1st
1) how to i pass the rowsource sql of the listbox on the main form to the listbox on the popup form
2) how on closing the popup form, do i update the rowsource sql listbox on the main form from the changed value of the popup form listbox rowsource sql
View 3 Replies
View Related
Oct 23, 2013
Okay then, after much trouble and confusion, I finally realized I need to use an Extended listbox in order to allow for multiple items to be selected from a list on my form (rather than the evil multiple selection combobox!).
However, now I am trying to figure out how to make one listbox (IndustryClassification) only be visible if the item "Industry" is selected in another listbox (TypeOfBusiness). Coding I can use for this in the AfterUpdate event of the listbox?
View 7 Replies
View Related
Jun 17, 2005
Hello,
I've got this multiple select listbox which writes data into a textbox:
Private Sub List2_AfterUpdate()
Dim Cursisten As String
Dim ctl As Control
Dim Itm As Variant
Set ctl = Me.List2
For Each Itm In ctl.ItemsSelected
If Len(Cursisten) = 0 Then
Cursisten = ctl.ItemData(Itm)
Else
Cursisten = Cursisten & "," & ctl.ItemData(Itm)
End If
Next Itm
Me.txtCursisten = Cursisten
End Sub
And I've got a SELECT ALL button to select all records in the listbox:
Private Sub cmdSelectAll_Click()
On Error GoTo Err_cmdSelectAll_Click
Dim i As Integer
If cmdSelectAll.Caption = "Alles Selecteren" Then
For i = 0 To Me.List2.ListCount
Me.List2.Selected(i) = True
Next i
cmdSelectAll.Caption = "Alles De-Selecteren"
Else
For i = 0 To Me.List2.ListCount
Me.List2.Selected(i) = False
Next i
cmdSelectAll.Caption = "Alles Selecteren"
End If
Exit_cmdSelectAll_Click:
Exit Sub
Err_cmdSelectAll_Click:
MsgBox Err.Description
Resume Exit_cmdSelectAll_Click
End Sub
The only thing is that when I use the SELECT ALL button, the function List2_Afterupdate doesn't work anymore. There must be a simple solution but I just can't figure it out. Can anyone please help me?
Tnx a lot!
View 13 Replies
View Related
Jul 16, 2006
Hello everybody,
Hopefully somebody can help me on this one. I searched the whole internet and access forums, but I didn't find the exact solution for my problem.
I've got a table with students, a table attendance, where I now only save the students who are absent, but I would like to save also the students who are PRESENT (at the same time).
I've got a combobox where I filter the Class, which then updates a listbox with the students from that class. What I do now is select the students from the listbox and then press a save button and it saves the records to the table absence with STATUS: ABSENT.
I would like to save the NON selected students also in that table, but with PRESENT in the column STATUS.
I thought of making another listbox next to it, where after selecting the absent students, they wil apear and disappear in the PRESENT table so I can store all the information.
But the only problem is that I can find this solution when the listbox is populated by a list of values instead by a table or query. And the other solution is to store the temporary data into 2 different tables, but that's not working for me because it's a multi user database and everything will be messed up.
Hope that someone can help me, I will be very happy.
View 4 Replies
View Related
Jan 20, 2014
Firstly, is it possible to hide unchecked values in a listbox? I have a user with several roles and I want to only show the ticked roles in the listbox.
Secondly, can you create a hyperlink on listbox values? i.e, if I click on "Manager" in the roles listbox, it follows that to another form and opens the record about managers?
View 10 Replies
View Related
Nov 23, 2006
What I am trying to do is to swap item position in ListBox without adding/removing items. Index doesn't need to change, but the text show in the ListBox need to. For example.
lstField contains:
Item 1
Item 2
Item 3 (selected)
What I want to do is when click a button (cmdUp) the Item 2 should be Item 3 and Item 3 should be Item 2
Item 1
Item 3
Item 2
How can I do this in MS Access.
Please let me know.
View 2 Replies
View Related
Jul 12, 2007
1)How to set show or not show key column for list box in propeties windows?
2)For list box, after doing
lstListBox.rowsource="select field1, field2 from tbl"
lstListBox.requery
How to
a)Set show keycolumn;
b)make the first item in the listbox selected?A
View 1 Replies
View Related
Feb 8, 2005
I have a list box that displays different times. Can some one help, how do get the value from the list box that was selected to show in a text box on another form?
Thanks
View 3 Replies
View Related
Mar 7, 2005
I have a list box on a form. What I want to do is get the value of one field in the first line of data in the list box.
View 1 Replies
View Related
Jul 18, 2005
I've been searching through all the forums and sample databases for listbox searches and can't seem to make head or tails of them. They are all so different and I can't seem to get something to work.
Could someone please point me in the direction of a fairly simple listbox search that I can understand.
Basically I have two cascading combo boxes and would like to search after this has been filtered into the listbox. Then I would like to select one of the options and have the whole record show up. Any help at all in pointing me in the right direction would be extremly helpful. I've been working on trying to get a search function to work for 3 days and haven't had any luck at all.
View 1 Replies
View Related
Oct 5, 2005
I would like the ability when I select multiple items in a list box, to count the selected items. I have a email management Module and I have users select multiple emails from this list to perform group emailing. I would like to provide a number of selected emails. Does anyone know how. I am trying to avoid several hours of worki in trying to figure this out. I apreciate it,
Thanks Antonio
http://www.newgenerationaccess.com
Application & Database Development Company
View 1 Replies
View Related
Dec 2, 2005
I have a form with a listbox on it. the list box contains site numbers. (lstSite)
i would like the user to select which site/s they want information for.
i have another query that includes site,date,and other data
i put in the criteria for site as being forms!main!lstSite.value but it is not showing any records, even if i did select the site. i also tried forms!main!lstSite. this did not work either.
I have a feeling i am refering to the object incorrectly.
any help would be greatly appreciated
tkaplan
View 1 Replies
View Related
