Forms PLEASE HELP

[Hubbymax]

I have changed the forms so there is now just the main form and 1
subform. I still have the 3 fields in the main form that need to be
transfered the the subform as they are entered in the main form
( placed in the 3 fields on the subform when sub form is entered).
The
must then stay the same as new detail records are entered using the
sub form. The only time the 3 fields in the sub form should chasnge
is
when a new main record is created using the main form and those
fields
are changed. I have included the original post and the one answer I
got.

Main form fields


ReqFilledDate
StoresReqDate
Cust


Subform fields


ReqFilledDate
StoresReqDate
Cust

I tried the suggestion below with no results although the person did
not get back to me on if I was doing it correctly.
This was for when I had 2 sub forms.

You may use the Before Insert event of sub form 2:

Private Sub Form_BeforeInsert()
Me![field] = Me.Parent!subForm1Control.Form![field]
End Sub

 

[Jeff Boyce]

In a well-normalized relational database, you would NOT need to copy fields
from one table to another (or, via forms, from one form to another). The
gist of normalization makes that unnecessary.

Regards

Jeff Boyce

I have changed the forms so there is now just the main form and 1
subform. I still have the 3 fields in the main form that need to be
transfered the the subform as they are entered in the main form
( placed in the 3 fields on the subform when sub form is entered).
The
must then stay the same as new detail records are entered using the
sub form. The only time the 3 fields in the sub form should chasnge
is
when a new main record is created using the main form and those
fields
are changed. I have included the original post and the one answer I
got.

Main form fields


ReqFilledDate
StoresReqDate
Cust


Subform fields


ReqFilledDate
StoresReqDate
Cust

I tried the suggestion below with no results although the person did
not get back to me on if I was doing it correctly.
This was for when I had 2 sub forms.

You may use the Before Insert event of sub form 2:

Private Sub Form_BeforeInsert()
Me![field] = Me.Parent!subForm1Control.Form![field]
End Sub

mfG
--> stefan <--

 

[Hubbymax]

In a well-normalized relational database, you would NOT need to copy fields
from one table to another (or, via forms, from one form to another).  The
gist of normalization makes that unnecessary.

Regards

Jeff Boyce

I have changed the forms so there is now just the main form and 1
subform. I still have the 3 fields in the main form that need to be
transfered the the subform as they are entered in the main form
( placed in the 3 fields on the subform when sub form is entered).
The
must then stay the same as new detail records are entered using the
sub form. The only time the 3 fields in the sub form should chasnge
is
when a new main record is created using the main form and those
fields
are changed. I have included the original post and the one answer I
got.

Main form fields

    ReqFilledDate
    StoresReqDate
    Cust

Subform fields

    ReqFilledDate
    StoresReqDate
    Cust

I tried the suggestion below with no results although the person did
not get back to me on if I was doing it correctly.
This was for when I had 2 sub forms.

You may use the Before Insert event of sub form 2:
Private Sub Form_BeforeInsert()
   Me![field] = Me.Parent!subForm1Control.Form![field]
End Sub
mfG
--> stefan <--- Hide quoted text -

- Show quoted text -

Thanks for the help........What I want to do is this. The main form
colects data on what department is placing the order. The subform
colects the details of the order. The department and order details are
kept in different tables. When an order is made the three fields I
listed must stay the same for each item entered. As it is now, the
user must reenter these fields data each time. I want to place these
three fields on the main form and show them on the sub form so they
would stay the same for each item ordered until they are changed on
the main form. Is there another way of gdoing this, I am anything but
an expert on this.

 

[John W. Vinson]

Thanks for the help........What I want to do is this. The main form
colects data on what department is placing the order. The subform
colects the details of the order. The department and order details are
kept in different tables. When an order is made the three fields I
listed must stay the same for each item entered. As it is now, the
user must reenter these fields data each time. I want to place these
three fields on the main form and show them on the sub form so they
would stay the same for each item ordered until they are changed on
the main form. Is there another way of gdoing this, I am anything but
an expert on this.

With properly structured tables you don't need *ANY CODE AT ALL* to do this,
and you certainly do NOT need to store these three fields redundantly in every
order detail record.

You should have an Orders table - one record per order, with these three
fields - related one to many to an OrderDetails table. The OrderDetails table
would have the product ID, etc., and an OrderID as a link to the Orders table.
You could use a Form based on the Orders table (with a combo box to select the
department, etc.), and a Subform based on OrderDetails.

 

[Hubbymax]

