Value In Text Box As Criteria For Query Which Populates Combo Box
Oct 25, 2006
Simple question but I've been stuck for a looong time.
So what I want to do:
1. enter a value into a text box (Home_Tel) in a form (frmStudentClass)
2. which runs a query (qryNameTel)
3. and return the results to a combo box (Student_Name) on the same form.
The same Home_Tel may have several Student_Name results.
-I have entered
[Forms]![frmStudentClass]![Home Tel]
for the criteria in the query
- However I can't get the results to turn up on the combo box
- I have the following:
Private Sub Home_Tel_AfterUpdate()
' run query
DoCmd.OpenQuery "qryNameTel"
Me.Student_Name.Requery
End Sub
Any ideas?
Or is there an easier way to tackle the problem?
Home_Tel and Student_Name are from the same tables.
I have created a query just for Home_Tel and Student_Name
I have a form with a combo box, whose afterupdate code is:
Private Sub CustomerName_AfterUpdate()
Dim rs As Recordset Dim person As String {This will contain the SQL Statement} person = "select people.department from people where people.name='" &
CustomerName.Value & "'" Set rs = CurrentDb.OpenRecordset(person) {Now the following are combo/text boxes that are populated} Department.Value = rs("department") InputDate = Date InputTime = Time
My code works: when you select something in CustomerName combo box, the other
boxes in the record get populated with data. The problem is that ALL the records in the
form get populated with this data as well... I want the records to be independent of each other - that each record will be filled
I have a form with a combo box, whose afterupdate code is:
Private Sub CustomerName_AfterUpdate()
Dim rs As Recordset Dim person As String {This will contain the SQL Statement} person = "select people.department from people where people.name='" & CustomerName.Value & "'" Set rs = CurrentDb.OpenRecordset(person) {Now the following are combo/text boxes that are populated} Department.Value = rs("department") InputDate = Date InputTime = Time
My code works: when you select something in CustomerName combo box, the other boxes in the record get populated with data. The problem is that ALL the records in the form get populated with this data as well... I want the records to be independent of each other - that each record will be filled separately.
When I enter a combo box on a form, whatever I choose from the combo box, it populates everything. If I go to a different row to change it, it changes them all. How do I get that to stop? And why does it do that?
I have set up a database that stores actions (i.e jobs). In the table; two of the fields are...'required completion date' and 'actual completion date'. I wish to lookup, by using a query, all of the open actions (those which havent yet been complete (i.e the 'actual completion date' is null)) and then later on all those which are overdue (i.e the 'actual completion date' is null And the 'required completion date' <today....this being the criteria for an overdue action).
However, I have used a form which has a combo box which contains the values open and overdue. When a selection has been made I want a form to display with the results depending on the selection that has been made. I am capable of creating a form based on a query, but am unsure of how to construct the query with the correct criteria based on the option that is selected from the form.
Currently have a Form / SubForm arrangement combining qryItemAllowedAndUnschedlued with tblInventoryItem.
The main form is based on the query and the subform on the table.
My effort is to create cascading combo boxes where a selection in the main form populates a "conditional" list in the subform.
More specifically, where the user selects ItemClass in the main form, I am attempting to populate a list of ItemCategories in the subform based on the selected ItemClass.
I have reviewed and continue to study existing posts on Cascading Combo Boxes and Form / Subform arrangements. However I currently have a question I do not see covered...
When I select my ItemClass it is populating throughout the query and not solely in the current record displayed.
Does anyone know why this may be?
I fear the answer is a simple setting that my current experience is making difficult to isolate. I trust it will present itself with patience, but in the interim more experienced feedback is welcome!
I have a query that populates a report linked to a mail merge. There is an IFF statement in it that checks to see if a condition is meet. If that condition is meet some standard text results. Is it possible to put a disclaimer with the standard text.For example,
Code: IIF ([Results]="Negative","Nothing detected" (the disclamer would go here)
I am not certain the correct syntax to use to insert "not all regions were tested".
Hi, How can I make the data in a textbox on my form automatically entered as the search criteria in a query. So say the box says Mike Johnson, can I make a command button (That I can eventually subsititute as the actual box) that puts the Nma emIk Johnson into a search query and brings up all the information on him from the database?
If "Calendar" is selected, I would like to click a button that runs query1 with this criteria: "Like "*" & "12/31" & "*" AND "*" & Forms!MyForm!cbo2"
Example:
If "Off Calendar" is selected, I would like to click the same button that runs query1 with this criteria: "Not Like "*" & "12/31" & "*" AND "*" & Forms!MyForm!cbo2"
I can't figure out what I am doing wrong... I want the query criteria to be defined by a combo box in the form. The user will select the criteria on the combo box, then click a button to run the query, however the query keeps coming up blank.
[Forms]![UserReview]![Staff] is what I have entered in the criteria for the field in the query. If I simply type in "casbds1" as the criteria, which is one of the choices in the combo box, it returns with the data I want, but I just can't get it to work with the combo box. Any ideas what I am doing wrong?
I am trying to get my head around some of the more advanced “features” of access..
At the minute I am trying to change the criteria of a query from selecting a value from within a combo box.. I believe I have got the syntax correct, but I keep getting no results returned..
I am using: [forms]![frmMovies]![combo2] as the criteria where frmMovies is the name of the form, and combo2 the name of the combo box.. I have set combo2’s source as the table containing the movie information “tblMovies” Before you ask I am not trying to setup my own movie store, more apply the theory to a bigger DB that I am working on :-p
Hopefully you can help, as I have now been scouring the forums for 2 days and am getting no where..
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?
I am currently having problems with a multiple criteria query with combo boxes (see attached file).
The problem:
- When user defines all 3 criteria (e.g. selects Project: A, Category: Services and Equipment Type: Packages), the query returns a result - no problem here.
- When user defines no fields (i.e. all combo boxes empty), query returns all records - again, not a probelm.
- However, say a user wants to display all records belonging to just Project: A, for example, the query returns no records. Obviously, there a a number of combinations of this (another example would be a user wanting to show records from project: B, with Category: Equipment Type).
I have included the Is Null statement in the query but to no avail. Could anyone advise on a solution based on the attached database. Your help would be greatly appreciated!
I have a two combo boxes that I want to use to help filter a query. The field the combo boxes need to filter is a numeric field with values that range anywhere from 200 to 1.5 million. To make the filtering easier I set the combo box up to several values (RowSource = Value List) that will serve as a range.
It seems that the query is not recognizing the values selected in the combo box. The numbers appear after being selected, but the query is not affected by the selection. However, When I enter a number NOT on the list (like 428) the query recognizes the value.
How can I get the query to recognize the Value List entries on the combo box?
I have a customers list that i am always ading to .I need to run a query to see what individual customers have ordered .
I have the query that works great when i manually insert the customers Surname .but i want the combo to do this job .Is it possible ,or should i be doing something different .
I've been developing the code below but when the length of .lstAnswers is longer than 128 it doesn't delete the question. Is there a reason under queries or SQL this should happen? I'm hoping that i'm just thick and there's an easy answer but nothing seems to work.
-------------------------------------------------------------------------- Private Sub cmdRemoveAnswer_Click() Dim Answers_SQL As String Answers_SQL = "DELETE tblOptions.* " & _ "FROM tblOptions " & _ "WHERE (((tblOptions.strAnswer)=[Forms]![frmQuestionWizard]![lstAnswers]) AND ((tblOptions.strQuestion)=[Forms]![frmQuestionWizard]![lstQuestion])) OR (((tblOptions.strAnswer) Is Null))" DoCmd.RunSQL Answers_SQL, 0 lstAnswers.Requery End Sub
When the query is run, it returns 0 results. However, if I put in Like '5???????' as the criteria in the query itself, it runs properly.
I tried closing the form and running the query so that it asks me for a value since it can't find that textbox since the form is closed. I pasted in Like '5???????' and it returns 0 results.
I have used this method for entering start and end dates for reports before and it has worked just fine. I can't figure out why it isn't working here. Any thoughts?
I'm trying set the query criteria from a textbox on a form. It's a Status field in the query. 1 = Open, 2 = Closed. If I set the criteria to "1" it shows all open, "2" and it shows all closed and "1" or "2" it shows all. The problem I'm having is setting these in the form. I've set the query to pull the value from the form. I can get the Open or Closed to work but not the All. My textbox shows exactly how the criteria should read "1" or "2" but doesn't show any results.
I've got a continuous form based on a query.Each of the fields have a search box below it (in the footer) which should ideally filter the query.I'm starting with the FirstName field.In the form's query, I've set the criteria to the following for the FirstName field:
Code: Like "*" & [Forms]![frmStudentDetails]![txtSearchFirstName] & "*" frmStudentDetails is the form name. txtSearchFirstName is the search box's name (in the footer). The AfterUpdate event for txtSearchFirstName is:
Code: Me.Requery
But, it doesn't work; when I switch to form view, it displays an error. The Microsoft Access database engine does not recognize '[Forms]![frmStudent Details]![txtSearchFirstName]' as a valid field name or expression.
I have a query with a date field that is formatted mm/dd/yyyy. I have a combo box that is formatted as mm/yyyy. I need the choice from the combo box to be the criteria for the date field in the query. the combo box has to contain the month and year only (which it does now) and the query must return mm/dd/yyyy. I have tried a few statements and the closest I got was a between statement that added 30 days to the combo box selection but that's not really accurate.
