inputing data from form into table issue

  • Thread starter Thread starter indigo
  • Start date Start date
I

indigo

hi...

currently have a database with 2 tables.
table 1 is has name and address info in it only.
table 2 has job run information. this links to table 1 via drop down box as
there can be several different job runs for the same customer in table 1.

what i would like to do, and seemed to have hit a bit of a wall, is to
select the name from drop down box in the form, which inputs that info into
table 2 (no problem there) but when i select the name i'd like the address
information to fill in that colum automatically based on the address in
table 1. this is to save having to look through addresses each time.

i've tried using =combobox.Column(n) to do this but so far have failed to
get the information into the table.

i'd be grateful if you could explain to me how i do this.

thanks
 
hi...

currently have a database with 2 tables.
table 1 is has name and address info in it only.
table 2 has job run information. this links to table 1 via drop down box as
there can be several different job runs for the same customer in table 1.

what i would like to do, and seemed to have hit a bit of a wall, is to
select the name from drop down box in the form, which inputs that info into
table 2 (no problem there) but when i select the name i'd like the address
information to fill in that colum automatically based on the address in
table 1. this is to save having to look through addresses each time.

i've tried using =combobox.Column(n) to do this but so far have failed to
get the information into the table.
Click to expand...

You're misunderstanding how relational databases work. The second
table should NOT contain the address field. It's redundant!

You can use a Query joining the two tables in order to see the address
data in conjunction with the job information; or, you can use a Form
based on Table1 with a Subform based on Table2.

John W. Vinson[MVP]
 
ah,.. your correct i wasn't fully aware of that.
i understand (a bit) what you mean now..
in essence i was thinking of using table 1 as a store of information,
partially quite seperate from the main table. rather than as it should be, a
working component of the db overall.. (am i close here)

So table 2 will include the customer details but wont include the address as
this is already linked via table 1 (duh! where's homer simpson when i need
him)
and in the subform will i use the =combobox.Column(n) to bring up the
address info?
Also with regard to putting the customer info into table 2 will this pick up
from the subform?

just about to sit and try this out..
i really appreciate your time answering these tiresome questions.

thanks
 
John's advice is invariably concise and accurate, so I hope I am not muddying
the waters here. Table 1 is the unchanging information about the customer
(name, address, etc.) and Table 2 is job details? I would think that if the
primary key field in Table 1 is Autonumber data type, Table 2 should contain
a field of the same name (helpful but not essential that the fields have the
same name) and data type Number. Use table design view to accomplish this.
Set up a relationship (see Help if needed) between the two tables by linking
the fields I mentioned. Use autoform to make a form based on Table 1, and
Table 2 should appear as a subform.
In general, don't store data twice. An exception might be that you need an
employee's name to appear on a record as it was at the time the record was
created, in which case you would store the name rather than the NameID. If
you need main form information (name and address, for instance) to appear
elsewhere on the form you could just add another text box bound to the
appropriate field. To add it to the subform, you could use an unbound text
box containing =Forms![frmCustomer]![txtCustName].
By establishing relationships you can view related information whenever you
want. You don't need to store it. If you store the customer primary key
(you can still view the customer name) your records will remain cohesive even
if the customer name changes.
By the way, maybe the names Table 1 and Table 2 are just for convenience in
posting your question, but if you are using the default Access names for
tables, forms, etc. and for controls such as combo boxes, do yourself a favor
and implement an orderly and descriptive naming convention.
 
thanks a lot for this..
set up a test and followed your instructions. obviously i'm missing out
something because the subform is never created.

it certainly hasn't muddied the waters the more detail the better. i've not
worked with db's since college (mainly do networking) and offered to help
somone by doing this.. sometimes i wonder why i'm such an idiot...

i'm going to do more mucking about to see if i can figure out where i'm
going wrong.
oh and i'm using the naming convention tbl_tablemame, frm_formname etc.

thanks


BruceM said:
John's advice is invariably concise and accurate, so I hope I am not
muddying
the waters here. Table 1 is the unchanging information about the customer
(name, address, etc.) and Table 2 is job details? I would think that if
the
primary key field in Table 1 is Autonumber data type, Table 2 should
contain
a field of the same name (helpful but not essential that the fields have
the
same name) and data type Number. Use table design view to accomplish
this.
Set up a relationship (see Help if needed) between the two tables by
linking
the fields I mentioned. Use autoform to make a form based on Table 1, and
Table 2 should appear as a subform.
In general, don't store data twice. An exception might be that you need
an
employee's name to appear on a record as it was at the time the record was
created, in which case you would store the name rather than the NameID.
If
you need main form information (name and address, for instance) to appear
elsewhere on the form you could just add another text box bound to the
appropriate field. To add it to the subform, you could use an unbound
text
box containing =Forms![frmCustomer]![txtCustName].
By establishing relationships you can view related information whenever
you
want. You don't need to store it. If you store the customer primary key
(you can still view the customer name) your records will remain cohesive
even
if the customer name changes.
By the way, maybe the names Table 1 and Table 2 are just for convenience
in
posting your question, but if you are using the default Access names for
tables, forms, etc. and for controls such as combo boxes, do yourself a
favor
and implement an orderly and descriptive naming convention.

