Programming Check Boxes

[Guest]

I have an Order form to which I have added a tab page. This tab page has two
combo boxes and seven check boxes (not in a group, multi selections). When I
open the saved record, the combo boxes have the information entered. I don't
know how to program the check boxes so that the saved selections are there. I
can't find anything in my books or sample databases on this. Any help or
direction would be much appreciated.
Thanks
Ron

 

[John W. Vinson]

I have an Order form to which I have added a tab page. This tab page has two
combo boxes and seven check boxes (not in a group, multi selections). When I
open the saved record, the combo boxes have the information entered. I don't
know how to program the check boxes so that the saved selections are there. I
can't find anything in my books or sample databases on this. Any help or
direction would be much appreciated.
Thanks
Ron

Just remember, that data is stored in tables, and ONLY in tables - the
controls on a Form are just tools to get that data there.

What Table is the Recordsource for the form? Are these checkboxes
bound to Yes/No fields in that Table? If not, there's noplace for the
information to be stored!

John W. Vinson [MVP]

 

[Guest]

John
I had created a table with these Yes/No Check boxes in it and then related
it to the Orders table. I then added the new table to the form's query. When
I add the new table, the form stops working. Should I just add the check box
fields directly to the Orders table?

 

[John W. Vinson]

John
I had created a table with these Yes/No Check boxes in it and then related
it to the Orders table. I then added the new table to the form's query. When
I add the new table, the form stops working. Should I just add the check box
fields directly to the Orders table?

I'm suspicious of these checkboxes: what are their fieldnames? What
are their datatypes? I suspect that you're denormalizing your database
and may come to regret it!

That said... open the Form in design view; view its Properties; click
the ... icon by the Recordsource property (first on the Data tab).
Make sure that these new fields are added to the query, in the Fields
row. They should now be available on the form (though you may lose the
ability to update the table, or may get wierd duplicate record
problems if you have a one-to-many relationship).

John W. Vinson [MVP]

 

[Guest]

John
I added these fields to the orders table in the query. These are yes/no
datatypes and allow for a choice of refreshments at a meeting: Coffee, Orange
Juice, Doughnuts, Danish, etc. Everything is working ok. I just wanted to
know if there was a better to do this. I hope I don't regret adding these
refreshments to the Orders table, but I don't know any other way to it. It
just seemed to me that a table for the refreshments would be the way to go,
but I can't get it to work. I still have a lot to learn.
Thanks

 

[John W. Vinson]

John
I added these fields to the orders table in the query. These are yes/no
datatypes and allow for a choice of refreshments at a meeting: Coffee, Orange
Juice, Doughnuts, Danish, etc. Everything is working ok. I just wanted to
know if there was a better to do this. I hope I don't regret adding these
refreshments to the Orders table, but I don't know any other way to it. It
just seemed to me that a table for the refreshments would be the way to go,
but I can't get it to work. I still have a lot to learn.

Well... that's NOT in fact a good idea.

Suppose you want to add Bagels and Cream Cheese to the list of
refreshments. What do you do? Open your backend Table in design view,
add two new fields, edit all your Queries, edit all your Forms and
Reports to include the new fields...!? Ouch!

If each Order can have a choice of several Refreshments, and each
Refreshment can be served in conjunction with many Orders, you have a
classic Many to Many relationship. The way to handle this is with a
THIRD table:

Orders
OrderID
<your other order-specific fields>

Refreshments
RefreshmentID <Primary Key, autonumber>
ItemType <e.g. "Coffee", "Orange Juice", "Champagne", ...>

RefreshmentsUsed
OrderID <Link to Orders.OrderID>
RefreshmentID <link to Refreshments>
<other fields, e.g. quantity, time desired, ... about THIS item for
THIS order>

On your Form you would have a subform based on RefreshmentsUsed, with
a combo box based on Refreshments to select the desired item.

John W. Vinson [MVP]

 

[Guest]

John

Ok, now I see see why it wasn't making any sense. This is my first many to
many relationship experience.
Thanks for your help!

Ron

 

[John W. Vinson]

This is my first many to many relationship experience.

The first in a long line of many of them, if you keep working with
databases - they're all but universal. Good luck!

p.s. - I'd like an onion bagel with cream cheese and some tomato juice
with my order, please... <g>

John W. Vinson [MVP]

 

[Guest]

Coming right up!

