Forms :: How To Get RowSource For Combo Box Based On Value Of Different Object
Sep 9, 2013
I have a form with several objects:
Projects -> which have Sources -> which have SourceTypes
The underlying DB has tables for Projects, Sources and SourceTypes:
Code:
Table Projects;
Fields:
ProjectID : AutoNumber;
etc, etc
[Code] ....
So, I'm trying to get all the Sources for a given ProjectID, and I'm using the following SQL statement:
Code:
SELECT ID, ProjectID, Sequence, Name from Sources WHERE [Sources].ProjectID= MyProject.ProjectID;
Which actually gets coded (because MyProject.ProjectID is a variable) as:
"SELECT ID, ProjectID, Sequence, Name from Sources WHERE [Sources].ProjectID= " & MyProject.ProjectID & ";"
What actually happens is that Access jumps in and says (via a Message Box) "Give me a value for Source.ProjectID.
I've just done that in the SQL statement!
How do I get a 'real' SELECT to work for the RowSource?
View Replies
ADVERTISEMENT
Nov 20, 2006
Hi
I have a combo box on a form for which I want the available values to be a subset of the values from a table (which I can do), but for older records, I want the combo box to be able to display any value from this table.
(The combo is to select a member of staff who has been assigned to a case, and I only want current members of staff to be available for new records, but want to be able to display older records which relate to members of staff who have now left.)
The only way I can think of doing this is to have LimitToList = False, but that defeats the object of having the drop-down list in the first place.
Can anybody suggest a way of doing this please? I can get round it by having a text box AND a combo box, but wondered if there was a way to do it with a single control.
Dave
Salisbury UK
View 2 Replies
View Related
Sep 30, 2005
I have a form that has both a Make and Model field and I am trying to set it up so that when the Make is selected or changed the Model Query filter updates.
I have read other threads regarding this matter but am still unable to get it to work. The Form pulls from one table (tblPCInfo) while the query pulls form another (tlkpProductsServer). Also the cmoMake ComboBox pulls from table (tlkpProductsCategoryServer)
Model Query:
SELECT tlkpProductsServer.ProductID, tlkpProductsServer.ProductName, tlkpProductsServer.ProductCategoryID
FROM tlkpProductsServer
ORDER BY tlkpProductsServer.ProductName;
I want to add a WHERE parameter to filter for the current ProductCategoryID which comes from what is selected in the Make Combo Box
Code I have Tried:
Private Sub cmoMake_AfterUpdate()
Dim strRowSource As String
strRowSource = "SELECT qry_Model.ProductName FROM qry_Model WHERE qry_Model.ProductCategoryID=frm_frmPCInfo.cmoMake"
Me.cmoModel.RowSource = strRowSource
End Sub
I am not sure if this is enough info to go with and I just started learning VBA so I know this is probably a mess.
View 8 Replies
View Related
Jul 10, 2007
Hi all,
I am trying to have the rowsource change on a "subcategory" combo box depending on what the "category" combo box says.
I added an event procedure to the "Category" combo box which changes the Row Source in the subcategory combo box to something else. I'm not sure that I did this right as if I close the database and reopen it, what I selected in the subcategory combobox is gone and it is blank. Any ideas? Here is my simple, likely incorrect event procedure:
Private Sub Category_Change()
If Category.Text = "Rare Books" Then
SubCat.RowSource = "scRareBooks"
End If
If Category.Text = "Legal Documents" Then
SubCat.RowSource = "scLegalDocuments"
End If
End Sub
Thanks!
View 5 Replies
View Related
Mar 31, 2014
I have a form that currently uses a "catch all" table for listing available equipment to choose from for an equipment field. I call it tblEquipment. What I want to do is to make it so when I type a name in (1 of 35) in one field of the current record, the record source for the equipment field immediately looks at a different table that has equipment available only for that name. To do this I plan on making 35 different tables with limited data originally found in tblEquipment. I would call these tblEquipment1, tblEquipment2, etc. I do not use a sub form, nor do I want to.
So my questions are:
1) can this be done
2)If it can be done, how can I do it?
View 3 Replies
View Related
May 21, 2014
I'm looking for a way to have a text box auto fill based on the selection of a combo box on the same form. I cannot use the method i find all over the internet of using multiple columns in the combo box and basing the text box on that because the combo box already has multiple columns being used to determine its own possibilities and other combo box possibilities.
I would really just like the text box to work like this, but im still kinda inexperienced in VBA...
If combo box is "F004-001", then text box is "237"
If combo box is "F004-003", then text box is "280"
I know how to add in an "after update" thing, but i do not know how to do If/then statements.
View 1 Replies
View Related
Jan 5, 2015
I am currently building a database for the company I work for that is fairly similar to the Northwind Database; however it is made from scratch so hopefully some of the common problems with that database won't find their way into mine.My problem is that when I go to my Orders form, I pick a customer from the main form, which creates a record on the Orders table. When I then go to the subform to choose a product/line item, I get the error in my title ("The LinkMasterFields property setting has produced this error: 'The object doesn't contain the Automation object 'OrdersT.") as soon as a product is chosen from the drop down list.
View 1 Replies
View Related
Jul 17, 2013
The error is:
The LinkMasterFields property setting has produced this error: 'The object doesn't contain the Automation object 'tblIndividual.' '
Then it also gives me the same one on another table.
I think it has something to do with the link master/child fields. I've tried all kinds of relationships with the three tables and can't figure it out.
I've tried uploading the database here but it won't. It's on my Sky Drive.
[URL] .....
View 5 Replies
View Related
Nov 29, 2014
I'm trying to build a database for daily work. My work in daily basis I have to fill all condition for several items.
We have two areas, with two locations under each area, and three systems under each location and each system contain more than 500 items.
I created all tables and fill by all information:
1-Table 1: Area 1, Area 2, Area 3, Area 4, Area 5, Area 6.
2-Table 2: Location 1, Location 2.
3-Table 3: System 1, System 2, System 3.
4-Table 4: all items under System 1-1-1
5-Table 4: all items under System 1-1-2
[Code] ....
Last table will be LogBook which will be as follow:
date l time l area l location l system l item l Conition1 l Conition2 l Conition3
My question regarding to the form of the above table:
How can I make a combo box for area field and when I select for example area1 will appear only the locations which under area1 in location field, and when I select location1 for example will appear only the systems under location1 in system field, and when I select system1 one for example will appear only items under system 1.Combo box list will be based on the selection in previous combo box and so on.
View 3 Replies
View Related
Jun 3, 2014
I am trying to select a value from one combo box and on the basis of this selection the other combo box show only those values which have link to the value I have selected.
View 14 Replies
View Related
Jun 25, 2013
I have a main form that filters data in a subform based on selections via combo box users make on the main form.
So I have the 'department' and 'manager's name'. If someone selects 'Human Resources' from 'department' combo box, then I only want to see the managers that are in the Human Resources departement when I drop down the combo box for 'manager's name'. Currently I'm seeing all the managers and a user can select a manager that is not in human resources and get no data returned. I prefer for him to get a list of those that are in that department only. The source of the combo box is a query.
I got it! Found here: [URL] ....
View 3 Replies
View Related
Jun 4, 2013
I have been creating a form, based on only one table. Here I am displaying data as text boxes and subfrom from the same table. At the beginning, I was interested in controlling the display of the data according to the combo box (in this case is the PO number). Now, I would like to add another combox box which is the year (I have a column with the date, and also I have a column that shows only the years digits in my table) Also, I have a subform that display the data from the same table specifics records that I want. It means that I want to pick the year first, and then in the combox box of po number shows only option of that year, and hence the text boxes and subforms change accordingly to the two combox boxes.
View 2 Replies
View Related
Aug 16, 2013
I have a basic SELECT DISTINCT query for my CBO rowsource. Is it also possible to add custom values to the rowsource? i.e. I'd like my CBO to look like....
Custom Value
Query Value
Query Value.....
View 4 Replies
View Related
Jul 15, 2014
I'm trying to change a rowsource depending on the value in a form. I want to be able to change the value and update the form accordingly. I can change the query but when I try to add the "where" statement to it, I get nothing.This is what I'm using:
Private Sub RepID_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT DISTINCT [Cust No] & ' ' & [Name] FROM [Address Book] WHERE [Address Book].[Cust No] = '" & [RepID] & "' "
Me.NamesList.RowSource = strSQL
NamesList.Requery
End Sub
View 5 Replies
View Related
Dec 23, 2014
I have two comboboxes on my form one 'cboMake' and the other 'cboModel'. Both of their values are stored in individual columns ('Make' and 'Model') in the same table 'tblStock'. What I would like to achieve is when I select a value from 'cboMake' that 'cboModel' will filter it's data values to only display Models that are linked to Make i.e. if I select Make Logitech I would like it to only display the following results for Model: 'K120', 'K200', 'G500', 'G502' etc. and not display any of the other values associated with another Make.
View 6 Replies
View Related
Jul 15, 2015
I have a Product form that shows the details of each of the Products in my Product table. I also have a "Pick a product" combobox (who's rowsource is being fed by the same Products Query that feeds the form). This allows you to go straight to a particular product by selecting its name. So far, so simple.
I've since added a couple of combobox filters which use different columns ("Product Type" and "Collection") to allow the user to get down to a more manageable recordset to then work with.
Of course, because the "Pick a Product" combobox is being fed by the Products Query, the combobox can end up showing products that are not contained in a filtered recordset. This is... sub-optimal.
Is there a way of telling the "Pick a Product" combobox to use the products listed in the recordset as its rowsource rather than the product query? So that it only lists the products the user has filtered down to?
View 3 Replies
View Related
Nov 5, 2013
I'm trying to get a combo box & button to work together. On the main form of the attached database, I just want the button to go to the associated record based off my selection (or entry) in the combo box.
I've tried the following:
- Looked through the Northwind sample database for a close example. Tried my best to replicate the functions but it didn't work
- Looked through other examples posted and tried to manipulate the code to no avail
- Tried using [Forms]![Main Form]![cmbLastName] in both the filter & criteria section of the macro
View 4 Replies
View Related
Jan 18, 2014
I am trying to design an unbound combobox whose rowsource can be dynamically set based on a union query in order to search 1000's of names returning each name that contains a user defined string. The search does not start until the user defined string reaches 3 characters in length.
There are more details in the attachment but basically the vba code I used all saves OK in the vba editor but I get a runtime error 13 type mismatch when the code attempts to utilise the query to populate the rowsource of the combobox.
View 10 Replies
View Related
Sep 29, 2014
The idea is populate the 2 Comboboxes and year and after that query something like:
Code:
SELECT FileEntryNo, GroupCode, SubGrpName, SubGrpNo, FileNo, GroupName, Subject, NameTo, NameFrom, ConnectedFile, DocumentDate, Month,
Spelmonth, Year, Reference
FROM dbo.Files
WHERE (GroupCode LIKE 'XXX') AND (SubGrpName LIKE 'YYY') AND (Year = ZZZ)
So after the query is executed the results will be available on a third Combobox that after selecting a record will show the detail on the fields.
View 2 Replies
View Related
Apr 14, 2014
So I have a combobox in a form that lists the values of a field in a table. The rowsource is this:
Code:
SELECT [Estes Training Modules].[Training Module], [Estes Training Modules].[Period] FROM [Estes Training Modules] ORDER BY [Period];
The problem is that ALL values on that field show up, including repeated values. I only need each different value to show up once. How can I do that?
For example, what I get in the combobox is: Annual, 2-Year, Annual, Annual, 2-Year, 3-Year,...
What I need is: Annual, 2-Year, 3-Year,...
View 10 Replies
View Related
Sep 26, 2013
I have 4 tables:
table 1: two fields: t_code (primary key), town_name
table 2: two fields: Distri_code (Primary Key), Distri_name
Table 3:two fields: T_D (Primary key), t_code (foreign key), distri_code (foreign key)
Table 4:A_T (Prim key), T_D (Foreign key), area_code (foreign key)
table 5: area_code (pri key), area
I want to make a form on table 4 where both foreign keys are numbers. On the data entry form on this child table, i am trying to link towns and areas. At the front end form, i want a combo or list item showing town_name (table1) and distri_name (table 2).
Once the end user selects the required town and district from available options, i want to retrieve T_D from table 3 through query based on input from both values and save it table 4 in place of T_D along with area_code, which i can retrieve simply from table 5.
View 1 Replies
View Related
May 6, 2014
On a form I am making, I have two combo boxes. Box 1 has "Title" and Box 2 has "Full Name". In the table associated with FullName, there is a Check Box name "Still Attached".
What I am trying to do when choosing a "Title" in Combo Box 1, I would like only the people with that "Title" and a check in "Still Attached" to be available in the drop down list.
I have tried and I can get combo 2 to filter by title, but not by the "Still Attached" value.
View 11 Replies
View Related
Sep 3, 2014
I have a form with a field called "comRpt" listing all the reports available to run on AfterUpdate procedure. I would like to add a command button when clicked the selected report shown in the "comRPT" field runs then it is attached to an emailed. How I can accomplish this?
View 2 Replies
View Related
Dec 31, 2014
I am trying to auto populate the text boxes in the form based on drop down from the combo box. As you can see in the form Frm_Input, this works fine for FL, but for some reason it will not populate for Skill, Role, FLM and Location. The table it is linking to is Tbl_Names. Why as I have changed the column source?
View 8 Replies
View Related
Nov 19, 2013
I have a text box that displays a name of a law from one table, based on that law name I want to populate a combo box with different options on how to comply with that law that come from a different table. The compliance options I need to pull in are different for each law. Finally I would like to have the selected option saved in the table the laws are coming from.
I have tried using a rowsource query [Form]![form1]![Law] for the combo box of law options but keep getting errors.
View 3 Replies
View Related
Jun 4, 2015
My database is using data that is entered by the employees to generate Quotations. There is one important piece of information that will not be entered by the employee. The quotes involve metals which are priced based on market price and weight. I am planning on integrating a data feed with this information, but for now I want to enter it manually in a table. The price depends on two combo boxes one for "Precious or Base Metals", and one for "Metal Name". I want those two values to call the price from a table, and automatically fill in the "Market Price" field in the form.Also once that is in I would like to do my calculations. I am planning on using queries to do these. Is that the correct method?
View 6 Replies
View Related
