Combo Box Controlled Query ?
Jul 15, 2005
Hi all im new to this and im trying to make a from that will display a sub from containg infomation that can be changed by altering a combo box.
im not sure if its possible but i want to have the combo box containing names of people and then the sub form to display records that relate just to the selected person. and when the combobox's selection changes so does the displayed data.
i don't know if its possible to make a dynamic query that will take the text value in the combo box and use it in a query to query the data i have. and then produce just the data corisponding to one person. :eek: :confused:
ANY HELP on this would be greatly appreaceated. i have a feeling thatis simple but i have been pulling my hair out trying to find out how to do it. :confused:
View Replies
ADVERTISEMENT
Jul 29, 2005
sorry for the repeating of a previous thread but i have looked at all of the replys on the first combo box controlled query and have followed what was reccomended but i still have some problems. :(
i have three combo boxes and i want to query a single table with the selected combo options.
atm i am using the criteria part of a query to filter the results by the combo box
[forms]![Form1]![cboStaff]
this works well for one thing but when i want two i have put them in line which means AND so both combo boxes have to have a value in and then the query looks for combo box ones value with combobox 2's value.
is there any way i can make the value in the second combo box mean any result. which would effectivly turn the query into a search for just the first criteria set by the combo box???
please help i have been going round in circles trying to figure out how i am going to do this :confused:
View 4 Replies
View Related
Sep 5, 2013
I am having an issue with a database application I created. I needed to add another query that could be controlled from combo box on a nested form.
The combo box is for dates for entries in a table. Which then filters the query based on the combo box selection. Easy enough stuff.
The problem I am having is that when I try to use the combo box as a criteria for the query using;
[Forms]![MasterForm]![NavigationSubform].[Form].[comboBox]
Access closes and wont let me use the reference to the combo box.
What has happened? Corrupt database? How do I fix it?
View 9 Replies
View Related
Oct 3, 2007
Hi
I have been trying to set up this query via VBA code but, with no luck, have gone back to the query in design view. Basically the parameters of the query are determined by combo boxes in a form. This is fine if one combo box indicates one criteria but a problem arisis when multiple combo boxes are possible parameters for one field.
i.e. Field 1 = combo box one
field 2= combo box two
field three = combo box three, four or five.
i have found it difficult running this, especially in terms of a returning all if a combo is left blank. the problem with setting this up on field three would be that if i run combo box four and five to return all if left blank it overides the other command to set a specific parameter. Also, for some reason i can tdecide whether this is an AND or OR command, its actually both i.e. Field three parameter = combo box three and/or combo box4 etc...
please help this is driving me crazy.
Thanks
Shapman
View 14 Replies
View Related
Apr 11, 2008
I've created a rather simple Access program that I use to import an employee's cookie summary txt file. I then sort the cookies for 1) time of day, and 2) appropriateness.
I found a way to create the queries - in simple design mode using criteria - but I want system administrators in other offices to be able to enter the time of day variables particular to their offices. Same with appropriate words. So I want a form they can use to do that - eg, list their office's break times. In essence, a setup page. Then the resulting queries (time of day and word list) will be customized to the particulars of that office rather than to mine.
Would appreciate some ideas on how to do this. Should I read up on SQL? Should I learn VBA? I'm trying to get around having to tell the other system administrators to go into each query and change the criteria manually.
Thanks!
View 6 Replies
View Related
Jan 15, 2007
First, thanks to all that have helped me with out even knowing it! This forum is a lifesaver.
Problem:
I have a control button that, through macro commands that seem to be working, changes a field value from locked=no to locked=yes. This macro control also changes the enabled setting to = false.
When I save and exit the form, upon reopening, the above cell value is not saved. Is there a simple fix for a simple-minded person?
Thanks in advance
View 5 Replies
View Related
Jun 17, 2013
I have a word 2010 reports that is linked to an access 2010 form. SQL is used to populate the report with some of the fields from the form. Is it possible to create a combo-box with two selections (Mike, Paul) in it. And then have two other fields populate based on the selection in that combo-box? For example, if Mike is selected then an Address: and Phone Number: fields are filled in with his information. And if Paul is selected his information fills in.
View 1 Replies
View Related
Sep 23, 2013
I need to create a form that automatically populates Dealer_ID in my Customers table when a user selects Dealership from a dropdown box.
The form is updating all of the user controlled fields in the Customer table as the user inputs the information. I have a separate Dealership table who's Primary Key is Dealer_ID where dealership information (including the dealer name) is stored. A one to many relationship is in place that connects Dealer_ID from the Dealership table to the same field in the Customer table. The user inputting the information will know what the dealership name is but will not know their ID. I need a solution that will allow the user to select a dealership name in the form and auto-populate the Dealer_ID field in the Customer table with the appropriate ID from the Dealership table.
View 1 Replies
View Related
Nov 7, 2005
Hello.
I've been reading these forums for a few months now while developing a database for logging bugs during website QA, but never posted.
I need a way to change the "Month" combo dynamically based on what a user enters into the "StartDate" and "EndDate" Fields. (I'm using Access '97)
e.g.
I have Validation set up on both start and end date fields so they are required before a record can be saved. I would like the "Month" combo box to update with the proper month after a user has entered the StartDate and EndDate.
(Basically a timesaver for users who are entering 20-25 criteria into a single form.)
There's one hangup:
the date ranges aren't typical, as in, "November 1-30 = November"... It's more like "10/23 through 11/30 = November"
here's what that chunk of the form looks like:
http://www.ewiessner.com/misc/images/sded_month.gif
So if I were to enter 9/27 start, and 10/27 end, "October" would populate in the "Month" combo box, and I could continue to other fields.
Any input would be appreciated, and I apologize if this is a repeat post, I couldn't find any similar topics.
Thanks,
-E
p.s. - If it helps, I have a 'Month' table with 1-12 = Jan - Dec, the 'StartDate' and 'EndDate' fields are text entry boxes bound to the master 'Details' table where all the data is stored.
View 3 Replies
View Related
Aug 4, 2006
Here's the deal:
I'm an extreme newbie, I do not know access very well, nor do I know VBA, I do know PHP.
I have a for in access that has 2 user input fields, one for prodid one for shipid. I have a combo box that upon entering data into the previous 2 fields, it does a query against an MsSQL database looking for a record that has both. In any case there will only be 2 outcomes, either 1 record, or null. I would like to have that same combo box automatically use the result as it's value so that users don't have to check the box, because they won't, and so that the rest of my VBA will be able to function properly.
Can anyone assist?
Thanks.
View 2 Replies
View Related
Sep 27, 2006
Hello, I have a combo box on a form which lists some names generated from a table.
I would like the selected name to be inputted into the 'critera' of another query called 'qryPBCustLevel' and for that query to be run.
I have tried to code this, but it is crashing at the point it trys to add the name into the query.
Can anyone help? Code listed below.
Sub cmbPB_AfterUpdate()
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()
'Constant Select statement for the Query definition
strSQL = "SELECT DISTINCT tblTempPB.PB_NAME" & _
"FROM tblTempPB"
strOrder = "tblTempPB.PB_NAME;"
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[PB_NAME] = '" & Me![cmbPB] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
'Pass the QueryDef to the query
Set qryDef = dbNm.QueryDefs("qryPBCustLevel")
qryDef.SQL = strSQL & " " & strOrder
'Open the Query
DoCmd.OpenQuery "qryPBCustLevel", acViewNormal
End Sub
Thanks, Steve. :confused:
View 2 Replies
View Related
Mar 24, 2008
Hi I am a new user. I'm sure this is simple but I just can't get it. I want to use a dropdown box with letters A-Z to return records in a second drop down box that begin with the lettered selected in the first dropdown box.
Any help would be appreciated.
Thanks
View 4 Replies
View Related
May 31, 2006
Hi
I am selecting a value from combo box. Based on that value the data should be fetched from the table and should be displayed in Text box. In total there are three fields.
Thanks in advance
View 1 Replies
View Related
Dec 5, 2006
Hi
I hope this is in the right section, only just stumbled across this site, and am not to sure of my way around it yet. So please let me know.
Ok, so basically I am creating a very basic database to store employee training details.
http://loane.co.uk/gallery/albums/RANDOM%20%20%20/table.jpg
The above is the basic layout of tables.
The field tblEmployee.DepartpmentName is a combo box lookup field where it is linked to a Query that lists tblTraining.DepartmentName, so only departments that are set up are listed, they are also grouped so no multiple data.
Now I want to create a similar but more complex lookup for the tblEmployeeTraining.TrainingID field. So when I input an Employee ID into tblEmployeeTraining.EmployeeID and then go to select tblEmployeeTraining.TrainingID from a drop down combo box, only those Training ID’s whose department name equals that of tblEmployeeTraining.EmployeeID departments name are listed.
Now I have made a stab at this, this is the SQL code behind the query:
SELECT [tblTraining].[ID], [tblTraining].[TrainingName]
FROM tblTraining INNER JOIN tblEmployee ON [tblTraining].[DepartmentName]=[tblEmployee].[DepartmentName]
WHERE [tblEmployeeTraining].[EmployeeID]=[tblEmployee].[ID];
This almost works, I have my combo box for tblEmployeeTraining.TrainingID but when I click on it I get the following input box. It doesn’t seem to see my value in the Employee ID field.
http://loane.co.uk/gallery/albums/RANDOM%20%20%20/table1.jpg
I can enter an employee number here and the correct Training ID’s based on the employees department name are listed. How can I get this to read the EmployeeID automatically so I don’t have to input it again?
Many Thanks
Gary
View 4 Replies
View Related
Jan 9, 2007
Hi all,
ok.....
i have a list box on a form which when an item is double clicked executes a SQL query. Its the first time i've ever used it so i'm unsure of whats possible and whats not.
Below the list box is a combo box that i want the user to make a selection in and which is tied to the value in the list box. (hope that made sense).
So, user select a listbox item. Before they can save they have to select a combobox item. When both are done i want to add the date() to the listbox item (in the underlying table) AND the value from the combobox.
I have the date part working but cant seem to link the combobox.
here's what i've got so far.......(the red is where it doesn't work)
Select Case MsgBox("Save Completed?", vbYesNo, "Save")
Case Is = vbYes
Dim StrId As String
Dim StrSql As String
Dim qryD As QueryDef
StrId = List0.Value
StrSql = "UPDATE TBL_StockEntry SET TBL_StockEntry.[Date Booked In] = Date(), TBL_StockEntry.[State] = me.combobox1.value " & _
"WHERE (((TBL_StockEntry.StockID)=" & StrId & "));"
Set qryD = CurrentDb.CreateQueryDef("", StrSql)
qryD.Execute
List0.RowSource = List0.RowSource
ps. the error i'm geting is: runtime error '3061':too few parameters, expected 1.
can anyone help?
cheers
spinkung.
View 2 Replies
View Related
Apr 23, 2007
I have a combo box that gets its values from a query. The query only shows those records whose yes/no field[ClosedIssue] value is equal to no.
This works, but where the description should show up in the combo box, I get the ID number associated with that description from its original table[IssueType]. How can I make it so that instead of the number being displayed, the actual text is displayed?
View 14 Replies
View Related
Jun 14, 2007
I have 6 cbo boxes on my form. I want to query by 1 box or 2 box or 3 box... or 6 boxes depending on what the user wants.
For example say I have Customer, City, State, Age.
Say I pick Atlanta for City and leave all of the other boxes blank, I want the query to pull where all of the records are city Atlanta and show all of the other data. Or if I want where city= Atlanta and Age = 20, I want to pull only those records. Anyone have an example of this, I'm pretty sure it's something commonly done.
View 10 Replies
View Related
Jun 17, 2007
Dear Friends,
I have made one query. To get the particular data, I entered ["Project Code"] in criteria of project_code. What I am looking that instead of entering project code, I have to have comobo box. So whenever I run my query I select project code instead of writting project code. I have tried this code =Forms!YourFormName!YourComboBoxName but not working.
can you help me please
mithani
View 5 Replies
View Related
Jan 16, 2008
I am trying to use a combo box and then a command button to open another form that would be filtered by the combo box on the first form. My command button macro reads where condition [Forms]![Form1]![Combo3]=[Query1]![Code] but when I click the command button I get a popup that says Enter Parameter Value Forms!Form1!Combo3. If I type the value in my second form opens filtered correctly. How do I get the query to recognize the value in the combo box?
View 3 Replies
View Related
Apr 19, 2008
i am using a parameter query that produces a pivot chart and the user must enter a value (use name) before the chart is produced.
My problem is that the user must know exactly how to spell the name for the query to work.
So i was wondering is it possible to have a form with a combo box and the user pick that name they require from the combo box and that selection would be the parameter used by the query to produce the pivot chart.
View 1 Replies
View Related
Apr 28, 2006
Ok I think this is going to be a really easy answer for someone but I'm going round in circles :confused:
I have a table, called "TBL_QUARTER"
It has 3 columns, as follows:
Quarter
Startdate
Enddate
I have a form that is bound to the table, which has a combo box which is using Quarter as it's row source and two text boxes, one bound to startdate and one bound to enddate.
So all I want this to do is, when I change the Quarter chosen in the combo box, the startdate and enddate update accordingly.
It works using the record navigation "goto next record" etc...
On the Combobox, on the afterupdate event, I have tried to requery the textboxes but to no avail.
Can anyone point me in the right direction ?
many thanks
MattP
View 3 Replies
View Related
Nov 17, 2004
I am trying to update records in a table using a selection query. I would like to update the information in a field that uses a combo box in that table. The down arrow appears and I can see the options in the combo box, but I cannot select any of them, or type into the cell.
Any suggestions?
Thanks.
View 2 Replies
View Related
Feb 4, 2007
Hey,
I'm setting up a patient database and I want to record family history. For this I want to use a combo box such that only relatives show up of a particular patient. Then a selection can be done e.g. who the mother is etc. How can I build a query for this combo box such that it depends on other fields of the form, e.g. a textbox. I have tried referring to the name of the textbox but this only works for the first entry in the database, after this the combo list doesn't get updated.
any help is appreciated!
View 1 Replies
View Related
Jun 28, 2005
I have a query based on two combo boxes in a form that links to a report, it works fine. What I want to be able to do is select criteria from one box and if the other is blank have it include everything (right now if one is blank the report will be be blank) and vice versa. Any suggestion on what to enter into the criteria section of the query.
Thanks in advance
View 1 Replies
View Related
Aug 17, 2005
First of all I want to thank in advance anyone who helps me out, I appreciate it.
I just started working with Access a week ago and I have pretty much expended my potential, and I need help to get any farther. This is probably very basic. In fact I have read a lot of pther people having the same problem but so far I haven't been able to apply thier solutions to my database.
Here goes:
I have a form that has so far 2 combo boxes (this will grow), a parameter text box and beginning/end date boxes.
I want the query (run through a "search" command button), to of course, dispay the records that match all the criteria, ingnoring the cireria that is left blank.
I can get each one to work individually, but when all the criteria are together, thats when the results get dramatically messed up.
I could go through the different criterias I have tested but I'll just ask if anyone knows the right way to query multiple criteria.
If you need more information, as in SQL or whatever, just let me know.
Thank you very much,
Billy
View 10 Replies
View Related
Aug 23, 2005
Working on a sales guest registration database and need a some assistance with a query for my report. I have the following fields in the table:
Communities
RepName
Date1stVisit
MoveTime
I have created a query which works without a problem. I have also created a dialog form to use with a report that I've created. Everything works ok if I fill in all the boxes on my form with data but in some instances they want reports that are not filtered all the way down. On my form I have an unbound combo box for each of the fields above. I've tried several of the suggestions found here but cannot get it to work completely. The last thing I tried was the following criteria for each field I want to filter on:
like [forms]![repdialog]![communities]& "*" or is null
like [forms]![repdialog]![repname]& "*" or is null
>= [forms]![repdialog]![datebeg] and <= [forms]![repdialog]![dateend]
like [forms]![repdialog]![movetime]& "*" or is null
This sort of works but I must be missing something because it gives me too much data. For instance if a just wanted to fill in the REPNAME box on the form it should give me every record that has that name in it but I get more that that - I think because of the is null comments. Any one help me correct this?
Thanks,
Chester Campbell
View 14 Replies
View Related
