Auto fill part of a form based on a look-up table???

  • Thread starter Thread starter BobC
  • Start date Start date
B

BobC

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
 
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.
 
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
Click to expand...

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.
 
Jeff said:
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
Click to expand...
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.
 
John said:
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.
Click to expand...

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
 
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.
Click to expand...

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 said:
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.
Click to expand...

THANK YOU VERY MUCH!!!!!
 
John said:
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.
Click to expand...

******************

It was not only a great idea! .... IT WORKS GREAT TOO!
THANK YOU VERY MUCH!!!

Bob
 
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 formall
"redundant" data (it is not redundant if the meaning of the field is "thi
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 th
combo's AfterUpdate event. Base the combo on a query containing all of th
fields that you want to record (they can be of zero width in the combo'
ColumnWidths property if you do not want them cluttering the dropdown)

In the AfterUpdate event you could use code lik

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 I
End Su

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/tutorial...48e-06ba96aa9966/cryptolicensing-for-net.aspx
 
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.
 
Back
Top