insert into code snags

[bjh29]

Thank you in advance for any help.

I am trying to insert data from an unbound form into a table. I keep
getting the run-time error 3134. syntax error in INSERT INTO statement.

I have tried single quotes, quotes as you see below. I have tried
db.Execcute and the DoCmd you see below. I inherited this unfinished project
and trying to work out the bugs.

The table I am inserting into is 'CO-OPStudentReg' with fields: LMHSCID,
sessionID, studentsID, ClassChoice. I don't have a primary key set or an
auto number field. The fields in the table look up values from four other
tables.

My form has LMHSCID (giving me info from a user initiated parameter query),
sessionID (again, info from parameter qry), studentsID (parameter qry), and
ClassChoice10 (selects info from my Classes table and sorts by the class
time), ClassChoice11 (info from same class table, just limiting to 11:00
time), ClassChoice1, ClassChoice2.

Essentially, I would like to enter up to four records into my table
depending on how many classes the student has chosen.

If I don't select any classes and click my button, the form clears as it is
supposed to, but no data is entered into table. As soon as I choose a class,
I get the error.

Thanks,
bjh29

Here is my code:
Private Sub btnADD_Click()

'**** add a new record ****
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb


If Not IsNull(ClassChoice10) Then
DoCmd.RunSQL "INSERT INTO CO-OPStudentReg
([LMHSCID],[sessionID],[studentsID],[ClassChoice]) VALUES (""" &
LMHSCID.Value & """,""" & sessionID.Value & """,""" & studentsID.Value &
""",""" & ClassChoice10.Value & """)"
End If
If Not IsNull(ClassChoice11) Then
DoCmd.RunSQL "INSERT INTO CO-OPStudentReg
([LMHSCID],[sessionID],[studentsID],[ClassChoice]) VALUES (""" &
LMHSCID.Value & "," & sessionID.Value & "," & studentsID.Value & "," &
ClassChoice11.Value & """)"
End If
If Not IsNull(CLassChoice1) Then
DoCmd.RunSQL "INSERT INTO CO-OPStudentReg
([LMHSCID],[sessionID],[studentsID],[ClassChoice]) VALUES (""" &
LMHSCID.Value & "," & sessionID.Value & "," & studentsID.Value & "," &
CLassChoice1.Value & """)"
End If
If Not IsNull(ClassChoice2) Then
DoCmd.RunSQL "INSERT INTO CO-OPStudentReg
([LMHSCID[,[sessionID],[studentsID],[ClassChoice]) VALUES (""" &
LMHSCID.Value & "," & sessionID.Value & "," & studentsID.Value & "," &
ClassChoice2.Value & """)"


MsgBox (Me!studentsID) & " has been added to the CO-OP
registration table."

End If

studentsID.Value = Null
ClassChoice10.Value = Null
ClassChoice11.Value = Null
CLassChoice1.Value = Null
ClassChoice2.Value = Null
End Sub

 

[SteveS]

What are the data types of these fields? (number, text, date/time)

LMHSCID =
sessionID =
studentsID =
ClassChoice =


Can you provide a few examples of the data you wish to insert into the table?

LMHSCID | sessionID | studentsID | ClassChoice



--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Thank you in advance for any help.

I am trying to insert data from an unbound form into a table. I keep
getting the run-time error 3134. syntax error in INSERT INTO statement.

I have tried single quotes, quotes as you see below. I have tried
db.Execcute and the DoCmd you see below. I inherited this unfinished project
and trying to work out the bugs.

The table I am inserting into is 'CO-OPStudentReg' with fields: LMHSCID,
sessionID, studentsID, ClassChoice. I don't have a primary key set or an
auto number field. The fields in the table look up values from four other
tables.

My form has LMHSCID (giving me info from a user initiated parameter query),
sessionID (again, info from parameter qry), studentsID (parameter qry), and
ClassChoice10 (selects info from my Classes table and sorts by the class
time), ClassChoice11 (info from same class table, just limiting to 11:00
time), ClassChoice1, ClassChoice2.

Essentially, I would like to enter up to four records into my table
depending on how many classes the student has chosen.

If I don't select any classes and click my button, the form clears as it is
supposed to, but no data is entered into table. As soon as I choose a class,
I get the error.

Thanks,
bjh29

Here is my code:
Private Sub btnADD_Click()

'**** add a new record ****
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb


If Not IsNull(ClassChoice10) Then
DoCmd.RunSQL "INSERT INTO CO-OPStudentReg
([LMHSCID],[sessionID],[studentsID],[ClassChoice]) VALUES (""" &
LMHSCID.Value & """,""" & sessionID.Value & """,""" & studentsID.Value &
""",""" & ClassChoice10.Value & """)"
End If
If Not IsNull(ClassChoice11) Then
DoCmd.RunSQL "INSERT INTO CO-OPStudentReg
([LMHSCID],[sessionID],[studentsID],[ClassChoice]) VALUES (""" &
LMHSCID.Value & "," & sessionID.Value & "," & studentsID.Value & "," &
ClassChoice11.Value & """)"
End If
If Not IsNull(CLassChoice1) Then
DoCmd.RunSQL "INSERT INTO CO-OPStudentReg
([LMHSCID],[sessionID],[studentsID],[ClassChoice]) VALUES (""" &
LMHSCID.Value & "," & sessionID.Value & "," & studentsID.Value & "," &
CLassChoice1.Value & """)"
End If
If Not IsNull(ClassChoice2) Then
DoCmd.RunSQL "INSERT INTO CO-OPStudentReg
([LMHSCID[,[sessionID],[studentsID],[ClassChoice]) VALUES (""" &
LMHSCID.Value & "," & sessionID.Value & "," & studentsID.Value & "," &
ClassChoice2.Value & """)"


MsgBox (Me!studentsID) & " has been added to the CO-OP
registration table."

End If

studentsID.Value = Null
ClassChoice10.Value = Null
ClassChoice11.Value = Null
CLassChoice1.Value = Null
ClassChoice2.Value = Null
End Sub

 

[bjh29]

Hi Steve, Thanks for responding. Feel free to reply as if I were a second
grader.

Here is the info you requested:

In my CO-OPStudentReg table LMHSCID, sessionID,studentsID, ClassChoice are
all number data types. They look up info in other tables. LMHSCID looks up
the LMHSCID, HusbandLastName, HusbandFirstName, WifeFirstName(user only sees
the name not the primary key- primary key is bound column), sessionID looks
up a sessionID and SessionName (user sees only name and not primary key- this
is like our semester of school), studentsID looks up a studentsID,
studentsLastName, studentsFirstName (again primary key not seen), ClassChoice
looks up ClassID, ClassName, GradeLevel (user only sees name and GradeLevel).

For my form: First the user sees a form where they select which family
(LMHSCID) and which session (sessionID) they would like to work with. Then
when the click an open button they come to my unbound form. The unbound form
is where my trouble is. I have a clear and exit buttons that work good.
Small miracle there. The form has 7 unbound combo boxes. First is the
LMHSCID, I have made it so the user is only able to choose the LMHSCID for
the family which they chose on the previous form, same for sessionID. The
studentsID give only students which are linked to that particular family.
The next four combo boxes are from the Classes table. The user is able to
choose a 10 am class, 11 am class, 1 pm class, 2 pm class (each combo box has
a parameter qry which narrows the classes by matching sessionID and
ClassTime) It allows the user to make up to four selections, one for each
hour of classes available to the students. They can choose between one and
four classes.

Example: The data I would like to insert to my CO-OPStudentReg table is:
LMHSCID, HusbandLastName, HusbandFirstName & WifeFirstName (30, Smith, John &
Jane), sessionID, SessionName (2, semester 2 06-07), studentsID,
StudentLastName, StudentFirstName (63, Smith, Johnny). ClassID, ClassName
(37, Art)

This example shows if they choose one class, if they choose more than one
(fields are not Null) I would like to insert the same LMHSCID, sessionID,
studentsID and then put in the second, third and/or fourth ClassID. It would
be AWESOME if I could make sure not duplicate choices were entered.

I toyed with the idea of having my CO-OPStudentReg table have four fields
for ClassChoice then I would have to mess with this unbound form. I could
just make a normal form and narrow the combo boxes with parameters. However,
when I go to make any reports about the students and classes, I would have to
do x4 because I couldn't just sort by time.

Thanks again!

bjh29

What are the data types of these fields? (number, text, date/time)

LMHSCID =
sessionID =
studentsID =
ClassChoice =


Can you provide a few examples of the data you wish to insert into the table?

LMHSCID | sessionID | studentsID | ClassChoice



--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Thank you in advance for any help.

I am trying to insert data from an unbound form into a table. I keep
getting the run-time error 3134. syntax error in INSERT INTO statement.

I have tried single quotes, quotes as you see below. I have tried
db.Execcute and the DoCmd you see below. I inherited this unfinished project
and trying to work out the bugs.

The table I am inserting into is 'CO-OPStudentReg' with fields: LMHSCID,
sessionID, studentsID, ClassChoice. I don't have a primary key set or an
auto number field. The fields in the table look up values from four other
tables.

My form has LMHSCID (giving me info from a user initiated parameter query),
sessionID (again, info from parameter qry), studentsID (parameter qry), and
ClassChoice10 (selects info from my Classes table and sorts by the class
time), ClassChoice11 (info from same class table, just limiting to 11:00
time), ClassChoice1, ClassChoice2.

Essentially, I would like to enter up to four records into my table
depending on how many classes the student has chosen.

If I don't select any classes and click my button, the form clears as it is
supposed to, but no data is entered into table. As soon as I choose a class,
I get the error.

Thanks,
bjh29

Here is my code:
Private Sub btnADD_Click()

'**** add a new record ****
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb


If Not IsNull(ClassChoice10) Then
DoCmd.RunSQL "INSERT INTO CO-OPStudentReg
([LMHSCID],[sessionID],[studentsID],[ClassChoice]) VALUES (""" &
LMHSCID.Value & """,""" & sessionID.Value & """,""" & studentsID.Value &
""",""" & ClassChoice10.Value & """)"
End If
If Not IsNull(ClassChoice11) Then
DoCmd.RunSQL "INSERT INTO CO-OPStudentReg
([LMHSCID],[sessionID],[studentsID],[ClassChoice]) VALUES (""" &
LMHSCID.Value & "," & sessionID.Value & "," & studentsID.Value & "," &
ClassChoice11.Value & """)"
End If
If Not IsNull(CLassChoice1) Then
DoCmd.RunSQL "INSERT INTO CO-OPStudentReg
([LMHSCID],[sessionID],[studentsID],[ClassChoice]) VALUES (""" &
LMHSCID.Value & "," & sessionID.Value & "," & studentsID.Value & "," &
CLassChoice1.Value & """)"
End If
If Not IsNull(ClassChoice2) Then
DoCmd.RunSQL "INSERT INTO CO-OPStudentReg
([LMHSCID[,[sessionID],[studentsID],[ClassChoice]) VALUES (""" &
LMHSCID.Value & "," & sessionID.Value & "," & studentsID.Value & "," &
ClassChoice2.Value & """)"


MsgBox (Me!studentsID) & " has been added to the CO-OP
registration table."

End If

studentsID.Value = Null
ClassChoice10.Value = Null
ClassChoice11.Value = Null
CLassChoice1.Value = Null
ClassChoice2.Value = Null
End Sub

 

[bjh29]

Ugh! I just read in another post that I shouldn't have combo boxes in my
tables. Your comments on this are welcome as well!

Hi Steve, Thanks for responding. Feel free to reply as if I were a second
grader.

Here is the info you requested:

In my CO-OPStudentReg table LMHSCID, sessionID,studentsID, ClassChoice are
all number data types. They look up info in other tables. LMHSCID looks up
the LMHSCID, HusbandLastName, HusbandFirstName, WifeFirstName(user only sees
the name not the primary key- primary key is bound column), sessionID looks
up a sessionID and SessionName (user sees only name and not primary key- this
is like our semester of school), studentsID looks up a studentsID,
studentsLastName, studentsFirstName (again primary key not seen), ClassChoice
looks up ClassID, ClassName, GradeLevel (user only sees name and GradeLevel).

For my form: First the user sees a form where they select which family
(LMHSCID) and which session (sessionID) they would like to work with. Then
when the click an open button they come to my unbound form. The unbound form
is where my trouble is. I have a clear and exit buttons that work good.
Small miracle there. The form has 7 unbound combo boxes. First is the
LMHSCID, I have made it so the user is only able to choose the LMHSCID for
the family which they chose on the previous form, same for sessionID. The
studentsID give only students which are linked to that particular family.
The next four combo boxes are from the Classes table. The user is able to
choose a 10 am class, 11 am class, 1 pm class, 2 pm class (each combo box has
a parameter qry which narrows the classes by matching sessionID and
ClassTime) It allows the user to make up to four selections, one for each
hour of classes available to the students. They can choose between one and
four classes.

Example: The data I would like to insert to my CO-OPStudentReg table is:
LMHSCID, HusbandLastName, HusbandFirstName & WifeFirstName (30, Smith, John &
Jane), sessionID, SessionName (2, semester 2 06-07), studentsID,
StudentLastName, StudentFirstName (63, Smith, Johnny). ClassID, ClassName
(37, Art)

This example shows if they choose one class, if they choose more than one
(fields are not Null) I would like to insert the same LMHSCID, sessionID,
studentsID and then put in the second, third and/or fourth ClassID. It would
be AWESOME if I could make sure not duplicate choices were entered.

I toyed with the idea of having my CO-OPStudentReg table have four fields
for ClassChoice then I would have to mess with this unbound form. I could
just make a normal form and narrow the combo boxes with parameters. However,
when I go to make any reports about the students and classes, I would have to
do x4 because I couldn't just sort by time.

Thanks again!

bjh29

What are the data types of these fields? (number, text, date/time)

LMHSCID =
sessionID =
studentsID =
ClassChoice =


Can you provide a few examples of the data you wish to insert into the table?

LMHSCID | sessionID | studentsID | ClassChoice



--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Thank you in advance for any help.

I am trying to insert data from an unbound form into a table. I keep
getting the run-time error 3134. syntax error in INSERT INTO statement.

I have tried single quotes, quotes as you see below. I have tried
db.Execcute and the DoCmd you see below. I inherited this unfinished project
and trying to work out the bugs.

The table I am inserting into is 'CO-OPStudentReg' with fields: LMHSCID,
sessionID, studentsID, ClassChoice. I don't have a primary key set or an
auto number field. The fields in the table look up values from four other
tables.

My form has LMHSCID (giving me info from a user initiated parameter query),
sessionID (again, info from parameter qry), studentsID (parameter qry), and
ClassChoice10 (selects info from my Classes table and sorts by the class
time), ClassChoice11 (info from same class table, just limiting to 11:00
time), ClassChoice1, ClassChoice2.

Essentially, I would like to enter up to four records into my table
depending on how many classes the student has chosen.

If I don't select any classes and click my button, the form clears as it is
supposed to, but no data is entered into table. As soon as I choose a class,
I get the error.

Thanks,
bjh29

Here is my code:
Private Sub btnADD_Click()

'**** add a new record ****
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb


If Not IsNull(ClassChoice10) Then
DoCmd.RunSQL "INSERT INTO CO-OPStudentReg
([LMHSCID],[sessionID],[studentsID],[ClassChoice]) VALUES (""" &
LMHSCID.Value & """,""" & sessionID.Value & """,""" & studentsID.Value &
""",""" & ClassChoice10.Value & """)"
End If
If Not IsNull(ClassChoice11) Then
DoCmd.RunSQL "INSERT INTO CO-OPStudentReg
([LMHSCID],[sessionID],[studentsID],[ClassChoice]) VALUES (""" &
LMHSCID.Value & "," & sessionID.Value & "," & studentsID.Value & "," &
ClassChoice11.Value & """)"
End If
If Not IsNull(CLassChoice1) Then
DoCmd.RunSQL "INSERT INTO CO-OPStudentReg
([LMHSCID],[sessionID],[studentsID],[ClassChoice]) VALUES (""" &
LMHSCID.Value & "," & sessionID.Value & "," & studentsID.Value & "," &
CLassChoice1.Value & """)"
End If
If Not IsNull(ClassChoice2) Then
DoCmd.RunSQL "INSERT INTO CO-OPStudentReg
([LMHSCID[,[sessionID],[studentsID],[ClassChoice]) VALUES (""" &
LMHSCID.Value & "," & sessionID.Value & "," & studentsID.Value & "," &
ClassChoice2.Value & """)"


MsgBox (Me!studentsID) & " has been added to the CO-OP
registration table."

End If

studentsID.Value = Null
ClassChoice10.Value = Null
ClassChoice11.Value = Null
CLassChoice1.Value = Null
ClassChoice2.Value = Null
End Sub

 

[SteveS]

Ugh! I just read in another post that I shouldn't have combo boxes in my
tables. Your comments on this are welcome as well!

Yep, it just causes problems by hiding the true values in the fields. And
you usually duplicate the effort by using combo boxes on forms!!

Also:

Never use spaces in object names (forms,queries, field names, ect). Use
underscores or camel back : "LastName" or "Last_Name"

Always use a naming convention - and follow it.
For *fields* I use a three letter prefix of the data type (string - str,
long integers - lng, ect)
For *controls* I use the control type (combo box - cbo, text box - tb, etx)

There are lots of examples - in books, web sites....

Always turn off "Autocorrect". It is a well known cause of MDB corruption.

Use LOTS of comments in your code. In six months you might look at your code
and wonder "What was I thinking???". (BTDT)

And turn off the subdatasheet option. -- You should never let users
add/edit/delete records using a table. That is what forms are for.

 

[SteveS]

Sorry it took me so long to bet back to you.

Example: The data I would like to insert to my CO-OPStudentReg table is:
LMHSCID, HusbandLastName, HusbandFirstName & WifeFirstName (30, Smith, John &
Jane), sessionID, SessionName (2, semester 2 06-07), studentsID,
StudentLastName, StudentFirstName (63, Smith, Johnny). ClassID, ClassName
(37, Art)

If you store LMHSCID in the CO-OPStudentReg table,, you shouldn't also
store HusbandLastName, etc in the same table. You can look up those fields.

I toyed with the idea of having my CO-OPStudentReg table have four fields
for ClassChoice then I would have to mess with this unbound form. I could
just make a normal form and narrow the combo boxes with parameters. However,
when I go to make any reports about the students and classes, I would have to
do x4 because I couldn't just sort by time.

And you wouldn't have a normalized table structure. It would be easier in
the beginning, but later on you would want to slap yourself silly because of
the problems you would have trying to get queries/reports made that returns
the data you want.


So looking at your code, this is what I came up with.

In the table "CO-OPStudentReg", there are (at least) 4 fields: LMHSCID,
sessionID, studentsID &ClassChoice. These fields are FKs to other tables. The
data types are (should be): Number - long integers.

I couldn't do a lot of testing, but the following should work. You *are*
working/testing on a copy....right???

Here is the modified code:

- watch for line wrap -


'----beg code-----
Private Sub btnADD_Click()

'**** add a new record ****
Dim db As Database
Dim rs As Recordset

Dim strSQL As String
Dim strSQL_LU As String

Dim k As Integer 'k = kount of records added

Set db = CurrentDb

k = 0

'general SQL to insert records
'still needs the classchoice value from the combo boxes -
' get that from the IF() statements below
strSQL = "INSERT INTO CO-OPStudentReg ([LMHSCID], [sessionID],
[studentsID], [ClassChoice])"
strSQL = strSQL & " VALUES (" & Me.LMHSCID & "," & Me.sessionID & "," &
Me.studentsID & ","

'general SQL to search for records
'still needs the classchoice value from the combo boxes -
' get that from the IF() statements below
strSQL_LU = "SELECT [LMHSCID], [sessionID], [studentsID], [ClassChoice]
FROM CO-OPStudentReg "
strSQL_LU = strSQL_LU & " WHERE [LMHSCID] = " & Me.LMHSCID & " AND
[sessionID] = " & Me.[sessionID]
strSQL_LU = strSQL_LU & " AND [studentsID] = " & Me.studentsID & " AND
[ClassChoice] = "

'classchoice parameter checks
'first class time choice
If Not IsNull(ClassChoice10) Then
'open a recordset
Set rs = db.OpenRecordset(strSQL_LU & Me.ClassChoice10)
'check for records. If both BOF and EOF, then no records
If Not (rs.BOF And rs.EOF) Then
'### here you need to finish the alert for potential duplicate
record.
' if you use Me.ClassChoice10.Column(1)
' or whatever the column is that has the times (as text)
'you can tell which class would be duplicated.
MsgBox "Record exists for class" & Me.ClassChoice10.Column(1)
Else
'insert record
db.Execute strSQL & ClassChoice10 & ")", dbFailOnError
'increment kound of records added
k = k + 1
End If
rs.Close
End If

'second class time choice
If Not IsNull(ClassChoice11) Then
Set rs = db.OpenRecordset(strSQL_LU & Me.ClassChoice11)
If Not (rs.BOF And rs.EOF) Then
MsgBox "Record exists for class" & Me.ClassChoice11.Column(1)
Else
'insert record
db.Execute strSQL & ClassChoice11 & ")", dbFailOnError
k = k + 1
End If
rs.Close
End If

'third class time choice
If Not IsNull(ClassChoice1) Then
Set rs = db.OpenRecordset(strSQL_LU & Me.ClassChoice1)
If Not (rs.BOF And rs.EOF) Then
MsgBox "Record exists for class" & Me.ClassChoice1.Column(1)
Else
'insert record
db.Execute strSQL & ClassChoice1 & ")", dbFailOnError
k = k + 1
End If
rs.Close
End If

'fourth class time choice
If Not IsNull(ClassChoice2) Then
Set rs = db.OpenRecordset(strSQL_LU & Me.ClassChoice2)
If Not (rs.BOF And rs.EOF) Then
MsgBox "Record exists for class" & Me.ClassChoice2.Column(1)
Else
'insert record
db.Execute strSQL & ClassChoice2 & ")", dbFailOnError
k = k + 1
End If
rs.Close
End If

If k > 0 Then
MsgBox (Me!studentsID) & " has been added to the CO-OP registration
table."
Else
MsgBox "No records added!!"
End If
'instead of setting the controls to NULL, you might want to close this
form

'this
Me.studentsID = Null
Me.ClassChoice10 = Null
Me.ClassChoice11 = Null
Me.ClassChoice1 = Null
Me.ClassChoice2 = Null

'or this
DoCmd.Close acForm, Me.Name, acSaveNo
End Sub
'------ end code------------


Something you might think about. Instead of a combo box for "ClassChoice",
you could use a multi-select list box. The code would be changed a little,
but then you are not limited to 4 choices for the class times. You could add
1 or 6 class no problems. (If I understand what you are doing.... just a
thought)


HTH

 

[bjh29]

Thanks, Steve. I will look this over and see if I can follow you.

Yes, this is all just testing right now. I have another version that we use
in the mean time.

If you store LMHSCID in the CO-OPStudentReg table,, you shouldn't also

store HusbandLastName, etc in the same table. You can look up those fields.

I have since fixed this. All I see now when I look at the table is numbers,
I will pull the other data out with queries. I have actually cut back on
some field in my table because I can pull that out in queries as well. So
now my table just has studentsID and ClassChoice.

Something you might think about. Instead of a combo box for "ClassChoice",

you could use a multi-select list box. The code would be changed a little,
but then you are not limited to 4 choices for the class times. You could add
1 or 6 class no problems. (If I understand what you are doing.... just a
thought)

Good idea - I may not have more class times, but the times may change'instead of setting the controls to NULL, you might want to close this

form

I think I agree

I have code that kind of works, but doesn't check for duplicates (was
scratching my head about that when I rec'd notice of your post)

Question: Many students sign up for the same class, with the code you wrote
check for duplicate classes only? If is does I do I check if the student has
already signed up for the class?

Here is my current code (It looks way different than yours). I get snagged
if I don't choose a 11 am class. Can you see what the problem could be (says
it cannot find input table or qry).

Thanks!

Current code:
Private Sub btnADD_Click()

'**** add a new record ****
Dim strSQL As String
Dim db As Database
Set db = CurrentDb


If Not IsNull(ClassChoice10) Then
strSQL = "INSERT INTO [CO-OPStudentReg] ([studentsID],[ClassChoice])
VALUES (" & studentsID.Value & ", " & ClassChoice10.Value & ")"
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError
End If
If Not IsNull(ClassChoice11) Then
strSQL = "INSERT INTO [CO-OPStudentReg] ([studentsID],[ClassChoice])
VALUES (" & studentsID.Value & ", " & ClassChoice11.Value & ")"
End If
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError
If Not IsNull(CLassChoice1) Then
strSQL = "INSERT INTO [CO-OPStudentReg] ([studentsID],[ClassChoice])
VALUES (" & studentsID.Value & ", " & CLassChoice1.Value & ")"
End If
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError
If Not IsNull(ClassChoice2) Then
strSQL = "INSERT INTO [CO-OPStudentReg] ([studentsID],[ClassChoice])
VALUES (" & studentsID.Value & ", " & ClassChoice2.Value & ")"
End If
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError

MsgBox (Me!studentsID) & " has been added to the CO-OP
registration table."


End Sub

Sorry it took me so long to bet back to you.

Example: The data I would like to insert to my CO-OPStudentReg table is:
LMHSCID, HusbandLastName, HusbandFirstName & WifeFirstName (30, Smith, John &
Jane), sessionID, SessionName (2, semester 2 06-07), studentsID,
StudentLastName, StudentFirstName (63, Smith, Johnny). ClassID, ClassName
(37, Art)

If you store LMHSCID in the CO-OPStudentReg table,, you shouldn't also
store HusbandLastName, etc in the same table. You can look up those fields.

I toyed with the idea of having my CO-OPStudentReg table have four fields
for ClassChoice then I would have to mess with this unbound form. I could
just make a normal form and narrow the combo boxes with parameters. However,
when I go to make any reports about the students and classes, I would have to
do x4 because I couldn't just sort by time.

And you wouldn't have a normalized table structure. It would be easier in
the beginning, but later on you would want to slap yourself silly because of
the problems you would have trying to get queries/reports made that returns
the data you want.


So looking at your code, this is what I came up with.

In the table "CO-OPStudentReg", there are (at least) 4 fields: LMHSCID,
sessionID, studentsID &ClassChoice. These fields are FKs to other tables. The
data types are (should be): Number - long integers.

I couldn't do a lot of testing, but the following should work. You *are*
working/testing on a copy....right???

Here is the modified code:

- watch for line wrap -


'----beg code-----
Private Sub btnADD_Click()

'**** add a new record ****
Dim db As Database
Dim rs As Recordset

Dim strSQL As String
Dim strSQL_LU As String

Dim k As Integer 'k = kount of records added

Set db = CurrentDb

k = 0

'general SQL to insert records
'still needs the classchoice value from the combo boxes -
' get that from the IF() statements below
strSQL = "INSERT INTO CO-OPStudentReg ([LMHSCID], [sessionID],
[studentsID], [ClassChoice])"
strSQL = strSQL & " VALUES (" & Me.LMHSCID & "," & Me.sessionID & "," &
Me.studentsID & ","

'general SQL to search for records
'still needs the classchoice value from the combo boxes -
' get that from the IF() statements below
strSQL_LU = "SELECT [LMHSCID], [sessionID], [studentsID], [ClassChoice]
FROM CO-OPStudentReg "
strSQL_LU = strSQL_LU & " WHERE [LMHSCID] = " & Me.LMHSCID & " AND
[sessionID] = " & Me.[sessionID]
strSQL_LU = strSQL_LU & " AND [studentsID] = " & Me.studentsID & " AND
[ClassChoice] = "

'classchoice parameter checks
'first class time choice
If Not IsNull(ClassChoice10) Then
'open a recordset
Set rs = db.OpenRecordset(strSQL_LU & Me.ClassChoice10)
'check for records. If both BOF and EOF, then no records
If Not (rs.BOF And rs.EOF) Then
'### here you need to finish the alert for potential duplicate
record.
' if you use Me.ClassChoice10.Column(1)
' or whatever the column is that has the times (as text)
'you can tell which class would be duplicated.
MsgBox "Record exists for class" & Me.ClassChoice10.Column(1)
Else
'insert record
db.Execute strSQL & ClassChoice10 & ")", dbFailOnError
'increment kound of records added
k = k + 1
End If
rs.Close
End If

'second class time choice
If Not IsNull(ClassChoice11) Then
Set rs = db.OpenRecordset(strSQL_LU & Me.ClassChoice11)
If Not (rs.BOF And rs.EOF) Then
MsgBox "Record exists for class" & Me.ClassChoice11.Column(1)
Else
'insert record
db.Execute strSQL & ClassChoice11 & ")", dbFailOnError
k = k + 1
End If
rs.Close
End If

'third class time choice
If Not IsNull(ClassChoice1) Then
Set rs = db.OpenRecordset(strSQL_LU & Me.ClassChoice1)
If Not (rs.BOF And rs.EOF) Then
MsgBox "Record exists for class" & Me.ClassChoice1.Column(1)
Else
'insert record
db.Execute strSQL & ClassChoice1 & ")", dbFailOnError
k = k + 1
End If
rs.Close
End If

'fourth class time choice
If Not IsNull(ClassChoice2) Then
Set rs = db.OpenRecordset(strSQL_LU & Me.ClassChoice2)
If Not (rs.BOF And rs.EOF) Then
MsgBox "Record exists for class" & Me.ClassChoice2.Column(1)
Else
'insert record
db.Execute strSQL & ClassChoice2 & ")", dbFailOnError
k = k + 1
End If
rs.Close
End If

If k > 0 Then
MsgBox (Me!studentsID) & " has been added to the CO-OP registration
table."
Else
MsgBox "No records added!!"
End If
'instead of setting the controls to NULL, you might want to close this
form

'this
Me.studentsID = Null
Me.ClassChoice10 = Null
Me.ClassChoice11 = Null
Me.ClassChoice1 = Null
Me.ClassChoice2 = Null

'or this
DoCmd.Close acForm, Me.Name, acSaveNo
End Sub
'------ end code------------


Something you might think about. Instead of a combo box for "ClassChoice",
you could use a multi-select list box. The code would be changed a little,
but then you are not limited to 4 choices for the class times. You could add
1 or 6 class no problems. (If I understand what you are doing.... just a
thought)


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Hi Steve, Thanks for responding. Feel free to reply as if I were a second
grader.

Here is the info you requested:

In my CO-OPStudentReg table LMHSCID, sessionID,studentsID, ClassChoice are
all number data types. They look up info in other tables. LMHSCID looks up
the LMHSCID, HusbandLastName, HusbandFirstName, WifeFirstName(user only sees
the name not the primary key- primary key is bound column), sessionID looks
up a sessionID and SessionName (user sees only name and not primary key- this
is like our semester of school), studentsID looks up a studentsID,
studentsLastName, studentsFirstName (again primary key not seen), ClassChoice
looks up ClassID, ClassName, GradeLevel (user only sees name and GradeLevel).

For my form: First the user sees a form where they select which family
(LMHSCID) and which session (sessionID) they would like to work with. Then
when the click an open button they come to my unbound form. The unbound form
is where my trouble is. I have a clear and exit buttons that work good.
Small miracle there. The form has 7 unbound combo boxes. First is the
LMHSCID, I have made it so the user is only able to choose the LMHSCID for
the family which they chose on the previous form, same for sessionID. The
studentsID give only students which are linked to that particular family.
The next four combo boxes are from the Classes table. The user is able to
choose a 10 am class, 11 am class, 1 pm class, 2 pm class (each combo box has
a parameter qry which narrows the classes by matching sessionID and
ClassTime) It allows the user to make up to four selections, one for each
hour of classes available to the students. They can choose between one and
four classes.

Example: The data I would like to insert to my CO-OPStudentReg table is:
LMHSCID, HusbandLastName, HusbandFirstName & WifeFirstName (30, Smith, John &
Jane), sessionID, SessionName (2, semester 2 06-07), studentsID,
StudentLastName, StudentFirstName (63, Smith, Johnny). ClassID, ClassName
(37, Art)

This example shows if they choose one class, if they choose more than one
(fields are not Null) I would like to insert the same LMHSCID, sessionID,
studentsID and then put in the second, third and/or fourth ClassID. It would
be AWESOME if I could make sure not duplicate choices were entered.

I toyed with the idea of having my CO-OPStudentReg table have four fields
for ClassChoice then I would have to mess with this unbound form. I could
just make a normal form and narrow the combo boxes with parameters. However,
when I go to make any reports about the students and classes, I would have to
do x4 because I couldn't just sort by time.

Thanks again!

 

[SteveS]

Feel free to reply as if I were a second grader.

OK.... here goes. <big grin>

*Good programming practices*

1) The first two lines of any module should be:

Option Compare Database
Option Explicit


2) Comments are GOOD! It makes the code easier understand what you are
trying to do.

3) White space is GOOD! White space is the blank lines between lines of
code; like before and after an IF() function. It makes the code easier to
follow the logic. (see code example below)

4) Naming convention - strongly recommended. Access names bound controls the
same name as the field..... sometimes makes it hard to know which is which in
code....(for Access also). You can (and should!) change the control name.

5) Indent and outdent your code. Again, it makes it easier to follow the
logic and see errors in syntax and logic. (see code example below)