indigo said:
ah,.. your correct i wasn't fully aware of that.
i understand (a bit) what you mean now..
in essence i was thinking of using table 1 as a store of information,
partially quite seperate from the main table. rather than as it should
be, a
working component of the db overall.. (am i close here)

So table 2 will include the customer details but wont include the address
as
this is already linked via table 1 (duh! where's homer simpson when i
need
him)
and in the subform will i use the =combobox.Column(n) to bring up the
address info?
Also with regard to putting the customer info into table 2 will this pick
up
from the subform?

just about to sit and try this out..
i really appreciate your time answering these tiresome questions.

thanks
Click to expand...
Click to expand...
 
The Autoform does not always work predictably, in my experience. If the
table relationships are set up as described, make a form based on each table
(or on a query based on each table if you want to sort the data or something
like that). In what follows Form 1 is based on Table 1, and Form 2 on Table
2. Close Form 2 (which will be the subform), and be sure Form 1 is open in
design mode. Drag the icon for Form 2 from the Database window to an empty
space on Form 1. Does that do the trick? If you want to see multiple job
details at the same time, set the Default View of Form 2 to Continuous (or
Datasheet if you prefer something more austere).

indigo said:
thanks a lot for this..
set up a test and followed your instructions. obviously i'm missing out
something because the subform is never created.

it certainly hasn't muddied the waters the more detail the better. i've not
worked with db's since college (mainly do networking) and offered to help
somone by doing this.. sometimes i wonder why i'm such an idiot...

i'm going to do more mucking about to see if i can figure out where i'm
going wrong.
oh and i'm using the naming convention tbl_tablemame, frm_formname etc.

thanks


BruceM said:
John's advice is invariably concise and accurate, so I hope I am not
muddying
the waters here. Table 1 is the unchanging information about the customer
(name, address, etc.) and Table 2 is job details? I would think that if
the
primary key field in Table 1 is Autonumber data type, Table 2 should
contain
a field of the same name (helpful but not essential that the fields have
the
same name) and data type Number. Use table design view to accomplish
this.
Set up a relationship (see Help if needed) between the two tables by
linking
the fields I mentioned. Use autoform to make a form based on Table 1, and
Table 2 should appear as a subform.
In general, don't store data twice. An exception might be that you need
an
employee's name to appear on a record as it was at the time the record was
created, in which case you would store the name rather than the NameID.
If
you need main form information (name and address, for instance) to appear
elsewhere on the form you could just add another text box bound to the
appropriate field. To add it to the subform, you could use an unbound
text
box containing =Forms![frmCustomer]![txtCustName].
By establishing relationships you can view related information whenever
you
want. You don't need to store it. If you store the customer primary key
(you can still view the customer name) your records will remain cohesive
even
if the customer name changes.
By the way, maybe the names Table 1 and Table 2 are just for convenience
in
posting your question, but if you are using the default Access names for
tables, forms, etc. and for controls such as combo boxes, do yourself a
favor
and implement an orderly and descriptive naming convention.

indigo said:
ah,.. your correct i wasn't fully aware of that.
i understand (a bit) what you mean now..
in essence i was thinking of using table 1 as a store of information,
partially quite seperate from the main table. rather than as it should
be, a
working component of the db overall.. (am i close here)

So table 2 will include the customer details but wont include the address
as
this is already linked via table 1 (duh! where's homer simpson when i
need
him)
and in the subform will i use the =combobox.Column(n) to bring up the
address info?
Also with regard to putting the customer info into table 2 will this pick
up
from the subform?

just about to sit and try this out..
i really appreciate your time answering these tiresome questions.

thanks


On Wed, 5 Jan 2005 01:33:14 -0000, "indigo"

hi...

currently have a database with 2 tables.
table 1 is has name and address info in it only.
table 2 has job run information. this links to table 1 via drop down
box
as
there can be several different job runs for the same customer in table
1.

