Hi there
This im sure is a easy question but I am just trying to learn access so I am having a few problems.
I have made a form that contains 2 comboboxs and a listbox.
How can I use the 2 combobox to filter a table and to place the information into the listbox.
Is it possible to use either 1 of the combo boxes or both to get the filtered result.
Any suggestions would be great!!
TIA!
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.
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!!
Thanks
Angela :eek:
I posted a few weeks back to see if I could get some advice on adding up ethnicities [from a combo box] in a query.
My advisor, in all his wisdom suggested this code:
SELECT [Ethnicity], count([Ethnicity])
FROM Clients
WHERE [Ethnicity]='Latino'
GROUP BY [Ethnicity];
This worked beautifuly! However, I also need the query to count the other selections too: Asian, Caucasian, African-American...having the number of all these ethinicities added up on one query report is ideal.
How do I incorporate this into the code? I tried some possibilities and have failed...
hi, i have list box and the source coming form Query. i want when i click on
one in list box want it will show [faults] field in textbox from query
the field [faults] is a memo can someone help me
__________________
David
I have a CRM database which also handles billing for my company. I have an Order Form which displays info from the Orders table, and also various subforms from related tables (Order Details, Payments, Collection Notes). The Payments table has a One to One relationship to the Orders table and the two are linked by the Key Field Order ID.
Here is the problem, if I open the Order Form and navigate to any record the correct payment information is displayed. However if I use a Filter on the Order table the Payment information disappears and only a blank payment form displays - making it appear that a payment has not been made. If I remove the filter the payment information is still gone until I close the form and reopen it. Also, this does NOT happen to my other subforms (ie Order Details) - note that the other subforms are for tables with a One to Many relationship.I have tried basing the subform on the actual table Payments and also on a query of the payments table.
I am using a form to filter information so that I can open a report. The report displays information pertaining job costs. Each month the company I work for records their labor, costs, and travel in a form with a year to date amount. Right now, I am having a problem opening up a report for one specific job and a specific month. So what I want the dialogue box to do is to pick from a list of jobs then pick from a list of months, click a button that prompts a preview of the report.
View 14 Replies View RelatedI want to filter a database based on the info typed into the text box, after this, clicking on the button search and filter the database..I have an issue in my code, because I know just how to filter it based on a field but not for all..The fields I have are: Batchnumber, ModelnumberID, OperadorID, Datet, TestResults, Units and Status
My code is:
Me.Requery
Me.Filter = ""
Me.FilterOn = False
[code]....
I created a navigation form on which I put a form call [frmAnimal Setup].
I then placed one combo box on the Navigation frm Header. I have bound it to its source and it actually queries the tbl and show the right info. However when I select one, it will not let me.
In addition, [frmanimal setup] will not allow me to select a breed although it does query the tbl and shows the right information.
Hi guys
Just wondering how could a make a listbox or combobox to have the names of all or some of the tables that are currently in my database
I only know how to get the values from a table or query in RowSource using SQL language... but how can I display the names of the tables
Thanks in advance for the help.. I really appreciate it
I'm trying to filter the listbox using a combo box.
So I have a Combo box that has the list of the subjects and a listbox with the list of students.
I wanted to show the list of students who are registered in selected subject.
Here's a link to a post with background (see the relationships image at the top): [URL] .....
So I got everything to work exactly as I wanted it to.. the listbox has its rowsrc manually changed via VB on form open or record change... so everytime the record changes or the form opens, VB creates a query string that pulls the TFE_Num and TFE_Name from the DB for only those records where the forms current key (me.key.value) equals the TFE_key values. And everything works.
The problem is that this VB based query is very slow... so everytime the record changes, we wait 1-5 seconds for this listbox to pull data and display it. My question is this: Is there a better way to do this, some way that doesn't require VB based SQL statement. I'm asking because all the non-listbox controls on the form use a control source and they get updated instantaniously on form open or record move, but the list boxes that are unbound and are populated using VB SQL are really slow... and there are really only 1 or 2 records ever displayed in the list box.
I have tried to set the rowsource for the list box within the properties tab and comment out the VB SQL code, but haven't succeeded. I suspect the issue is that the form is linked to all fields in the master table while the list box is linked to a 1st and 2nd generation child table... I just can't get it to work! I've tried simple adding the TFE tables and fields to the forms record source poperties, but when I do that I can only navigate to records that have TFEs associated with them.
I suspect I could resolve the issue myself, if only access queries allowed "me.key.value" to be used in the WHERE statement. Am I correct that you cannot do this in Accesses query builder or the SQL version? When I tried, it worked but then I notices the me.key.value got converted to a number (the first record) when it ran the first time.
I have a list of staff that have a conflict of interest with a particular entity. As a result, these staff are not allowed to interview these entities.
I have a query that matches all staff with their respective entities that they have a conflict of interest with (CoI) and that is functioning correctly.
When the form loads to add an interview, there is a listbox that pulls all the people who are not allowed to do an interview with that particular entity. That is also working correctly.
I have a subform, that is a continuous form, which will allow the user to add staff, one at a time, via a drop down box. These people are stored in their own table with a FK Id to the interview table. This also works correctly.
How to filter the combobox on the subform to exclude the people in the listbox.
Here is what I have tried, loosely based on what I have found on Google and researching here. I am 100% sure it is not working correctly, but what I am missing.
The query the listbox is based on has 3 colums, the ID, the Name, and the business contract number.
Code:
Private Sub Form_Load()
Dim strSource As String
Dim i As Integer
For i = 0 To Me.lstCoI.ListCount - 1
[Code] ....
In the immediate window, I get the following result:
SELECT [staff] FROM lutStaff WHERE Staff <> name1
SELECT [staff] FROM lutStaff WHERE Staff <> name2
SELECT [staff] FROM lutStaff WHERE Staff <> name3
SELECT [staff] FROM lutStaff WHERE Staff <> name4
SELECT [staff] FROM lutStaff WHERE Staff <> name5
SELECT [staff] FROM lutStaff WHERE Staff <> name6
The issue is that the box is not filtering all the names out of the list it is built on. It is only filtering out the last name.
Obviously I need to save the results for comparison, but I am at a loss on how to do that.
Hi all,
In form I have listbox. I need show in listbox only filtered records.
For example, I have 20 records in form, listbox shows 20. I make some filter in Form, Form shows 10 records - 10 records must show listbox too.
How can I make it?
Thank You in advance for answers.
Ok I have a list box (CounselorInitials) and a listbox (AssignedToYou)
The list box has 6 columns and is using a query (AssignedToMe). The important one is the 6th one (Counselor). All working off a table called DityLog.
I want to select initials in the combo box and then it will filter the listbox and only show records that are the same.
So if I select initials MC from the combo box I want the Listbox to show all records that have the initals with MC.
Whenever I create a combobox, Access asks me if I want to grab the values from another table, or if I just want to manually input the possible values.
My question is: is one of the two solutions "better" than the other?
Let's imagine I am creating a Form where I can create a new Contact, and in a listbox (with multiple selections allowed) I want to list the possible character traits, so I have
- Funny
- Annoying
- Smart
- Interesting
- Good-looking
- ...
I think that if I need to store in the Contact records these possible traits, I will have to create a Many-to-Many relationship (therefor creating a junction table) between Contact and Traits.
On the other hand, could I do everything without grabbing values from an extra table and directly inputting the values in the listbox? How would these elements be stored in the Contact record if I allow multiple values in one single field?
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.
What is the easiest way to do this?
Any help would be greatly appreciated.
Hi,
I have a table with training records. Each attendee is listed with date and the related document he was trained on.
Now i want to make a listbox where the user can choose between the different dates of the trainings. Now when several people where trained on the same day, i get some dates back multiple times in my listbox. When you had 100 on the same day, 100 times the date in that listbox. Is there a way to filter out the same dates and show each date only one single time in my listbox?
Thanks
I'm stuck
I have List box called 'lstNote' base on a query 'qryNotes' with filter tied to textbox 'filter' on my main form 'frmNotes'. The filter specifies criteria for field called 'ntype' on 'qryNotes'.
I have buttons that change the filter field to specific criteria. The listbox works fine this way for filtered records.
I want to also be able to not have any filter for the 'lstNote' so that all records for 'qryNotes' are listed without criteria.
I tried, but was not successful, to set the filter criteria to a wildcard or no filter with
IIf(IsNull(Forms!frmNotes!filter),(qryNotes.nType = Like *),Forms!frmNotes!filter)
But this produces no records at all.
Any suggestions? Do I need to go down another road?
Basically I want to listbox to either show any of the filters and also all records.
I have a form called “frm_TrainingLog” based on “TrainingLog” table. This form is continuous and tabular – I need to have it this way. In the header of this form I’ve created a listbox called “QuickSearch” which displays staff details i.e. StaffID, Name etc.
The mainform and the listbox are linked using the following code:
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[StaffID] = " & Str(Me![QuickSearch])
Me.Bookmark = rs.Bookmark
The above code does partially work and selects the right record in the mainform but it doesn’t filter out rest of the records which are not equivalent to selected StaffID from the listbox.
I could really do with someone’s help….thanks
I'm creating a Form called Pharmacy where I can select a [Diagnosis] from a combo box in the form. This combo box source references a table called tblDiagnosis where each Diagnosis also has a multi-valued field called [Indications].
The Pharmacy form also has a multi-select listbox with all possible Indications. I would like to write a VBA code such that when a Diagnosis is selected, the Indications for that Diagnosis are automatically selected/highlighted in the listbox on the form as well.
As such, by selecting a Diagnosis, all the indications attached to that Diagnosis will automatically be selected; however, if additional indications are needed, they can still be selected afterward.
I know that there is a function Me!Listbox.Selected(i) = True, where i is the row of the entry in the listbox. However, the i in the listbox does not correspond to the ID of the Indication. I think that if there is a way to select listbox items by name, that would be much more efficient.
The reason I have a listbox, is because this listbox of Indications then references another table called tblDrugs where all drugs that are approved for the selected indications selected are filtered and displayed.
I have a listbox with several options.
When the user selects one of these items, i want a combo box's text to be changed to the selected item in the list box.
The listbox is named : D_OutputLsb
The combobox is named : D_ComponentNameCmb
I am trying to write a code that will execute at the change even of the combobox/Listbox and when a character is typed in it then all the data from "DocumentType" field whose first character matches with the first character typed in Combo/Listbox will be stored in it.
The following code doesn't work:
Private Sub ComboBox4_Change()
Dim strText, strFind As String
strText = Me.ComboBox4.Text
If Len(Trim(strText)) > 0 Then
strFind = "BarcodeRef like '" & strText & "*'"
End If
[Code] .....
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?
Thanks,
PC
Hi everyone
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.
Thanks for your patience.
Juan
I have three comboboxes on a form – Fabric, Degree, Color.
The row source type for each is Table/Query. The row source is a Select Distinct query. I’m using a query so the list will populate itself. The choices the user makes in the comboboxes are used to filter called forms.
Would there be a way to include the word “ALL” in a drop down list in addition to the data generated by my query. If the user chooses “ALL”, the called page will not be filtered on that particular choice. I just created a record with “ALL” in those particular fields. That seems to work, but I think there might be another way.
Thanks