6) Value is the default property for the control and is not needed.
(studentsID and studentsID.Value both return the same thing)

7) When refering to controls on a form in code behind the form, use
Me.ControlName (as in Me.studentsID). It is a shortcut for
Forms!FormName.ControlName.

If you store LMHSCID in the CO-OPStudentReg table,, you shouldn't also

I have since fixed this. All I see now when I look at the table is numbers,
I will pull the other data out with queries. I have actually cut back on
some field in my table because I can pull that out in queries as well. So
now my table just has studentsID and ClassChoice.

I don't know what the field "LMHSCID" is, but since you have a combo box for
it, you might need it in the "CO-OPStudentReg"table.

I think you also need the sessionID. How will you know which session the
class/student is in?

I have code that kind of works, but doesn't check for duplicates (was
scratching my head about that when I rec'd notice of your post)

Question: Many students sign up for the same class, with the code you wrote
check for duplicate classes only? If is does I do I check if the student has
already signed up for the class?

It checks for duplicate LMHSCID, SessionID, studentsID & ClassChoice.

Here is my current code (It looks way different than yours). I get snagged
if I don't choose a 11 am class. Can you see what the problem could be (says
it cannot find input table or qry).

The code runs (for me), but there are logic errors. Single step thru the
code to find which line is causing the error. In the IDE, in the toolbar
select DEBUG/COMPILE. Fix any errors.


