Modules & VBA :: OrderBy When Opening A Form
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 Replies
ADVERTISEMENT
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
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
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
Jul 22, 2015
I've got a table with field define as hyperlink which stores link to a external pdf.
Then i create a form including this field and add a unbound field which has got intermet browser format (as a type).
What code I have to put in onclick event in a hyperlink property sheet to open PDF inside the form?
View 1 Replies
View Related
Feb 10, 2015
I am using Access 2010 and want to open a new form based on an ID from a previous form. Access makes this very easy to do in the macro builder but I am unable to replicate this in VBA afterwards. The reason behind this is I want it to also run some code to export some info to Outlook. I am only filtering the ID from one form to the next. The ID is from a single table.
View 12 Replies
View Related
Nov 21, 2013
I have a table that has two forms linked to it - one is the Main Form which is used to input the data for the bulk of entries, however on some entries there will be some additional data required which the other form (lets call it Time Form) takes care of.
What I would like to do is have a button on the Main Form that opens the Time Form, and then go to the specific record that is open on the Main Form;I have an ID field (Autonumber + Primary Key) which displays in a text box on the main form, and it would provide the number of the record for the second form to open.
View 7 Replies
View Related
Jul 19, 2013
IThe db has a form called ClientFormNew which logs client details into a table called Clients.Each client has a unique client ID in the table called ClientID. Sometimes Clients call back for further information and we want to log that information into a related table called ClientHistory.Each record in the ClientHistory table has a unique ID called HistoryID which is the same number as the ClientID so these fields are related.
When a client calls back and the telephone adviser opens up the ClientFormNew form and discovers they have called before they need to click on a button called Client History which then opens up the ClientHistoryForm..I am struggling with the code to put on the Client History button as I want the new form to display the details of the clients first contact then allow details of the second contact to be added in a new row. Also the new form needs to show all that client past history which would be in the form of the following fields from the ClientHistory table.HistoryID, ContactDate, Name, ContactMethod, ContactReason, AdviceGiven, Notes.
View 2 Replies
View Related
Sep 21, 2014
I am trying to program a button on my ms access form to open up an excel file.. So far the simplest code ive found online was from URL....
<code>
Private Sub Command57_Click()
Dim xlTmp As Excel.Application
Set xlTmp = New Excel.Application
xlTmp.Workbooks.Open "C:Excel1.xls"
xlTmp.Visible = True
[code]...
However the code doesn't work, any way to open a file from within access?
View 1 Replies
View Related
Jun 16, 2015
I want to that the WHERE clause for a SQL statement that I am using options on a form to build. I intend to use the clause in opening a datasheet form.
This is the code I have for getting the substring
Code:
Dim intPos As Integer
Dim tempString As String
Dim BaseQueryFormStr As String
'BaseQueryFormStr is used to reopen the BaseMasterQueryFrm with the specified parameters
tempString = "WHERE"
intPos = InStr(1, strSQL, tempString, vbTextCompare)
BaseQueryFormStr = Left(strSQL, intPos - 1)
MsgBox (BaseQueryFormStr)
The value of intPos remains=0 and when the program hits the second to last line I get "run-time error 5"
View 10 Replies
View Related
Jun 25, 2013
I have tried a few times to get open args to work while opening a new form.I never could get it to work.
View 4 Replies
View Related
Sep 10, 2014
I have the following module in my database to allow for multiple alarms.
It all works great, except before showing the actual alarm record called, the form first pops up showing the first record in its record source.
I.e. it flashes up showing the first record in the table, then changes to the 'correct' record.
I would just like to change it so it either doesn't show until the record is loaded, or it shows blank to begin with.
Here's the module...
Code:
Option Compare Database
Option Explicit
Public clnPopUpAlarm As New Collection 'Instances of frmPopUpAlarm
[Code].....
View 2 Replies
View Related
Dec 4, 2013
How I configure my Form Window to pop up as soon as I open my Access Database?
View 3 Replies
View Related
Dec 5, 2013
i have a calender of which show's records on date box's i m having a issue when i double click on a datebox it opens a form call update which is filtered on open to show only records for this date but on date boxs on 1st to the 11th i have add the code to do this but when the form open's the form is blank and shows no records but the code works fine for 12th to 31st which i can't understand why? as im using the same code that does dateboxs 12th to 31st on datebox 1st to 11th but doesnt work i'm pulling my hair out on this one !!
View 3 Replies
View Related
Apr 7, 2014
I have a Form opening from Access Options. I would like to close this Form using the Timer. The following is the code I have used but it is not working.
Private Sub Cover_Page_Form_Load()
OpenTimer = Timer
End Sub
Private Sub Cover_Page_Form_Timer()
If (Timer - OpenTime) = 5 Then DoCmd.Close acForm, "Cover_Page_Form", acSaveYes
End Sub
Next question. If I can get this to work can I then use a DoCmd to open new Form within the code above or do I need a new process.
View 5 Replies
View Related
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
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 14 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
Oct 20, 2014
I have a form based on an underlying query in a .mdb database. I want to be able to navigate backwards and forwards in the form using .movenext and .moveprevious etc.
Which sort of recordset should I use? I've seen discussions about ADO and DAO, and have come across the term "forward-only", which I'd not been aware of before.
Having made the choice, what is the correct syntax for opening the appropriate recordset, and also for .movenext and .moveprevious etc?
View 6 Replies
View Related
Jul 2, 2013
I'm building a lab environment into one of my projects. I'm testing the distribution of values over a largish number of attempts to create a unique value.I have a table called LAB_UniqueIDTest, with two fields:
LAB_ID - the string value being tested, and
LAB_UsageCount - the number of times the value has been created.
I'm trying to open the table using the following code:
Code:
' Initialize access to the LAB_UniqueIDTest table
Set rs = New ADODB.Recordset
rs.Open "LAB_UniqueIDTest", _
CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
I have similar snippets of code all over the application - either with a literal (as here) or as a string parameter.I do not, ever, use the options parameter of rs.open.I get error message "Run time error -2147217900 (80040e14) Invalid SQL statement - expected DELETE, INSERT, PROCEDURE, SELECT or UPDATE
My guess is it's because somehow the Open procedure is trying to interpret the tablename as a SQL statement. But how come it isn't this obtuse at other times?
View 6 Replies
View Related
Apr 14, 2015
I have been tasked with creating a tool to analyse mobile phone bill data and present the analysis, and our recommendation, to a customers. Being new to Access (other than basic tuition) this has been a slow uphill task, which is finally nearing completion, however there is a problem which I have not yet been able to overcome.
The requirement is for the DB to open first on a splash screen (lets call it Form A) with fancy picture where our customer is selected from a combo box, the customer is then telephoned, a linked computer screen is established and our staff then click "Go" to proceed to a second form (Form B) showing an account overview and more details.
The problem I have is when "Go" is clicked, the second form loads via on click event, and even populates the correct customer in its combo box. Unfortunately that is as far as it gets - the combo does not look up the information. The customer needs to be selected again for the subforms and subreports to load with the customer overview. To clarify, form B just sits there blank until the customer is re-selcted from the combo box in form B.
View 9 Replies
View Related
Sep 4, 2013
I am building a faux Electronic Medical Records database for the purpose of training med students. I need a command button to pull up a report , but i want to delay the opening of the report (as if waiting for "tests" to come back or be uploaded) is there a way to do this with VBA? i read about the sleep api but i dont know how to get it to work or where to put the code
so what would i add and where would i add it to delay opening the report that is called EKG W/ Subreport ...
View 3 Replies
View Related
