Combo Query
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 Replies
ADVERTISEMENT
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
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
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 14 Replies
View Related
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
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
Aug 2, 2006
Hi,
I'm trying to add a combo box to my query so when I select the item from the combo box it executes my query. I don't know where to begin... Any help??
View 7 Replies
View Related
Sep 14, 2006
Is it possible to use a drop down combo box to specify criteria?
I have a query which asks to select a name ( Criteria [Enter Name] ). What I want is to be able to select from a list of names available.
I create my queries using the Wizards, so specific SQL to enable the above would be appreciated if there is not a straight forward solution.
Thanks
Richard
View 3 Replies
View Related
May 2, 2007
Hello again all... Today's problem is as follows.... I have an "after update" combo box that is coded as "CurrentDb.Execute "Uno", dbFailOnError". I have an update query which I am trying to have a combo box from a form input information, update a table with what the combo box was inputted with. (if you follow). In my query, I have one field as showing the "Update To:" filed as "Forms![Loc-1L]!Combo8". When I try to run the query, it works in the query if I manually type in a location, however when running it in the form, it gives me the error: "Too few parameters. Expected 1". Any ideas where I'm going wrong?
View 2 Replies
View Related
Jul 4, 2007
Hi;
I'm using Access 2003 and trying to select the criteria for a query from a form that include combo box. The form that have combo box is work.
Our SQL statement is;
SELECT Names.Name
FROM [Names]
WHERE (((Names.Name)=[Formlar]![Form1]![Name]));
When I run the query the box comes but not the list.
Could you give me some advise to correct the problem.
Thank you for your help.
ibrahim
View 8 Replies
View Related
Feb 26, 2008
Is it possible, when using a parameter query to make the message box that appears asking for the parameters be a combo box to reduce the chances of the user misspelling something?
Thanks
View 3 Replies
View Related
Mar 28, 2008
hi, i have a query which retrieves data on products.
my problem is how to display a specific total i need.
there are three fields;
[itemprice],[itemlabour],[itemdiscount]
the item price and labour columns are in currency format, but the discount column allows the user to select a discount between 0 and 50%.
now in a form, when i want to calculate the total price to be paid i have a simple code like this:
[itemtotal] = ([itemprice]+[itemlabour])/[itemdiscount].column(1)
the idea being, when a discount is selected, there is a hidden column in the combo which contains a specific number to divide the total of [itemprice]+[itemlabour], thus giving a total.
now i tried to use this code within a query, but it doesnt work.
any ideas?
jared james
View 1 Replies
View Related
Apr 6, 2008
Hi all.
I am a relative beginner using Access and I am still using 2003 I am trying to develop a database to store student details, module details and area of expertise of lecturers
The tables are Students,ModuleName, Instructors and Expertise. I have a students form with a module subform. In this subform I am entering the following data, Module Name; Area of Expertise and Lecturer. The idea being to select the area of expertise (cboExpertise) and this filters the lecturers listed in the following combo box (cboInstructor).
I have looked at and tried to use the various examples but I am doing something stupid.
Query in RowSource of cboInstructors
SELECT [Expertise].[Expertise], [Expertise].[AreasID] FROM Expertise;
Query in RowSource of cboExpertise
SELECT [Instructors].[ InstructorsID], [Instructors].[AreasID], [Instructors].[ Instructor] FROM Instructors;
Private Sub cboInstructors_AfterUpdate()
Dim sInstructorSource As String
sInstructorSource = "SELECT [Instructors].[InstructorsID], [Instructors].[AreasID], [Instructors].[Instructor] " & _
"FROM Instructors " & _
"WHERE [AreasID] = " & Me.cboInstructors.Value
Me.cboExpertise.RowSource = sInstructorSource
Me.cboExpertise.Requery
End Sub
Help please:eek:
View 5 Replies
View Related
