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 |
#1
|
|||
|
|||
Auto fill part of a form based on a look-up table???
I have a form that will be utilized by several individuals. I want to
make it easier to fill out by having a name/address/tel#/etc. look-up table to fill in most of the basic info based on a reference number. The idea is that 'if' they insert their reference number, most of the repetitive info would automatically be filled in and they could then make minor changes plus add the unique data. It seems like the filling in of the data would have to be triggered by the entry of the reference number. Any suggestions?? Bob |
#2
|
|||
|
|||
Auto fill part of a form based on a look-up table???
Bob
It all starts with the data ... If you are trying to prefill the form with data that's already been recorded in a table, so you can then re-record the same data (with the changes you mention), your underlying table structure probably would benefit from more work on normalization. If you'll post a description of your underlying data structure, folks here will be able to offer more specific suggestions. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "BobC" wrote in message ... I have a form that will be utilized by several individuals. I want to make it easier to fill out by having a name/address/tel#/etc. look-up table to fill in most of the basic info based on a reference number. The idea is that 'if' they insert their reference number, most of the repetitive info would automatically be filled in and they could then make minor changes plus add the unique data. It seems like the filling in of the data would have to be triggered by the entry of the reference number. Any suggestions?? Bob |
#3
|
|||
|
|||
Auto fill part of a form based on a look-up table???
On Fri, 08 Jan 2010 10:44:25 -0500, BobC wrote:
I have a form that will be utilized by several individuals. I want to make it easier to fill out by having a name/address/tel#/etc. look-up table to fill in most of the basic info based on a reference number. The idea is that 'if' they insert their reference number, most of the repetitive info would automatically be filled in and they could then make minor changes plus add the unique data. It seems like the filling in of the data would have to be triggered by the entry of the reference number. Any suggestions?? Bob Well, I'd suggest that it's a Bad Idea, most likely! You'll end up with the same reference number having its address, phone, name stored redundantly in many different records. Any one of them could be CHANGED in any one of these records. If you find out that the person has changed their phone number, you now have to track down all the instances of the phone number and change them... and that may be difficult because he's "Robert Wilson" in the master table, and "Bob Wilson" in a couple of them. Relational databases use the "Grandmother's Pantry Principle": "A place - ONE place! - for everything, everything in its place". You're usually much better off just storing the reference number, and *displaying* the constant information using a Query linking the tables, or by including the fields in the combo box's row source and using a textbox with a control source like =cboReference.Column(n) to display data from the combo (n is the zero based position of the field, i.e. the third column of the combo would be (2)). In addition, unless the reference numbers are well known and routine out in the "real world", you can use the combo box to display a human-meaningful value (the full name and address, say) while storing the (computer friendly) reference number. -- John W. Vinson [MVP] |
#4
|
|||
|
|||
Auto fill part of a form based on a look-up table???
Jeff Boyce wrote:
Bob It all starts with the data ... If you are trying to prefill the form with data that's already been recorded in a table, so you can then re-record the same data (with the changes you mention), your underlying table structure probably would benefit from more work on normalization. If you'll post a description of your underlying data structure, folks here will be able to offer more specific suggestions. Regards Jeff Boyce Microsoft Access MVP There are 5; soon to be 6, linked tables as the main database that define a top down data configuration structure with a few hundred sites each containing 6 groups of equipments, containing 20-50 pieces of similar equipment with. The use of the database is a new process; much of which is still manual and unfortunately involves a mixture of various programs and processes. For the moment at least, it is necessary for individuals to process order forms that contain 10 to 30% of repetitious data involving addresses, telephone numbers, etc. These addresses, phone numbers, etc. do in fact change somewhat with time, but the records should not reflect these changes, but should reflect what the data was at the time the order was placed. This does in fact place some degree of repetition in the database. That being said; the table that I want to use to draw somewhat repetitious data from is being maintained by a 3rd party. I just want the process of filling out the form (about 40 or so text boxes) to be as easy as possible for the individual. I expect that there will be 10-15 text boxes that could draw there information for a table (the one being maintained by a 3rd party). The table is in Access 2007. The idea of using a combo box to select the specific record in the table seems like a good idea. I want the data dump to the order form from the table to be a one shot deal such that it just initializes the text boxes, but is further editable before it is finalized and saved. I hope this helps, because I do not know the best way to do this. I believe I can customize VB code to fit the situation, but I could sure use some help with a basic code structure if that is the best approach? Thanks, Bob Portions of the current process require Users are required to input equipment requirement reports that include some necessary repetition due to a changing environment. This means that in many cases users will be repeating some data and customizing the rest of the data. |
#5
|
|||
|
|||
Auto fill part of a form based on a look-up table???
John W. Vinson wrote:
On Fri, 08 Jan 2010 10:44:25 -0500, wrote: I have a form that will be utilized by several individuals. I want to make it easier to fill out by having a name/address/tel#/etc. look-up table to fill in most of the basic info based on a reference number. The idea is that 'if' they insert their reference number, most of the repetitive info would automatically be filled in and they could then make minor changes plus add the unique data. It seems like the filling in of the data would have to be triggered by the entry of the reference number. Any suggestions?? Bob Well, I'd suggest that it's a Bad Idea, most likely! You'll end up with the same reference number having its address, phone, name stored redundantly in many different records. Any one of them could be CHANGED in any one of these records. If you find out that the person has changed their phone number, you now have to track down all the instances of the phone number and change them... and that may be difficult because he's "Robert Wilson" in the master table, and "Bob Wilson" in a couple of them. Relational databases use the "Grandmother's Pantry Principle": "A place - ONE place! - for everything, everything in its place". You're usually much better off just storing the reference number, and *displaying* the constant information using a Query linking the tables, or by including the fields in the combo box's row source and using a textbox with a control source like =cboReference.Column(n) to display data from the combo (n is the zero based position of the field, i.e. the third column of the combo would be (2)). In addition, unless the reference numbers are well known and routine out in the "real world", you can use the combo box to display a human-meaningful value (the full name and address, say) while storing the (computer friendly) reference number. Please see my response to J. Boyce... The overall implementation is probably not the greatest. My effort is directed toward helping the guys in the field do their data entry. Any suggestions/help you can provide would be greatly appreciated! As I mentioned ... I believe I can customize VB code, if that is what it takes, but I could sure use some help with a generic code structure if that is the best approach? Thanks much! Bob |
#6
|
|||
|
|||
Auto fill part of a form based on a look-up table???
On Fri, 08 Jan 2010 17:46:46 -0500, BobC wrote:
These addresses, phone numbers, etc. do in fact change somewhat with time, but the records should not reflect these changes, but should reflect what the data was at the time the order was placed. Ok... if that is in fact the case you have a reason to store the formally "redundant" data (it's not redundant if the meaning of the field is "this person's address as of January 8, 2010" rather than "this person's address"). You can "push" data from a combo box into other controls on the form in the combo's AfterUpdate event. Base the combo on a query containing all of the fields that you want to record (they can be of zero width in the combo's ColumnWidths property if you don't want them cluttering the dropdown). In the AfterUpdate event you could use code like Private Sub cboPerson_AfterUpdate() If Not IsNull(Me!cboPerson) Then ' if the user selected someone... Me!txtLastName = Me!cboPerson.Column(1) Me!txtFirstName = Me!cboPerson.Column(2) ... Me!txtPhone = Me!cboPerson.Column(9) End If End Sub to copy columns 2 through 10 into textboxes. -- John W. Vinson [MVP] |
#7
|
|||
|
|||
Auto fill part of a form based on a look-up table???
John W. Vinson wrote:
On Fri, 08 Jan 2010 17:46:46 -0500, wrote: These addresses, phone numbers, etc. do in fact change somewhat with time, but the records should not reflect these changes, but should reflect what the data was at the time the order was placed. Ok... if that is in fact the case you have a reason to store the formally "redundant" data (it's not redundant if the meaning of the field is "this person's address as of January 8, 2010" rather than "this person's address"). You can "push" data from a combo box into other controls on the form in the combo's AfterUpdate event. Base the combo on a query containing all of the fields that you want to record (they can be of zero width in the combo's ColumnWidths property if you don't want them cluttering the dropdown). In the AfterUpdate event you could use code like Private Sub cboPerson_AfterUpdate() If Not IsNull(Me!cboPerson) Then ' if the user selected someone... Me!txtLastName = Me!cboPerson.Column(1) Me!txtFirstName = Me!cboPerson.Column(2) ... Me!txtPhone = Me!cboPerson.Column(9) End If End Sub to copy columns 2 through 10 into textboxes. THANK YOU VERY MUCH!!!!! |
#8
|
|||
|
|||
Auto fill part of a form based on a look-up table???
John W. Vinson wrote:
On Fri, 08 Jan 2010 17:46:46 -0500, wrote: These addresses, phone numbers, etc. do in fact change somewhat with time, but the records should not reflect these changes, but should reflect what the data was at the time the order was placed. Ok... if that is in fact the case you have a reason to store the formally "redundant" data (it's not redundant if the meaning of the field is "this person's address as of January 8, 2010" rather than "this person's address"). You can "push" data from a combo box into other controls on the form in the combo's AfterUpdate event. Base the combo on a query containing all of the fields that you want to record (they can be of zero width in the combo's ColumnWidths property if you don't want them cluttering the dropdown). In the AfterUpdate event you could use code like Private Sub cboPerson_AfterUpdate() If Not IsNull(Me!cboPerson) Then ' if the user selected someone... Me!txtLastName = Me!cboPerson.Column(1) Me!txtFirstName = Me!cboPerson.Column(2) ... Me!txtPhone = Me!cboPerson.Column(9) End If End Sub to copy columns 2 through 10 into textboxes. ****************** It was not only a great idea! .... IT WORKS GREAT TOO! THANK YOU VERY MUCH!!! Bob |
#9
|
|||
|
|||
Similar Issue? - Fill table based on entry in form
I believe I have a similar issue to this and would really appreciate if you could help me. I am by no means an Access expert but sadly I know more than anyone else in the office so I'm stuck trying to reconfigure our database.
I have a main table with updated information on our clients and several other tables with specific information about their tax returns by year. People access the database through a form. They have a "search" screen where they can enter the individual's ID (related to main table) then the individual's information is displayed in a form. What I would like to happen is to link the per-year tables to the form so that someone can enter a piece of information in the form and the corresponding per-year table will either update the corresponding information because the client's info is already there - or will automatically enter the id/general info for the client they are already working on along with whatever other information they are entering. To clarify, the per-year tables will only have a client's info if some item from this form has been filled out. Could you please help me with this? I would really appreciate it! John W. Vinson wrote: Ok... 08-Jan-10 Ok... if that is in fact the case you have a reason to store the formally "redundant" data (it is not redundant if the meaning of the field is "this person's address as of January 8, 2010" rather than "this person's address"). You can "push" data from a combo box into other controls on the form in the combo's AfterUpdate event. Base the combo on a query containing all of the fields that you want to record (they can be of zero width in the combo's ColumnWidths property if you do not want them cluttering the dropdown). In the AfterUpdate event you could use code like Private Sub cboPerson_AfterUpdate() If Not IsNull(Me!cboPerson) Then ' if the user selected someone... Me!txtLastName = Me!cboPerson.Column(1) Me!txtFirstName = Me!cboPerson.Column(2) .... Me!txtPhone = Me!cboPerson.Column(9) End If End Sub to copy columns 2 through 10 into textboxes. -- John W. Vinson [MVP] Previous Posts In This Thread: Submitted via EggHeadCafe - Software Developer Portal of Choice CryptoLicensing for .NET Product Review http://www.eggheadcafe.com/tutorials...g-for-net.aspx |
#10
|
|||
|
|||
Similar Issue? - Fill table based on entry in form
Nina
In the future, you'll probably get a lot more responses if you start a new thread, rather than burying your question this far down ... If you haven't done so already, take a look at Access' main form/subform construction. You can use this to put the client info on the main form, and use the subform to display the (one-to-many) related "tax year" data records. Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. Nina B. wrote in message ... I believe I have a similar issue to this and would really appreciate if you could help me. I am by no means an Access expert but sadly I know more than anyone else in the office so I'm stuck trying to reconfigure our database. I have a main table with updated information on our clients and several other tables with specific information about their tax returns by year. People access the database through a form. They have a "search" screen where they can enter the individual's ID (related to main table) then the individual's information is displayed in a form. What I would like to happen is to link the per-year tables to the form so that someone can enter a piece of information in the form and the corresponding per-year table will either update the corresponding information because the client's info is already there - or will automatically enter the id/general info for the client they are already working on along with whatever other information they are entering. To clarify, the per-year tables will only have a client's info if some item from this form has been filled out. Could you please help me with this? I would really appreciate it! John W. Vinson wrote: Ok... 08-Jan-10 Ok... if that is in fact the case you have a reason to store the formally "redundant" data (it is not redundant if the meaning of the field is "this person's address as of January 8, 2010" rather than "this person's address"). You can "push" data from a combo box into other controls on the form in the combo's AfterUpdate event. Base the combo on a query containing all of the fields that you want to record (they can be of zero width in the combo's ColumnWidths property if you do not want them cluttering the dropdown). In the AfterUpdate event you could use code like Private Sub cboPerson_AfterUpdate() If Not IsNull(Me!cboPerson) Then ' if the user selected someone... Me!txtLastName = Me!cboPerson.Column(1) Me!txtFirstName = Me!cboPerson.Column(2) ... Me!txtPhone = Me!cboPerson.Column(9) End If End Sub to copy columns 2 through 10 into textboxes. -- John W. Vinson [MVP] Previous Posts In This Thread: Submitted via EggHeadCafe - Software Developer Portal of Choice CryptoLicensing for .NET Product Review http://www.eggheadcafe.com/tutorials...g-for-net.aspx |
Thread Tools | |
Display Modes | |
|
|