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.
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??
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"
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 ?
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.
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 :(
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".
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.
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.
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.
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....
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.
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?
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..
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.
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
I would like to update the SQL for a subform. I have an SQL statement that I have placed into a string and now I would like to update the subform using it's RowSource property, but I get an error when trying to use the following statement:
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"
I have a form that contains a combo box, I used the access wizard to make this combo box select a record from a table of customers and display that record on the form.
The user then has the option to print that record.
This works fine.
I now want to be able use the same combo box but with a different table so I can select Records from a list of suppliers.
Is there a way I could use a toggle button to change the rowsource of the combo box and then use that same combo box to select records from my supplier table.
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?
