Insert values into a table based on check box being on or off.

C

CLU73

Good morning and thanks to all who answer questions on this board. It is
very helpful. My question is this.

I have a database I am trying to create in which I am trying to populate
values in a table based on a selection in a form. I have a table with the
following columns; SessionID, CamperID, WeekNo, Day. I would like to have my
form setup so that it will call the CamperID and all I have to do is select a
check box representing a day of the week and it will insert into the Session
table a WeekNo and Day that I tell it to.

So for example, in my form, I would have the camper ID which is already
pulled for me in the form and "06/20" as the label and it would be a check
box. Once this check box is selected, I would want an insert statement to
insert the values of "1" in the WeekNo column and "1" in the Day column of my
Session table. The next check box would be "06/21" and upon it's selection,
I would want to insert the values of "1" in the WeekNo column and "2" in the
Day column, etc. Upon the checking of each box, this would insert a new row
for whatever camper I was on at that time and whatever values I specify.

My problem is that I am trying to use the "On Click" function and add the
code:

Private Sub Check6_Click()
INSERT INTO SESSION (WeekNo,Session)
VALUES (1,1)
End Sub

When I try to run this, I get an error highlighting "SESSION" and stating,
"Compile Error: Expected end of statement."

I am pretty sure I am not doing this right and am wondering if anyone has
any suggestions. Can anyone help with this? I have looked at the insert
statement with no luck.

Thanks in advance,
clu73
 
S

Steve Schapel

Clu,

Try like this...

CurrentDb.Execute "INSERT INTO Session ( WeekNo, Day ) VALUES ( 1, 1 )"

Also, I wouldn't recommend the Click event of the checkbox... this means
it will run whether you check the box or uncheck it. Maybe After Update
would be preferable, and only run the code if you are checking the box,
eg...
If Me.Check6 Then
CurrentDb.Execute...
End If
 
C

CLU73

Thanks Steve. That gets me closer. Can I ask another question then. In my
form, I have a Camper ID and I would like my script to include the camper ID
and insert that into the table as well. Can you tell me what the code would
look like to insert that information into the script. So if the camper ID I
was working on was 13, I would want to insert that into the form but I don't
want to have to type it. I want it to pull from the form.

Thanks Steve or anyone else who assists,
CLU73
 
S

Steve Schapel

CLU73,

CurrentDb.Execute "INSERT INTO Session ( [Camper ID], WeekNo, Day )
VALUES ( " & Me.Camper_ID & ", 1, 1 )"

(assumes Camper ID is a number data type)
 
Top