With properly structured tables you don't need *ANY CODE AT ALL* to do this,
and you certainly do NOT need to store these three fields redundantly in every
order detail record.

You should have an Orders table - one record per order, with these three
fields - related one to many to an OrderDetails table. The OrderDetails table
would have the product ID, etc., and an OrderID as a link to the Orders table.
You could use a Form based on the Orders table (with a combo box to select the
department, etc.), and a Subform based on OrderDetails.

Thank you very much. I do have it set up with the main form showing
the Department info with a drop down box to choose the department. The
sub form holds the order details including those three fields. If I
understand you correctly I should remove these three field from the
Req table and create a new table for them. By "link" to you mean
"join"? Sorry, I am trying so hard to understand this....

 

[John W. Vinson]

Thank you very much. I do have it set up with the main form showing
the Department info with a drop down box to choose the department. The
sub form holds the order details including those three fields. If I
understand you correctly I should remove these three field from the
Req table and create a new table for them. By "link" to you mean
"join"? Sorry, I am trying so hard to understand this....

So is you main form based on the Department table!? Why? Do you add new
departments that frequently?

You may have posted your table structures previously, and I apologize if I'm
going over ground again, but I'd suggest the following table structures:

Departments
DepartmentID
DepartmentName

This table would have a form just for doing maintenance - changing department
names, adding new departments, etc. This form will only rarely get opened at
all.

Orders
OrderID <Autonumber, Primary Key>
RequestDate <Date/Time, when the order was requested>
FilledDate <date/time, when it was filled>
DepartmentID <Long Integer, link to Departments>
CustID <link to a table of Customers>
(other info about the order as a whole)

OrderDetails
OrderDetailID <Autonumber, primary key>
OrderID <long integer, link to Orders>
ProductID <link to Products, what was ordered>
Quantity <how many were ordered>
<other fields about THIS product in THIS order>

You would have a one to many relationship from Departments to Orders on
DepartmentID, and a one to many relationship from Orders to OrderDetails on
OrderID - and doubtless other relationships, which I can't specify without
more knowledge of your tables.

Your main form would be based on Orders, not on Departments; you would have
controls - such as a Combo Box based on Departments to store the departmentID.
The Subform would be based on OrderDetails so that a single order (with a
request date, a filled date, a departmentID, etc.) can have multiple items
included.

If I'm totally off base, please post the structure *of your tables* - not your
forms, the forms are NOT primary, the tables are! - in the format above and
indicate how they are related.

 

[Hubbymax]

So is you main form based on the Department table!? Why? Do you add new
departments that frequently?

You may have posted your table structures previously, and I apologize if I'm
going over ground again, but I'd suggest the following table structures:

Departments
  DepartmentID
  DepartmentName

This table would have a form just for doing maintenance - changing department
names, adding new departments, etc. This form will only rarely get openedat
all.

Orders
  OrderID <Autonumber, Primary Key>
  RequestDate <Date/Time, when the order was requested>
  FilledDate <date/time, when it was filled>
  DepartmentID <Long Integer, link to Departments>
  CustID <link to a table of Customers>
  (other info about the order as a whole)

OrderDetails
  OrderDetailID <Autonumber, primary key>
  OrderID <long integer, link to Orders>
  ProductID <link to Products, what was ordered>
  Quantity <how many were ordered>
  <other fields about THIS product in THIS order>

You would have a one to many relationship from Departments to Orders on
DepartmentID, and a one to many relationship from Orders to OrderDetails on
OrderID - and doubtless other relationships, which I can't specify without
more knowledge of your tables.

Your main form would be based on Orders, not on Departments; you would have
controls - such as a Combo Box based on Departments to store the departmentID.
The Subform would be based on OrderDetails so that a single order (with a
request date, a filled date, a departmentID, etc.) can have multiple items
included.

If I'm totally off base, please post the structure *of your tables* - notyour
forms, the forms are NOT primary, the tables are! - in the format above and
indicate how they are related.

This is actually close to how I am set up. The info in the Department
table is shown on the main form just for referance. It is not edited
on the main form. I do have a small form for adding or changing
department info. I do not have any "auto" number fields as I have
heard they are a bit undependable. I have a field called RecNo that is
unique to each order and is used to join the tables. My table
structure is as follows.

DeptInfo4 table - Holds info on departments
Req4- Holds details of each order
Products2 - Holds info on products

Simply put, in the end I need to end up having,

