Checkbox Set Value

[Guest]

I'm a newbie so I hope to get some help here...I have created a contact
management database:

tbl_Company: CoID (key), CoName

tbl_Product: ProductID (key), Product
Records in this table are like: ID 1-toys, ID 2-clothes, ID 3-food

tbl_CoProduct: CoID, ProductID
This table has no key. If the company carries 3 products,there are 3
records here.

I want to setup a form with 3 check boxes for products. When user checks
the box, it puts the corresponding ProductID into tbl_CoProduct.

Private Sub Check1_AfterUpdate()
If Check4.Value = True Then
Me.ProductID.Value = 1
Else
End If

End Sub

Private Sub Check2_AfterUpdate()
If Check6.Value = True Then
Me.ProductID.Value = 2
Else
End If
End Sub

I don't know why it didn't work. But my other question is, what if user
checks the box, and then uncheck it later? How do I make it remove the
record?

Thank you in advance!

 

[Guest]

Agnes

Try taking the "else" out. It is not required as you have not set an
statement.

If me.Check4 = True Then
Me.ProductID = 1
End If

 

[Guest]

Paul,

Thanks for your reply. I think I know what the problem is.

Since my tbl_CoProduct is a many to many table like this (assuming company
ID 1 has 3 kinds of products)
CoID ProductID
1 1
1 2
1 3

If more than 1 checkbox is checked, I need an action to make it to insert
new records in this table. I tested my original code and it actually works,
but it only put 1 record in this table. If I check more than 1 box, it
overwrites the original values.

Do you have any advice on this?

Thanks,
Agnes

Agnes

 

[Guest]

Hi

I think it may be the way you have set your db up. many to many links are
not good.

I have put together an example of how I would set that up

http://www.paulwest.me.uk/accessstuff/agnus.zip

hope it helps

Paul

 

[Guest]

Thanks. Say, if I setup my tables according to your sample, how can I create
a form with checkboxes that allow users to select multiple products for the
company?

I can add an AfterUpdate event to each checkbox to set ProductID.value if
checkbox = True, but I don't know how to make it insert new records into the
CompanyProduct table (say, if 3 boxes are checked, then insert 3 records).

Thanks again,
Agnes

 

[Tim Ferguson]

tbl_CoProduct: CoID, ProductID
This table has no key. If the company carries 3 products,there are 3
records here.

In that case it's not a table. You really do need a PK defined on both
fields.

I want to setup a form with 3 check boxes for products. When
user checks the box, it puts the corresponding ProductID into
tbl_CoProduct.

This is not going to work because the Access UI is not up to it. In VB
you can create control arrays, but not Access. This method is going to
fall over as soon as a company has a different number of products.

May I suggest another approach? Start with a list box full of all the
products offered by a single company (lisCurrentProds), and add another
listbox filled with all the products that are not currently owned
(lisAvailableProds). Add two command buttons, one to remove a product
from lisCurrentProds, and the other to add whatever is selected in
lisAvailableProds to the other side.

It takes a little bit of programming, but it's only a couple of SQL
insert and delete commands. Post back if you need more help.

Best wishes


Tim F

 

[Marshall Barton]

I think it may be the way you have set your db up. many to many links are
not good.

As a general statement, that's nonsense.

There are numerous situations that are appropriately modeled
by a many to many relationship and using a junction table is
the proper way to resolve a many to many relationship.

 

[Guest]

chill out - i stand corrected.

Instead of putting comments like that post something that will help with the
problem being encountered

Paul

 

[Guest]

Thanks for everybody's help. I truly apprecaited it.

My orginal design is to let user choose products for a company from a list
box, but it's not very convenient. By putting it in checkbox format, it
allows user to see what selections are available.

So for each check box, I added the following codes:

Private Sub Check1_Click()
If Check1.Value = True Then
DoCmd.RunSQL "INSERT INTO tblCoBusinessType (CompanyID, BusinessTypeID)
VALUES(" & Form_frmCompany.CompanyID & ", 17)"
Else
DoCmd.RunSQL "DELETE FROM tblCoBusinessType WHERE CompanyID=" &
Form_frmCompany.CompanyID & " AND BusinessTypeID=17"
End If
End Sub

If the box is check, get companyID from the form and value 17 (the
corresponding productID), and insert them into tblCoBusinessType. If the box
is uncheck, delete such record.

But now my problem is, I need a script to get values from tblCoBusinessType
and check the appropriate boxes.

Any help here? Thanks in advance.

-Agnes