Forms :: Applying Sort And Filter In Form Results In Design Changes To Form
Dec 27, 2014
When I'm applying a sort and filter in a form, Access is updating the Filter and Order By properties of the form, so that it is possible to re-use in conjunction with Filter on Load and Order By on Load properties. However, a consequence of this is that when the form is closed, it prompts the user whether they want to save the design of the form. I want to circumvent this as I don't want to re-use the sort and filter and I don't want to be prompted to save the design of the form.
Although I can circumvent this by closing the form using a method that doesn't prompt for saving, the additional complication here is that the form in question is in the Navigation subform of a Navigation Control. Hence when I click on a another Navigation button, it (not me) closes my current form and hence prompts me whether I want to save the design of the form (if I have been sorting and/or filtering). I can't see how to circumvent this and the prompting is resulting in unacceptable usability.
View Replies
ADVERTISEMENT
Sep 3, 2013
I have a pretty simple form that includes subform. Subform's table is linked to main form's table with parent/child relation. Connecting fields are main table's ID field and corresponding field in child table. Subform is in datasheet view. This is pretty basic stuff so there should not be any problems, but every time I apply a quick filter in main form it causes data in subform become invisible. There is single row in subform, but all it's fields are empty.
View 5 Replies
View Related
Aug 14, 2013
Essentially I have a table for rooms around my school and what the rooms contain. Most of the details for the rooms are Boolean (e.g. If the room has a projector, Yes/No).
So what I am trying to do is have a form where i can select a checkbox(s) and if i tick one, a query that holds all the room details will only show rooms with projectors in them, which i can then display those results in another form.
My question is how do I get the check boxes in the form to narrow down the room results to only show the ones with the criteria i have selected in the form?
View 1 Replies
View Related
Jun 6, 2013
I am fairly new to VBA and am trying to apply a filter within a form so that agent records can be filtered based on an agent's name (so that they can only see information that refers to them).
I also want a record to be removed when it has been completed (when this habbens a date completed field will populated) however I do want to see those completed records that refer to the agent that have been completed today.
Here is my code. The 2 filters work on their own howver when I put them together I get a mismatch error.
If Me.txtRole = "Agent" Then
DateCompletedFilter = "(DATECOMPLETED Is Null) Or (DATECOMPLETED = Date())"
AgentFilter = "CASEOWNER ='" & Me.txtName & "'"
DoCmd.ApplyFilter , AgentFilter And DateCompletedFilter
Exit Sub
End If
View 13 Replies
View Related
Oct 20, 2014
The recordsource is a query with over 6,000 records. The form currently lists the records in datasheet format with header and footer for things like buttons and filtering. The client wants to be able to go from page to page of the souce query, showing 100 records on the form at a time.
But at the same time, they should be able to filter or sort the data source in it's entirety. The person who created the form came up with what seems like an awful solution to the problem. It seems to use a random number generator to determine how many records to portray at a time. I see this in the code as well as in operation, because the number of records on page to page varies. It doesn't even start out at 100! Worse yet, using a sort on the page only sorts the records that are visible.
View 9 Replies
View Related
Apr 30, 2013
I have a Main form with a sub form.The main form is a customer record (form view), and the sub form is all other customers (same fields) datasheet view.I have a navigation bar to filter (using different queries) the results in the sub form
same postcode
Same company (different location)
everyone except Main form customer
I cannot get a different filter (Query) to show only customers whose name begins with the same letter as the customer in the main form?I have been trying Like but think I am referencing it wrong, as it prompts me to enter a value rather than using the value in the field in the main form.This is what I have in the query (of the subform)
Customer ID: <>[Forms]![NewAllCustomerListF]![CustomerID]
Customer Name: Like "[Forms]![NewAllCustomerListF]![CustomerName]" & "*"
View 2 Replies
View Related
Jun 22, 2014
Was trying to use a button to apply a filter from a form to a subform
the subform (frmLogOverview_Subform) has a field call AttendedBy_FK and I wish to filter this from the main form (frmCallOverview)
how can I do it ?
View 2 Replies
View Related
Sep 23, 2014
While applying filter in forms, i have 6 fields in the form and i want to display the data even one of the filed is with 0.
I used the below code, but this one display the data where all the fields are 0. I tried or in place of and but still its not working.
Private Sub Form_Open(Cancel As Integer)
Me.Filter = "[Placed]=0 and [receievd]=0 and [Ordered]=0 and [processed]=0 and [delivered]=0 and [closed]=0"
Me.FilterOn = True
End Sub
View 2 Replies
View Related
Mar 25, 2013
I am having a problem with a filter that i am trying to apply to a subform.
I have a button on the form that when clicked should filter the subform (which is in datasheet view) to the criteria i set.
This criteria will eventually run from a combo box but I wanted to just get the filter working first.
I put the following code into the onclick event of the button:
Items.Form.Filter = "Items.Form.[Master Category] = 2"
Items.Form.FilterOn = True
I chose the criteria 2 for the filter just as a test as I knew there are some records with that value in the master category field.
The problem is when ever i click the button to apply the filter it clears all the data as if it has not found any records with that value.
Is my syntax and method OK? Why its filtering everything out?
The only other thing to consider is that the field I am filtering on was set up using a lookup wizard linked to a table so the values stored are a foreign key (hence the value being 2 rather than something descriptive).
View 3 Replies
View Related
Jul 31, 2014
I have a Main form, and a subform which lists client details. On the Main form I have an unbound field. I want to be able to type a word into this unbound field and have it display all company names that have this word in them. ie. I type "Ltd" into the unbound field and it displays all companies with "Ltd" in the title.
I have created a query that does exactly this (Like ("*" & [Enter Word] & "*")), it displays a dialog box and I type in "Ltd" and it displays all relevant companies.
I have tried everything I know to make this work when I use the unbound field on the Main form, but I've had no luck.
View 3 Replies
View Related
Mar 6, 2015
I'm trying to sort and filter a continuous form. Sort ascending and filters works perfectly fine. But my descending button doesn't work. I basically have a combo box which contains a field list. Then two buttons (asc. and desc.), then a text box for filter, a button to filter, and another button to reset filter. Here's my code:
Code:
Private Sub cmdAscending_Click()
If IsNull(Me.cboField) Then
MsgBox "Please choose a field.", vbOKOnly, "No field to sort."
Else
Me.OrderBy = Me.cboField
Me.OrderByOn = True
[Code] ....
View 8 Replies
View Related
Aug 28, 2013
I have after much stress gotten my comments history field to appear on a form.
The following code gives me what I needed
Code:
=ColumnHistory([RecordSource],"LastUpdateBy","[ID]=" & Nz([ID],0))
I have only one simple thing left, that is perplexing to say the least.
Some comment fields will be many short notes, spread over time.
My desire is to change the sort order of the results so the most recent (rather than the initial) comment is at the top.
View 5 Replies
View Related
Apr 10, 2005
Not sure whether this really belongs in the Forms, or VBA or forum, picked Forms because I think it stems from that, despite probably involving VBA in the solution...
I'm getting used to using filter by form and find it quite useful for those off-the-cuff queries, however, it is possible to 'capture' the records so that the results of the filtering can be passed to a readymade report (so that they can be printed out in a more presentable manner than the table view)?
(To what would the Record Source of the report be set?)
Tim
View 1 Replies
View Related
Jul 23, 2013
I want to use buttons on a form to change the sort order on a continuous form. In the buttons click event I am using a public function (named Sort_1) to change the sort order. The first element of the event call is the name of a generic query (named Sort_1_Query1) and the query field to sort (LAST_NAME OR FRIST_NAME, depending on the button.)This is the Click Statement.
=Sort_1("Sort_1_Query1","LAST_NAME")
This is the Public Function
Public Function Sort_1(SortName As String, FieldName1 As String)
DoCmd.ApplyFilter SortName, FieldName1 & "between 'A' and 'Z'"
End Function
I think the problem is in the use of quotation marks or trying to pass the query field name to the Do Command or the use of an ampersand.
View 4 Replies
View Related
Jan 3, 2006
I have a query form that allows user to search by two criteria (in which, user can select "And" or "Or" clause for the two criteria). I also allow user to filter the results by date range. On the form, I have two command button, one will prompt the results in a query table, and the other will prompt a report.
Problem 1
I manage to prompt report with a date range (i.e. two unbound text boxes for start date and end date). But, I dont' manage to disable the filter if the date range is null. Below is the code for this report command button. Should I use a toggle button to make a select case?
Private Sub cmdReport_Click()
Dim varItem As Variant
Dim strDocName As String
Dim str1MainCate As String
Dim str2MainCate As String
Dim str2MainCateCondition As String
Dim strDate As String
Dim strSQL As String
Dim strFilter As String
' Build criteria string for 1st ComboBox
For Each varItem In Me.fstMainCate.ItemsSelected
str1MainCate = str1MainCate & ",'" & Me.fstMainCate.ItemData(varItem) & "'"
Next varItem
If Len(str1MainCate) = 0 Then
str1MainCate = "Like '*'"
Else
str1MainCate = Right(str1MainCate, Len(str1MainCate) - 1)
str1MainCate = "IN(" & str1MainCate & ")"
End If
' Build criteria string for 2nd Combo Box
For Each varItem In Me.SecMainCate.ItemsSelected
str2MainCate = str2MainCate & ",'" & Me.SecMainCate.ItemData(varItem) & "'"
Next varItem
If Len(str2MainCate) = 0 Then
str2MainCate = "Like '*'"
Else
str2MainCate = Right(str2MainCate, Len(str2MainCate) - 1)
str2MainCate = "IN(" & str2MainCate & ")"
End If
' Get 1toggle button condition
If Me.optAnd2MainCate.Value = True Then
str2MainCateCondition = " AND "
Else
str2MainCateCondition = " OR "
End If
' Build SQL statement
strSQL = " SELECT NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment FROM NewsClips " & _
"WHERE NewsClips.[1CategoryMain] " & str1MainCate & _
str2MainCateCondition & "NewsClips.[2CategoryMain] " & str2MainCate & ";"
' Build criteria string for Date
If Not IsNull(Me![dateTo]) Then
strDate = strDate & " NewsClips.IssueDate Between #" + Format(Me![datefrom], "mm/dd/yyyy") + "# AND #" & Format(Me![dateTo], "mm/dd/yyyy") & "#"
'Format(Me.dateTo, "mm/dd/yy")
Else
strDate = strDate & " NewsClips.IssueDate >= #" + Format(Me![datefrom], "mm/dd/yyyy") + "#"
End If
' filter string
strFilter = strDate
' Open report
strDocName = "RptCateDateQry"
DoCmd.OpenReport strDocName, acViewDesign, , strFilter
With Reports(strDocName)
.RecordSource = strSQL
.Filter = strFilter
.FilterOn = True
End With
DoCmd.Save acReport, strDocName
DoCmd.OpenReport strDocName, acViewPreview
Exit_cmdReport_Click:
End Sub
Problem 2
I have no idea how to filter the query results by date range in the query table. Below is the code of the query table button, which do not offer the filter feature. Would really appreciate it if you can give me some advice.
Private Sub cmdOK_Click()
On Error GoTo cmdOK_Click_Err
Dim blnQueryExists As Boolean
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
Dim qry As ADOX.View
Dim varItem As Variant
Dim strDate As String
Dim str1MainCate As String
Dim str2MainCate As String
Dim str1MainCateCondition As String
Dim str2MainCateCondition As String
Dim strSQL As String
' Check for the existence of the stored query
blnQueryExists = False
Set cat.ActiveConnection = CurrentProject.Connection
For Each qry In cat.Views
If qry.Name = "QryCateDateForm" Then
blnQueryExists = True
Exit For
End If
Next qry
' Create the query if it does not already exist
If blnQueryExists = False Then
cmd.CommandText = "SELECT NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment FROM NewsClips"
cat.Views.Append "QryCateDateForm", cmd
End If
Application.RefreshDatabaseWindow
' Turn off screen updating
DoCmd.Echo False
' Close the query if it is already open
If SysCmd(acSysCmdGetObjectState, acQuery, "QryCateDateForm") = acObjStateOpen Then
DoCmd.Close acQuery, "QryCateDateForm"
End If
' Build criteria string for Date
If Not IsNull(Me![dateTo]) Then
strDate = strDate & " NewsClips.IssueDate Between #" + Format(Me![datefrom], "mm/dd/yyyy") + "# AND #" & Format(Me![dateTo], "mm/dd/yyyy") & "#"
'Format(Me.textStartDate, "mm/dd/yy")
Else
strDate = strDate & " NewsClips.IssueDate >= #" + Format(Me![datefrom], "mm/dd/yyyy") + "#"
End If
' Build criteria string for 1MainCate
For Each varItem In Me.fstMainCate.ItemsSelected
str1MainCate = str1MainCate & ",'" & Me.fstMainCate.ItemData(varItem) & "'"
Next varItem
If Len(str1MainCate) = 0 Then
str1MainCate = "Like '*'"
Else
str1MainCate = Right(str1MainCate, Len(str1MainCate) - 1)
str1MainCate = "IN(" & str1MainCate & ")"
End If
' Build criteria string for 2MainCate
For Each varItem In Me.SecMainCate.ItemsSelected
str2MainCate = str2MainCate & ",'" & Me.SecMainCate.ItemData(varItem) & "'"
Next varItem
If Len(str2MainCate) = 0 Then
str2MainCate = "Like '*'"
Else
str2MainCate = Right(str2MainCate, Len(str2MainCate) - 1)
str2MainCate = "IN(" & str2MainCate & ")"
End If
' Get 1MainCate condition
If Me.optAnd1MainCate.Value = True Then
str1MainCateCondition = " AND "
Else
str1MainCateCondition = " OR "
End If
' Get 2MainCate condition
If Me.optAnd2MainCate.Value = True Then
str2MainCateCondition = " AND "
Else
str2MainCateCondition = " OR "
End If
' Build SQL statement
strSQL = " SELECT NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment FROM NewsClips " & _
"WHERE NewsClips.[1CategoryMain] " & str1MainCate & _
str2MainCateCondition & "NewsClips.[2CategoryMain] " & str2MainCate & _
str1MainCateCondition & strDate & ";"
' Apply the SQL statement to the stored query
cat.ActiveConnection = CurrentProject.Connection
Set cmd = cat.Views("QryCateDateForm").Command
cmd.CommandText = strSQL
Set cat.Views("QryCateDateForm").Command = cmd
Set cat = Nothing
' Open the Query
DoCmd.OpenQuery "QryCateDateForm"
' If required the dialog can be closed at this point
' DoCmd.Close acForm, Me.Name
' Restore screen updating
cmdOK_Click_Exit:
DoCmd.Echo True
Exit Sub
cmdOK_Click_Err:
MsgBox "An unexpected error has occurred." _
& vbCrLf & "Procedure: cmdOK_Click" _
& vbCrLf & "Error Number: " & Err.Number _
& vbCrLf & "Error Description:" & Err.Description _
, vbCritical, "Error"
Resume cmdOK_Click_Exit
End Sub
Sorry for posting this question again, as I thought it's better to make it a seperate posting, rather than a reply to my early post. Your advice will be greatly appreicated.
View 1 Replies
View Related
May 23, 2014
I have kept the names as simple as I can for the example sake to which I can substitute my names in after.
I have a split form - form1, I have a check box on this form - checkbox1 - That I would like to use to make only the checked results from the table - table1 - show and if it is not checked to show all the results. How would I go about doing this? The table field is called field1.
View 6 Replies
View Related
Mar 6, 2006
Hey, i'm working on creating a database.
it's involves a customer booking a ticket for a flight.
i have three tables, customer, booking and flight.
anyways, i'm having trouble applying the price the customer needs to pay
say there's an attribute on table Flight -> flightprice.
when a customer makes a booking, discounts are applied to the price.
discounts include:
1. Special seasonal discount, (eg from Sept to November) - 4%
2. Member discount - 6%
3. frequent flyer discount - 5%
4. other discount - 4%
the thing is that these discounts are stackable. ie, applied together.
(eg a person eligible for member discount and frequent flyer discount will get 11% discount total)
i know that if only one of the discount rates apply,
then i'd be able to create a new table called Discounts
and have a one to many relationship with the Booking table.
but i'm not sure how i need to bypass this when several discount rates can be applied at once to a single booking.
-------------------
also on another note.
how can i make the seasonal booking only be applied if the date of the booking lies in between the promotion season?
View 1 Replies
View Related
Jun 2, 2014
Basically I need to design form that looks very similar to either Split Form or Subform. I have attached a print screen of what I roughly need. The form is split into two parts. In the first/top part there are some List Box/Date fields that act as filters to the bottom form, so the bottom form displays only records that match values in red. The second part of the top form has some field that require input from user and then these records are added to the bottom form. I was trying to use both Split Form and Subform and none of it is working,I have attached an example of database and this form would be based on data from Query1.
View 5 Replies
View Related
Feb 20, 2007
Good Morning
I am attempting to apply a filter to a query i have setup. I only want the user to be able to search for specific words within two fields (technical and behvaioural) i have setup. I know i have to use a WHERE expression but am confused as how to exactly setup the expression... any idea's???
View 3 Replies
View Related
Oct 25, 2006
I am using MS Acess2000 and need to make a report that will be passed around with production work. My primary key is the invoice number of the work order. Currently to open an invoice i have a macro, attached to a query with the following qualifiers; Like [Enter invoice]
This pulls up the current record fine.
for the form and flags the folloing in the property filter sectin of the form
(((([CustomerTableMasterRef].[Invoice]) Like [Enter invoice])))
PART 2
Now I am trying to use microsofts how to filter a report using a forms filter...
This picks up on the Invoice query as shown above but does not just insert the query results...
Is there better code or another way to approach this... Currently i am using:
Name:cmdOpenReport
Caption: Open Report
OnClick: [Event Procedure]
Private Sub CmdOpenReport_Click()
If Me.Filter = "" Then
MsgBox "Open an Invoice First"
Else
DoCmd.OpenReport "rptCustomers", acViewPreview, , Me.Filter
End If
End Sub
Using this code not only does my report not detect the correct fields to import data (no data is filled in) but it requerys the invoice or atleast should, which I could do with out all of that code...
Where should i go from here?
View 1 Replies
View Related
Sep 24, 2013
I am using Microsoft Access 2010. It provides great filtering and sorting options in the ribbon for any object. But what if I hide the ribbon and want to create custom buttons on the form to do the filtering and sorting job? There are some filter options available in macros but are not quite like the ribbon's own Filter button. When the Filter button is clicked from the Robbin, a filter menu pops-up under the active field, which doesn't happen when I try to do it using Macro functions like "Apply Filter" or "Set Filter" etc. I want to have that big "FILTER" button from the Ribbon on my form.
View 2 Replies
View Related
Mar 16, 2014
I am trying to apply a filter on a subform but i get "Type mismatch" and i dont know why below is the code.
Basically when they select a line on one subform it filters another from that selection is it somthing to do with the dates?
Code:
Dim MyProd As String
Dim ReqDate As Date
MyProd = Me.ProductCode
ReqDate = Me.RequestDate
Forms!FrmReplenishments.FrmMasterReplenDetail.Form.Filter = "[ProductCode]= '" & MyProd & "'" And "[DeliveryDate]= #" & ReqDate & "#"
Forms!FrmReplenishments.FrmMasterReplenDetail.Form.FilterOn = True
View 2 Replies
View Related
Jul 14, 2015
I am using two combo boxes to filter a list box with the code below. The combo boxes work, but when the form opens, the list box is empty until it is filtered using the combo boxes.
I want the list box to return all records when no filter is applied.
Here is the code:
SELECT Q_Gender_Statistics.ParticipantID, Q_Gender_Statistics.Gender, Q_Gender_Statistics.Date,
Q_Gender_Statistics.Year, Q_Gender_Statistics.[First Name], Q_Gender_Statistics.[Last Name],
Q_Gender_Statistics.[Other Names], Q_Gender_Statistics.[Passport No], Q_Gender_Statistics.[Duty Station], Q_Gender_Statistics.[Contact Number] FROM Q_Gender_Statistics WHERE (((Q_Gender_Statistics.Year) = Forms!F_Gender_Statistics!cboYear) AND ((Q_Gender_Statistics.Gender) = Forms!F_Gender_Statistics!cboGender)) ORDER BY Q_Gender_Statistics.Date DESC;
View 4 Replies
View Related
Apr 14, 2015
I have a form named "frmItems" with a list box in it called "lstItems"
I would like to create a find (or search) button for it, but how? I also have a search box called "txtSearchI" ...
View 14 Replies
View Related
Sep 23, 2013
I have a "Date Select" form that is linked directly to a table that only has one column in it, "Dates". The column in the table is a Date/Time column and is sorted in date order, Lowest to Highest, and when any new dates are added to the table (through another form) they re-sort into date order.
The "Date Select" form allows the user to highlight a date and open a record containing information logged against that date, however the dates on the "Date Select" form will not sort into the logical date order that the table that feeds it is in!
I've set the "Date Select" form Property "Order By" to [Datse_List DESC], (Dates_List being the name of the List Box on the form that displays the list of dates from the table), and set the "Order By On Load" to "Yes", but still the dates in the list box remain out of order.
Dates are entered into the table "Dates" column retrospectively / randomly.
how to make the dates in the list box conform to Lowest to Highest?
View 5 Replies
View Related
Aug 1, 2013
I have placed a filter button on a form as a filter and written the following on-click event procedure:
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdApplyFilterSort
Me.Filter = "ACCOUNT_DO_NOT_EMAIL = 'HS'"
Me.FilterOn = True
When I click the filter button I get a blank message box titled 'Microsoft Access" and an OK button, when closed the filter works perfectly.I have checked this procedure in other forms and it works without showing the blank message box.The only difference with this form is that its control source is a union query.
View 10 Replies
View Related
