Form with checkbox

[Dharma]

At first sorry for my poor English, I'm speeking and writing not often the
language.

I have a table of archer-competitions, and I have a table of categories
(archery). I have a form, with a combo-box, where I can choose one of the
competitions.

I want to make a subform, where I can check the categories (with checkboxes...
all of the categories is on the subform, and I check the categories, which is
eligible on that competition). After that with a push on a button I want to
save the result of the form in a third table (competition_id, category_id)

I hope that all is understable... Thank you for your answers!

 

[Evi]

Actually this is really clear.
The checkbox needs to be a field in your Categories table. You can clear the
check box whenever you want using a simple Update Query I'll assume your ID
fields are numbers.
If you have not done so, set up an Index (using the Index button in Table
Design) so that the comination of category_ID and competition_ID is unique
(assuming that each category can only be in each competition once)

Try the following on a copy of your database to see if it gives the results
you want.

We'll call your checkbox field Choose,
Your category Table TblCat and your
Competition table TblCompetition.
Your joining table is TblCompCat
Your combo is cboComp based on the Competition Table.
It contains the CompID primary key of the competition table and Competition
Your Category SubForm is CatSub.


The button can have this code

Dim MyControl As Integer
'value of control in form
Dim MySql1 As String
Dim MySql2 As String
Dim MyToTable As String
Dim MyFromTable As String
Dim MyCheck As String
Dim MyToField1 As String
Dim MyToField2 As String
'value of check box in form

If Not IsNull(Me.cboComp) then
'in case you didn't choose something in your combo

MyToField1 = "category_ID"
MyToField2 = "competition_ID"
MyToTable = "TblCompCat"
MyFromTable = "TblCategory"
MyControl = Me.cboComp
MyCheck = "Choose"
'combo should contain competition_ID, Competition Name
'change table names combo name and checkbox name to your own


Me.CatSub.Requery
'so that your checkbox data is transferred to the category table

MySql1 = "INSERT INTO " & MyToTable & " ( " & MyToField1 & ", " & MyToField2
& ") "
MySql1 = MySql1 & "SELECT " & MyFromTable & "." & MyToField1 & ", "
MySql1 = MySql1 & MyControl & " AS " & MyToField2
MySql1 = MySql1 & " FROM " & MyFromTable
MySql1 = MySql1 & " WHERE (((" & MyFromTable & "." & MyCheck & ")=True));"
'add the categories
Set MyCheck back to false
MySql2 = "UPDATE " & MyFromTable & " SET " & MyFromTable & "." & MyCheck & "
= False;"


DoCmd.RunSQL MySql1
DoCmd.RunSQL MySql2
Me.CatSub.Requery


End If


Evi

 

[Dharma via AccessMonster.com]

Dear Evi,

Thank you for your help!

Dharma