tab control issue

[Guest]

I have a form which represents an incoming truck. On this form I have a
subform which represents the items on that truck, which can give me several
subforms for each form. On that subform I have a tab control with four tabs
(Bulk Corn, Bulk Oil, Bulk Flour and Bulk Masa). My question is, when I fill
out a form/subform for Bulk Flour and fill out the tab control for this item,
how do I get that tab to remain on top for that record instead of defaulting
back to the top tab when I leave that record and come back? In other words,
keep the tab that goes with each record on top.

I hope someone can help me. I have searched the forum but could not find
this question exactly.

Thanks!

 

[tina]

the best i can offer is some general direction. on the form that holds the
"bulk corn, or oil, or flour, or masa" records, you need to add some code
the Current event procedure. something along the lines of

Select Case <a field in the record that designates corn, oil, etc>
Case "corn"
Me!SubformControlName.Form!TabCtlName = <page index value of tab
page for corn>
Case "oil"
Me!SubformControlName.Form!TabCtlName = <page index value of tab
page for oil>
Case "flour"
...
Case "masa"
...
Case Else
' whatever tab page you want the record to default to.
End Select

read up on the Select Case statement in VBA Help, so you'll understand how
it works. keep in mind that tab control pages have a zero-based index. so
the first page has a Page Index value of zero (0), the second page's Page
Index value is 1, and so on. also, you'll want to run the code in the
AfterUpdate event procedure of the control bound to the field that
designates "corn, oil, flour or masa", so the tab will change when the value
in the control is edited.

hth

 

[Guest]

Thank you so much!
--
Miranda

the best i can offer is some general direction. on the form that holds the
"bulk corn, or oil, or flour, or masa" records, you need to add some code
the Current event procedure. something along the lines of

Select Case <a field in the record that designates corn, oil, etc>
Case "corn"
Me!SubformControlName.Form!TabCtlName = <page index value of tab
page for corn>
Case "oil"
Me!SubformControlName.Form!TabCtlName = <page index value of tab
page for oil>
Case "flour"
...
Case "masa"
...
Case Else
' whatever tab page you want the record to default to.
End Select

read up on the Select Case statement in VBA Help, so you'll understand how
it works. keep in mind that tab control pages have a zero-based index. so
the first page has a Page Index value of zero (0), the second page's Page
Index value is 1, and so on. also, you'll want to run the code in the
AfterUpdate event procedure of the control bound to the field that
designates "corn, oil, flour or masa", so the tab will change when the value
in the control is edited.

hth

 

[BruceM]

I would just add that if you have a separate field for each product you
should reconsider your design. If there is a ProductType field I don't see
a need for separate tab controls for each product.

 

[Guest]

Thanks for your concern. Each product has different attributes that have to
be checked (i.e. you don't check temperature on corn but you do on oil). I
also have packaging and minor ingredients along with the bulk ingredients.
When a product is chosen, code runs to unhide the attributes that must be
checked. Since there are several different "types" of bulk ingredients, I
put them on a tab control instead of writing code to unhide only the ones I
need. Do you think that it would be easier another way?

 

[BruceM]

I'm not sure the structure of your database, so I don't know if each product
is a separate field in a table or if it is a separate table. In either case
it can cause problems down the road if you ever add a product, in which case
you need to modify queries, forms, reports, and code to accomodate the extra
field. If instead you have a ProductType field you can simply add a
product, just as you can add a person to an Employee database without
redesigning the project. I would be inclined to show/hide controls as
needed, or something of the sort. If there are minor ingredients I would
probably put those into a separate table, but it is difficult to be specific
without knowing more about the structure.

 

[Guest]

I have a table for products (all products in one table), my form has a table
of it's own and my subform has a table of it's own to store the actual
checks. The products table has info on the products and has the "Ingredient
Number" as the primary key with each product being a new record, therefore I
am able to add or remove products as nessasary and only store the Ingredient
Number in the subform table which is linked to the form table by the Bill of
Lading Number (which ties everything together becuase no matter what it is
carrying or where it comes from each truck will have a distinct number). I
also have different tables with links to my products table that have the
available info (i.e. Bulk Ing, Minor Ing, Packaging or Supplier Name and
Phone Number). I am pretty sure that I have everything broken down ok, but I
am always open to improvement! Thanks!

 

[tina]

you're welcome
i did read your comments on your tables, elsewhere in this thread, and noted
that several times you spoke of "form tables". maybe it's just your way of
describing them - but, from a development standpoint, that sounds like
you're putting the cart before the horse. in a relational database, you
don't build tables just to bind to a form that you want to use. the first,
and most vital, aspect of the database is proper normalization of the data
into correctly structured tables/relationships. *after* that's done, you
build forms for your user(s) to interact with the data.
i really recommend that you stop, take a step back, read up on relational
design principles, and then evaluate your database's tables/relationships in
that context. make sure they're structured correctly, *then* concern
yourself with forms, reports, etc. for more information, see
http://home.att.net/~california.db/tips.html#aTip1.

hth

 

[BruceM]

When you say you have a table for products, do you mean you have a table
that lists *only* products? Each truckload consists of some quantity of a
product. It makes a difference in the design, by the way, if a truckload
ever consists of more than one product. In either case, the Product table
is more or less static, which is to say products are added to it only
occasionally. On the other hand, a new record is added to the Truckload
table every time a truck is loaded. Assuming that a truckload consists of
only one product, you may have a structure something like this:

tblProduct
IngredientNo (primary key, or PK)
Description
UnitSize
PricePerUnit
other fields specific to the product

tblTruckload
BillOfLadingNo (PK)
IngredientNo (foreign key, or FK)
DateShipped
Destination
other fields specific to the truckload

There is a one-to-many relationship between the two IngredientNo fields,
which you can create in the Relationships window. Note that the PK field is
designated in table design view, but that the FK field becomes the foreign
key because of its relationsip to the PK. It is not designated in table
design view as the FK. It needs to be the same data type as the PK, or Long
Integer if the PK is autonumber.

If each truckload can consist of several products (which I assume is the
case since you mention Minor Ingredients), and each product is part of many
truckloads over time, there is a many-to-many relationship between Products
and Ingredients. A third table is needed to resolve this relationship:

tblProductTruckload
ProdTruckNo (PK)
IngredientNo (FK)
BillOfLadingNo (FK)
QuantityShipped
PricePerUnit
other fields specific to a particular product in a particular truckload

Note that IngredientNo would not be a part of tblTruckload in this scenario,
and that there is no direct relationship between tblIngredient and
tblTruckload.

The three-table scenario assumes you would want to look at the Truckload
records to see what Ingredients were shipped, and that you would want to
look at the Ingredient records to see the Truckloads in which they were
included.

Similar decisions need to be made about suppliers, customers, packaging, and
anything else involved. You will probably want to store the customer's
Address information in tblTruckload (assuming each truckload goes to a
single customer) so that you can look back and see the actual address to
which the truckload was shipped, but for billing purposes you want the
current address. For UnitPrice, you want to store the price at the time the
Ingredient was shipped, the but price in tblProduct will need to be updated
from time to time. With packaging, you need to decide if you want to track
the use of packaging (maybe for inventory control, or cost analysis, or
whatever).

These are some of the questions that should be answered before you make the
first form. The best way to work out a design is with a pencil and some
paper, keeping in mind that a table should store information about a single
entity (truckload, ingredient, etc.).