DoCmd.RunSQL() not reading variable from combo box

Help! I don't know why this isn't working:

Private Sub Combo9_Change()

Dim UsersCourseSelection As String
UsersCourseSelection = Me("Combo9").Value
Combo13.Visible = True

'the following SQL thing is all on one line in the actual code.

DoCmd.RunSQL ("INSERT INTO TEMP(courseCode, studentID) SELECT
studentsInCourses.courseCode, studentsInCourses.studentID FROM
studentsInCourses WHERE (courseCode=Me!Combo9.Value);")

End Sub

The combo box correctly reads the data into the variable, but for some
reason the SQL line doesn't read the value. I also tried the following:

Private Sub Combo9_Change()

Dim UsersCourseSelection As String
UsersCourseSelection = Me("Combo9").Value
Combo13.Visible = True

'the following SQL thing is all on one line in the actual code.

DoCmd.RunSQL ("INSERT INTO TEMP(courseCode, studentID) SELECT
studentsInCourses.courseCode, studentsInCourses.studentID FROM
studentsInCourses WHERE (courseCode=UsersCourseSelection);")

End Sub

HERE'S WHAT HAPPENED: When the query runs it prompts for the value, then
inserts the proper data according to the courseCode that I enter.

Is there a better way to get the variable information in there?

re: DoCmd.RunSQL() not reading variable from combo box

You are reading in the variable correctly with the first part of the code.
however in your RunSQL command you have everything within the quotation
marks "" therefore it is taken literally (a string) and does not use the
value stored in the UsersCourseSelection variable.

Have another variable strSQL (as string)
build this variable as (I've broken this down to really show things, you
could do it all in one hit if you like. The following would be three lines
of code)

strSQL = ("INSERT INTO TEMP(courseCode, studentID) SELECT
studentsInCourses.courseCode, studentsInCourses.studentID FROM
studentsInCourses WHERE (courseCode= "
strSql = strSql & UsersCourseSelection
strSql = strSql & " )"

note how the literal strings are encased in quotes and the variables are
not.
you can then easily test eaxctly what data is being fed to the RunSql
command with a
debug.print strSql

then simply
DoCmd.RunSQL strSql


hth
Mal.


"Richard Hollenbeck" <richard.hollenbeck@verizon.net> wrote in message
news:apDLc.39994$lz2.25175@nwrddc03.gnilink.net...[color=blue]
> Help! I don't know why this isn't working:
>
> Private Sub Combo9_Change()
>
> Dim UsersCourseSelection As String
> UsersCourseSelection = Me("Combo9").Value
> Combo13.Visible = True
>
> 'the following SQL thing is all on one line in the actual code.
>
> DoCmd.RunSQL ("INSERT INTO TEMP(courseCode, studentID) SELECT
> studentsInCourses.courseCode, studentsInCourses.studentID FROM
> studentsInCourses WHERE (courseCode=Me!Combo9.Value);")
>
> End Sub
>
> The combo box correctly reads the data into the variable, but for some
> reason the SQL line doesn't read the value. I also tried the following:
>
> Private Sub Combo9_Change()
>
> Dim UsersCourseSelection As String
> UsersCourseSelection = Me("Combo9").Value
> Combo13.Visible = True
>
> 'the following SQL thing is all on one line in the actual code.
>
> DoCmd.RunSQL ("INSERT INTO TEMP(courseCode, studentID) SELECT
> studentsInCourses.courseCode, studentsInCourses.studentID FROM
> studentsInCourses WHERE (courseCode=UsersCourseSelection);")
>
> End Sub
>
> HERE'S WHAT HAPPENED: When the query runs it prompts for the value, then
> inserts the proper data according to the courseCode that I enter.
>
> Is there a better way to get the variable information in there?
>
>[/color]

re: DoCmd.RunSQL() not reading variable from combo box

It is because you have included the "reference" to the value inside a quoted
string. Try this, instead, which will pick up the value from the Combo and
concatenate it into the string.

DoCmd.RunSQL ("INSERT INTO TEMP(courseCode, studentID) SELECT
studentsInCourses.courseCode, studentsInCourses.studentID FROM
studentsInCourses WHERE (courseCode=" & Me!Combo9 & ");")

I removed ".Value" -- that's the default property, so you don't need to code
it specifically.

Larry Linson
Microsoft Access MVP



"Richard Hollenbeck" <richard.hollenbeck@verizon.net> wrote in message
news:apDLc.39994$lz2.25175@nwrddc03.gnilink.net...[color=blue]
> Help! I don't know why this isn't working:
>
> Private Sub Combo9_Change()
>
> Dim UsersCourseSelection As String
> UsersCourseSelection = Me("Combo9").Value
> Combo13.Visible = True
>
> 'the following SQL thing is all on one line in the actual code.
>
> DoCmd.RunSQL ("INSERT INTO TEMP(courseCode, studentID) SELECT
> studentsInCourses.courseCode, studentsInCourses.studentID FROM
> studentsInCourses WHERE (courseCode=Me!Combo9.Value);")
>
> End Sub
>
> The combo box correctly reads the data into the variable, but for some
> reason the SQL line doesn't read the value. I also tried the following:
>
> Private Sub Combo9_Change()
>
> Dim UsersCourseSelection As String
> UsersCourseSelection = Me("Combo9").Value
> Combo13.Visible = True
>
> 'the following SQL thing is all on one line in the actual code.
>
> DoCmd.RunSQL ("INSERT INTO TEMP(courseCode, studentID) SELECT
> studentsInCourses.courseCode, studentsInCourses.studentID FROM
> studentsInCourses WHERE (courseCode=UsersCourseSelection);")
>
> End Sub
>
> HERE'S WHAT HAPPENED: When the query runs it prompts for the value, then
> inserts the proper data according to the courseCode that I enter.
>
> Is there a better way to get the variable information in there?
>
>[/color]

re: DoCmd.RunSQL() not reading variable from combo box

I tried both of your answers (practically the same.) Thanks. It still
doesn't work. Here's what I did and then I'll tell you what happened:

Private Sub cmboSelectCourse_Change()
Dim strSql As String
Dim courseChoice As String

courseChoice = Me("cmboselectcourse")

strSql = "INSERT INTO TEMP(studentID, courseCode) SELECT
studentsInCourses.studentID, studentsInCourses.courseCode FROM
studentsInCourses WHERE(studentsInCourses.courseCode="
strSql = strSql & courseChoice
strSql = strSql & ");"

DoCmd.RunSQL strSql

End Sub

I got a run time error 3464 Data Type Mismatch in Expressions. I thought it
might be the fact that Course Code is a string with only numbers and VBA may
be interpreting it as a number. So I tried
str(courseChoice) but that didn't do the trick either.

I'll look up the error code. Thanks. Oh, I did get it out to an append
action but it found zero records, yet I know there are almost fifty records.


"Mal Reeve" <lorimal@earthlink.net> wrote in message
news:2zFLc.8841$f4.3942@newsread3.news.atl.earthli nk.net...[color=blue]
> You are reading in the variable correctly with the first part of the[/color]
code.[color=blue]
> however in your RunSQL command you have everything within the quotation
> marks "" therefore it is taken literally (a string) and does not use the
> value stored in the UsersCourseSelection variable.
>
> Have another variable strSQL (as string)
> build this variable as (I've broken this down to really show things, you
> could do it all in one hit if you like. The following would be three lines
> of code)
>
> strSQL = ("INSERT INTO TEMP(courseCode, studentID) SELECT
> studentsInCourses.courseCode, studentsInCourses.studentID FROM
> studentsInCourses WHERE (courseCode= "
> strSql = strSql & UsersCourseSelection
> strSql = strSql & " )"
>
> note how the literal strings are encased in quotes and the variables are
> not.
> you can then easily test eaxctly what data is being fed to the RunSql
> command with a
> debug.print strSql
>
> then simply
> DoCmd.RunSQL strSql
>
>
> hth
> Mal.
>[/color]

re: DoCmd.RunSQL() not reading variable from combo box

RunSQL (which can be run from a macro) doesn't know which
form 'Me' is. If you want to refer to a control in RunSQL,
you need to use Forms!myform!myctl.

Or you can use Me!myctl in VBA to get a value, like you
have done, and then use that value in RunSQL or Execute:

.... (courseCode = " & UsersCourseSelection & ") ...

(david)

With RunSQL, you can either refer to a fixed value, like '3',
or to a VBA function, or to a control on a form. On any form.
"Richard Hollenbeck" <richard.hollenbeck@verizon.net> wrote in message
news:apDLc.39994$lz2.25175@nwrddc03.gnilink.net...[color=blue]
> Help! I don't know why this isn't working:
>
> Private Sub Combo9_Change()
>
> Dim UsersCourseSelection As String
> UsersCourseSelection = Me("Combo9").Value
> Combo13.Visible = True
>
> 'the following SQL thing is all on one line in the actual code.
>
> DoCmd.RunSQL ("INSERT INTO TEMP(courseCode, studentID) SELECT
> studentsInCourses.courseCode, studentsInCourses.studentID FROM
> studentsInCourses WHERE (courseCode=Me!Combo9.Value);")
>
> End Sub
>
> The combo box correctly reads the data into the variable, but for some
> reason the SQL line doesn't read the value. I also tried the following:
>
> Private Sub Combo9_Change()
>
> Dim UsersCourseSelection As String
> UsersCourseSelection = Me("Combo9").Value
> Combo13.Visible = True
>
> 'the following SQL thing is all on one line in the actual code.
>
> DoCmd.RunSQL ("INSERT INTO TEMP(courseCode, studentID) SELECT
> studentsInCourses.courseCode, studentsInCourses.studentID FROM
> studentsInCourses WHERE (courseCode=UsersCourseSelection);")
>
> End Sub
>
> HERE'S WHAT HAPPENED: When the query runs it prompts for the value, then
> inserts the proper data according to the courseCode that I enter.
>
> Is there a better way to get the variable information in there?
>
>[/color]

re: DoCmd.RunSQL() not reading variable from combo box

On Thu, 22 Jul 2004 04:41:40 GMT, Richard Hollenbeck wrote:
[color=blue]
> I tried both of your answers (practically the same.) Thanks. It still
> doesn't work. Here's what I did and then I'll tell you what happened:
>
> Private Sub cmboSelectCourse_Change()
> Dim strSql As String
> Dim courseChoice As String
>
> courseChoice = Me("cmboselectcourse")
>
> strSql = "INSERT INTO TEMP(studentID, courseCode) SELECT
> studentsInCourses.studentID, studentsInCourses.courseCode FROM
> studentsInCourses WHERE(studentsInCourses.courseCode="
> strSql = strSql & courseChoice
> strSql = strSql & ");"
>
> DoCmd.RunSQL strSql
>
> End Sub
>
> I got a run time error 3464 Data Type Mismatch in Expressions. I thought it
> might be the fact that Course Code is a string with only numbers and VBA may
> be interpreting it as a number. So I tried
> str(courseChoice) but that didn't do the trick either.
>
> I'll look up the error code. Thanks. Oh, I did get it out to an append
> action but it found zero records, yet I know there are almost fifty records.
>
> "Mal Reeve" <lorimal@earthlink.net> wrote in message
> news:2zFLc.8841$f4.3942@newsread3.news.atl.earthli nk.net...[color=green]
>> You are reading in the variable correctly with the first part of the[/color]
> code.[color=green]
>> however in your RunSQL command you have everything within the quotation
>> marks "" therefore it is taken literally (a string) and does not use the
>> value stored in the UsersCourseSelection variable.
>>
>> Have another variable strSQL (as string)
>> build this variable as (I've broken this down to really show things, you
>> could do it all in one hit if you like. The following would be three lines
>> of code)
>>
>> strSQL = ("INSERT INTO TEMP(courseCode, studentID) SELECT
>> studentsInCourses.courseCode, studentsInCourses.studentID FROM
>> studentsInCourses WHERE (courseCode= "
>> strSql = strSql & UsersCourseSelection
>> strSql = strSql & " )"
>>
>> note how the literal strings are encased in quotes and the variables are
>> not.
>> you can then easily test eaxctly what data is being fed to the RunSql
>> command with a
>> debug.print strSql
>>
>> then simply
>> DoCmd.RunSQL strSql
>>
>>
>> hth
>> Mal.
>>[/color][/color]

Here is your answer:[color=blue]
> I thought it might be the fact that Course Code is a string with only numbers and VBA may
> be interpreting it as a number.[/color]

When you write the where clause for a variable that is a Number
DATATYPE, you write it outside the quotes, as indicated above.
.... Where CourseCode = " & UsersCourseSelection & ");"
The resulting SQL clause, with data, will look like this ...
..... Where (CourseCode =12345);"

However, if the variable is a string datatype (even if it's just
numbers in the string), you must write the clause a bit differently:

WHERE (courseCode= '" & UsersCourseSelection & "');"

With spaces added for clarity it looks like this:
WHERE (courseCode= ' " & UsersCourseSelection & " ' );"

The resulting SQL clause, with data, will look like this ...
..... Where (CourseCode ='12345');"
note the CourseCode value is within single quotes.

The entire SQL should look like this:

strSql = "INSERT INTO TEMP(studentID, courseCode) SELECT
studentsInCourses.studentID, studentsInCourses.courseCode FROM
studentsInCourses WHERE(studentsInCourses.courseCode='"
strSql = strSql & courseChoice & "');"

See Access help files on
Where clause + restrict data to a subset of records + Text Datatype

I hope this helps.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

re: DoCmd.RunSQL() not reading variable from combo box

Thank you everybody! I got it. It's very tricky. Single, double quotes,
whether to put parentheses or semicolons, etc. But I got it. Thanks.

"Richard Hollenbeck" <richard.hollenbeck@verizon.net> wrote in message
news:apDLc.39994$lz2.25175@nwrddc03.gnilink.net...[color=blue]
> Help! I don't know why this isn't working:
>
> Private Sub Combo9_Change()
>
> Dim UsersCourseSelection As String
> UsersCourseSelection = Me("Combo9").Value
> Combo13.Visible = True
>
> 'the following SQL thing is all on one line in the actual code.
>
> DoCmd.RunSQL ("INSERT INTO TEMP(courseCode, studentID) SELECT
> studentsInCourses.courseCode, studentsInCourses.studentID FROM
> studentsInCourses WHERE (courseCode=Me!Combo9.Value);")
>
> End Sub
>
> The combo box correctly reads the data into the variable, but for some
> reason the SQL line doesn't read the value. I also tried the following:
>
> Private Sub Combo9_Change()
>
> Dim UsersCourseSelection As String
> UsersCourseSelection = Me("Combo9").Value
> Combo13.Visible = True
>
> 'the following SQL thing is all on one line in the actual code.
>
> DoCmd.RunSQL ("INSERT INTO TEMP(courseCode, studentID) SELECT
> studentsInCourses.courseCode, studentsInCourses.studentID FROM
> studentsInCourses WHERE (courseCode=UsersCourseSelection);")
>
> End Sub
>
> HERE'S WHAT HAPPENED: When the query runs it prompts for the value, then
> inserts the proper data according to the courseCode that I enter.
>
> Is there a better way to get the variable information in there?
>
>[/color]

re: DoCmd.RunSQL() not reading variable from combo box

"Richard Hollenbeck" <richard.hollenbeck@verizon.net> wrote in message news:<yBJLc.16971$Iz3.9944@nwrddc01.gnilink.net>.. .[color=blue]
> Thank you everybody! I got it. It's very tricky. Single, double quotes,
> whether to put parentheses or semicolons, etc. But I got it. Thanks.
>
> "Richard Hollenbeck" <richard.hollenbeck@verizon.net> wrote in message
> news:apDLc.39994$lz2.25175@nwrddc03.gnilink.net...[color=green]
> > Help! I don't know why this isn't working:
> >
> > Private Sub Combo9_Change()
> >
> > Dim UsersCourseSelection As String
> > UsersCourseSelection = Me("Combo9").Value
> > Combo13.Visible = True
> >
> > 'the following SQL thing is all on one line in the actual code.
> >
> > DoCmd.RunSQL ("INSERT INTO TEMP(courseCode, studentID) SELECT
> > studentsInCourses.courseCode, studentsInCourses.studentID FROM
> > studentsInCourses WHERE (courseCode=Me!Combo9.Value);")
> >
> > End Sub
> >[/color][/color]
Richard,

I always create an SQL string so I can see the actual values that
were pulled from controls, et cetera. Try this.

strSQL = "INSERT INTO TEMP(courseCode, studentID) SELECT
studentsInCourses.courseCode, studentsInCourses.studentID FROM
studentsInCourses WHERE (courseCode=Me!Combo9.Value);"

DoCmd.RunSQL strSQL

Then that combo box value can be examined in the debugger and the
problem will probably be obvious to you.

Hank Reed