If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
A rookie's first effort - I'm so proud :o)
Good question, I don't really know. As most people, particularly
residentail, will not likely know the +4 for their code, I don't see that working. I like your idea. It would be a bit advanced for a newbie, but conceptually, you could test to see how many city names have the zip code, then offer the user the choices. -- Dave Hargis, Microsoft Access MVP "Hunter57" wrote: Hi, Wow, thanks for the info, I never heard that before. Are the last four digits in the extended zipcode the same or are they different? Hunter57 "Klatuu" wrote: That will not always work. There are situations (I live in one) where one zip code has more than one town. I don't know about where you are, but here, the Post Offce gets snarly if you use the incorrect town name even when the zip code is correct. -- Dave Hargis, Microsoft Access MVP "Hunter57" wrote: Hi James, I am not an expert, but I think you did a great job for a newbie! Here is a tip you might want to use as your database grows: If you forsee that you will have many records, there are some things you can do to help your database run faster by not storing the same data many times. For example, think of how many times the same state, zipcode, or country will be stored in your DB. To avoid needless repetitive data storage you can create a PostalCode table that holds the ZipCodes, Cities, States (if you plan to do mailings you might want to use the two letter state abbreviation), and countries. I use the Postal Code as the Primary Key Field since the Postal Code numbers are not likely to change. (For almost everything else I use an ID Autonumber field as the Primary Key) In your Main table keep the Postal_Code field. Create a relationship between the two tables based on the Postal Code. You can use a query as your form Record Source or you can use the following method to add the information to your Form: Change your Postal Code text box to a combo box. Then you can use the combo box on your form to add the Postal Code and the Citiy, State, and Country information automatically. You will have to create a query for your Postal Code combo box that includes the City, State, and Country information. Use that query as the Row Source for the combo box. Open your properties sheet for your combo box. Click the "Format" tab. Set the following properties: Column Count: 4 --Be absolutly sure that you have the total number of columns here. Column Heads: Either Yes or No Column Widths: 0.7";1.5";1";1.5" --Change these to however it suits you List Rows: 25 List Width: 5" --Be sure to set this wide enough if you want to see the data when you click on the combo box. After this, you will need to make your City, State and Country text boxes Unbound. Then add to your Record Source for the City, for example, the following expression: =cboPostal_Code.Column(1) The Column is the Column in your Query. Access assigns the first column in a Query the number 0, the second column number 1, and so on. You will use the column number for "City", whatever that may be. Do the same with the State and Country text boxes. You will need to add the following code to the Forms On Current Event so the Combo Box and text boxes will display the information. Your code should look like this: Private Sub Form_Current() Me.cboPostal_Code.Requery End Sub Using this method you will store the City, State, and Country only once. This can save a lot of memory and help your database to run faster. Once you learn how to do this, you can use this method for your Membership Status, Type, etc. Of course, you can use a Query as the Form's record source which includes the Postal Code Table Fields. Most of the time that will work very well. However, sometimes when you use a query for the Form Record Source the query will not allow you to add data to your tables. This can happen when your Query uses data from more than one table. If you prefer to use queries, and have that problem, you may be able to use the method I described to solve it. I hope that is not too much info at one time. You can alway try it later if you prefer. Hunter57 http://accesstips.wordpress.com/ http://www.churchmanagesoftware.com/ "James Ivey" wrote: Its really no big shakes... its just a form But I'm proud of it )) "Kirstie Adam" (nospam) wrote in message ... Hi James, i am no way an expert, still a beginner myself, but i still was interested to have a look! (i am overly excited by other people's databases. my friends worry!) For some reason the link doesn't work for me though, can i get to it by clicking any links on the website - i can get to that? Kirstie "James Ivey" wrote in message ... http://www.iampeth.com/stuff/IAMPETH.mdb Total Access Newbie here. I would be very appreciative if a few experts would take a look at my first efforts. Its a membership database for an organization with approximately 450 members. Its only got 5 records in it... filesize is 316 Kb I'm wide open to critique and suggestions. Thanks in advance! James Ivey |
#22
|
|||
|
|||
A rookie's first effort - I'm so proud :o)
Of course you are right about that. Oh, well. For once I thought I would not
have to use the AutoNumbers. James, here is how to take care of that problem. We will just have to add an PostalCodeID field as the Primary key with the data type as Autonumber to the PostalCode Table. When you create the query for the combo box make sure that the ID field is the first column. In your combo box's Properties sheet, click the Format tab and be sure that the Column Count number includes all of the query columns. Here is a neat trick that works well: for the first number in the Column Widths: set it to 0" like this: 0";0.7";1.5";1";1.5" This way your Postal_Code combo box will display the zipcode instead of the autonumber (which will be hidden). If you have a zipcode that includes more than one city, you can add the same zipcode with the different city to your table. That way you can choose the correct city when you are selecting the zip code. Thanks Klatuu, Hunter57 "Klatuu" wrote: Good question, I don't really know. As most people, particularly residentail, will not likely know the +4 for their code, I don't see that working. I like your idea. It would be a bit advanced for a newbie, but conceptually, you could test to see how many city names have the zip code, then offer the user the choices. -- Dave Hargis, Microsoft Access MVP "Hunter57" wrote: Hi, Wow, thanks for the info, I never heard that before. Are the last four digits in the extended zipcode the same or are they different? Hunter57 "Klatuu" wrote: That will not always work. There are situations (I live in one) where one zip code has more than one town. I don't know about where you are, but here, the Post Offce gets snarly if you use the incorrect town name even when the zip code is correct. -- Dave Hargis, Microsoft Access MVP "Hunter57" wrote: Hi James, I am not an expert, but I think you did a great job for a newbie! Here is a tip you might want to use as your database grows: If you forsee that you will have many records, there are some things you can do to help your database run faster by not storing the same data many times. For example, think of how many times the same state, zipcode, or country will be stored in your DB. To avoid needless repetitive data storage you can create a PostalCode table that holds the ZipCodes, Cities, States (if you plan to do mailings you might want to use the two letter state abbreviation), and countries. I use the Postal Code as the Primary Key Field since the Postal Code numbers are not likely to change. (For almost everything else I use an ID Autonumber field as the Primary Key) In your Main table keep the Postal_Code field. Create a relationship between the two tables based on the Postal Code. You can use a query as your form Record Source or you can use the following method to add the information to your Form: Change your Postal Code text box to a combo box. Then you can use the combo box on your form to add the Postal Code and the Citiy, State, and Country information automatically. You will have to create a query for your Postal Code combo box that includes the City, State, and Country information. Use that query as the Row Source for the combo box. Open your properties sheet for your combo box. Click the "Format" tab. Set the following properties: Column Count: 4 --Be absolutly sure that you have the total number of columns here. Column Heads: Either Yes or No Column Widths: 0.7";1.5";1";1.5" --Change these to however it suits you List Rows: 25 List Width: 5" --Be sure to set this wide enough if you want to see the data when you click on the combo box. After this, you will need to make your City, State and Country text boxes Unbound. Then add to your Record Source for the City, for example, the following expression: =cboPostal_Code.Column(1) The Column is the Column in your Query. Access assigns the first column in a Query the number 0, the second column number 1, and so on. You will use the column number for "City", whatever that may be. Do the same with the State and Country text boxes. You will need to add the following code to the Forms On Current Event so the Combo Box and text boxes will display the information. Your code should look like this: Private Sub Form_Current() Me.cboPostal_Code.Requery End Sub Using this method you will store the City, State, and Country only once. This can save a lot of memory and help your database to run faster. Once you learn how to do this, you can use this method for your Membership Status, Type, etc. Of course, you can use a Query as the Form's record source which includes the Postal Code Table Fields. Most of the time that will work very well. However, sometimes when you use a query for the Form Record Source the query will not allow you to add data to your tables. This can happen when your Query uses data from more than one table. If you prefer to use queries, and have that problem, you may be able to use the method I described to solve it. I hope that is not too much info at one time. You can alway try it later if you prefer. Hunter57 http://accesstips.wordpress.com/ http://www.churchmanagesoftware.com/ "James Ivey" wrote: Its really no big shakes... its just a form But I'm proud of it )) "Kirstie Adam" (nospam) wrote in message ... Hi James, i am no way an expert, still a beginner myself, but i still was interested to have a look! (i am overly excited by other people's databases. my friends worry!) For some reason the link doesn't work for me though, can i get to it by clicking any links on the website - i can get to that? Kirstie "James Ivey" wrote in message ... http://www.iampeth.com/stuff/IAMPETH.mdb Total Access Newbie here. I would be very appreciative if a few experts would take a look at my first efforts. Its a membership database for an organization with approximately 450 members. Its only got 5 records in it... filesize is 316 Kb I'm wide open to critique and suggestions. Thanks in advance! James Ivey |
Thread Tools | |
Display Modes | |
|
|