Look at this section of the code. What do you notice?

'------SNIP ---------
If Not IsNull(ClassChoice10) Then
strSQL = "INSERT INTO ........
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError
End If

If Not IsNull(ClassChoice11) Then
strSQL = "INSERT INTO ............
End If
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError

If Not IsNull(CLassChoice1) Then
strSQL = "INSERT INTO ............
End If
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError

If Not IsNull(ClassChoice2) Then
strSQL = "INSERT INTO ............
End If
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError

MsgBox (Me!studentsID) & " has been added to the CO-OP registration
table."
'----SNIP --------


I added the white space.... If you only have an entry for the combo box
"ClassChoice10", four identical records are added, not just one.

" CurrentDb.Execute strSQL" is outside the IF() statement for the last three
combo box checks.
So if the first combo box ("ClassChoice10") is NULL, and the next combo box
is NULL, the line, "CurrentDb.Execute strSQL", will try and insert a record
with NULLs which fails with the error message you are seeing. Ta-Dah!!


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Thanks!

Current code:
Private Sub btnADD_Click()

'**** add a new record ****
Dim strSQL As String
Dim db As Database
Set db = CurrentDb


If Not IsNull(ClassChoice10) Then
strSQL = "INSERT INTO [CO-OPStudentReg] ([studentsID],[ClassChoice])
VALUES (" & studentsID.Value & ", " & ClassChoice10.Value & ")"
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError
End If
If Not IsNull(ClassChoice11) Then
strSQL = "INSERT INTO [CO-OPStudentReg] ([studentsID],[ClassChoice])
VALUES (" & studentsID.Value & ", " & ClassChoice11.Value & ")"
End If
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError
If Not IsNull(CLassChoice1) Then
strSQL = "INSERT INTO [CO-OPStudentReg] ([studentsID],[ClassChoice])
VALUES (" & studentsID.Value & ", " & CLassChoice1.Value & ")"
End If
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError
If Not IsNull(ClassChoice2) Then
strSQL = "INSERT INTO [CO-OPStudentReg] ([studentsID],[ClassChoice])
VALUES (" & studentsID.Value & ", " & ClassChoice2.Value & ")"
End If
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError

