I'm stuck on something that should be simple, but maybe it isn't:
Problem 1:
To speed data entry in a contact table, I want an entry in the Zipcode (postal code) field to automatically populate the City and State fields from the data in a Zipcode table (Zipcode-PK, City, ST (2 letter code)). The Zipcode table has only one city /st for each zipcode, so it can be the primary key (always a five digit number, sometimes with leading zero(s)).
Sometimes there are several names for a City associated with a particular Zipcode, so I want the City field to be able to be overwritten with a different city name if the data entry person needs to do that. Alternatively, I could establish a separate primary key for the zipcode table, and allow several choices for the rare zipcode that has more than one city name in use.
If I did that I wouldn't have to store the city and st in the contact table (which is better database design, I realize), but I really do want to be able to store the city and state in the contact table because there will be times when the right zipcode isn't known, and the city and state are known, and forcing the input of a possibly incorrect zipcode would be undesirable.
Problem 2: I've got a table with 4,000 records where I have the zipcode, but the city and state information were lost, and I want to be able to re-populate those fields by writing, I guess, an update query that taps the info in the zipcode table.
Problem 3: When entering addresses, sometimes there won't be info about a certain zipcode, and I need some automatically generated dialog that allows adding city and state info for a new zipcode that won't slow down the entry of contact records.
Any help would be greatly appreciated. Thanks in advance. Send an email if you prefer.
Does any one know of a place online where you can get the tables (for free I hope) for a city state zip comboboxes, or at least the tables for all cities in the US.
I need help with the code for a combobox. The thing is that I've created a form where the user has to select the country, then the state and finally the city. What I need is that once the user selects the country, the combobox of the state shows only the states of that country and then the combobox of the city shows only the cities of the chosen state. I'd appreciate all the help given. Andrew.
Hello everyone. How do I go about automatically inserting a date from one table into another table. This are my tables:
tblPAF (Personnel Action Form) PAFNo EmployeeNo Date Department Division Salary Comments
tblBenefits BenefitsNo EmployeeNo Date BenefitType CoverageDate
Every time an employee’s salary or benefits changes we fill-out a PAF (paper format) on it we write all the changes. In my PAF db form I’m thinking adding a command button to open my Benefits Form. Once my Benefits form is open I don’t want to renter the date that was entered in the PAF form I wanted to automatically insert that same date. Hopefully I made myself clear.
I'm a bit new to access and not sure how I should go about doing this. I have a list of businesses located in new york, with their name, address, zipcode, and other demographics. I need to filter out all the rows of businesses that are located in new york city. To do this I got a database file with a list of all zip codes (5 digit format) that are considered new york city. So what I'd like to do is run a query based on the new york city zip codes and get a list of the businesses that are only located in nyc.
Another thing is that in the business database some zip codes are 5 digits others are in 9 digit format (xxxxx-xxxx), so I want it only to look at the first 5 digits as it filters out the zip codes im looking for.
I hope this isn't out of place.. thanks in advance.
All, I am using Access 2003 I have a table with zipcodes and the data type is text. Some of the zipcodes were entered incorrectly. In a query, I need to use "Trim" because there are leading and trailing spaces surrounding the zipcodes also I need to get those problem zipcodes where they don't equal the length of 5 like:99999 or format the 9 digit zipcode format like: 99999.9999. I tried: not like "*****.****" and not like Len([Zipcode])>5 I keep getting "" around the 5 and it doesn't list all the problem zipcodes. How can I get just the problem zipcodes? Thanks
I have a table with contact names and addresses - Tel no ect, i want to set it up so that when i enter a town it automatically looks up from another table what zip code it belongs to and updates the record to include the zip code and even the state ?
In the zip code field of my form, if the first number of the zip code is a zero, such as 02436, the zero is deleted as soon as I leave that field. The zero won't stay and I want it to so I will have a valid 5 digit zip code. In other words, as soon as I leave the field the zipcode is changed to read 2436 - without the zero. How do I fix that? Thanks.
Hi My name is Dana and this is my first post here . I am a DB newbie , i started to teach my self MS Access with a small project as a start . I made a simple client data table where there are country and city fields + phone , fax , etc etc ... . I made 2 tables 1 for the countries and 1 for the cities . I know that it is a very familiar question :o How can we do the following when you select a country in the client table , only cities of this country show up in the city combo box . I found some answers here and there , but as a newbie , the codes where difficult to understand . I attached my source file and i will be thankful if someone could help and to give me a simple explanation of the codes concept . I just want to add the codes in the table not in a form , i found some answers by adding codes to a Form :confused: . Because as i know i can export only tables and queries to sql.
I've got a large Excel spreadsheet with contact information that I want to import into Access. Everything seems to import fine except for the two zipcode columns (a 5-digit zip and a 4-digit zip, both have leading zeroes in many entries). The problem is that in Excel the data are formatted with a special input mask that allows for the leading zeros to show. When I import them into Access, this formatting is not recognized and the zeros are lost. I've tried a few things, such as saving the Excel file as text and Dbase 4 and then trying to import them. In these cases the leading zeros are present during import, but are again dropped as soon as the import completes. Any help would be greatly appreciated.
Ok Im having trouble with this, I need to remove cities from an address field but don't know a simple query that does this. Can any body help
heres an example
Address1 City 10/F, HONG KONG LAI CHI KOK EXCHANGE IIHONG KONG
Now I want to be able to pull Hong kong out of the address1 field but without searching in the address field with *HONG KONG* as this would involve checking every city in a table.
I have a task of managing hardware assets like desktop, monitor, HDD, routers etc. Each item is marked with a unique ID.
These items could be transferred from one city to another city; so need to track that too.
1. There will be two tables
One (say AssetDetail) will have the following fields RecordID City AssetID Description of the asset (desktop/monitor etc) ProductSerialNumber of the asset
Second (say AssetTransfer) will have the additional fields NewCity AssetTransferredDate
2. I only want to open a single form :
a) where on the basis of either AssetID or the ProductSerialNumber, I could open a particular record and edit it.
i) if the asset is transferred from city A to city B; then the "City" field value in AssetDetail table should change to "city B"(New City field value).
ii) At the same time, each asset transfer record should get appended to the AssetTransfer table even if the table has an existing record with the same AssetID or ProductSerialNumber.
3. Also need an option to make a new entry to the AssetDetail table from the above mentioned single form.
Got a problem I can't figure out. I will try and explain in my newbie language.
I have a list of companies and a very long list of employees who work in those companies. On my form, would want the user to be able to select a company from a combo box. Afterwards, I would want another combo box be populated with only the employees from the selected company so that they could select the employee without scrolling down for an hour.... How can I make this happen?
1. how do i auto populate a form from another table. like a repair form pulling up information from the vendor table.
2. I have been trying to pull information into my form 'check stock' from a table called 'inventory'. i want to be able to just enter the serial number and then autopopulate the rest of the data from the inventory table.
the quantity that is displayed in the check stock table must be the difference of the quantity in the inventory table and the requisition table.
3. Also, every time i enter something in the inventory table, i want it to be able to iteratively check for any avaliable stock and update the quantity instead of making a new entry every time.
I have a table that I'm storing Horsepower and Watt in. Is there anyway to set it up so that when I provide Horsepower it automatically populates Watts? (just a simple multiplication)
Can # (Lookup/dropdown) Movement Date (date/time) Current Location (lookup/Dropdown) Current Address (lookup/Dropdown) Prior Location Prior Address
When I drop the can # down in the form, I want the Prior Location and Address to auto Populate with the last known current Location and address of that can.
Any ideas on the best way to do that?? I assume there is code and a query, but I am an absolute database illiterate so exact directions would be nice.
To explain again, whenever I put a new record in, as soon as I select the Can# from the drop down tab, I want the prior location and address to be populated with the last known current address for that Can#. And if I change the can # I want the prior location and address to change with it.
I have a Subform/Subreport that shows in a datasheet view. I hosts two (2) fields on it: "Job Performed" & "Details". The "Job Performed" field is a drop-down list. The "Details" field is a memo field.
What I am trying to do is, well, one of the selections in the drop-down list will ALWAYS have the same details - "Changed Oil & Filter". But all of the rest will vary constantly.
So my question is, can I make it so if "Monthly" is selected from the first (drop-down) field, it will ALWAYS populate the Details field with the words "Changed Oil & Filter" without affecting the other selections?
I am trying to create a 2-table database where we can enter our survey data. I want them to be linked by "head"# so that the "Temp" "Date" "island" and "site" are autofilled into the "time dep"table from the data I entered in the first "perm"table.
I would also like if the lookups were filtered so that, for example, when I choose "Cat Island" my only two options are "fishbowl" and "Cuda" then from there depending on which of those I choose, The "head"# can be chosen from a dropdown as well. There are about 22 coral-heads per site, and two sites per island. I don't want to have a giant dropdown of 80+"head"#s, but at the same time, if we are typing them in, it increases chance of error. limiting the options of head number to be site specific would be ideal.
I feel like I may be going about this all wrong, and need a form? But I cannot figure out how to make the form data fill the datasheets that I have created.
Ok, I know you guys have heard about this problem quiet a few times, my search told me that. But my problem has a bit of a twist. The message appeared when I tried to open the database yesterday, so I went to the backups and tried to open them. All the backups for the past month give the same message. The problem is with the FE only, the BE is working fine. The backups are connected to the same workgroup as the FE and BE. Has anyone seen this before and know what might of happened?
I am trying to determine the state that a job is located in. If the ProjectID begins with a 2 then it is in California. If it does not then the job is located in Nevada.
If the check box is null (grey) - the default value, I do not want to include it is the sql statement. If I use the code below I get the error 94 Invalid use of null. How else can I exclude this ststement is the check box is grey.
thanks
If CheckStaged Then strWHERE = strWHERE & " AND s.Staged = " & CheckStaged End If
1) how can i have a record automaticly fill in the next date from the privous record? (Example if record 1 has date:10/14/05 i want record 2 to fill in in that same feild 10/15/05?
2) how can i have a reccord automaticly fill in the same number from the privios record? (Example if record 1 has Number :34 i want record 2 to fill in in that same feild 35?
I keep on changing these numbers, so its vitel that it repeats the privous record
I know the way to SELECTDISTECT on a combobox referring to a table so the value will autopopulate in the combobox. But is that doable in a subform?
I have a table with 3 columns. First Name, Last Name, Relationship.
I want to let the user adding data directly to the subform, but when typing RICHARD in the first record of First Name, then on the second record, when they type R, it will suggest RICHARD, a kinda auto fill if desire.
