Refreshing Rowsource Query For Combobox?
May 18, 2005
Hi all,
I have a combo box which gets it's values from a table. It also has a where statement to filter the data given the value in another combo box. I.e.
SELECT DISTINCT Table1.some_field FROM Table1 WHERE Table1.some_other_field = Forms!frmSomeForm.Combo_box
The first time I change the value in Combo_box combo box it will filter the values in my other combo box. But when I change it successive times it doesn't refresh the query, so the same values stay. Any suggestions?
View Replies
ADVERTISEMENT
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
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
Dec 4, 2012
I've go a subform with 2 comboboxes. It's a datasheet view.
The combobox controls are called cmb1 and cmb2 for the sake of this...
cmb2 is dependent on cmb1. So when you select a certain value in cmb1, cmb2.rowsource should get updated to use a certain table.
But the kicker is that this should be on a per row basis. So if on row 1 of the subform datasheet you set
cmb1=Manager, cmb2.RowSource should get set to 'tblManagers'.
Then on the next row, cmb1 is set to Directors, cmb2.RowSource should get set to tblDirectors.
But the cmb2.rowsource on row 1 is still set to tblManagers.
View 2 Replies
View Related
Jan 17, 2014
I have a combo box which I define a SQL statement as its rowsource.
When I open the form, I have yet to define the SQL statement for the combo box.
My code is:
if me.cmbobox.listcount = 0 then
me.cmbobox.enabled = false
else
me.cmbobox.enabled = true
end if
But it shows the box as still enabled.
When I do debug.print me.cmbobox.listcount, it shows a value of 1. But that is impossible since I have yet to define a rowsource for it or its empty.
I also have column heading turned off too.
View 3 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
Aug 17, 2015
I am trying to use a combo box to refresh a subform. I am working with Access 2007 as my front end and SQL 2014 on the back end. On my main form called frmDealers I have an unbound combobox where the user selects a dealer and then text boxes are populated with address and phone number. I have it unbound because I don't want the user to make changes to the dealership at this point. On the subform I have people that work at the dealership. That form is called sfrmDealerContacts. The subform could have multiple contacts for each dealership. The subform is bound to a query called qryDealerContacts. I have it bound to a query because I want email hyperlinks in the table too. I tried searching the forum and found this thread number 279791.
After searching I added:
Form_sfrmDealerContacts.Form.RecordSource = "SELECT * FROM dbo_DealerContact WHERE DealerID = " & Me![cboDealerName].Column(1)
But this brings up an input box so I must have something wrong. My code is:
Private Sub cboDealerName_AfterUpdate()
Me.txtDealerAddress = Me![cboDealerName].Column(2)
Me.txtDealerAddress2 = Me![cboDealerName].Column(3)
Me.txtDealerCity = Me![cboDealerName].Column(4)
Me.txtDealerState = Me![cboDealerName].Column(5)
[Code] .....
View 2 Replies
View Related
Jul 17, 2013
I have a mainform in access with a subform (continous form mode). The subform's source is a query that has a criteria parameter that gets the values from a combobox in mainform.(list of projects) Combobox name= cmbProject
It works fine for some records (old records), so when I migrate new data into the table the comboxbox display the new data but when I select the record to be dispayed on the subform, the record or data is not in the subform, so the record is in blank.
View 1 Replies
View Related
Sep 29, 2005
Ive posted similar in the forms section but it has gone unanswered so I've pulled out the offending query and hope someone here can offer insight into this frustrating problem.
I have a form [Training Record] that has 2 combo boxes.
Box A (course_title_combo) has the titles of the various training courses. It pulls 2 columns from the training_courses table, namely courseID and course_title. Only course_title is shown in the box. The control source of this is set to CourseID as this is the field to be written to the training_record table, showing who attends what course.
Box B (course_date_combo) is MEANT to pull through the dates that each course is on, by pulling through the course dates for each course where courseID (as selected by combo box A) is equal to the courseID in the course_dates table.
Here is the current rowsource query for box B - which is basically showing blank fields...
SELECT tbl_course_dates.course_date, tbl_course_dates.CourseID FROM tbl_course_dates WHERE forms![training record test form]!course_title_combo=tbl_course_dates.CourseID
I cant see what is wrong with the query, but Im guessing it must be wrong as it is the rowsource which determines what is included in combo box B.
Any help would be appreciated no end - im totally stuck now :(
View 8 Replies
View Related
Jul 22, 2007
I have a list box with a query as its row source (screenshot attached).
The query gets data from my Contracts table with information on a Consultant ID, Contract ID, and a Contract Status indicator. This effectively represents each contract with a specified status.
What I'm trying to do is allow the user to select a specific contract from this list box, and for my main form to open and display the actual information on the contract they just selected.
A little background on my main form, the main form consists of a main page followed by several Pages, with a subform in each. Thus, my contract screen is designated as Forms![frmConsultantList]![sbfrmContracts].
So, what I'm trying to do right now is collect multiple fields from the selection the user makes (on the listbox with the query row source). To further clarify, each line has a Consultant ID and a Contract ID. How do I pull each from the user's current selection on the listbox and assign them to new variables? As of now, I am only able to select the first field in the list (Contract ID) and assign it to a variable.
How do I do this, or is there a better way?
I apologize if I'm unclear about my situation, so please feel free to ask any questions. And of course, thank you to all those who spend time reading this.
View 3 Replies
View Related
Sep 18, 2006
Can someone help me out with this.. I have a listbox in a form that the rowsource changes based on search criteria.. The rowsource is a select query... I am trying to do two things:
1. View the query in a datasheet view with the click of a button that I've created..
2. Export the query to excel with a click of a button
I have started to set the query as a string by doing the following...
Dim SEARCHQUERY as String
SEARCHQUERY = LISTBOX.Rowsource
Whats next?
I tried: DoCmd.OpenQuery SEARCHQUERY
but it doesn't work... This command only opens an existing access query..
Any Ideas? I know it should be simple!
View 4 Replies
View Related
Apr 1, 2007
I have a 'Snapshot' report, which gives up-to-the-minute data on a business - sales year-to-date, month-todate, week-to-date and today. They run this many times a day, but ideally, they would like to have the information display on a monitor, and be updated automatically every few minutes.
Is there any way that a query can run by itself every few minutes, and gather the current information?
Robert
View 4 Replies
View Related
Dec 1, 2004
I have a form on which i make a selection. This selection then populates a query which in turn generates a report. The problem that i have is that when i go back into my form and make another selection the query, and therefore the report, holds the original data.
My question is how can i refresh the selection and therefore produce different reports based on this selection without exiting the application?
Many thanks for any help provided in advance.
View 4 Replies
View Related
Jun 21, 2005
Hi All,
I have a question:
I have 3 forms (a,b,c). Forms A and B are seperate forms and C will be activated by either A or B. The recordsource of C is a query which can be:
[forms]![formA]![text1] = Qr1
[forms]![formB]![text2] = Qr2
How can I write a code (or adjust it in the query) that that lets form C change it's rowsource in Qr1 or Qr2?
View 5 Replies
View Related
Jun 2, 2006
hi everyone,
i'm new to this forum.
I have a drop-down list that I want to populate from one of the columns in a table. But i want to do this with VBA, instead of building the query.
The reason for this is that I'm saving the project as an MDE file, and my database is residing in another *.mdb file. Thus the MDE file is just the executable version.
I can't seem to "read" the table without using VBA to do it.
Can anyone help me please?
thanks.
View 2 Replies
View Related
Jun 28, 2006
Hi,
I create the the combox in the form.
The Enter Event of the combox is
name.rowsource = "(select 'ALL' From [tbl]) Union (select [Name] from [tbl] Group By [Name] Order By [Name])"
The list solutions are:
Alan
ALL
Amy
Jeff
....
...
Can I put "ALL" in the first postion?
ALL
Alex
Amy
....
Because "ALL" means all the values.
I want to put "ALL" in the first place.
Thanks.
View 2 Replies
View Related
Jul 29, 2005
following problem:
1) a textbox gets value from a combobox
2) after selection in combobox value exists in table but isn't shown in textbox
until form is closed and newly opened
-> how do i requery so that the value that is existing is shown instantly??
View 3 Replies
View Related
Dec 5, 2005
I have the following code in the second combo box on a form
Me.cboReportType.RowSource = "select fldReportTypeID,fldReportType, fldFrom, fldTo, fldDue from tblReportType Where fldReportTypeID Not in (select fldReportTypeID from tblReport) order by fldReportType"
right now this code is filtering so that once a specific reporttype is used that specific reporttype does not appear again as a choice HOWEVER i need it to do this for a specfic record chosen in the first combo box...at this point once a reporttype is used for one record it does not appear again for any record -
the first combo box rowsource:
Me.cboProject.RowSource = "select fldProjectID,fldProjectNo, fldTitle, fldPurpose from tblProject order by fldTitle"
any help is appreciated
View 4 Replies
View Related
Jan 25, 2006
I know that for 1 column in a combo box you can say cboBox = a;b;c;d;e;f; But for my situation i need to have multiple columns... Does anybody know what the string should look like for multiple columns of values ?
View 4 Replies
View Related
Sep 22, 2004
I am trying to retrieve a record from a table (M_Paint) using a text box (txb_oldrecord) and a command button (cmd_getrecord). I want the record to show up on a list box (lst_display). I have written the following code:
Code:Private Sub cmd_getrecord_Click() With Me.lst_display .RowSource = _ "SELECT [M_Paint].[Old_Code], [M_Paint].[Supplier], [M_Paint].[Old_Color], [M_Paint].[Metallic], " & _ "[M_Paint].[Color_Number], [M_Paint].[Finish_Comments], [M_Paint].[Size], [M_Paint].[Number_of_Samples], " & _ "[M_Paint].[Project_Number], [M_Paint].[Date_Received] " & _ "FROM M_Paint " & _ "WHERE [M_Paint].[Old_Code] = '" & Me.txb_old_rec.Value & "';" End With End Sub
When I test this, I get the following error:
Quote: Data type mismatch in criteria expression.
What am I doing wrong?
Thanks in advance!
View 1 Replies
View Related
Jan 10, 2006
Hi. Basically, I've been having trouble with this for a while now. I thought I got it working yesterday, but it turns out not. Here is the situation.
I have built a form that contains several combo boxes and a few text boxes for input. The user enters some or all of this information and clicks "Generate Report". Upon clicking this button, the form needs to launch a generic report that is populated with data using the search terms specified in the form. However, to add complexity, only certain parts of the SELECT and FROM clauses can exist depending on which options are completed on the search form.
I tried: DoCmd.OpenReport stDocName, acPreview, sqlQuery, whereCond
Where sqlQuery changed based on which settings were specified. However, this did not help because the actual query that belongs to the Report never seemed to change: sometimes it was missing columns.
I tried also to set rptNominationsList.RowSource = sqlQuery, but that also doesn't seem to work. On this one I get "Object required".
Thanks in advance!
Alex
View 5 Replies
View Related
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
Nov 15, 2004
I have a reset button that restores my combo boxes and list boxes to their default values.
I want the 'on click' event on the reset button to first clear all the existing values
before it resets them to their defaults. Does anybody know how can I do this?
The reason i need to know is because, if the user presses reset again, I get an error message because
those values are already inserted in their respective boxes. And since I'm using queries from tables as
the rowsources for these combo and listboxes, I get an error because it will not allow duplicate values.
Yes, it's true that I don't want duplicate values so i just want the values cleared first. , so that the
error doesn't have the chance to occur.
View 5 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
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
Nov 18, 2014
I have a form with many comboboxes. The comboboxes need to have their original rowsource set to a table in my database. However, when the user selects a value in a combobox, it is programatically fed into a SQL string which serves to populate a subform. This part all works great.
However, I'm trying to reset the comboboxes as well so that options will "narrow down" until only a single option is possible. So far, I have:
Code:
Private Sub cmb_Author_Change()
Dim strSQL As String
strSQL = "SELECT tbl_Records.RecordName, tbl_Records.RecordDistinction, tbl_Records.Title, tbl_Records.Author, " & _
"tbl_Records.ProjectManager, tbl_Records.[Site Name], tbl_Records.ChargeCode,
[Code] ....
GetWhere() is the portion that feeds info from the comboboxes to the Finder string.
The red highlighted bit is the part I'm working on. Obviously, once I figure out the proper syntax, all of the other
comboboxes will be set up the same way.
What's funny is that in testing (all of the data currently input is simple and allows me to select options where I know what the response will be in other boxes), when I change Author and go to use cmb_RecordName, it replies that "the record source 'My Data' specified on this form or report does not exist" (where 'My Data' represents real data).
So, clearly, it is finding the data, just not using it as a record source, but rather believing that the name of the recordsource is the value.
View 9 Replies
View Related