MsgBox (Me!studentsID) & " has been added to the CO-OP
registration table."


End Sub

 

[bjh29]

Thank you so much for your time. I promise I will implement the good
programming practices. "Sheepish grin"

First though, since we are so close, I would like to finish this code so I
can file it for future use (after I fix things). I am designing this for a
non-profit that I run. We have been through 5 "experts" who later bailed
out. The last one gave me a bit of useful info (pointed me in the right
direction) but it was full of holes. Oh, well.

On to useful dialogue...
I decided (realized) I didn't need the LMHSCID (parents info) or the
sessionID because the LMHSCID is already linked to the studentsID and the
sessionID is linked to the Classes table.

My module starts with Option Compare Database but does not have Option
Explicit.
when I added it the following two subs stopped working (variable not defined.)
Can you tell me the how to change this? And where to put them?

If IsNull(ClassChoice10) And IsNull(ClassChoice11) And IsNull(CLassChoice1)
And IsNull(ClassChoice2) Then
MsgBox "Please select at least one class."
Cancel = True
End If

If IsNull(studentsID) Then
MsgBox "Please select a student."
Cancel = True
End If
Exit Sub


Here is my code as I think you showed me to do it. I am getting run-time
error 13 type mismatch. When I click debug it brings me to the line with:
Set rs = db.OpenRecordset(strSQL_LU & Me.ClassChoice10)

