I have a combo box based on an employee table. I would like a former employee's name to be removed as a choice combo box list, but their name kept in the employee table (and noted as an former employee) and other records related to them.
View RepliesI have a list box and a combo box on a form based on a query. The list box is a multi-select, with column values of Client, ClientEmail, and Medical.
Medical is a simple yes/no field, and it is what I want the combo box to sort by. I have a Where clause in the query: WHERE (Medical=[forms]![frmOne]![cmbbx]), and the only values in the combo box are Yes and No (1 column). I also have the combo box set to run a requery macro after updating. I've also tried to change the macro to VBA:
Code:
DoCmd.Requery "lstbx"
When I pick a string from the combo box, the list box just goes blank, rather than updating to clients who either do or don't have Medical, and I can't understand why.
I have a form wich includes listbox of employees. When I click on them, it shows his or her data (date of birth,address,etc...). For every employee there are also three diferent checkboxes - if he is regularly employed, temporaly employed or student.So now what I need is to filter listbox of employees - only student or only regular or only temporar or all.I was wondering if I can do that with combobox, but I really dont know how. I am prety new to VBA.
View 13 Replies View RelatedI need the ability to assign employees to a different task in each of four different timeslots on a daily basis. What I would like to do is as I assign an employee to a task in timeframe 1, I would like for them to become unavailable to assign to another task in the same timeframe.
For Example: Three employees to assign to three tasks at the 8 AM to 10 AM time slot.
Employee
Task
Time
Assigned
Emp 1, Emp 2, Emp 3
Task 1
8 AM to 10 AM
Emp 2-Task 1
Emp 1, Emp 3
Task 2
8 AM to 10 AM
Emp 3-Task 2
Emp1
Task 3
8 AM to 10 AM
Emp1-Task 3
It would be nice to set this up in a form where my person doing the scheduling can assign an individual to a task and that individual name disappears from the combo boxes, list boxes, or is "greyed-out" in a listing of employees available for the time period for the remaining assignments.
Allen Browne's "Use a multi-select list box to filter a report" solution, in particularly with two multi-select list boxes? The code works fine for me for either box so long as I code it for one box alone. Combining the two into one code results in a type mismatch error. I'm trying to use the code to pass the contents of both multi-select boxes as Where conditions to a report. Both boxes are based on number fields. To try to isolate the problem, I've removed Allen's setDescription and OpenArgs conditions. We're unfortunately still on Access 2003 as the company desires to squeeze every dime by using until end-of-life next year.
Code:
Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the list box.
Dim varItem As Variant 'Selected items
[Code] .....
I have a form with 2 list boxes, part number and modification. There is a subform containing another list box that is supposed to show the part information (bpn,vendor,status,etc.) that corresponds to the selected part number/modification in the parent form list boxes.
The part info list box has multiselect enabled and what i want to is be able to select multiple line items and press a button which then sets all of the selected line items status to "Request Removal". This is my code for the button:
Private Sub removeButton_Click()
Dim varItem As Variant
With Me.acbModList
For Each varItem In .ItemsSelected
MsgBox (Me.Status.Value & Me.[Part Number].Value)
Me.Status = 6
Next
End With
End Sub
The msgbox was for debugging purposes. Here's my issue; the for each actually does iterate through each selected item but the value for the line item doesn't change along with it. For example, when I selected 3 items, the msgbox will pop up 3 times but each time will have the same information (first item in the table) even when that item isn't selected.
My next issue is that I am receiving an error message with "Me.Status = 6" stating "You cant assign a value to this object". 6 refers to the id of the status i want to set it to.
I have seen a few articles here and there on using a combo box to filter records in a sub-form and to filter records in another combo box, but I am not getting anywhere. I hope someone can belp
Exercise 1
For this exercise, I have the following tables:
tblClients containing client names
tblProjects containing some project details
I want to set up a simple form, so that the user can select a client name from a combo box on the main form which filters the project detail records in the Projects sub-form.
Once the user enters project details, I want this info as well as the selected ClientID to be fed back to tblProjects.
Exercise 2
I have the following tables:
tblProjects as above
tblWorkstream containiig names of workstreams and some other details.
Every project has one or more workstreams
I have a form where users will enter hours worked on each workstream. They will first select Project from a combo box on the main form. This should then filter records to be displayed in the Workstream Combo box, before they can then enter hours.
Please let me know if you need me to explain any part of this better.
Thanks in advance.
ps. I am a novice, so I'm hoping to do the above using default MS stuff, not with complex programming!!!
Thanks
i have a table with three column Named
1-State
2-City
3-Customer
on a form i m placing 3 combo box for each column how can i filter combo 2 from table after select value from combo 1
I'm having a little difficulty setting up a combo box to filter another combo box. I've actually got one working but the second one is giving me all sorts of errors. On the attached database, there's a form called frmAddNewRecord. At the top of the form there's a combo box which allows you to select a name and another combo box beside it which acts as a filter so only names from a certain section are shown (working fine).
The subform which is attached to this form and contains the training details, has another combo box which allows you to select a job...I've tried to add a combo box beside this to filter it but I can't get it to work.
I'm writing a form to filter a listbox by what is entered into a textbox. I need it to requery the listbox when the textbox changes on (keydown) but it does requery on any of the textbox procedures eg keydown onchange keypress etc. Is there another way of doing this. I can't understand why it wouldn't requery the listbox on keydown. The code works under a button but it has to do it as your typing.
Please help
I appreciate your help in advance. I am new to most of this and I need a little assistance. I have searched the web and found some articles etc... I am just not sure how to apply it to mine because I am unfamiliar with some of the wording etc.. I understand a very little of what people are saying but I can't quite put it all together.
I have a form called "reportfilterfrm" in this form I have 3 list box's. On 2 of the list box's multi select is selected to "None" and I have them bound to the criteria field in my query and that works perfect. The 3rd list box multi select is set to "Simple" and I am not sure how to get the query to refrence this.
The query is called "reportfilterqry"
listbox #3 is called [opponent] I would like it to be the criteria for [opponent] in the "reportfilterqry" It is a text field.
Thanks again for your help. I really appreciate it!
ok i have 3 tables.
JOB TABLE
jobid(pk)
date
companyid(fk)
staffid(fk)
COMPANY
companyid(pk)
name
STAFF
staffid(pk)
companyid(fk)
name
the primary and foreign keys are joined and RI is enforced. like this infact http://www.cambridgeupholstery.co.uk/relate.JPG
using a method ive just learnt ;) thanx pat and rv the user can choose a company and a staff member for each job. the relevant id is placed in the job fields and thats all good.
the form looks like this. http://www.cambridgeupholstery.co.uk/myform.JPG
eventually there will be many companies and many more staff members.
what i would like to happen on the form is when the user selects which company they are dealing with...only the relevant staff who work for that company are displayed in the listbox.. in other words i want to filter the staff names by the chosen companyid.
what code do i need to place and where.. my company list box is called "complist" and my staff list box is called "stafflist" and the form they are on is called "enterjob"
i hope that is clear.. thank you very much for any potential replies.
I have a query based report Linked to a form. That is, in my form, i have a combo box. Selecting this combo box pulls out a certain field values in list box in the same form. (Cascading Combo/ List Boxes).
So after i select a certain value in combo box, it shows a certain field values in the list box. Now, i want to open a report and view only the records that has the selected combo box and list box values.
Note: I have seen a guy made an access report based on a query. He made a form with a combo box and a list box.
The report is based on the combo box value selected by the user. Once, the user selected a value in the combo box, the list box updates automatically. ( I have done till here successfully). Then he used a toggle button on the same form.
This toggle button opens the report with the combo box value as the criteria but filters the report based on the field value in the list box.
How to place a toggle button with a filter command to open a query based report?
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.
View 14 Replies View RelatedI want to be able to show all records from tblRecords where, The [tblUser][PositionID] = The Value selected on the list box.The problem being I don't know how to do this with them all being on seperate tables and linked via relationships. I.e. [tblRecords] only holds the UserID, [tblUser] has the PositionID.
I also need this value to keep building towards my filter string, i.e. I can select this position ID and records from within the past 4 days.I've attached pics of the form and relationships.
Here is my current code:
Code:
Option Compare Database
Option Explicit
Private Sub cmdReset_Click()
'Purpose: Clear all the search boxes in the Form Header, and show all records again.
Dim ctl As Control
[code]....
I have 2 forms which allow the user to first select a catergory. They can then select a sub category based on the selection made in the first box.
I have the form working 90% but can't get the list to filter based on the previous selections.
The code I'm using to generate the listbox rowsource is shown below;
"SELECT tblcatctry.CtryID, tblcatctry.Country, tblcatvtry.zoneID " & _
"FROM tblcontactsCountry RIGHT OUTER JOIN " & _
"tblcatctry ON " & _
"tblcontactsCountry.CtryID = tblcatctry.CtryID " & _
"WHERE (tblcontactsCountry.CompanyID IS NULL) OR " & _
"(tblcontactsCountry.CompanyID <> " & VarCompanyID & ") " & _
"GROUP BY tblcatctry.CtryID, tblcatctry.Country " & _
"ORDER BY tblcatctry.Country"
I want to be able to add in a join to the table tblcontactszone which has the selections previously made for the fields, ZoneID and CompanyID. How do I filter the above further?
I've set up a split form for data entry on a form called "frmqryTask" that once opened, I'd like to be able to filter using list box controls.I've set up a couple of different reports with cascading list boxes to only pull in the information wanted on the reports and would basically like "more or less" the same functionality within a split form. I know I could do this before the form is actually launched using the same cascading list boxes, however the users need to easily by able to filter once inside the form. (I tried to show a couple people how to filter on the bottom section on a column called "SourceName" and they would up changing the values. SourceName is linked to another table as a combo box thru the lookup wizard in the table setup.)
There will probably be three different list boxes to allow for different filtering based on the combination of the values for;
1) SourceName
2) SourceNameType;
3) SourceNameStatus.
I'm not familiar with listbox yet and i want to filter my form using it.
I have two separate listboxes which display "category" & "type" data from the same table.
Here is the situation i wanted for my listbox.
1. Select one data from "category" listbox.
2. Then it will automatically filter data from "type" listbox or it will list all related "type" data corresponding to "category" data.
3. Then select one data from "type" listbox and it will filter all related data on the form/sub-form.
Is it possible to do that way? Can i do it on multicolumn listbox instead of using 2 listbox?
I have a form with data fields and a list box, data is coming from a query. When I add a toggle button to apply a filter to the data on the form, the data in the fields are filtered, but the list box still shows all the data items. How do I use a toggle button or something on the form that when activated it filters the data in the list box and the list box only shows the filtered content.
View 14 Replies View RelatedThe main form has textbox & a Listbox with which I filtered a datasheet inside a sub form. Everything worked fine only the listbox [Discipline] is not working !! it cuase Run-time error: 3831. The multi-valued field "[Category]" cannot be used in a WHERE or HAVING clause.So how do I filter a multivalued Listbox field [Discipline] ?
Private Sub cmdSearch_Click()
'On erorr GoTo errr
Me.tblFLM_subform1.Form.RecordSource = "SELECT * FROM tblFLM " & BuildFilter
Me.tblFLM_subform1.Requery
Exit Sub
errr:
MsgBox Err.Description
End Sub
[code]....
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?
View 6 Replies View RelatedI have 7 multi-select list boxes, which I want the user to be able to select multiple items and have it filter a subform by what is selected. The subform will not be visible.
Here is the code I tried for filtering my subform (which is throwing an error when I call it).
Private Function MasterSearch()
On Error GoTo Error_MasterSearch
Dim StrgSQL As String
Dim WhereClause As String
StrgSQL = "SELECT * FROM MasterTbl"
[Code] ...
The above code was to test just 1 of the 7 listboxes. When I call it on click for the command button, it throws an error about the "Call MasterSearch".
On [Form1] I have a Multi-Select Listbox[List1] which shows Job Numbers [WBS] (The job numbers displaying are a result of a separate query filtered by the Fiscal Year combobox). When I click [CMD1] I want [Query1] results to only be selected [WBS] from the form.What is the best way to code this? I'm a beginner when it comes to VBA.
View 12 Replies View RelatedHi ya, the database that I'm working on has many employees for example 2 directors, 6 managers, 22 tutors (there are different types of tutors permanant temporary etc), 4 finance managers etc etc when you click on one of the employee type it expands and shows how many employee are under that section and their details etc, like when i click on director, there is this + sign beside it and when I click on it, it expands and shows me info on all the directors only.. How would I get that?? Do I have to make a seperate table for named employee type?? but then how would I add a relationship to it between employee and employee type?? how would I go about doing that...
Also I need to know how to do it since I need to use queries lateron which will only be about tutors and the courses they teach, and since there are many different types of tutors I'm kinda lost... Thank you for your help in advance!
I have an access database in which I have a table A and table B. Table A has a list of 200 website URLs. Table B has one column ID and another criteria.
I want to create a query to filter websites list which does not have values or characters from table b.
I have these values in table B that I want to be filtered out or not shown in my URL Select Query
.org
.gov
.du
.pk
.dk
I would keep on adding more criteria into this so criteria table so adding new criteria into table B should not disturb our filtering.
Below is what I have tried but in vain and it says atmost you can atmost one criteria row in sub query
SELECT tableA.WEB_ADDRESS
FROM tableA
WHERE ((([tableA].[wEB_ADDRESS] Not Like '*'+(SELECT * FROM tableB)+'*')=True));
Hi Folks!
I've been asked to make a 5 questions test to evaluation potential new employees' knowledge of MS-Access. Honestly, I need your input on what to ask. The goal is not to make a very hard test, but rather to see if somebody as at least basic knowledge of SQL and Ms-Access.
Some background information of what we do with MS-Access
We use MS-Access as a central hub for different download from different systems. We make a lot of daily imports. We export queries in Ms-Excel. We open it from Access and format the report. We make hundreds of reports every day. We don't really use forms or reports.
What questions to ask?
I was thinking of having 2 questions about SQL. One simple query with a formula, and a query with 2 tables (perhaps a left join). On what should my other questions be?