Forms :: ComboBox Populated By Query Not Showing Values
May 9, 2013
I have a form for data entry that have multiple comboboxes. I am trying to get one combobox to base its "list" on an entry from a previous combobox. The first combobox is based on a QuantityType table and has four options.
The next combobox is based on a ProductDesign table and will have close to fifty options. I want to limit the ProductDesign combobox based on the QuantityType selection which will give the QuantityTypeID. This will refer to the QuantityTypeID linked to the ProductDesign table.I have created a query that looks like this:
SELECT Product_Design.Product
FROM Product_Design, Quantity_Type
WHERE Product_Design.QuantityTypeID = Quantity_Type.QuantityTypeID
AND Quantity_Type.QuantityType = [Forms]![Product]![Qty_Type];
This Query works and if I simply run the query I get the needed information from it.What I would like to do with this query is to populate the ProductDesign combobox with this data.I have tried putting the query in the RowSource field but I get an empty combobox without the data. The strange this is that the combobox must be getting something from the query because the length of the combo box varies based on my QuantityType selection. I.E. if I select "Single" in the QuantityType, the ProductDesign dropdown shows three empty places for data whereas if I select "Multiple" in the QuantityType I see that there is ten empty places for data.getting the combo box to actually show each option?
Row Source Type = Value List RowSource = 1;test1;2;test2;3;test3;4;test4;5;test5;6;test6;7; test7;8;test8;9;test9;10;test10;11;test11;12;test1 2 ColumnCount = 2 ColumnWidths = "0cm;8cm" Bound Column = 1
For some reason unknown to me, although the combobox does drop down when entered, the values are not visible until selected, then the selected item is visible in the combobox, but still isn't visible in the drop-down list.
i have done a search.. and found a post which half answered my problems..
I have a Table with some columns which contain Yes/No check boxes as field types.
I have a form with two unbound combo's with their rowsource property set to a Value List "0";"-1"
When I run my form I get 0 and -1 as options in the combos..how would i get Yes or No as options. (i have changed the Value List to "Yes";"No" but the query gives me an error. I also tried to set the rowsource to SELECT Distinct Car from Pupils; and this 'does' work but i dont get the ALL column. I have also tried SELECT Car from Pupils UNION SELECT "" from Pupils;
This gives me the ALL at the top..but 0 and -1 as values to choose from..
I understand that Access stores values in checkboxes as 0 and -1
so to recap..
I want to click on the dropdown combo and have a blank 1st entry.. then entry 2 and 3 will be Yes and No ..not 0 and -1
I am having a problem with calculated fields that are populated on the main form from the subform not saving to the table the main form is linked to.
I created a Purchase Order table that has information on what emplyee created the record, reason for order, customer information if its ordered for a customer, creation date, eta date and notes while the Purchase Order details table is for information on the supplier, product item number, product name, quantity and cost. they are linked by the Purchase Order number which is the primary key in the Purchase order table. it all looked good so i made the form with the Purchase Order Detail as the subform, i got the form all working great and how i wanted it but then i decided i wanted the supplier name and the subtotal of the cost in the main form populated or calculated by the values in the subform.
I copied the fields i wanted populated in the main form to the subform footer and added the calculation for the subtotal then added the formula to the source code on those fields in the main form so the values would populate. it all worked great on the form and the values populated as they were supposed to so i saved the record and went to look at the tables and i found that while the values in the calculated fields that I populated from the subform showed up in the form they didnt save to the table while the non calculated fields saved fine. i did some experimenting and found that if i delete the code and put the data source back to the table in the form and just type something in the text box it would save to the table but not if i had the code in and let it populate.
an example of one of the codes i used to populate the data in the main form is:
I have a combo box that pulls account name data from tblAcctInfo. the combo box has an OnChange event which updates a textbox, txtAcctAddr.
when i have a new account that i would like to be listed in the combo box, i use a form, frmAcctAdd, to add a record to tblAcctInfo.
what i would like to do, is: 1) when i type a new value in the combo box that isn't in the list, have that string value pre-populate in the frmAcctAdd. 2) when i have added the new account info into frmAcctAdd and then saved the record, i would like the new value to pre-populate in the combo box, with the txtAcctAddr textbox also updated via the OnChange event (or maybe a different event is more appropriate?).
I have created a long version of this which requires a lot more user interaction (1-user typing in a new value into the combo box, 2-user RE-typing the SAME value into a data entry form, frmAcctAdd, 3-user saving the new record, 4-user re-clicking the combo box and selecting the newly added value) but i am trying to streamline the data entry with auto-populated fields.
Any idea why a combobox would not populate? :confused:
When the form is opened, it is opened with DOCMD.OPENFORM with selection criteria to send only those records for a particular dog (dogregnbr). The query for the rowsource of the combobox works when run from the query design window.
combobox: cboTrialDt (trial date) combobox control source: trialclassID (from tblDogTitles) form record source: tblDogTitles
The query for the combobox rowsource is:
SELECT tblDogTitles.dogtitleID, tblTrials.trialdt FROM tblTrials INNER JOIN (tblDogTitles INNER JOIN tblTrialClass ON tblDogTitles.trialclassID = tblTrialClass.trialclassID) ON tblTrials.trialID = tblTrialClass.trialID;
tblDogTitles has the following fields: dogtitleID - PK dogregnbr - FK (key to record in tblDogs) titleID - FK (key to record in tblTitles) trialclassID - FK (key to record in tblTrialClass and then some other fields not involved in this problem.
I have another combobox setup similarly that does get populated properly. It's control source is titleID.
It's rowsource is: SELECT tblDogTitles.dogtitleID, tblTitles.title FROM tblTitles INNER JOIN tblDogTitles ON tblTitles.titleID = tblDogTitles.titleID;
After explaining why this is happening, do you have a suggestion for how I can get the trial date (trialdt) from tblTrials to display for the current dogtitle record (tblDogTitles)?
Hello all, I am trying to dispay results in a query which is based upon time.
e.g.
Sample data - class and class start time
Class one - 7pm Class two - 7pm Class three - 7pm Class Four - 8pm Class Five - 8pm Class Six - 9pm Class Seven - 9pm Class Eight - 9pm Class Nine - 9pm
If i was using the sample data above and the time was between 7pm to 8pm i would like to show only the classes which started at 7pm, or if the time was 8pm to 9pm, only show the classes which started at 8pm and the same for 9pm
I hope i have explained myself clearly, and I will be thankful for any help.
I have 7 fields in a listbox (1st is the ID, hidden) from the single table. 3 of these fields are foreign keys. How do I get them to display their related values instead of the foreign key value?
Background:I'm purpose-building my db to essentially track individually cataloged items, somewhat like a library system would. I have four main tables: tblMediaItems, tblUsers, tblTransactions, and tblLocations. Users wills be spending 90% of their time on the Details form for the particular media item record they're viewing.
I'm trying to show an item's transaction history on the main form. I added a listbox (because I liked that compact presentation style) and got the desired fields to show up from tblTransactions. I figured out how to accomplish this for a single column combo box with the wizard, but so far I don't see how I would do this with multiple fields in a listbox.
I have a form with a combobox and a treeview. When I select an item in the combobox, the treeview is updated with info related to the combobox value. When I browse through the nodes in the treeview, I want to see which other values will give that very same node. I could use the concatenation function* I found at Allen Browne's site, which will return a number of values in a single string.
What I'm looking for, is basically some sort of tag cloud. If the user selects node X, the tag cloud needs to show which values in the combobox will all lead to node X. The combobox values that lead to node Y shouldn't be visible at all in the tag cloud, or maybe just shown in a different color. The user should be able to click on the value, which will trigger the combobox_click event.
how many values there will be in the table behind the combobox. That's what's making this problem difficult to solve. Putting the form in design view, adding a textbox and returning to form view is not an option. How do I create a dynamic tag cloud? Is it possible to transpose a table column on a form?
how I can set multiple default values in a ComboBox in a form. The ComboBox is based on a table and my main table also uses the ComboBox.
Example:
Main table has a column "Brands". The ComboBox is based on another table which includes the following items (and thus the ComboBox also shows them):
- CocaCola - Fanta - Sprite
The form has the same ComboBox which is linked to the main table.
Now, if a user creates a new form entry, all ComboBox items should be preselected (checked). It does work with one item over the standard "Default" property.
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 form where the user will choose a contact name or names from a multivalue combobox and I want the email address field to update with the corresponding emails separated by "; ".
I have a form in which I have a combobox. The code in the row source is:
Code: SELECT [tblBooks].AuthorFirst & " " & [tblBooks].AuthorLast AS Expr1 FROM tblBooks GROUP BY tblBooks.AuthorLast, tblBooks.AuthorFirst HAVING (((tblBooks.AuthorFirst & " " & tblBooks.AuthorLast)<>"") And ((Count(*))>1)) ORDER BY tblBooks.AuthorLast, tblBooks.AuthorFirst;
This is an unbound box.
The rest of the form contains bound textboxes and other bound comboboxes.
This form is processed by the following VBA code:
Code: Private Sub cmdAddBook_Click() 'Place the authors first and last name in an array splitAuthor = Split(cboAuthor.Value, " ") 'Create a new record with the form information DoCmd.GoToRecord , , acNewRec
[Code] ....
What happens is that it places all the records (Except those in the array) in a field and overwrites the values in that field.
I have used the same GoToRecord statement on other databases and it works just fine. I suspect that it is due to the unbound combobox.
What I need to find a way to place all the data in the corresponding fields of a new record including separating the author's first and last name when retrieved from the combobox. I just need to figure out how to get the data in the table somehow.
In a form used to record a sale for a company we have a dropdown box with the contact names for that company and when one is selected it populates other boxes like phone number etc however the combobox brings up all possible contacts but when some of them are selected all the boxes return blank, including the combobox, whereas most of the contacts work fine.
The SQL used for the combobox is as follows..
Code: SELECT tbl_Contacts.ContactTelephone, tbl_Contacts.ContactMobile, tbl_Contacts.ContactEmail, tbl_Contacts.ID_Contact, tbl_Contacts.ID_Company, tbl_Contacts.Salutation & " " & tbl_Contacts.ContactForename & " " & tbl_Contacts.ContactSurname AS MainContact FROM tbl_Contacts WHERE (((tbl_Contacts.ID_Company)=[tbl_CompanyBookings].[ID_Company])) ORDER BY tbl_Contacts.ContactForename;
I have a data base with names, email IDs, phone numbers of 5 types(example quality, security, IT,finance etc) of person from ten different project teams. I created a basic form for the end user to enter values and created fields for each of the person's name, email ID etc based on the personal type( liek quality, IT, finance etc) on each team. The background table contains records of almost 200 people now.
All of these people will be trained for one task and some will be trained for multiple tasks. I know how to make the combo box allow multiple values/selections but, i cannot list out all 200 personal names in the combobox's rowsource/value list. Is it possible to combine all different name fields and list out all personal(of one team), so that the end user can just select multiple names for that team who have been trained and so on. i need to set the rowsource of the combobox to the values of all personal from one team so the team leader can just go select who has been trained and who has not.
I have an issue with a couple of my combo boxes. When in form mode the drop down menu displays a list of options (taken from my source table), this is fine, but the problem is that it allows the user to select more than one of the options in the form of checkboxes. This is not what I intended.
I've attached to pictures to demonstrate the problem. The first is ComboBoxQuery (the one with the problem) and the second is ComboBoxQueryWorkign (the one without a problem).
I'm not sure why this is happening and I've tried comparing all the properties of both these combo boxes and changing some of them to see if I can eliminate the problem without any joy.
I use a very standard SELECT statement to pull the data for the combo box:
SELECT CountryID, CountryName FROM tblCountryInfo ORDER By CountryName
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 a trying to search some product from a search button and two combo boxes text values ,and on serch the vba code is :
Code:
Private Sub Command4_Click() Dim db As DAO.Database Dim rs As DAO.Recordset Dim varRecords As Variant
[code]...
I just dont know,the fuile is created on click of button but with headers only,dtaa is not coming but wheni debug ,in immediate window,data is oming but just not coming in excel file.
I made a form that has a combobox that references the combo box above it, and it all worked fine, then I moved it into a navigation form and it stopped working. This was because the source was still using the static reference of the standalone form, not the subform it is now. So I've changed the row source a number of times, but each time, when run, access fails to find it, instead asking for a parameter to be input. The lines
The Nav form is Navigation_Form, the subform is MCR_Log, the combo box is Affected_Subservices and the referenced combobox is Affected_Services.
Code: Forms!Navigation_Form!MCR_Log.Form!Affected_Services or Code: Me!Affected_Services
does not work, even if I change the bangs and dots around. I can't attach the file to this forum, so here is a dropbox link to have a play around.
Bonus Question, I want the first combobox to accept multiple arguments, and the second to display all the subservices under each service, would the record source for that look something like
Code: SELECT Subservices.Subservice FROM Subservices WHERE Subservices.Parent = Me!*Affected_Services*; ?
I have a number of tables in my database, and they're linked in with a form. I'd like to have a field in a form that's populated automatically based on what's inputted in a different field.For example, I've got one table with names and descriptions on widgets (name in column A, description in column B).
Widget 1 This widget is blue Widget 2 This widget is green Widget 3 This widget is yellow
When I'm in my form, I want when I select Widget 1 to have the corresponding text (This widget is blue) appear in a field below.
I have a form for 2800 different records. I want to auto populate 4 text boxes based on the value of 1 combo box but when I change the data on 1 form, all the forms show the same data. I want to be able to able to change the data on each form and save separately.I have Table with Tech Initials(PK), Full Name(689), CellNum(691), TestGaugeSN(630) and Cal Exp Date(632). Want to use Tech Initials to populate the rest.
Here's what I'm using:
Private Sub Combo687_AfterUpdate() Me.Text689 = Me.Combo687.Column(1) Me.Text691 = Me.Combo687.Column(2) Me.Text630 = Me.Combo687.Column(3) Me.Text635 = Me.Combo687.Column(4) End Sub
I am fairly new to access, but do have some experience with SQL & PHP.
I am trying to do something which I would have thought was quite straight forward. But I keep hitting a brick wall and all I get is #name? error on my form.
I have a table "tbl_Job_Spec" and a table "tbl_Tool_List".
I would like to have on the form for tbl_Job_Spec a drop down for selecting the tool number from the "tbl_Tool_List", which then automatically puts into a text box on the form the tools location, which is stored alongside the Tool Number Field on tbl_Tool_List.
I've tried loads of things, I have a query setup ("qry_tool1loc") which seems to correctley identify the relevant information, but when I try to reference that query on the form if just comes up with #Name? in the box on the form.
Anybody got any ideas? I'm only at an early stage in what is quite a big project, but to be honest this is about as complicated as it's going to get, so once I can get past this hurdle it should be plain sailing!!!!!
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 am trying to use a combo box to control which table a data entry form will write data to. I want to create a form that has a combo box to select from a top level table that I will call "Stores". Once a "Store" is selected from the drop down, the next field on the form will be a data entry field. The data entered in that field will be written to the table selected by the preceding combo box.
So, basically I would have say Wal-Mart, Macy's, Sears, K-Mart and etc, listed in my "Stores" table. Once I select one of the stores from the drop down, I would then enter a "department" name in the data entry field and based on which store I selected from the previous combo box, the data would be written to that stores department table (which each store will have its own department table), e.g., WalMartDepts, MacyDepts, SearsDepts, etc...
