Using SQL in VBA - inserting text

[echorley]

I cannot seem to get this portion of code to work:

Dim StrSQL As String
Dim StudName As String

StudName = Me.StudentName

StrSQL = "INSERT INTO Attend (StudentName) " & "SELECT " & StudName & ";"

DoCmd.RunSQL StrSQL

Where "StudentName" is the field name with data type "text" in the table "Attend". When I type a name into the textbox "StudentName", and I run the code, a pop up asks me for a paramater value.

However, the code below works find if the items I am inserting into the table Attend are numbers or dates. For example:

Dim StrSQL As String
Dim StudNumber As Long

StudNumber = Me.StudentNumber

StrSQL = "INSERT INTO Attend (StudentNumber) " & "SELECT " & StudNumber & ";"

DoCmd.RunSQL StrSQL

works perfectly when I type a number in the text box "StudentNumber."

What am I missing? Thanks.

 

[WayneRyan]

echorley,

DoCmd.RunSQL "Insert Into Attend (StudentName) " & _
             "Values ('" & Me.StudName & "');"

But, you can accomplish the same thing by binding the textbox StudName
to your Attend table on the form.

Wayne

 

[echorley]

Found my mistake...

I need to use Paramaters to declare it is text. I am having some difficulty with how to type it in VBA. This gives me an error.

StrSQL = "PARAMATERS [StudName:] Text ;" & "INSERT INTO Attend (StudentName) " & "SELECT " & StudName & ";"

Thanks.

 

[echorley]

Thanks for the

bit of code. This is actually a small part to a larger piece of code I am trying to adapt. The code below (I found on this forum) inserts the student number, date and attendance code when you click on a calandar.

StrSQL = "INSERT INTO Attend ( AttStudent, AttDate, AttType, Term ) " _
& "SELECT " & Me![scrStudent] & " AS F1, #" _
& Format(TDate, "mm/dd/yy") & "# AS F2, " & TypeAttend & " AS F3, " & CurrentTerm & " AS F4;"

I was trying to break it down to its simpliest parts to see how it worked and then tried to get the "query" to insert the actual student name in the table.

 

[WayneRyan]

echorley,

You are not getting StudName from a table!

Isn't it just a textbox on your form?

Need more info ... it seems like you're making it too complicated.

Wayne

 

[echorley]

StudName is a control on my form.

Here is the project I am working on. I found the basic part on the forum and adjusted it to my needs. All I want is the student's name to be passed on (Insert Into) to the "Attend" table along with the other information (Date, type of absence, student number, term).
Thanks.

 

[WayneRyan]

echorley,

Interesting little form. I changed your insert. You only use the format -->

Insert Into ... Select From ...

when you inserting into one table from another. For this single-row insert
you need --> Insert Into ... Values ().

If IsNull(RecDetect) Then
   StrSQL = "INSERT INTO Attend ( AttStudent, AttDate, AttType, Term) " & _
            "Values(" & Me.scrStudent & ", #" & _
                        Format(TDate, "mm/dd/yy") & "#, " & _
                        TypeAttend & ", " & _
                        CurrentTerm & ");"
   DoCmd.RunSQL StrSQL
Else
   StrSQL = "UPDATE Attend " & _
            "SET    Attend.AttType = " & TypeAttend & " " & _
            "WHERE  Attend.AttStudent = " & Me![scrStudent] & " AND " & _
            "       Attend.AttDate    = #" & Format(TDate, "mm/dd/yy") & "#;"
   DoCmd.RunSQL StrSQL
End If

hth,
Wayne

 

[echorley]

Same error

I copied and pasted your code changes and they worked well. But when I try to add the student name to the code, I get a syntax error (missing operator).

CurrentTerm = Me.Term
StudName = Me.StudentName

RecDetect = DLookup("[scrStudent]", "Attend", "[AttStudent] = " & Me![scrStudent] & " AND [AttDate] = #" & Format(TDate, "mm/dd/yy") & "#")

If IsNull(RecDetect) Then
StrSQL = "INSERT INTO Attend ( AttStudent, AttDate, AttType, Term, StudentName) " & _
"Values(" & Me.scrStudent & ", #" _
& Format(TDate, "mm/dd/yy") & "#, " _
& TypeAttend & ", " & CurrentTerm & ", " _
& StudName & ");"

DoCmd.RunSQL StrSQL
Else
StrSQL = "UPDATE Attend " & _
"SET Attend.AttType = " & TypeAttend & " " & _
"WHERE Attend.AttStudent = " & Me![scrStudent] & " AND " & _
" Attend.AttDate = #" & Format(TDate, "mm/dd/yy") & "#;"
DoCmd.RunSQL StrSQL
End If

What do you think?

Thanks.

 

[WayneRyan]

echorley,

When you are generating sql:

String: sql = "[Field] = '" & Me.SomeField & "'" <-- Single-Quote
Date: sql = "[Field] = #" & Me.SomeField & "#" <-- Pound Sign
Number: sql = "[Field] = " & Me.SomeField <-- No delimiter

Wayne

 

[echorley]

Yes!

That did it. Thanks for seeing this through with me.