Run-Time error '3061': executing SQL query using DAO

[Gurvinder]

I am picking up VBA after 6 years and need help with running a SQL
query from Word to get data from Access MDB file.

Error:
Run-Time error '3061':
Too few parameters. Expected 1


' Debug Window at the time of the error
debug.Print strsql
SELECT [Oral_Communication] FROM MidYearComments WHERE ([Grade]=1) AND
([GradingCode]='E');

VBA Code in Word Docment
<snip>
Public Function GetData(ByVal tblName As String, ByVal fldName As
String, ByVal NumGrade As Integer, ByVal StrGradingCode As String) As
String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long

' Numbers in Grade in Access range from
' -1 for JKG and 0 for KG
' followed by 1 - 12 for grades.

If Str(NumGrade) = "JKG" Then
NumGrade = -1
ElseIf Str(NumGrade) = "KG" Then
NumGrade = 0
End If

' Open the database
Set db = OpenDatabase("C:\Reports\Comments.mdb")
Dim strSQL As String

' Retrieve the recordset
' This one works fine
' Set rs = db.OpenRecordset("SELECT [Oral_Communication] FROM " _
' & "MidYearComments")

' Retrieve the recordset
' This one generates the error
strSQL = "SELECT [" & fldName & "] FROM " & tblName & " " _
& "WHERE ([Grade]=" & Int(NumGrade) & ") AND " _
& "([GradingCode]='" & StrGradingCode & "');"
Set rs = db.OpenRecordset(strSQL) , dbOpenSnapshot)




' Determine the number of retrieved records
With rs

.MoveLast

NoOfRecords = .RecordCount

.MoveFirst

End With

' Set the number of Columns = number of Fields in recordset

ListBox1.ColumnCount = rs.Fields.Count

' Load the ListBox with the retrieved records

ListBox1.Column = rs.GetRows(NoOfRecords)

' Cleanup

'rs.Close

db.Close

Set rs = Nothing

Set db = Nothing

' Not the value I want but I am using a sample value
' I want the value of the field in the recordset
GetData = Str(ListBox1.ColumnCount)
End Function


Private Sub Oral_Communication_FinalYear_Grade_Change()
strcomments = GetData("MidYearComments", "Oral_Communication",
Grade_ComboBox.Value, Oral_Communication_FinalYear_Grade.Value)
Me.Oral_Communication_Comments_TextBox1.Value = strcomments
End Sub

</snip>

 

[Cindy M -WordMVP-]

Hi Gurvinder,

I can't see anything at a glance. An Access group would probably be the
better place to ask this. My advice for trouble shooting this would be
to

1. See if the OpenSnapshot thing is the problem. Try removing that, or
add it to the hard-code test that works.

2. Try hardcoding a result with the WHERE clause the way Debug outputs
it and fiddle with that until you find the problem.

3. Try removing the semicolon at the end of the SELECT (since there's
none at the end of the SELECT you say does work)

I do see one typo in the code you show us: the closing parentheses after
the characters Snapshot. There's no matching opening parens.

I am picking up VBA after 6 years and need help with running a SQL
query from Word to get data from Access MDB file.

Error:
Run-Time error '3061':
Too few parameters. Expected 1


' Debug Window at the time of the error
debug.Print strsql
SELECT [Oral_Communication] FROM MidYearComments WHERE ([Grade]=1) AND
([GradingCode]='E');

VBA Code in Word Docment
<snip>
Public Function GetData(ByVal tblName As String, ByVal fldName As
String, ByVal NumGrade As Integer, ByVal StrGradingCode As String) As
String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long

' Numbers in Grade in Access range from
' -1 for JKG and 0 for KG
' followed by 1 - 12 for grades.

If Str(NumGrade) = "JKG" Then
NumGrade = -1
ElseIf Str(NumGrade) = "KG" Then
NumGrade = 0
End If

' Open the database
Set db = OpenDatabase("C:\Reports\Comments.mdb")
Dim strSQL As String

' Retrieve the recordset
' This one works fine
' Set rs = db.OpenRecordset("SELECT [Oral_Communication] FROM " _
' & "MidYearComments")

' Retrieve the recordset
' This one generates the error
strSQL = "SELECT [" & fldName & "] FROM " & tblName & " " _
& "WHERE ([Grade]=" & Int(NumGrade) & ") AND " _
& "([GradingCode]='" & StrGradingCode & "');"
Set rs = db.OpenRecordset(strSQL) , dbOpenSnapshot)

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question
or reply in the newsgroup and not by e-mail