Syntax for variables in docmd.runsql

Spocks

New member
Local time
Today, 21:58
Joined
Aug 25, 2013
Messages
9
Hi all. I'm trying to execute an sql query with variables, but it won't work. Code is below.

DoCmd.RunSQL "INSERT INTO Students ([Student ID], [Student Name], [House]) VALUES (" & TempID & ", " & TempName & ", " & TempHouse & ")"

The TempName and TempHouse won't work and parameter value boxes appear.
 
The TempName and TempHouse won't work
What are the TempName and TempHouse? Variables? Controls? What do the parameter boxes prompt for?
 
TempName and TempHouse are variables.
The parameter value boxes pop up with the title "enter parameter value" with the text being the value of Variable.
 
Private Sub cmdNew_Click()
Dim Response As Integer

Response = (MsgBox("Are you a new student?", vbYesNo, "New Student"))

If Response = 6 Then
TempName = InputBox("Please enter your name", "Enter A Name")
TempID = InputBox("Please enter your student number", "Enter a student number")
TempHouse = InputBox("Please enter your house", "Enter a house")
End If

Response = (MsgBox("Is this information correct?" & vbCrLf & "Student Name is: " + TempName & vbCrLf & "Student ID: " + TempID & vbCrLf & "House is: " + TempHouse, vbYesNo, "Check Entered Information"))

If Response = 6 Then
DoCmd.RunSQL "INSERT INTO Students ([Student ID], [Student Name], [House]) VALUES (" & TempID & ", " & TempName & ", " & TempHouse & ")"
End If


End Sub
 
You need to wrap the String variables in either Single Quotes or Double quotes. Try the following,
Code:
Private Sub cmdNew_Click()
    If MsgBox("Are you a new student?", vbYesNo, "New Student") = vbYes Then
        TempName = InputBox("Please enter your name", "Enter A Name")
        TempID = InputBox("Please enter your student number", "Enter a student number")
        TempHouse = InputBox("Please enter your house", "Enter a house")
    End If
    
    If MsgBox("Is this information correct?" & vbCrLf & _
              "Student Name : " & TempName & vbCrLf & _
              "Student ID : " & TempID & vbCrLf & _
              "House : " & TempHouse , vbYesNo, "Check Entered Information") = vbYes Then
        DoCmd.RunSQL "INSERT INTO Students ([Student ID], [Student Name], [House]) VALUES (" & _
                        TempID & ", " & Chr(34) & TempName & Chr(34) & ", " & Chr(34) & TempHouse & Chr(34) & ")"
    End If
End Sub
Things to Ponder over :
1. What happens if it is not a New Student?
2.What happens if the details entered needs correction?
3. What happens if no value was entered in the input box?
 
if you want to run sql any text values must be enclosed by " and dates by #, numbers dont need to be enclosed....

Example assuming TempName is a text TempHouse is a date and TempID is a number
DoCmd.RunSQL "INSERT INTO Students ([Student ID], [Student Name], [House]) VALUES (" & TempID & ", """ & TempName & """ , #" & TempHouse & "#)"
 
Just so it's noted, the most reliable solution is to use a QueryDef, like . . .
Code:
const SQL as string = _
   "INSERT INTO Students " & _
      "( StudentID, StudentName, House ) " & _
   "VALUES " & _
      "( p0, p1, p2 )"

with currentdb.createquerydef("", sql)
   .parameters(0) = TempID
   .parameters(1) = TempName
   .parameters(1) = House
   .execute dbFailOnError
   .close
end with
Note how delimiting the values to be inserted is all handled automatically here, and the readability of the code is pretty good too. It's clear at a glance what is happening.
 

Users who are viewing this thread

Back
Top Bottom