The department that placed the order from the deptinfo4 table
The details of each order, product name, amount issued out of each
item ordered, the purchase code for the dapartment, the req number for
each order, and other infor from Req4 table. As long as I have this
data stored my query for the report organizes and groups the
information for the report.

 

[John W. Vinson]

This is actually close to how I am set up. The info in the Department
table is shown on the main form just for referance. It is not edited
on the main form. I do have a small form for adding or changing
department info. I do not have any "auto" number fields as I have
heard they are a bit undependable. I have a field called RecNo that is
unique to each order and is used to join the tables. My table
structure is as follows.

DeptInfo4 table - Holds info on departments
Req4- Holds details of each order
Products2 - Holds info on products

Simply put, in the end I need to end up having,

The department that placed the order from the deptinfo4 table
The details of each order, product name, amount issued out of each
item ordered, the purchase code for the dapartment, the req number for
each order, and other infor from Req4 table. As long as I have this
data stored my query for the report organizes and groups the
information for the report.

If each Req4 record can involve more than one product *you need one more
table*.

If the following statements are true:

Each Product can be in one or more different Orders.
Each Order can include one or more Products.

I'm still confused by your description of the Dept4Info table. It sounds like
you're mixing different entities! A Department is one kind of entity; a
Department does NOT have a product name or an amount! Is this just my
confusion, and is Dept4Info in fact the "order details" table? If so, then it
should NOT contain the req number or *ANYTHING* else from the Req4 table; all
it needs is the ReqNo as a link to the Req4 table.

Are you perhaps making the (very common) mistake of assuming that all the
information needed for a report must be stored in one table? If so, be aware
that a Report will typically NOT be based on one table, but rather on a
multitable query pulling the department name from the Department table, the
ReqNo and Purchase code and order date from the Req4 table, the quantity
ordered from the "details" table (whatever it's named), and the product name
from the Products table; all four tables will be joined in the query by the
appropriate linking fields. It's neither necessary nor appropriate to copy any
data from one table into another for the purpose of the report!

 

[Hubbymax]

If each Req4 record can involve more than one product *you need one more
table*.

If the following statements are true:

Each Product can be in one or more different Orders.
Each Order can include one or more Products.

I'm still confused by your description of the Dept4Info table. It sounds like
you're mixing different entities! A Department is one kind of entity; a
Department does NOT have a product name or an amount! Is this just my
confusion, and is Dept4Info in fact the "order details" table? If so, then it
should NOT contain the req number or *ANYTHING* else from the Req4 table;all
it needs is the ReqNo as a link to the Req4 table.

Are you perhaps making the (very common) mistake of assuming that all the
information needed for a report must be stored in one table? If so, be aware
that a Report will typically NOT be based on one table, but rather on a
multitable query pulling the department name from the Department table, the
ReqNo and Purchase code and order date from the Req4 table, the quantity
ordered from the "details" table (whatever it's named), and the product name
from the Products table; all four tables will be joined in the query by the
appropriate linking fields. It's neither necessary nor appropriate to copy any
data from one table into another for the purpose of the report!
--

             John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

I do draw the report info from 3 different tables. It gets the
department info, DepName and DeptNu (name and id number). It gets the
product name and description from the Products table, them gets the
details from the Rec4 table. Rec4 includes the dates ordered and
filled, product numbers, ordering departments billing code, and
requsition number. Based on these fields the query for the report
draws all info needed. The only place this info is saved is in the
Rec4 table and comes from the input form. Each department has multiple
billing codes so it is easier to just enter this code at the time of
order as they are subject to change at any time. It doesn't sound like
I am too for off does it?

 

[John W. Vinson]

I do draw the report info from 3 different tables. It gets the
department info, DepName and DeptNu (name and id number). It gets the
product name and description from the Products table, them gets the
details from the Rec4 table. Rec4 includes the dates ordered and
filled, product numbers, ordering departments billing code, and
requsition number. Based on these fields the query for the report
draws all info needed. The only place this info is saved is in the
Rec4 table and comes from the input form. Each department has multiple
billing codes so it is easier to just enter this code at the time of
order as they are subject to change at any time. It doesn't sound like
I am too for off does it?

My only concern is that it appears that you're storing some of the same
information (dates orderd and filled, billing code, requisition number)
multiple times - as many times as there are items. Is this intentional? If a
department orders five items, all on one "order", should there perhaps be one
record in an Orders table (for the information that is common to all items in
the order), and five records in a related OrderDetails table (for information
pertinent to each item in the order), rather than repeating the information?