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.
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?
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.
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.
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.
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
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?
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.
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.
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?
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.
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.
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.
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.
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.
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:
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:
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.
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?
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?
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?
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.
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
