Forms :: How To Change Drop Down Box Options In Form Without Affecting Table
Jun 20, 2014
I'm a new user of Access and I'm required to input a survey into access. The data collected is being analyzed afterwards and therefore there cannot be any alpha content in the original table so I assigned multiple choice options numbers to correspond to the answers. In form view however I need the drop down boxes to spell out the answers, not just the numbers so the interviewer can read them out, I've seen this done on other similar surveys..I also tried going into form view and editing the drop down choices but that changes the table values.
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?
Currently, I have 10 Users running a front end form that connect to back end data where they add to current records and eventually check that the record is complete.
I also have an "Apply Filter" button on the right hand side of the form that allows the user to apply filters to the records to show specific data that is not complete.
I am currently hearing that while the user is working, whether they apply filter or not, it seems as though all of a sudden, all of their completed work dissapears from their recordset hence not allowing them to go back to make changes to it if needed. I can only conclude that when a different user clicks apply filter, all users are affect some how... here are some examples of the code for the filter:
Code: Select Case [cboFilterValues] ' Where the user selects a filter parameter from a dropdown Case "All Data" strSQL = "SELECT * " & _ "FROM [Data Table] " & _ "WHERE [Complete] = No
Then it does:
Code: Me.RecordSource = strSQL
I am basically trying to find a way where only the user wanting to view the filter is affected... FYI, the other users arent actually seeing all the filters from the other user filter choice, they simply loose all their completed work from their form.
i want to add list of 2015 courses and have a seperate tab for 2015 courses(just like 2012,2013,2014).All the courses are setup as queries, which is why i can not seem to add to them.If you go in 2014, 2013 tab you will see list of coures for those years, i just want exactly same thing for 2015. All the courses are same, juwst their name is slightly diff.
I have a drop down list to select a committee, but my list is only showing half of the list. There's no scroll bar, and when I try typing in one of the others, it tells me it's not on the list and asks if I would like to edit the list. When I select edit the list, it shows all of them.
I have an Orders table and an Inventory table. Whenever I enter a new order and type in the Item # and Quantity ordered, I would like the Inventory amount in the other table to automatically be reduced by that amount. Possible?
EDIT: Okay, so I've figured out that this needs to be done in a query, but my expression isn't coming out right. I tried this: QUANT: [Inventory]![QuantityOnHand]-[Orders]![Quantity], but it simply calculates the new inventory based on the original inventory number. In other words, if I have 100 on hand, every new order just subtracts itself from 100, and not from the result of the previous transactions.
I have an issue with a couple of my combo boxes. When in form mode the drop down menu displays a list of options (taken from my source table), this is fine, but the problem is that it allows the user to select more than one of the options in the form of checkboxes. This is not what I intended.
I've attached to pictures to demonstrate the problem. The first is ComboBoxQuery (the one with the problem) and the second is ComboBoxQueryWorkign (the one without a problem).
I'm not sure why this is happening and I've tried comparing all the properties of both these combo boxes and changing some of them to see if I can eliminate the problem without any joy.
I use a very standard SELECT statement to pull the data for the combo box:
SELECT CountryID, CountryName FROM tblCountryInfo ORDER By CountryName
What I am trying to do is create a form with 2 drop down boxes (A,B,C,D) &1,2,3,4...) what I want to do is make it so if you select option A in the first box only 1,2&3 are available in the second box but if you select option B 3,4&5 will be the only options available.
I am trying to create a form that will allow me to populate a list. For instance, I could have a drop down box with grocery shopping items in, then by selecting an item and then pressing a button, it would add it to a list I could see on my form. Then I would select another item and add that to my list. Therefore I would be creating a "shopping list" that I could see on my form, from a drop down box on my form.
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.
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.
I have a spreadsheet that has been downloaded from a website. On the website people have chosen from a group of 28 checkboxes their answer. I have imported this spreadsheet into access. I now need to be able to run a report that shows only the one they chose instead of listing all 28..
I have drop down list linked to table included "agent names" , the names appeared normally in the form but not Alphabetic (A-Z) although the table was alphabetic .
I want to track which user has which phone and track issues with them.
I have all the relationships set up right.
I have tables:
DeviceStatus - for the drop down box, contains IT Stock, Faulty, With User and Retired DeviceTypes - Contains model numbers for devices Assets - Contains a link to DeviceTypes, a link to DeviceStatus and the serial number of a phone.
I have a query:
StockStatus - shows Assets.ID, DeviceStatus.Status, DeviceTypes.description and Assets.SerialNumber
What I want as an end result is a form with a drop down box that shows DeviceStatus.Status (which I have working at the moment) with a go button next to it which will call the query that only shows what was selected in that drop down box.
DLookup function. (this is for a stamp collection database).
On my form ("InventoryInput") I have a text box called "Catalog" for a numeric entry and a text combo box for selecting a "Country" in drop down list.
I want to query a table called "CatNameList" to get the "StampName" of the item (based on the entries of Catalog and Country) and populate that name in the text box. The fields in this table are called "StampName", "CatNumb" & "CName" respectively.
I have successfully placed the following expression in the control source of that textbox and able to populate the StampName I need based solely on the catalog number alone.
That express is : =DLookUp("StampName", "CatNameList", "CatNumb = Form![Catalog]")
So it will populate the "StampName" data to match the "Catalog" number entry just fine.
However, I need to add a second layer to incorporate the Country.
Example : There is a catalog "1" for "USA", and a catalog "1" for "Canada" but both have different "StampName".
I have been attempting to get that second piece added with no success. Here is the expression I have been trying to get to work :
Right now, the text box is just blank with the above expression. I thought it may be because there was no match found, but I have triple checked to ensure I have the spelling correct on the country name in both places.
Basically, I just need the dlookup to take the "catalog" and "country" off the form and match it to the "CatNameList" table fields of "CatNumb" and "CName" to give me "StampName" field back on the form.
I have a master form for lack of a better phrase and 3 other forms that represent specific items in the original drop down box. In the master drop down box, I have all 50 states, and the 3 forms are for 3 specific states. Here's the code I have so far but when I save, it says "The 'OpenForm' macro action has an invalid value for the 'Where Condition' agrument."
Option Compare Database Option Explicit Private Sub State_AfterUpdate() Select Case Me.State Case "CA" DoCmd.OpenForm "Auditor Form (CA)"
I created a form that has a drop down menu to select a name. I attempted to set it up so that once a name is selected, another form is opened with that person's information. I was able to get it set up so that the first form closes and the second form comes up, but I can't get it to filter the name. I tried using SetTempVar and then SearchForRecord, but got nothing. I tried using OpenForm and Where Condition equals the search, but just got the automation error. It is currently set up using SetTempVer, OpenForm, SearchForRecord.
I have tab control form with (5) tabs. For this discussion - Tabs 1 through 5. For a blank (new) form sheet tabs 4 & 5 need to be hidden. Based on what is selected via the drop down box (on tab 1) then tabs 4 & 5 may remain hidden or needed to be un-hidden. Example: [DropDown1]
I think one I figure this out then I can use the 'OnCurrent' event to check the drop down selection as a user selects the a record or scrolls through records.
I have built a form that holds details of training records. What I want is when a drop down is selected in the main form, that it will populate some of the fields in the subform. I have this working at the moment, that for example, when a certain course is selected, that their modules will appear in the subform. Where my problem arises is that I have a relationship between two tables that I want to appear on the subform, so that details can be filled in on the subform against the list of modules that automatically appear.
Having problems getting dlookup to work in the control source field of a text box.
My form has fields : Catalog # (numeric value) and Country (drop down text selection).
I would like to query a table CatNameList for a name (text) if the catalog # and country find a match on the table. My field names on the CatNameList table are : Name, Number (to validate against the Catalog # entered on the form) and CName (to validate against the Country drop down on the form).
I am successfully able to populate the name from the CatNameList table on my form using lookup of the catalog # using this :
However, I will eventually have several catalog numbers that will be identical in the table CatNameList, thus why the country is important as the second criteria to be added into the dlookup.
I have tried for a few hours unsuccessfully to add the second portion to my dlookup.
This is what I have currently (not working) that I have been playing with, I'm sure I'm missing a quote mark, & or something simple.
I have a simple data entry form with drop down facilities on 2 fields. One of these fields incorporates a drop down list from a table but there are occasions when I wish to make a free text entry for the single record, but do not wish to add it to the drop down list.
I have tried to achieve this with a Combo Box but without success, although I am sure that I have read that it is possible.
In a local DB table or a data grid view, the columns have an arrow at the top next to the name. When the arrow is clicked, all the entries in that column are displayed with check boxes. They are listed underneath the "sort" and "text filter" options. I am working on a DB project with ODBC linked tables instead of a local access table.
My linked table does not have this same functionality. It is missing the names with check boxes where I can select individual entries. I don't know the correct terminology for this functionality I am describing. That makes searching tough. What this is called and why the tables would be different.
