I have an unbound form with a combo box and a bound subform on it.
I am using the combo box to get a value to filter (and display) records in the subform.
My code is:
Private Sub cboSessions_AfterUpdate()
On Error GoTo cboSessions_AfterUpdate_Err
Me.SSubform.Visible = True 'subform was initially invisible
Me.SSubform.Form.FilterOn = True
Me.SSubform.Form.Filter = "[LinkID]=" & Me.cboSessions
Me.SSubform.Form.Requery
cboSessions_AfterUpdate_Exit:
Exit Sub
cboSessions_AfterUpdate_Err:
MsgBox Err.Description, vbCritical, "PROGRAM ERROR"
Resume cboSessions_AfterUpdate_Exit
End Sub
The subform is initially invisible to stop it showing all records.
This works fine AFTER the first selection(!) - the first Update of the Combo Box just doesn't filter the subform??
I have tried putting code in the main form's On Load Event - allocating a value to the Combo Box and running cboSessions_AfterUpdate but that does not work either.
I use Access 2003 and saw Ken Higg's sample database to filter records in a form using a combobox. I managed to replicate his settings and wondered about an extension to the principle, but I don't know if it's possible.
In the sample, a value is selected using a combobox and all matching records are filtered so it's possible to scroll through them. Is it possible to have a second (or more) combobox on the form so it is possible to filter on one or another criterion? This would mean one combobox filling the other one and I realise that the one that Ken provided is unbound.
The reason that I'd like to do this is, for instance, if there are records with Surname, 1st, 2nd and 3rd lines of the Address and Postcode, I may wish to filter records by entering the Surname or Postcode. Each of these fields would have to be displayed in a combobox. As the sample is set up at present, only one of the Surname or Postcode has to be selected via the combobox and the other is a textbox.
It may be that what I'd like to do isn't possible - or someone may have come up with some other idea. I'd prefer to have the search/filtering on the form, rather than using the buttons on the Form View toolbar.
I do review SQL sometimes to check what's going on. Access has been a steep learning curve, but my understanding of queries and relationships improved a lot recently.Some of the finer points of forms are just giving me frustration. My form related knowledge probably lags behind my data/table/relationship/query related knowledge. Its probably simple, but as you know these things are not too intuitive at first.
I have a query which (built on another query and some tables in turn) delivers exactly the data I want. I want that data to come up in my form (or datasheet subform actually) BUT, I want to be able to filter for PERSON.ID. I want the user to be able to use a combo-box to do that filtering.
OK then, main form, with unbound combo-box at the top. I assume it should be unbound because I don't want any data edited based on selections which are supposed to cause temporary filtering only (correct me if I'm wrong). For the Row Source I've used a qryWholeName. This query creates a WholeName for each person, and includes their PERSON.ID as well.
[URL]....I cannot see the example DB they have attached in the thread. Finally, my database is likely to be accessed by several people at once. Different users will want to be selecting and filtering for different people simultaneously.
I made a subform based of a query that I put in a main form. The subform contains a date field, where a full date is inserted (example 24.12.2015.). What I am doing is making a form where the subform would be filtered by a combobox by months. This may be wrong (correct me if it is), but for values in the combobox I placed number from 1 to 12 (as months). This combobox would then only show records from that month. I used a Change function in VBA on the combobox but I keep getting an error.
This is the VBA on the combobox where I change the record source of the subform. I probably made the WHERE part wrong but dont know how to fix it.
Combobox name is cboMonth. Field where the full date is, is DateOut. Subform is called subCustomer.
Code:
Private Sub cboMonth_Change() Dim SQL As String SQL = "SELECT qryCustomers.SID, qryCustomers.KID, qryCustomers.MMT, " _ & "qryCustomers.Owner, qryCustomers.User, qryCustomers.Policy, " _ & "qryCustomers.DateOut " _ & "FROM qryCustomers " _ & "WHERE Month([DateOut]) LIKE '*" & Me.cboMonth.Text & "*'" Me.subCustomer.Form.RecordSource = SQL Me.subCustomer.Form.Requery End Sub
I have a table which has a "Start" and "End" fields, and i made a query based on this fields which continues to look up the next points until the last end points. I wanted to filter the last end points on the query table that was related on the combobox value.
I have almost completed creating a continuous form which I want users to be able to filter though the records based on to/from date, badgenum and a response string.
I then want to have a button, btnSelect, that will select the remaining records and open a report based off only the remaining records.
1.) Everything on the continuous form works except when trying to filter a combo box, cboBadge, field name "BadgeNum". BadgeNum is data type "short text". I have modified the code below from the following Allen Browne link. It keeps throwing a debug on me.Filter = strWhere at the bottom of the btnFilter sub.
2.) As far as the btnSelect and printing remaining records, I guess I'm not sure where to start.. I currently have another print button that will print individual records only which works fine.
[URL] ....
Code: Private Sub btnFilter_Click() Dim strWhere As String 'The criteria string. Dim lngLen As Long 'Length of the criteria string to append to. Const conJetDate = "#mm/dd/yyyy#" 'The format expected for dates in a JET query string.
I have a database that is used to generate quotations. I have a form that I want to use to Re-Quote something. By Re-Quote, I mean I want the employee to be able to go in and search for the Part desired for Re-Quoting and create a new quotation with all of the information from that part and have the ability to change any of the information as needed. The Form is unbound, and has tab control with three tabs. I would like to search by three different categories "QuotationNumber", "Customer", Or "PartNumber". How do I have the combobox filter the list box below based on what is being typed in the combobox?
I'm trying to use a cascading combo box to filter the options that can be entered into a second combo box.
In frmBabies, a Mode of Delivery is selected from 8 options. 4 of these are "normal" deliveries and 4 are caesarean sections.
A second combo box selects the Indication for Operative Delivery. The table tblIndOpDel contains options for normal as well as caesareans. I would like to filter the indications so that the user cannot enter an indication for normal delivery for a caesarean section.
Both tblIndOpDel and tblDelMode have a foreign key to tblDelModeCat, which categories deliveries as normal or caesarean.
I'm stuck on how to proceed from here. Sample database attached.
I have 2 comboboxes, the first one is called "activity", whereby I have 3 options to choose from, and the second is called "level".
When I click an "activity", for example Drawings, I want the "level" combobox to list a unique set of options for that category. and if I click on a different "activity" for example Planning, I want the "level" combobox to show a completely different set of options.
If anyone can give me any help on how to do this then I would be very grateful!!
I have two Tables, "staff data" (staffID, name) and "issues log" (IssueID, StaffID, 20+ fields about individual Issues). StaffIDs are linked together so each employee can have multiple issues..I have (so far) Form1 containing individual Issues that can be filled in, or retrieved one at a time / scrolled through in IssueID order
What I want is a second form, containing a combobox with all StaffIDs (+ names) listed; when a StaffID is selected, a table is loaded containing all Issues for that employee. When any of these is selected, Form1 is loaded
I have created the second form, including the combo that contains both StaffIDs and Names. I can't yet grasp how to show the set of Issues applicable to that employee when I change that combo...If I could have the combo on Form.
I have a ComboBox which looks up the records in a query. The query contains the names of current employees. The table upon which the query is based lists Employees and their start date and finish date. The Query is based on finish date being Null (ie they're still working here).
In the main form which details tasks, I would like the ComboBox to just list current employees (which it does as it's based on the query) but when an employee leaves, their name is deleted from all previous records.
Is there a way to keep the ComboBox options limited to current staff, while also keeping the names of previous employees in the form?
I checked the table which the form is based on and it still has the values, so I'd like to show in the form too as that's what people will be working on.
Made some tables with data, then wanted to filter it by form with comboboxes approx 10 of them, so started to write a simple query but it doesn't really work. Then I found this forum and when started to review some threads, noticed that some (most) of you write a query in vba under a button with on_click function.
The question is, what is the correct way of writing a query? Is is a vba code under a button, or is it a code in the actual query?
I can create a dropdown list to filter my form (text) however im struggling to get back to showing all the data prior to my drop down selection.
ive tried refreshing and requerying with no success.
I would either like to get a drop down with the addition of "all records" in it or a refresh button.
the form that this is on is a sub form.
I could reference the drop down to a number if required.
Additionally: When I have selected my choice from the drop down box it blacks out "selects all". Is there away of making the list just select and lose focus so to speak.
I have added a listbox inside my form that needs to filter records based on a field in the databases. For example if I have a record that has the field filled in with a text "reports", then it should only show those record that are on the second listbox. I have duplicated Outlook as an access database where I store all the old emails. The first listbox is called "FolderNames" where it shows all the folders names that are in the column called "FolderNames". The second Listbox is called "Subject" and shows all the subjects from the colun called "Subject".So when I select from the first listbox "Reports", then all the records that have the field filled in with "Reports" should show up in the second listbox.
I have a summary form with the company name,week number, week-ending automatically populated. e.g. this week is week 4 and users enter records for week 4 as the week number is automatically generated. Basically the system will just recognise today's date and generate the week number. Problem is I am trying to display previous week records and I do not know how to filter it as my week number is generated automatically, week by week. All the other forms are linked to this week number. How can I display records for a specific week that I want? say, I want to display all records for week2, etc. I am dumbfounded as I am on a learning curve with Access.
I have two text boxes and I'm wondering how I can filter my records of my table (through my query), between two numbers on my form. So for example lets call the two text boxes Num1 and Num2, the logical process is:
Code: IF Num1 or Num2 IS empty THEN filter records with "*" ELSE IF Num1 and Num2 contain a number THEN filter records between those two numbers
SQL, Me.Filter, BETWEEN in criteria. How I can accomplish this.
I have created a form for a table which contains ~600 movies and their name, genre, rating, director, year it was made, and length (min).I need to be able to enter numbers into the two Year boxes, and then it filters the movies in my database and only shows me records from between those two years. The years in my database are just in one column in the format of: XXXX e.g. 1996 etc..I've tried the code:
Code:
Private Sub Year2_AfterUpdate() Me.Filter = "[Year] BETWEEN" & Me.Year1 & "AND" & Me.Year2 Me.Filteron = True Debug.Print "[Year] BETWEEN" & Me.Year1 & "AND" & Me.Year2 End Sub
Year1 and Year2 are the boxes Year: and To: respectively. [Year] just being the column name which contains all my years.When I try to run my query it shows me my records, but it shows me all of them! It doesn't filter it at all!
I have a main form with 3 subforms. Each subform is identical except for the value of the filter property. The filter is for the same field, but with a different value for each subform. So, for example, the first subform has a filter of:
Code: [WBS Element]="DEF" And [Period]=Forms!frm_ProjectFinancials!Period
while the second subform has a filter of:
Code: [WBS Element]="PPE" And [Period]=Forms!frm_ProjectFinancials!Period
and the third subform has a filter of:
Code: [WBS Element]="EXP" And [Period]=Forms!frm_ProjectFinancials!Period
The recordset for each subform results in a single record with numeric values in each field or no records at all. When the resulting recordset is empty (no records), the bound text fields on the subform display as blank. I want these fields to display 0 instead of blank so I can use them in other calculated fields. Functions such as Nz or IsNumeric do not work since there are no records and the values are neither null nor numeric.
How I can display zeroes in the bound fields when no records exist that meet the filter criteria? Or is there a way that I can dummy a resulting recordset to have all zero values when there would otherwise be no records?
I have been building a database for use in a charity shop and am struggling with an issue regarding one of the forms.
I currently have a form which displays all expenses from the shop. I have added a combo box to the top of this form which allows users to filter records based on an expense ID Code. It all works fine but I would like to be able to add a start date and end date box to the form also so that records can be shown between two dates.
I have tried adding parameter boxes to the query which runs the form (which works) but the issue I am having is that when a new id is selected from the combo box the parameter boxes pop up again asking me for start date and end date again. This happens every time a new combo box id is selected.
I think the way resolve this issue may be to add a start date and end date box to the form but I don't know how to implement this.
Please see the attached files for images of what I currently have. The forum won't let me post images directly here until I have 10 posts so I have had to attach the files instead.:
Query running the form - Attachment 1
The Form itself - Attachment 2
Combo Box - Attachment 3
Bound Column on combo box - Attachment 4
Code in Combo Boxes after update event - Attachment 5
I have created a piece of code that filters a sub form of diary records using criteria the user has selected or entered. It was working fine when I made it a month or so ago and now is seems to be returning incorrectly filtered records, for example, I enter 2 dates to return all diary entries between the 2 dates. Yet it misses some records out that should be within the date range, and it sometimes include records that are outside the date range. I am also getting an error when I try and filter the diary entries via the supplier, "data type mismatch", here's the code that, bearing in mind, was previously working fine.
Code: Public Function filter_diary() Dim dbs As Database 'Dim qdf As QueryDef Dim Sqlstr As String Dim sqlstrwhat As String
I need to be able to use a combo box on a form to filter the records shown. The fields on the table are as follows:
ID No, Ref No, Start Date, Comp date, Price etc.
I have set the form up to run off a query that shows all records with a certain ID No. Each ID No has a number of reference numbers. I would like to use a combo box on the form so the user can select a Ref No and the form would be filtered to show only the details of that specific ref no.
I'm trying to hash two scripts I've found into 1 functioning filter, however I'm still relatively new to vba and can't figure out how to get this working.
I'm trying to use Allen Browne's Search Criteria:
with another snippete of code I found here:
Code: 'Purpose: This module illustrates how to create a search form, _ where the user can enter as many or few criteria as they wish, _ and results are shown one per line.
[Code]....
It's the date part I'm having trouble with, the rest of the search criteria work fine without the date, but I can't get it working when I try to modify and merge the date sections of each code.
Also I'm using a listbox for the "Yesterday";"Last 4 days";"Last 9 days" and not a combo box.
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.
I'm building a filter using comboboxes and I'm having trouble with two of them. One is to filter by records that are completed or by records that are not completed. The other combobox is to filter by records that have comments or by records that have no comments.
Filter7 Row Source Type: Value List Row Source: Completed;[tblMainData.Status]='Complete';Not Completed;[tblMainData.Status]<>'Complete' Column Count: 2 Bound Column: 1
Filter8 Row Source Type: Value List Row Source: Comments;[tblMainData.Comments] Is Not Null;No Comments;[tblMainData.Comments] Is Null Column Count: 2 Bound Column: 1
The VBA Code is: ================== If Me(Filter7) <> "" Then strWhere = strWhere & " " & Me.Filter7.Column(1) & " " & " And " Debug.Print strWhere End If If Me(Filter8) <> "" Then strWhere = strWhere & Me.Filter8.Column(1) & " And " Debug.Print strWhere End If ==================
I'm getting an error on building the strWhere variable. Can I use filter criteria statements in a combobox column?
