Problem With ComboBox Selection To Drive Query Criteria.
Jun 8, 2007
Hi, I have been reading through the forum and found alot of really interesting stuff but cannot seem to find the answer to my specific problem. I'm sure it's easy but i'm sick of trying to figure it out now.
I am trying to build a form to display a shift rota. My tables contain daily shift patterns for 6 staff members for 2007. I want to build a form that has a subform displaying the rota for a particular analyst and a control in the master form to select which of the 6 staff members you wish to view. I have built a master form with a combo box. The combo box holds the names of the six staff and gets this data with a select statement in the Control Source field from the employee table. I constructed this with the wizard. I have constructed a query to drive the sub form. The query builds the rota, getting data from my tables but gives all shifts for all dates for all staff . I want to restict this in my subform to all shifts for all dates but for 1 member of staff. The member of staff should be defined by the selection made in the combo box in the master form. I then have a statement in the criteria of the query driving the subform to restrict the results by name to the name selected in the master form combo box. I'm sure this should work as i have used similar methods in the past. The statement in the query criteria reads like this:
[forms]![frmRota].[cmbAnalystSelect]
this is in the field of analystName, so the criteria should find all instances where the value in the analystName field matches the value in the cmbAnalystSelect control. However, the query always returns no results.
When i run the quey i have already opened the form (frmRota) and selected a value for cmbAnalystSelect, so the quey should collect the value from the combo box and filter the result according to the criteria but it seems to filter out all the results. If i run the query without first opening the frmRota form then i am prompted by a dialog box to enter a value for [forms]![frmRota].[cmbAnalystSelect]. If i enter a corresponding correct value (a valid name for a member of staff) the query returns a correct set of results, i.e. all shift patterns for all dates for the selected member of staff. When i have the form already opened i am not prompted for a value by the dialog box which would suggest it is at least recognising the form is open and that the control is there.
The last thing is that my ComboBox control is not bound to anything, when i view the control in design view it says it is unbound. I have nothing in the control source for the ComboBox. In the row source i have a select statement to pick out the employee names from the employee table, this then poulates the list in the ComboBox.
I hope this is enough info for someone to understand my problem and offer some advice, i can elaborate, attach screen shots etc. if needed.
I am designing a nomination form (web database so no vba macros can be used).
The form has 3 combo boxes: cboStaffName, cboLevel, cboNominee.
The form is bound to the tblSubmit table where the submissions are populated.
I used a select statement:SELECT Staff_List.Staff_Name FROM Staff_List; to populate the combo box for the Staff Name selection.
This is the select statement to populate the job level combobox:
SELECT Staff_List.Level, Staff_List.Staff_Name FROM Staff_List WHERE (((Staff_List.Staff_Name)=[forms]![frmtest]![cboStaffName]));
The select statement to populate the combobox for the nominee combobox:
SELECT Staff_List.Staff_Name FROM Staff_List WHERE (((Staff_List.Level)=[forms]![frmtest]![cboLevel]));
The problem is that the staff_name in the first combobox is still found in the nominee combobox which should not be because a staff cannot nominate self. There is a field in the Staff_List called YesNo that should be activated for each staff that is selected so that the select statement on the nominee combobox can be updated accordingly to remove items with the field "Active"
How to get the checkbox selected for each corresponding staff.
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 a report that is based on a query. In the query, I have a field called "Mode". In the criteria section for the Mode field, I am calling the selection from a combo box on a form called "frm_main". So the criteria for the Mode field is "[Forms]![frm_Main]![Mode_ComboBox]". This combo box has selections for 1,2,3,4,5. I want a selecton on the combo box that will work with the query to show all modes. Sometimes something weird gets entered into the database like "NoMode" for example. I have tried adding a combo box selection of "*" , "Is Not Null" and "". If I manually type these into the criteria section of the query, it works fine. But when I use these in the combo box and call the combo box selection from the query, it does not work.
Does anyone have any idea what I can use in my combo box selection that when selected, the query (and hense report) will show all data?
Porbably a stupid queston for most of you, but I am new to the game. Trying to get the selection of one combo box to filter the selections of a second combo box. Have one table called Sections with the colums SectionID and SectionName, a Second Table called SubSection with Colums SubSectionID, SubSectionName, and SectionID. Third Table called Points which has a column for Section, and SubSection, need to have the section combobox selection filter the SubSection combobox selections. Any help would be appreciated!
Below is the SQL for a query I have, which returns events that are scheduled for today, I need to change this so that The user can specify events to be displayed in a date range and If possible only show those events for the users windows ID
Code:
SELECT tblEvent.EventStart, tblEvent.EventOwner, qryCompany.Company, ltDescriptionType.Description FROM (tblEvent INNER JOIN qryCompany ON tblEvent.Company = qryCompany.ContactID) INNER JOIN ltDescriptionType ON tblEvent.EventDescrip = ltDescriptionType.[DescriptionType ID] WHERE (((tblEvent.EventStart)=Date())) ORDER BY tblEvent.EventStart, tblEvent.EventOwner;
My workaround was to temporarily map the URL to a vacant drive letter on the local machine, then copy the file over, then drop the mapped drive again. A bit clunky but doesn't incorporate much of a delay and this download only has to happen once per day.
The good news is, the file download / copy now works every time. The bad news is that removing the temporarily mapped drive after the copy has taken place, doesn't and I can't figure out why.
Here are the functions I use to map / unmap the drive :
Code:
Option Compare Database Option Explicit
Private Const RESOURCETYPE_ANY = &H0& Private Const CONNECT_UPDATE_PROFILE = &H1& Private Const RESOURCE_CONNECTED As Long = &H1&
[Code] .....
I have separate functions to check existing drive mappings on the local machine and thus determine an appropriate vacant letter to use for the temporary mapping - they work fine.
Unfortunately the UnMapDrive function returns False (even though I switch the active drive to C: and force the connection to be cancelled with the fForce flag) So the mapping always remains on the users profile.
I don't want to permanently map drives on the users' profiles, just briefly for the purposes of this daily file download.
I'm populating an unbound ComboBox with a Query via the QueryDesigner. The value of the Rowsource depends on the value of another ComboBox at the main Form.
After several hours of trial and error, I came to the solution showed at the attached image ("works"); my concern is.. why does my first approach does not work via the IIF clause? ("not work")...
I have a form which lets the user enter a contract ID and then a combo box that has a list of reports which run against whichever ID is entered.
What I want to know if I put the ID's in a table if theres a way to allow the user to select multiple ID's in one go and run the report against them the range rather than 1 at time?
I'm thinking about utilising the check/tick boxes would this be possible? or another way?
I am a relative newbie to VBA, and not very familiar with loops, but I need to add a loop to my function that exports a query with criteria contained in a bound ComboBox on a form. I've gotten my code to work fine without the loop, but I would like to export one file for each item "Team_ID" contained in the ComboBox without the user having to manually select and re-run the function each time. Here is what my code currently looks like:
Code:
Option Compare Database Option Explicit Public Function CreateQCChartsforReports() As Boolean Dim qdf As DAO.QueryDef Dim strSQLStatic As String Dim BookName As String Dim BookName2 As String Dim intCounter As Integer Dim cboCode As ComboBox
I have 2 comboboxes. i select the value from one combobox. i want the second combobox to be filled with the values corresponding to the selected value in first combo. first combo is bound with some other table and second combo takes vales from some other table. How can i do this?
I have an orders subform displaying the line items in an order.
I have two combo boxes in the subform that allow the user to select 'Product catagory' and 'Product'. To enter a product the user must first selct a product catagory which filters the products available for selection. If the exact product is not listed then on option to 'Add new item' must be offered
The problem: At the moment the combo boxes show the same product catagory on every line of the order, even though each line is different and the second combo box is empty
How do I: 1. make the combo box only display the entry for the line it is on
2. force it to filter the available entries for the second combo box
3. offer an option in the second combo to 'add new item' (if the users selects this then Access should open the products entry form and pass the vale of 'Product catagory' from that line as a parameter to preset the form.
other info: The data for the subform is generated by a query on 3 seperate tables all linked by relationships. (tblProductCatagory, tblProducts, tblOrders)
I would like to be able to clear a combobox selection with code but nothing I have tried is working.
Most recently I have tried this:
Code: Me.cboCompanies.ListIndex = -1
To no avail. The selection still remains highlighted in the control after the code is executed. I want the control to be blank. Also the control goes from being a combo box to a text box until escape is pressed and it is cleared
I have also tried using a simple Undo and that is not working either.
I have 1 combo box contains 2 columns look-up directly from the properties(Not VBA)
Now i want to select record from the list
Example: i want to select PM-1234-1111 so i dont want to type starting letter PM to select but i want to type 234 or 123 or somewhere in the middle or end to filter that contained text in all the items is it possible?
I have a table customers, which i can select from an combobox. In my form two tables are bound. one is the customers, and the other is a table where i want to copy the customer information.
I tried this code to copy the selection I made in the combobox afterupdate.
[T_Invoice_firstname] = [T_customer.firstname]
But I get an error 2448 saying I cant assign a value to this object.
I tried msgbox [T_customer.firstname] and i see the name
I tried msgbox [T_invoice.firstname] and i see the empty record
I even filled in a name in the t_invoice.firstname and it shows with the msgbox also.
it's easy to set up a form with a combobox that will list all records and moves the user to the selected record. But I can't figure out how to just show a selection of the records in this combobox, instead of all records.
So, to use the example used in most online tutorials: a drop down menu shows you all the names in a customer database. However, I'd like to have instead a dropdown menu with an overview of all surnames in the database and that I move to the first record with the selected surname.
I would like that when the user opens the form and adds a new record the combobox showed the row immediately following the condition defined in the previous record.
For example.
First time that I open the form I select the combobox the condition A. Second time that I open the form the combobox automatically selects the condition B Third time that I open the form then the combobox automatically show me the condition 3
I have generated a combobox search for my form and I am able to select names that are different and the information is generated on the form. However, when I have two names that have the same last name my selection will default to the primary key instead of the name I selected in the combobox. Here is the code I am using:
Here is my RowSource: SELECT [Client Info].CltLname, [Client Info].CltFname, [Client Info].DOB, [Client Info].[Last 4 SSN] FROM [Client Info] ORDER BY [Client Info].CltLname, [Client Info].CltFname;
I have run a query to sort by CltLname and then by CltFname
Private Sub cboClientSearch_AfterUpdate() 'Moves to Customer Name text box and 'finds the record of whatever name is selected in the combo box DoCmd.ShowAllRecords Me!CltLname.SetFocus DoCmd.FindRecord Me!cboClientSearch 'Set value of combo box equal to an empty string Me!cboClientSearch.Value = "" End Sub
how to get the combobox to display what I have selected instead of defaulting to the earliest primary key associated with the same last name?
Hello. I am trying to make an invoice sheet in Access 2000. My program consists a Order Sheet (frmOrder2), Delivery (frmDelivery) Sheet and a Invoice Sheet (frmInvoice). All three are linked by DoNo. The DoNo is made by frmOrder2.
I have a form that is called frmInvoice and a subform called subInvoice.
What I basically want to do is, to get the subform (subInvoice) to show records depending on the selection in the combobox DoNo in the main form.
subInvoice should show the Items, UnitPrice, Amount, TotalPrice, Remarks. These Items should show the records of frmOrder2.
I am kind of confused and totally lost, because I have no clue how to start! And I am quite a beginner so, bear with me! Thank you!
I have a form based on a query.On the form I have 4 comboboxes.The combo boxes filter eachother without a problem (based on custom select query).Now I want after the fourth combobox value is selected, I want to populate a text field with a value from a different column from the master query (after the 4 selections only 1 value should be possible)I try to say this easy.Master query contains 5 columns:
- group - type - job - insurance - charge
combo1 selects group (and filters records) combo2 selects type (from remaining records and filters again) combo3 selects job (from remaining records and filters again) combo4 selects insurance (from remaining records and filters again)
[code]....
This works great and the dropbox only shows 1 OF EACH DIFFERENT record...If I add a text box and want to see the "charge" value, that I thought I could use the ME.text-code. But in order to do this, I have to add the charge column into the query of Combo4.the dropbox for insurance gives me multiple values that are the same. Is there any way to make this work?
I am having issues with my combo box populating values based on the selection from another combo box.
Database : contains 2 tables: Staff_List, Level_Type and a form called frm_Test The first combobox cboLevel gets its value from the Level_Type table with the select statement SELECT [Level_Type].[ID], [Level_Type].[Level_Type] FROM level_Type;
The values to be displayed on the second combobox cbo_Name is expected to be dependent on the Level selection made from the first combobox i.e if Level 1 is selected in cboLevel then only Names of people in Level 1 will be displayed in second combobox.
The select statement I put there is SELECT Staff_List.ID, Staff_List.Staff_Name FROM Staff_List WHERE ((([Staff_List]![Level])=[Forms]![frm_test]![cboLevel])); This is not displaying anything in the second combobox after I tried it in the Form view, just empty.
I have a table of raw materials that have two fields, their stock code and longer description.
I have another table that is tracking the usage of these materials. I want both the stock code and longer description to be recorded in the usage table.
I want the "Description" field in the usage table form to auto populate when the user selects the corresponding stockcode from a combobox.