what i would like to do, and seemed to have hit a bit of a wall, is to
select the name from drop down box in the form, which inputs that info
into
table 2 (no problem there) but when i select the name i'd like the
address
information to fill in that colum automatically based on the address in
table 1. this is to save having to look through addresses each time.

i've tried using =combobox.Column(n) to do this but so far have failed
to
get the information into the table.

You're misunderstanding how relational databases work. The second
table should NOT contain the address field. It's redundant!

You can use a Query joining the two tables in order to see the address
data in conjunction with the job information; or, you can use a Form
based on Table1 with a Subform based on Table2.

John W. Vinson[MVP]
Click to expand...
Click to expand...
Click to expand...
 
thanks for this.
eventually got it working..

BruceM said:
The Autoform does not always work predictably, in my experience. If the
table relationships are set up as described, make a form based on each
table
(or on a query based on each table if you want to sort the data or
something
like that). In what follows Form 1 is based on Table 1, and Form 2 on
Table
2. Close Form 2 (which will be the subform), and be sure Form 1 is open
in
design mode. Drag the icon for Form 2 from the Database window to an
empty
space on Form 1. Does that do the trick? If you want to see multiple job
details at the same time, set the Default View of Form 2 to Continuous (or
Datasheet if you prefer something more austere).

indigo said:
thanks a lot for this..
set up a test and followed your instructions. obviously i'm missing out
something because the subform is never created.

it certainly hasn't muddied the waters the more detail the better. i've
not
worked with db's since college (mainly do networking) and offered to help
somone by doing this.. sometimes i wonder why i'm such an idiot...

i'm going to do more mucking about to see if i can figure out where i'm
going wrong.
oh and i'm using the naming convention tbl_tablemame, frm_formname etc.

thanks


BruceM said:
John's advice is invariably concise and accurate, so I hope I am not
muddying
the waters here. Table 1 is the unchanging information about the
customer
(name, address, etc.) and Table 2 is job details? I would think that
if
the
primary key field in Table 1 is Autonumber data type, Table 2 should
contain
a field of the same name (helpful but not essential that the fields
have
the
same name) and data type Number. Use table design view to accomplish
this.
Set up a relationship (see Help if needed) between the two tables by
linking
the fields I mentioned. Use autoform to make a form based on Table 1,
and
Table 2 should appear as a subform.
In general, don't store data twice. An exception might be that you
need
an
employee's name to appear on a record as it was at the time the record
was
created, in which case you would store the name rather than the NameID.
If
you need main form information (name and address, for instance) to
appear
elsewhere on the form you could just add another text box bound to the
appropriate field. To add it to the subform, you could use an unbound
text
box containing =Forms![frmCustomer]![txtCustName].
By establishing relationships you can view related information whenever
you
want. You don't need to store it. If you store the customer primary
key
(you can still view the customer name) your records will remain
cohesive
even
if the customer name changes.
By the way, maybe the names Table 1 and Table 2 are just for
convenience
in
posting your question, but if you are using the default Access names
for
tables, forms, etc. and for controls such as combo boxes, do yourself a
favor
and implement an orderly and descriptive naming convention.

:

ah,.. your correct i wasn't fully aware of that.
i understand (a bit) what you mean now..
in essence i was thinking of using table 1 as a store of information,
partially quite seperate from the main table. rather than as it should
be, a
working component of the db overall.. (am i close here)

So table 2 will include the customer details but wont include the
address
as
this is already linked via table 1 (duh! where's homer simpson when i
need
him)
and in the subform will i use the =combobox.Column(n) to bring up the
address info?
Also with regard to putting the customer info into table 2 will this
pick
up
from the subform?

just about to sit and try this out..
i really appreciate your time answering these tiresome questions.

thanks


On Wed, 5 Jan 2005 01:33:14 -0000, "indigo"

hi...

currently have a database with 2 tables.
table 1 is has name and address info in it only.
table 2 has job run information. this links to table 1 via drop down
box
as
there can be several different job runs for the same customer in
table
1.

what i would like to do, and seemed to have hit a bit of a wall, is
to
select the name from drop down box in the form, which inputs that
info
into
table 2 (no problem there) but when i select the name i'd like the
address
information to fill in that colum automatically based on the address
in
table 1. this is to save having to look through addresses each time.

i've tried using =combobox.Column(n) to do this but so far have
failed
to
get the information into the table.

You're misunderstanding how relational databases work. The second
table should NOT contain the address field. It's redundant!

You can use a Query joining the two tables in order to see the
address
data in conjunction with the job information; or, you can use a Form
based on Table1 with a Subform based on Table2.

John W. Vinson[MVP]
Click to expand...
Click to expand...
Click to expand...
 
Back
Top