Almost Embarrassed to Ask Insert Questino 2

[JAES]

I've been away from Access for years and simple things seem to baffle me.
DoCmd.RunSQL "INSERT INTO tStudents (StudentID) VALUES (StudentID) "

The Student ID is an Integer. As I close the current form I want to insert the StudentID value into the table tStudents Column StudentID

Any help is appreciated

 

[dhookom]

Try this:

DoCmd.RunSQL "INSERT INTO tStudents (StudentID) VALUES " & Me.StudentID

Duane
Hook'D on Access
MS Access MVP

 

[JAES]

Thanks Duane, It still has an error and the line is highlited. The Me.StudentID has the correct value though. The table I'm working with currently is named tActivities. Once I get it working I will use is other places.

Private Sub Command116_Click()
DoCmd.RunSQL "INSERT INTO tActivities (StudentID) VALUES " & Me.StudentID
DoCmd.Close acForm, "frmAddNewStudent"
End Sub

The Table tActivities has the following:
Field Name StudentID data type is Number (long Integer)
10 other fields with Short Text and Numbers

Thank you

 

[dhookom]

What is the error message?

Duane
Hook'D on Access
MS Access MVP

 

[JAES]

Run-Time error '3134'
Syntax error on Insert Into Statement.

I'm trying different tables and different fields and the result is the same. But the Me. statement always has the data I'm trying to insert.

 

[JAES]

OK, all of a sudden this works - DoCmd.RunSQL "INSERT INTO tActivities (StudentID) VALUES (StudentID)". My StudentID was 7 digits long and I had them formatted as Integer. One of the few things I did was change them to Text.

 

[dhookom]

I would be very surprised if this worked:

DoCmd.RunSQL "INSERT INTO tActivities (StudentID) VALUES (StudentID)"

Duane
Hook'D on Access
MS Access MVP

 

[JAES]

I don't know what t say. Here is the code. It is the click event for closing the form. I need to populate the other three tables with only the StudentID. This code does that. If something goes terribly wrong later I will know where to look!

Private Sub Command116_Click()
Dim StudentID As Integer
DoCmd.RunSQL "INSERT INTO tActivities (StudentID) VALUES (StudentID)"
DoCmd.RunSQL "INSERT INTO tEmContact (StudentID) Values (StudentID)"
DoCmd.RunSQL "INSERT INTO tInteractions (StudentID) Values (StudentID)"
DoCmd.Close acForm, "frmAddNewStudent"
End Sub

 

[Andrzejek]

JAES,
You declare [tt]StudentID[/tt] but never assign any value to it.

You may also consider this:

Private Sub Command116_Click()
Dim StudentID As Integer
Dim strSQL As String

StudentID = [red]???[/red]

strSQL = "INSERT INTO tActivities (StudentID) VALUES (" & StudentID & ")"
DoCmd.RunSQL strSQL

strSQL = "INSERT INTO tEmContact (StudentID) Values (" & StudentID & ")"
DoCmd.RunSQL strSQL

strSQL = "INSERT INTO tInteractions (StudentID) Values (" & StudentID & ")"
DoCmd.RunSQL strSQL

DoCmd.Close acForm, "frmAddNewStudent"
End Sub

This way you can examine your INSERT statement before is executed.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[JAES]

Thanks Duane and Andy. I had changed the value of StudentID to String but didn't change the declare statement. The latest code works great by changing the declare to string. It's amazing how much you lose not doing this for 7 years. Hopefully this will wake up some memory cells! Thank you again.