insert data from unbound form & combo boxes to table

[bjh29]

Greetings! Thanks in advance for any help. I have been teaching myself
access as the programmer we hired for our non-profit group went AWOL with an
incomplete project.

I have a form in which I have several unbound combo boxes which I would like
a user to click a button to insert into a table. I have a start on the code,
but it is not working even with a single entry and I need up to four entries
per click of button.

Let me explain what is happening. We have a home school group that meets
once a week for classes. This database is used to register students for
classes. Students can chooses between one to four classes each. My form
(actually it is a subform) has the LMHSCID, sessionID (semester), Student
Name, and 10 am, 11 am, 1 pm and 2 pm class choices. These options are all
selected by user through unbound combo boxes based on parameters (such as
time of class for the class choices).

I would like my button to insert into the CO-OPStudentReg table the
following info: LMHSCID (family they are in), sessionID (semester), Student
Name, ClassChoice (if not null). The table has an auto number primary field.
If a student chooses four classes they would have four entries. The only
difference between the entries would be the different classes. The other
info (LMHSCID, sessionID, Student Name) would be identical and come from the
form.

So far I have tried to code entering in only one class time (10 am) to see
if I could get it to work before I tried to add in the possible four entries.


Here is my code:

Option Compare Database

Private Sub cmdEnter_Click()

Dim strSQLEnterStudent As String
strSQLEnterStudent = "INSERT INTO CO-OPStudentReg ( LMHSCID,
sessionID,Student_Name,ClassChoice)" &_ "VALUES(" & Me.LMHSCID.Value & ", " &
Me.sessionID.Value & ", " & Me.Student_Name.Value & "," &
Me.ClassChoice10.Value & ")"
DoCmd.RunSQL strSQLAddComment

End Sub

Thanks again!

 

[Roger Carlson]

If you're just starting to learn Access, it would be far easier to use bound
controls. Unbound forms are not Access's forte (although it can be done).
However, if you feel you must use unbound forms, my website
(www.rogersaccesslibrary.com), has two small Access database samples called
"Unbound.mdb" and "UnboundSQL.mdb" which illustrate how to use unbound
forms.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[bjh29]

Thanks. I will look into your samples. I really need to use unbound
controls. I have tried to get around it, but because of structure of other
things further in, I can't.

I am running access 2007. Your coding shouldn't be a problem for that,
should it?