When I scroll over the different areas - all my controls show data (numbers)
rs.BOF =<Object variable or With Block variable not set> the same shows for
rs.EOF
dbFailOnError = 128 strSQL and strSQL_LU show the string
rs = Nothing

Thank you again - and again second grade level is good.
Current code:
******************************
Private Sub btnADD_Click()

'**** add a new record ****

Dim db As Database
Dim rs As Recordset

Dim strSQL As String
Dim strSQL_LU As String

Dim k As Integer 'k = kount of records added

Set db = CurrentDb

k = 0

' keep comments for future reference
'general SQL to insert records
'still needs the classchoice value from the combo boxes -
' get that from the IF() statements below

strSQL = "INSERT INTO [CO-OPStudentReg] ([studentsID],[ClassChoice])
VALUES (" & Me.studentsID & ","


'general SQL to search for records
'still needs the classchoice value from the combo boxes -
' get that from the IF() statements below

strSQL_LU = "SELECT [studentsID], [ClassChoice] FROM [CO-OPStudentReg]
WHERE [studentsID] = " & Me!studentsID & " AND [ClassChoice] = "
'classchoice parameter checks
'first class time choice
If Not IsNull(ClassChoice10) Then
'open a recordset
Set rs = db.OpenRecordset(strSQL_LU & Me.ClassChoice10)
'check for records. If both BOF and EOF, then no records
If Not (rs.BOF And rs.EOF) Then
'### here you need to finish the alert for potential duplicate
record.
' if you use Me.ClassChoice10.Column(1)
' or whatever the column is that has the times (as text)
'you can tell which class would be duplicated.
MsgBox "Student already enrolled in: " &
Me.ClassChoice10.Column(1)
Else
'insert record
db.Execute strSQL & ClassChoice10 & ")", dbFailOnError
'increment kound of records added
k = k + 1
End If
rs.Close
End If

'second class time choice
If Not IsNull(ClassChoice11) Then
Set rs = db.OpenRecordset(strSQL_LU & Me.ClassChoice11)
If Not (rs.BOF And rs.EOF) Then
MsgBox "Student already enrolled in: " &
Me.ClassChoice11.Column(1)
Else
'insert record
db.Execute strSQL, dbFailOnError
k = k + 1
End If
rs.Close
End If

'third class time choice
If Not IsNull(CLassChoice1) Then
Set rs = db.OpenRecordset(strSQL_LU & Me.CLassChoice1)
If Not (rs.BOF And rs.EOF) Then
MsgBox "Student already enrolled in: " & Me.CLassChoice1.Column(1)
Else
'insert record
db.Execute strSQL, dbFailOnError
k = k + 1
End If
rs.Close
End If

'fourth class time choice
If Not IsNull(ClassChoice2) Then
Set rs = db.OpenRecordset(strSQL_LU & Me.ClassChoice2)
If Not (rs.BOF And rs.EOF) Then
MsgBox "Student already enrolled in:" & Me.ClassChoice2.Column(1)
Else
'insert record
db.Execute strSQL, dbFailOnError
k = k + 1
End If
rs.Close
End If

If k > 0 Then
MsgBox (Me!studentsID.Column(1)) & " has been added to the CO-OP
registration table."
Else
MsgBox "No records added!!"
End If
'consider closing form instead of resetting


Me.studentsID = Null
Me.ClassChoice10 = Null
Me.ClassChoice11 = Null
Me.CLassChoice1 = Null
Me.ClassChoice2 = Null


End Sub

Feel free to reply as if I were a second grader.

OK.... here goes. <big grin>

*Good programming practices*

1) The first two lines of any module should be:

Option Compare Database
Option Explicit


2) Comments are GOOD! It makes the code easier understand what you are
trying to do.

3) White space is GOOD! White space is the blank lines between lines of
code; like before and after an IF() function. It makes the code easier to
follow the logic. (see code example below)

4) Naming convention - strongly recommended. Access names bound controls the
same name as the field..... sometimes makes it hard to know which is which in
code....(for Access also). You can (and should!) change the control name.

5) Indent and outdent your code. Again, it makes it easier to follow the
logic and see errors in syntax and logic. (see code example below)

6) Value is the default property for the control and is not needed.
(studentsID and studentsID.Value both return the same thing)

7) When refering to controls on a form in code behind the form, use
Me.ControlName (as in Me.studentsID). It is a shortcut for
Forms!FormName.ControlName.

If you store LMHSCID in the CO-OPStudentReg table,, you shouldn't also

I have since fixed this. All I see now when I look at the table is numbers,
I will pull the other data out with queries. I have actually cut back on
some field in my table because I can pull that out in queries as well. So
now my table just has studentsID and ClassChoice.

I don't know what the field "LMHSCID" is, but since you have a combo box for
it, you might need it in the "CO-OPStudentReg"table.

I think you also need the sessionID. How will you know which session the
class/student is in?

I have code that kind of works, but doesn't check for duplicates (was
scratching my head about that when I rec'd notice of your post)