The first in a long line of many of them, if you keep working with
databases - they're all but universal. Good luck!

p.s. - I'd like an onion bagel with cream cheese and some tomato juice
with my order, please... <g>

John W. Vinson [MVP]

 

[Guest]

Hey John

I'm having another problem, I hope I'm not being a pest. I was having this
problem before when I related the new table to my Orders table. Per your
instructions, I have created two tables: a one to many and a many to many.
The relationship is made, but when I add them to my Orders form query and go
to form view, the form is blank. Would you look at the following SQL and see
if you see anything suspicious?
SELECT Orders.CustomerID, [FirstName] & " " & [LastName] AS Expr1,
Customer.LastName, Customer.Address, Customer.City, Customer.State,
Customer.ZipCode, Customer.Phone, Customer.Fax, Orders.OrderID,
Orders.CustomerID AS Orders_CustomerID, Orders.Room, Orders.TodaysDate,
Orders.StartDate, Orders.EndDate, Orders.ArriveTime, Orders.StartTime,
Orders.EndTime, Orders.Notes, Customer.CustomerNumber, Orders.CustomerID,
Customer.CustomerID, Customer.Email
FROM tblFoodBev INNER JOIN (((Customer INNER JOIN Orders ON
Customer.CustomerID = Orders.CustomerID) INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) INNER JOIN tblFoodBevUsed ON
Orders.OrderID = tblFoodBevUsed.OrderID) ON tblFoodBev.FoodBevID =
tblFoodBevUsed.FoodBevID;
The form works fine until I try to add a table to the query.
Thanks for looking.

 

[John W. Vinson]

I'm having another problem, I hope I'm not being a pest. I was having this
problem before when I related the new table to my Orders table. Per your
instructions, I have created two tables: a one to many and a many to many.
The relationship is made, but when I add them to my Orders form query and go
to form view, the form is blank.

Well then... DON'T DO THAT. <g>

Normally one would not create a Grand Master Query joining all your
tables together, and base a form on that. Such a query would usually
show no records (a default Inner Join will show only records which
have matching fields in both joins), and will probably not be
updateable.

Instead, use a Form based on the "one" side table, with a subform
based on the "many to many resolver table" - the table which is on the
many side of both relationships. In this case, you would have a Form
based on Orders with a subform based on RefreshmentsUsed. The
Refreshments table would be used only as the rowsource of a combo box
on RefreshmentsUsed subform; you would have a separate maintenance
form to add new refreshments as needed.

See the Northwind sample database Orders form for an example - the
Products are like your Refreshments, the Orders like your orders, and
the OrderDetails like your RefreshmentsUsed.

John W. Vinson [MVP]

 

[Guest]

Verrrry interesting!

I,ll get it.
Thanks again.

Ron

 

[Guest]

One more question on this. I have done this just as you said and it is
working perfectly. My question: This database was set up after the Northwind
database with both Products and OrderDetails tables. Does it matter that both
the OrderDetails table and the RefreshmentsUsed tables have the same
relationship with OrderID on the Orders table?
Everything seems to be working ok, I just want to be sure this won't cause a
problem.
Thanks again.
Ron

 

[John W. Vinson]

One more question on this. I have done this just as you said and it is
working perfectly. My question: This database was set up after the Northwind
database with both Products and OrderDetails tables. Does it matter that both
the OrderDetails table and the RefreshmentsUsed tables have the same
relationship with OrderID on the Orders table?
Everything seems to be working ok, I just want to be sure this won't cause a
problem.

If an order has both OrderDetails and also Refreshements, and if the
Refreshments are independent of the OrderDetails, then no - your setup
is perfectly legitimate.

The only problem that might arise is if you create a query on all
three tables - if you have five OrderDetails records and six
Refreshments records for a given order, you'll see all thirty possible
combinations. Solution: don't use such a query; use Subforms or
Subreports instead.

John W. Vinson [MVP]

 

[Guest]

Great!
Thanks again.

Ron

If an order has both OrderDetails and also Refreshements, and if the
Refreshments are independent of the OrderDetails, then no - your setup
is perfectly legitimate.

The only problem that might arise is if you create a query on all
three tables - if you have five OrderDetails records and six
Refreshments records for a given order, you'll see all thirty possible
combinations. Solution: don't use such a query; use Subforms or
Subreports instead.

John W. Vinson [MVP]