linking fields from form to subform

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Somebody please help!!! I've been working on this for 3 months now, on my spare time at work, racking my brain, doing, and redoing to trying to make this work. And probably when someone tells me what to do, I'll feel REALLY ignorant. PLEASE! I don't care, make me feel REALLY ignorant!!!! I gotta get this to work

Specifically, this is what I'm trying to do. You want to create a new workorder. I press a command button. It takes me to the "New WorkOrder" Form which feeds from a query that asks me what store I want to create the work order on. So I type in for example "M630". When this happens it feeds the store information (address1 and 2, city, state, zip, store #, store name) to the form AND 3 of those fields to the subform. The FORM is linked to the "jobsite table". The SUBFORM is linked to the "main table" (this table keeps all the pertinent information about what happens at the store. Thus at the end of the day when I get ready to invoice the customer, I can print the complete store information from the main table without actually having to type in the store information. I want it to feed from the job site table to the main table. This happens in this form and subform that I talked about earlier. However, I can only get it to feed 3 fields (because that's all it allows when you create the subform through the wizard).

I tried creating one field and linking all fields (ie: [address] & [address1] & [city] & [state] & [zip]) pulling the information together as someone had suggested. However, when presented to the boss, he says that he wants the information to go into separate fields for query and report purposes, which is understandable.

I've tried to use several subforms to feed the information to the main table but to no avail!!!!! When I click add record, it keeps only the 3 original fields that were set at the beginning. I know this has to be able to work. I just don't know how or what to do to make this work. The new customer that is requesting the COMPLETE address on the invoices is about ready to sign a contract with us. I do the invoicing and do NOT want to make more work for myself than I have to :-). But also this would help me with the quotation stuff that I'm working on to keep the other girls from having to do double work. So any information would be MOST helpful

Thanks

Mar
 
Mary,

Don't feed ANY address, etc. fields from your Jobsite
table to your main table. You do not need them nor want
them. You just want access to them any time you want--for
a report, printing an invoice, etc.

All you need to store in the "main" or as I see it, your
WorkOrder "detail" table, is the key field from the
Jobsite table that uniquely identifies the store. This
field in your detail table is a "foreign key"; which I
think of just like a housekey--it opens up a foreign table
to allow access to all of its fields IF the foreign key
matches a value in the Jobsite table.

Also, don't confuse what you *display* on a form, and what
you *store* in an underlying table. Tables should contain
fields that describe one "thing", e.g., Customers, Stores,
WorkOrders, etc. The only fields that should be
duplicated among them are the Primary Key/Foreign Key
combinations.

So, from the information you've given, a reasonable table
structure might be:

Jobsites
---------
JobsiteID AutoNumber to let Access assign a random number
or another type so that you may do so (Primary
Key)
Address
City
State
Zip
Phone
etc.

WorkOrders
----------
WorkOrderID AutoNumber (Primary Key)
JobsiteID Same type as JobsiteID in Jobsites (Foreign
Key)
-----Original Message-----
Somebody please help!!! I've been working on this for 3
Click to expand...
months now, on my spare time at work, racking my brain,
doing, and redoing to trying to make this work. And
probably when someone tells me what to do, I'll feel
REALLY ignorant. PLEASE! I don't care, make me feel
REALLY ignorant!!!! I gotta get this to work!
Specifically, this is what I'm trying to do. You want to
Click to expand...
create a new workorder. I press a command button. It
takes me to the "New WorkOrder" Form which feeds from a
query that asks me what store I want to create the work
order on. So I type in for example "M630". When this
happens it feeds the store information (address1 and 2,
city, state, zip, store #, store name) to the form AND 3
of those fields to the subform. The FORM is linked to
the "jobsite table". The SUBFORM is linked to the "main
table" (this table keeps all the pertinent information
about what happens at the store. Thus at the end of the
day when I get ready to invoice the customer, I can print
the complete store information from the main table without
actually having to type in the store information. I want
it to feed from the job site table to the main table.
This happens in this form and subform that I talked about
earlier. However, I can only get it to feed 3 fields
(because that's all it allows when you create the subform
through the wizard).
I tried creating one field and linking all fields (ie:
Click to expand...
[address] & [address1] & [city] & [state] & [zip]) pulling
the information together as someone had suggested.
However, when presented to the boss, he says that he wants
the information to go into separate fields for query and
report purposes, which is understandable.
I've tried to use several subforms to feed the
Click to expand...
information to the main table but to no avail!!!!! When I
click add record, it keeps only the 3 original fields that
were set at the beginning. I know this has to be able to
work. I just don't know how or what to do to make this
work. The new customer that is requesting the COMPLETE
address on the invoices is about ready to sign a contract
with us. I do the invoicing and do NOT want to make more
work for myself than I have to :-). But also this would
help me with the quotation stuff that I'm working on to
keep the other girls from having to do double work. So
any information would be MOST helpful.
 
sorry, Mary, I clicked before finishing... :)

continuing with WorkOrders:

....
Date
Description
etc.

Base your subform on WorkOrders, and choose all fields
except the foreign key field--there's no need to display
it since it will be displayed on the main form. When
inserting the subform into the main form, set the Link
Master Fields and Link Child Fields properties. These two
fields specify the field from the main form (Master) and
the subform (Child) that contain the matching field (the
Primary Key of Jobsites and the foreign key in WorkOrders.

With this configuration, the JobsiteID will be stored in
WorkOrders for each detail record, even though you don't
have to type it. But because it's there, you can get at
all the Jobsite fields to print an invoice or other report-
-just create a query that links the two tables by
JobsiteID (drag and drop in query design view), select the
fields you need for your report, and base the report on
this query.

HTH
Kevin Sprinkel

-----Original Message-----
Somebody please help!!! I've been working on this for 3
Click to expand...
months now, on my spare time at work, racking my brain,
doing, and redoing to trying to make this work. And
probably when someone tells me what to do, I'll feel
REALLY ignorant. PLEASE! I don't care, make me feel
REALLY ignorant!!!! I gotta get this to work!
Specifically, this is what I'm trying to do. You want to
Click to expand...
create a new workorder. I press a command button. It
takes me to the "New WorkOrder" Form which feeds from a
query that asks me what store I want to create the work
order on. So I type in for example "M630". When this
happens it feeds the store information (address1 and 2,
city, state, zip, store #, store name) to the form AND 3
of those fields to the subform. The FORM is linked to
the "jobsite table". The SUBFORM is linked to the "main
table" (this table keeps all the pertinent information
about what happens at the store. Thus at the end of the
day when I get ready to invoice the customer, I can print
the complete store information from the main table without
actually having to type in the store information. I want
it to feed from the job site table to the main table.
This happens in this form and subform that I talked about
earlier. However, I can only get it to feed 3 fields
(because that's all it allows when you create the subform
through the wizard).
I tried creating one field and linking all fields (ie:
Click to expand...
[address] & [address1] & [city] & [state] & [zip]) pulling
the information together as someone had suggested.
However, when presented to the boss, he says that he wants
the information to go into separate fields for query and
report purposes, which is understandable.
I've tried to use several subforms to feed the
Click to expand...
information to the main table but to no avail!!!!! When I
click add record, it keeps only the 3 original fields that
were set at the beginning. I know this has to be able to
work. I just don't know how or what to do to make this
work. The new customer that is requesting the COMPLETE
address on the invoices is about ready to sign a contract
with us. I do the invoicing and do NOT want to make more
work for myself than I have to :-). But also this would
help me with the quotation stuff that I'm working on to
keep the other girls from having to do double work. So
any information would be MOST helpful.
 
Back
Top