Question: Many students sign up for the same class, with the code you wrote
check for duplicate classes only? If is does I do I check if the student has
already signed up for the class?

It checks for duplicate LMHSCID, SessionID, studentsID & ClassChoice.

Here is my current code (It looks way different than yours). I get snagged
if I don't choose a 11 am class. Can you see what the problem could be (says
it cannot find input table or qry).

The code runs (for me), but there are logic errors. Single step thru the
code to find which line is causing the error. In the IDE, in the toolbar
select DEBUG/COMPILE. Fix any errors.


Look at this section of the code. What do you notice?

'------SNIP ---------
If Not IsNull(ClassChoice10) Then
strSQL = "INSERT INTO ........
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError
End If

If Not IsNull(ClassChoice11) Then
strSQL = "INSERT INTO ............
End If
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError

If Not IsNull(CLassChoice1) Then
strSQL = "INSERT INTO ............
End If
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError

If Not IsNull(ClassChoice2) Then
strSQL = "INSERT INTO ............
End If
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError

MsgBox (Me!studentsID) & " has been added to the CO-OP registration
table."
'----SNIP --------


I added the white space.... If you only have an entry for the combo box
"ClassChoice10", four identical records are added, not just one.

" CurrentDb.Execute strSQL" is outside the IF() statement for the last three
combo box checks.
So if the first combo box ("ClassChoice10") is NULL, and the next combo box
is NULL, the line, "CurrentDb.Execute strSQL", will try and insert a record
with NULLs which fails with the error message you are seeing. Ta-Dah!!


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Thanks!

Current code:
Private Sub btnADD_Click()

'**** add a new record ****
Dim strSQL As String
Dim db As Database
Set db = CurrentDb


If Not IsNull(ClassChoice10) Then
strSQL = "INSERT INTO [CO-OPStudentReg] ([studentsID],[ClassChoice])
VALUES (" & studentsID.Value & ", " & ClassChoice10.Value & ")"
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError
End If
If Not IsNull(ClassChoice11) Then
strSQL = "INSERT INTO [CO-OPStudentReg] ([studentsID],[ClassChoice])
VALUES (" & studentsID.Value & ", " & ClassChoice11.Value & ")"
End If
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError
If Not IsNull(CLassChoice1) Then
strSQL = "INSERT INTO [CO-OPStudentReg] ([studentsID],[ClassChoice])
VALUES (" & studentsID.Value & ", " & CLassChoice1.Value & ")"
End If
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError
If Not IsNull(ClassChoice2) Then
strSQL = "INSERT INTO [CO-OPStudentReg] ([studentsID],[ClassChoice])
VALUES (" & studentsID.Value & ", " & ClassChoice2.Value & ")"
End If
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError

MsgBox (Me!studentsID) & " has been added to the CO-OP
registration table."


End Sub