Two Drop Down Lists In A Table - One Dependent On The Other
Sep 12, 2014
I would like the user to enter information directly into an Access table (it's ok). I want one column to have month ended. In this column, there would be a drop down/lookup menu with each month of the year. The user selects the month we are being billed for.
Then another column called week ending date, would display all week ending dates (ends on a Sunday) but only for the month that was selected.
If the user selected another month on another row, the available weekending dates to select from would show only what pertains to the month selected. Is it possible to do this?
I am building a query for a university and the table i am working from has a column with every course run by the department.
How do i create a query that allows me to add a drop-down menu to the course instead of having to write out the course name everytime i want to query the number of pupils on a particular course.
In my table I have the following fields that are tick boxes
Vee Cathedral Round Bilge Bilge Keel RIB Semi-Displacement Keel Lifting Keel
Each record can take a couple of these options. For example record 1 could be ticked for a 'vee' and a 'cathedral' whereas record 2 could be ticked for all of them.
I have been trying to use these to make a search form. I want to be able to chose on ie. 'Round Bilge' and it will search for a record that is ticked 'round bilge'. I have done this for both a combo box and a list box and have used the formula for each field [Forms]![Boatsearch]![hulltype]
When I have clicked run query I have an error message saying: 'This expression is typed incorrectly, or it is too complex to be evaluated. For example a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables'.
This is my first time posting at Access World Forums and I hope you can help me out.
I think this is a fairly simple problem but due to my lack of experience (this is actually my first time using Access) I am unable to figure out what is wrong.
I have a entry form for a series of problems that needs to be documented on the plant floor.
The issue is in the 'Manual_Entry_Rewinder_Detail' form where I have two drop-downs -one for the category of which the problem lies and another drop-down for the actual problem itself. I have made (or tried to at least) make the problem drop-down dependent to the category drop-down. The purpose is to list only the relevant problems after the category is selected. I have made a category ID of which I labelled for each problem. This of course corresponds correctly to the actual (an existing) category ID. Please take a look at the two tables: "Problem" and "Category" for details.
I followed this tutorial http://www.blueclaw-db.com/download/dependent_combo_box.htm . I even downloaded and inspected their sample database with no idea on what is wrong with my own.
I have a table which lists amendments (amendments history file) which need to take effect of another table (M0070), I can select the records fine and so the sub query looks to be okay but I'm missing something (probably obvious) in the deletion query where by it does only delete the records selected but everything (I know that the asterisk is wrong in the code below but I'm not sure what to replace it with).
At this point I'm trying to delete all records for a specific employee on a specific contract (they can be 1 or more employee records for that individual on a contract - and they could exist on multiple contracts).
DELETE * FROM M0070 WHERE EXISTS (SELECT M0070.[Contract No], M0070.[Contract Name], M0070.[Employee No], M0070.Forename, M0070.Surname, M0070.Branch, M0070.[Long Desc], M0070.[Emp Post Start Date], M0070.[Days per week],
I know experienced DB developers say never to use a lookup in a table for a foreign key and instead to use it on the form level. For this reason, I am going through my tables and removing lookups from the table level now.
But how do y'all feel about lookup value lists? (so the list is typed-in instead of looking up a table value)
If you think they shouldn't be used then what should I do instead? Should I make a table for the handful of values and link with a FK field?
Or is there a better way? I would rather not have to make a million tables for these short, stable value lists.
I have an ... interesting issue. I am wondering if others have dealt with this. I have a solution, but am not the happiest with it.
I basically have 2 databases here. (Or I will when I'm done making them) and one is dependent upon the other. In fact, the 2nd's tables are dependent upon the fields in the first database's table.
This database is for quality control checking purposes.
First off,
Database 1: Basically a list of standardized audits and what needs to be checked for those audits. What needs to be checked can change as things progress, but stays pretty standard.
Database 2: This will hold the checks that the QC branch will do, based on what type of Quality Check they are doing, they have an audit standard. This audit standard exists in database 1.
What basically ends up happening is that the data in Database1 become Table fields in database2.
Example:
Database1: Radio Check Audit Standard. Needs to check following: Process Specs EDMS Drawings Manuals SoP / Policy Used Radio callback Used radio during emergency Etc.
Another Random Check Audit Standard Needs to check the following Process Specs EDMS Drawings Manuals SoP / Policy Did something specific for this Audit Didn't do something else, also specific for this Audit. Etc.
Continue on with other Audits.
Database2: Will keep track of all the quality checks that have been preformed.
Today we will check Radio Protocol Did they: Process Specs EDMS Drawings Manuals SoP / Policy Used Radio callback Used radio during emergency Etc.
Yes, No as necessary.
2 Days later, we need to do another QC on the Radio Protocol Need to check if they did: Process Specs EDMS Drawings Manuals SoP / Policy Used Radio callback Used radio during emergency Etc.
Etc.
But what will be checked for in Database2 depends on what the standard is for the "Radio Check Protocol," or etc.
Right now, i see 2 options:
Option1 Make the 2 databases, and when a user "Picks" the Radio Check protocol, a linked table is filled with the Name / object that needs to be checked with a field in the same row to hold the data if the object was done / not done etc. The fields in Database1 are automatically put into this specific instance of Quality Check, etc.
Only problem with this: Requires coding to auto update. Easy to do, but if errors occur, things will screw up.
Option2: When the user picks the Radio Check protocol, simply allow a subform with a refreshed drop down box. Allow user to pick items they need (Limit it to only this Audit list, etc) to set to true. Assume false for other items not specifically chosen by user.
Problem with this: If an Audit standard changes to remove an item, or add item. Any quality checks done will also change for the assumed false items. If another Item added in, then all old QCs will now also have that item added in / reported assumed false. And if it is deleted, all old QCs will loose the item that was reported false.
I'm seen the first option as the only viable one.
Am I missing this, is there some other way to do this that would be better / wiser? Etc?
I have a db that had to be designed taking into consideration a previous db. Merging went well. On several of the forms that feed the tables there are drop down boxes for entry consistency. Those drop down boxes have two columns. Ex is Counties field: Column 1 = County Name Column 2 = Numerical Designation.
On the form the dropdown shows the name for selection.
On the table it is a different story. On the portion of the table that came from the old db the field shows the Name. On the portion of the table that pertains to data entered since the new db, only the number shows. This also means it won't let me search by county name - only by number which doesn't work.
I have tried to compare properties for the old and new database for the forms, the tables, etc. and can't find a discrepancy.
1. How can I get the field in the table to show only the name and not the number? 2. Will that solve my search problem?
Oh, does it make a difference that there isn't any sort of pk or fk in the lookup table?
Thanks.
p.s. I am learning as I am going - so really complicated answers are difficult for me
have used the wizard to take values for a table dropdown field from another table
the table has
surname forename and class all of which reside in another table... ive tried to set up the bound columns so when you select a surname from the drop down the other two fileds also referencing that 6able are automatically filled in with their corresponding values.
so if the ref table had
smith john 1m
by selecting smith in the new tables drop down, the forename and class fields automatically select the corresponding values from the ref table..
im sure this is possible but cant get it to work and cant find anything on the web (although this is probably down to not knoing the right search string)
I know this can be done i MySQL and with SQL Server but is there a way to drop the table but check if it exists first in Access before running a query ?
Is there anyway that access could create a drop down box within a drop down box? For instance, when a selection is made in a drop down box, it opens another drop down box with choices.And is it possible to link an inserted image from a form to open in MSPaint that when it opens, the image is already there?
As you can see a table can be linked to any number of tables.
Now for my actual question. I don't want to have to type in all the queries and tables. I would like to choose any query and table from a drop down of all possible queries and tables that are currently in the database - like a list, where the list contains all the possible values.
It is essential that i use MS access to do this, what i need to do is have a drop down list on an ms access table to make sure the data in the field is exact, i could use validation if this is not possible but i would prefer this method also i dont need an answer to this but is there a way to make the key field dependant on whatever is selected on the dropdown menu? sort of use a general input mask but alter something dependant on the drop down menu to have it as specific characters, i have some experience with VB if there is a way to link vb programming with access
I had 3 drop down filters that worked when the word being filtered was written in the table. I have since then linked them to new tables to be more database-y. The problem I have found (a couple weeks later too) is that my drop downs broke.
It makes sense why, they are now 1,2,3,4 instead of words but how do I make this work now that it isn't in the current table?
Code: Private Sub cboMDS_AfterUpdate() Dim myPlane As String If Me.cboMDS = 0 Then myPlane = "Select * from Baseline"
I was browsing the Northwind database and notice that there is a dropdown in the table (See attached print screen). I've been trying to recreate it but I'm having a hard time.
I have a form with a drop down list, when the user selects an option and press a button. I would like to add the value of the drop down list to a table as a new record.
I have a sizable list of GIS polygon shapes(~9000 data points), collected over 15 years, that I am trying to shrink down to unique ID's. Each years data has a unique label, but for many of the polygons the shape does not change from year to year, so I want to assign a unique ID that encompasses all of the years that shape existed. Some examples:
1950- Parcel A - 2 acre star same 1951- Parcel K - 2 acre star same 1952- Parcel L - 2 acre star same 1953- Parcel F - 2 acre star same
1954- Parcel J - 3 acre box same 1955- Parcel Z - 3 acre box same
Ok, simple enough. I created a unique polygon ID in a separate table, and set the first 4 records to some value, let's say AAAA. The latter 2 records I called something else, BBBB let's say. These values are used in a combobox so my data entry stooges can easily assign each duplicated polygon the same ID, so a quick query will show that polygon XXXX is comprised of so many years worth of the same value, just with a different label. Now my question...
In the above example, after 1953 the 2 acre star polygon never exists again, so I want to 'retire' that option from the combobox so it cannot be chosen in error. As it stands now, if I were to enter the following date point 1956- Parcel N - 3 acre box
I can still choose AAAA or BBBB as the unique polygon ID to equate it to. Is there some way (maybe a boolean option) to disable just a specific combobox value so it cannot be entered in new records, but STILL shows up in old records, a.k.a. is not deleted?
In my form's table (tblMain), I've got a lookup field (drop-down list) that lists the primary key field from a different table (tblDiff). tblDiff includes 3 more fields. In my form for tblMain, I want to include 3 more textboxes that get filled up with these 3 fields from tblDiff when the corresponding primary key is selected in the drop-down box.
Is it possible to sort a natural table drop down by date from within a query? What im doing:
1. making a query that has certain results displayed 2. within the query you can select one of the field boxes and it has a list of all the items in that table. 3. is it possible to sort this natural table listing from within the query. I have attached a screenshot. Same thing happens with the client ID listing from within the query. Want to know if it is possible or not to sort those. I cant see how.
I am trying to setup a database in order to demonstrate a tie in between active directory and the HR side of a business.As such, I would like to select two fields from drop down menus that reference in Department and Location tables, but use this data to actually Populate the Personnel Records table rather than Query.I know this is not normally best practice from a DBA perspective.
I have this setup already in Filemaker, just through using relationships.However, I want to move to Access, as it is more industry standard and much lighter weight, so am trying to find the best way.I have attached two pics of my filemaker database, and a zipped copy of my Access file.
I have a drop box that has four selections from a table (NORTH, EAST,SOUTH or WEST) I also have a another table that has two fields which is a NAME of a person and either NORTH, EAST,SOUTH,WEST depending on the persons name. What I need to happen (within a form) is that when I select NORTH from a drop down box, I can then select names in another drop down box that correspond to NORTH only. Then only names from the South list if I select SOUTH etc
I have a fairly large table with approx 15k records. I have a form where I have a drop down/select control box that displays all of these records showing a few of the fields. I select one of these records to create a new record as it places one of the fields into the new record. The problem is that there are so many records that it takes a lot of scrolling to get through all the records.
Once I scroll through all the records, the next time I scroll, it is very fast and shows all the records in one scrolling. However, when I get off the form and then go back it starts over with all the records having to scroll through etc.. What I want to be able to do is have all these records loaded so that they can be scrolled through quickly the first time I open up the form.
