Y/N fields/ Option groups. How to design a table?

Sprinks thats for your continual responses.

Unfortunately there are not just five fields to put into the form there are
about 30 different controls (checkboxes and textboxes). Also I could not put
the information into the original table because the information does not
relate to all information in that table. It only realtes to certailn records
(ie ones in the thread category)

However, I have put the form as a hidden subform unless thread is selected.
I have squeezed all the information together so that it fits (i don't know
how to do tab/page controls so I didn't take that option)

So now when entering a new product into the main Product form if thread (or
4) is selected the ThreadDetailsSubform appear and relevant information can
be typed in. If anything apart form Thread (4) is selected then the form is
invisible.

I tried the OpenForm code but it didn't work. As when hanging the cursor
over the detail it said Me.subtxtProductID = 0 and if I tried it on
Me.subtxtProduct it said = IsNull.

However, after I type information into the subform because I have linked
ProductID and Product in master and child fields after I save the information
it is stored in the relevant txtboxes.

Is there any way of getting the relevant information to populate the boxes
automatically?
--
Thanks
WoodyAccess


"Sprinks" wrote:

Woody,

I have no idea how to open a form with a hyperlink so I can't advise as to
its nuances. The straightforward way to open a form is the OpenForm method.

You can certainly include a subform, linked by the ProductID. You don't
need a hyperlink, however. Just set its Visible property to No in design
view and toggle it back on when you need it. I described how to do this in
my initial post.

In my opinion, though, I think it's much simpler to just add the five fields
to the original table and place controls bound to them on your main form,
arranged in a way that demarcates them as *special*, and toggle their
visibility as I described. I avoid one-to-one relationships except for
security reasons because they add a needless level of complexity.


Sprinks

"WoodyAccess" wrote:

I'll change the name of the control.

Yes the other form is open

I am trying to open it through a hyperlink (whether that makes a difference
or not I don't know)

I think I should somehow join the forms because at the moment they are just
2 seperate forms, I think the new form should be a subform of the main
products form (but a subform that is optionally brought up through a
hyperlink rather than a subform that is actually part of the main form!) ?
--
Thanks
WoodyAccess


"Sprinks" wrote:

Is Product the name of a form control (textbox, combo box, etc.) or the name
of a field in the underlying table? Or both? If the latter, this is not a
good practice for a variety of reasons. Most developers use a naming
convention of a 3-character prefix in naming controls--txtProduct,
cboProduct, etc. The references in the code are to the form controls, not
the fields to which they're bound.

Is the 1st form open so that the code can get the value?

Sprinks

"WoodyAccess" wrote:

In the OpenEvent of the new form it won't accept Me![Product]= Forms![Add or
Delete Products]![Product]

When I go in to DeBug it is highlighted in yellow and when hovering over
Me![Product] the cursor displays Me.[Product] = IsNull
--
Thanks
WoodyAccess


"Sprinks" wrote:

Woody,

Be careful to use quotes around literal strings but not around numbers. The
following are valid expressions:

Me.CategoryID = "Thread"
Me.CategoryID = 4

You posted them exactly the opposite, although maybe it was a typo.

By real estate, I meant room on your form that you could place the invisible
controls which would then appear only on Thread category records. You could
also do the same thing for a Tab page. It would be invisible by default;
code could make it visible when needed.

Do not use quotes when entering a value into the Tag property, but use them
in the reference in your code:

Tag Property: T
Code:

If ctl.Tag = "T" ...

Regarding the new form, the solution depends on whether these details are in
the same table as the table underlying the first form or whether they are a
separate table in a one-to-one relationship. If it's the former, then you
simply need to find the appropriate record on opening:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "YourSecondForm"

stLinkCriteria = "[ProductID]=" & Me![YourProductIDControlOn1stForm]
DoCmd.OpenForm stDocName, , , stLinkCriteria

If it's the latter, you're entering a new record, so initialize the record
in the Open event:

Me![ProductID] = Forms![NameofFirstForm]![NameofControl]
Me![YourFirstCheckbox].SetFocus

Hope that helps.
Sprinks

"WoodyAccess" wrote:

Thanks for your response Sprinks

My thread detail options are static so I have opted to go for the checkboxes
as they are easier for data inputting.

I have noted your comment about the Bound column being a number and have
revised my If Me.CategoryID = Thread, to be Me.CategoryID = "4" Then ...
visilble=True... Else etc

I didn't understand what you meant when you say if I have "real estate on my
form or if I'm out of real estate"?? And I couldn't get the tag expression to
work.

I don't really want to put a tab control in because the extra form is only
for Thread categories and not for the other 3 categories. Thus a quick link
to put in a few extra details I thought would be easier.

One problem I am encountering though is when I click the link to the new
form I want the related ProductID to populate the Product Textbox so that all
the information in both forms is stored under the unique ProductID? at the
moment when I click to the new form the Product ID textbox is blank??
--
Thanks
WoodyAccess


"Sprinks" wrote:

Woody,

My first choice for modeling a one-to-many relationship is a continuous
subform, in your case, with a combo box that lists the choices.

Your can certainly do it with a series of checkboxes if your thread detail
options are static, that is, it's unlikely that you're going to add a sixth
or seventh option next week. That's the beauty of a combo box; any new items
are simply added to a table, and appear in the combo box list.

If you have real estate on your existing form, simply place the control(s)
on it with the Visible property set to No. Then in the AfterUpdate event of
the Category field and the OnCurrent event of the form, set the Visibility of
the controls. A convenient way to do it without listing each control is to
assign a unique Tag property to these controls, loop through the controls on
the form, and set Visibility for those controls only:

Dim ctl as Control
For Each ctl in Me.Controls
If ctl.Tag = "T" Then ' for "Thread"
ctl.Visible = (Me![Category] = "Thread")
End If
Next ctl

If you're out of real estate, consider adding a Tab control to the form
rather than an entirely new form.

Hope that helps.
Sprinks

"WoodyAccess" wrote:

I already have a form that displays the basic necessary information about a
product (ProdID, ProdName, ProdCode, UnitPrice etc) but what I want to have
is a "further details" link related to the Product if it has a "thread"
category. So if the product chosen is "3mm spiral pattern" and the category
is "thread" then a link appears saying "thread details". If the product
chosen is not thread the link doesn't appear.

The problem is these details are often selecting 1 or more of about 5
options. I want to store this information in a table but am not sure how.

Do I just set up 1 table and have numerous fields with yes/no checkboxes for
the various different options and then on the form just put a rectangle
around them to show they are linked?

And would I put ProdID on the new thread details form so that they can be
linked to the original Product Form?

I hope this makes sense! Any help would be grately appreciated
--
Thanks
WoodyAccess