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.
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
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.
On clicking a button to open a form I want to filter a list in a combo Box located on the forms subform
The main form is called 'Frm_orders' The sub form is called 'sub' The combo box field is called 'CostCodeID' and I want to filter column 0 of the combobox which is Tbl_InvoiceCostCodes.Type to show records with a type of 'Repairs'
I have a report, on a control tab, on a main form.
On the form are two buttons: one to show all items, and one to filter them based on a boolean field called showitem.
The buttons work with the code below.
What I want to do but cannot seem to figure out is to have the report default to no filter.
The bound query has no criteria.
I'm trying to set the filter property via the on open or on load event and even if I isolate the report, cannot seem to reach it.
Code: Private Sub b_hide_items_Click() Me.Profile_Timeline_wNotes_subreport.Report.Filter = "timeline.showItem <> 1" Me.Profile_Timeline_wNotes_subreport.Report.FilterOn = True Me.Profile_Timeline_wNotes_subreport.Requery End Sub
Private Sub b_show_all_Click() Me.Profile_Timeline_wNotes_subreport.Report.Filter = "timeline.showItem = 0" Me.Profile_Timeline_wNotes_subreport.Report.FilterOn = False Me.Profile_Timeline_wNotes_subreport.Requery End Sub
If you click on the main form called frmpatient you will see 2 text boxs one called Primary Insurance the other Called Secondary Insurance
Above those text boxes are to hyper links which open the list box
What I'm trying to do is if a user double clicks the name of the Insurance From the List Box it will insert that Name of the Insurance Into either The Primary Insurance or Secondary Insurance Text Fields
I do not Want to Use combo Boxes because I have about 1,000 Insurances in my database.
Hi I have placed a ListView on the form. I have written a DblClick event for the ListView. The application is a multi-user one. The issue is that the DblClick event was working for few days and now it does not work for any of them but it is working only in my system.
Is it related to reference ? I developed the tool on my system and placed in a shared folder from which everybody uses. Do I need to give reference in all the system ? Please help. I am stuck with this problem.
I currently use this code to trigger a not in list event, ask the user if they want to add it to the respective table, and then add it.
I'd like to be able to add the text in the combo box that triggers the event to show up in the msgbox.
Private Sub cboCategory_NotInList(NewData As String, Response As Integer) Const Message1 = "The data you have entered " & me.cbocategory.text &" is not in the current dataset." Const Message2 = "Add now?" Const Title = "Unknown entry in CATEGORY Field..." Const NL = vbCrLf & vbCrLf
I have a basic form linked with a subform inside of it. The main form has a list of customers in a combobox and the subform lists all the things they have ordered from us. This all works perfectly fine, I can add data to the list of things ordered and it's ok.
I decided instead of using a whole extra form to add customers I'd just have a not in list event and allow users to add customers through there, bit more intuitive and cut down on forms.
It works to certain extent except one small problem, if I add a customer I have to close and reopen the form to be able to add/remove data from them in the subform. If I leave the form open and try to enter in data in the subform immediately it just shows whatever customer I had last on the combobox and adds it to the last customer as well.
It works perfectly fine if I reopen it so I thought it was some kind of query or update snafu but all the VBA code examples I found don't seem to do anything. Not exactly sure where to go from here.
PHP Code:
Private Sub cboDept_NotInList(NewData As String, Response As Integer) Dim oRS As DAO.Recordset, i As Integer, sMsg As String Dim oRSClone As DAO.Recordset Response = acDataErrContinue If MsgBox("Add dept?", vbYesNo) = vbYes Then Set oRS = CurrentDb.OpenRecordset("tblDepartments", dbOpenDynaset)
I have a form (about patients) containing a listbox (list of visit dates) and a subform (showing medications at that date). When you click on the listbox, the subform is re-queried with the selected date as a parameter to show the medications that were in use on that date. This is working fine.
However, when you move to a new record (patient), the subform is initially blank because the listbox hasn't been clicked on yet. This means that it looks like the person isn't on any medications, which is causing some confusion.
I need a way to have the subform show the "top" (most recent) date because that is the current medication list, and the thing most people want to see first. It is always the top of the list, so I would like to do something like this (pseudocode)
Code: Private Sub Form_Current() Forms![MedSearch].SelectDateBox.Requery Send MouseClick Event to line 0 of SelectDateBox End Sub
I am trying to populate a list box with an event after update in a combo box. I can get the formula to work using 2 criteria, the problem is i nee to add a third criteria. When I try to add it I get the run-time 13 error.
Here is the code I am trying to use:
Private Sub cboStatusRFQ_AfterUpdate() Me.cboSupplier.RowSource = "SELECT DISTINCT [Consolidated_Master_Req_Pool.RFQ Contact] " & _ "FROM Consolidated_Master_Req_Pool " & _ "WHERE consolidated_master_req_pool.Complete = FALSE AND [Consolidated_Master_Req_Pool.RFQ Supplier] = '" & Nz(cboStatusRFQ.Value) & "'" And "[cosolidated_master_req_pool.Status] = '" & "[SUPPLIER_RFQ FOLLOW-UP]" & "'" & _ "ORDER BY [Consolidated_Master_Req_Pool.RFQ Contact];" Me.cboSupplier = Null End Sub
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!
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.
I 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 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
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.
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;
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 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.
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.
The 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
