Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

3061 Error - Possibly SQL Errors. 1

Status
Not open for further replies.
boab

boab

Programmer
May 30, 2001
75
0
0
GB
Hi folks hope you can help,

The application I am trying to build is in tended to search the data base for all items associated with a particular course Elements Learning Outcomes etc, The application then outputs the data to a word document.

The code I am using is below (note that I have used message boxes to try and debug already and can see nothing wrong with the statements that they produce.

Code: 
Private Sub Sheets_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer
Dim bcf1 As Variant
Dim bcf2 As Variant
Dim bcf3 As Variant
Dim arrVars As Variant
Dim sqlEL As String
Dim sqlLO As String
Dim sqlBcf1 As String
Dim sqlBcf2 As String
Dim sqlBcf3 As String
Dim ElLines As String
Dim ValLines As String
Dim LOLines As String
Dim bcfLines As String
Set db = CurrentDb()
'Delivering our Business
'Relationships with People
'Developing our Organisation
'define sql statements
sqlLO = "SELECT LOuts.LOId, LOuts.Name, LOuts.Content, LOuts.Validation FROM LOuts WHERE LOuts.Course_id=" & Me.Course_ID & " ORDER BY LOID;"
MsgBox sqlLO
sqlEL = "SELECT Element.El_Id,Element.Content FROM Element WHERE Element.Course_id ='" & Me.Course_ID & "' ORDER BY [EL_Id];"
MsgBox sqlEL
sqlBcf1 = "SELECT Bcf_info.Name, Bcf_Info.Content FROM Bcf_info, Link_Bcf WHERE Bcf_info.Bcf_Id= Link_Bcf.Bcf_Id AND Link_Bcf.Course_id='" & Me.Course_ID & "' AND Bcf_info='Delivering our Business' ORDER BY "
MsgBox sqlBcf1
sqlBcf2 = "SELECT Bcf_info.Name, Bcf_Info.Content FROM Bcf_info, Link_Bcf WHERE Bcf_info.Bcf_Id= Link_Bcf.Bcf_Id AND Link_Bcf.Course_id='" & Me.Course_ID & "' AND Bcf_info='Relationships with People' ORDER BY "
MsgBox sqlBcf2
sqlBcf3 = "SELECT Bcf_info.Name, Bcf_Info.Content FROM Bcf_info, Link_Bcf WHERE Bcf_info.Bcf_Id= Link_Bcf.Bcf_Id AND Link_Bcf.Course_id='" & Me.Course_ID & "' AND Bcf_info='Developing our Organisation' ORDER BY "
MsgBox sqlBcf3
' Retreive the Element Details  / Format them - ELnumber. EL Content

Set rs = db.OpenRecordset(sqlEL)
    arrVars = rs.GetRows(rs.RecordCount)
    For i = 0 To rs.RecordCount - 1
        ElLines = arrVars(0, i) & ".  " & arrVars(1, i) & vbCrLf
    Next i
'Retreive the LO Details / Format them thus -> LO Number. LO Name  & VBcrlf & "      " & LO Content
'Retreive the LO Validation Details / Format them  thus -> LO Number.  LO Validation
    
Set rs = db.OpenRecordset(sqlLO)
    arrVars = rs.GetRows(rs.RecordCount)
    For i = 0 To rs.RecordCount - 1
        LOLines = arrVars(0, i) & ".  " & arrVars(1, i) & "    " & vbCrLf & "     " & arrVars(2, i) & vbCrLf
        ValLines = arrVars(0, i) & ".  " & arrVars(1, i) & "    " & vbCrLf & "     " & arrVars(3, i) & vbCrLf
    Next i
Set rs = db.OpenRecordset(sqlBcf1)
    arrVars = rs.GetRows(rs.RecordCount)
    For i = 0 To rs.RecordCount
        bcf1(0, i) = arrVars(0, i)
        bcf1(1, i) = arrVars(1, i)
    Next i
Set rs = db.OpenRecordset(sqlBcf2)
    arrVars = rs.GetRows(rs.RecordCount)
    For i = 0 To rs.RecordCount - 1
        bcf2(0, i) = arrVars(0, i)
        bcf2(1, i) = arrVars(1, i)
    Next i
Set rs = db.OpenRecordset(sqlBcf3)
    arrVars = rs.GetRows(rs.RecordCount)
    For i = 0 To rs.RecordCount - 1
        bcf3(0, i) = arrVars(0, i)
        bcf3(1, i) = arrVars(1, i)
    Next i
    
    Dim Wrd As Word.Application
    Set Wrd = CreateObject("Word.Application")
    Wrd.Documents.Add "D:\PACK.dot"
        
    Wrd.Visible = True
        
    'Populate Training Pack Template for this course
    With Wrd.ActiveDocument.Bookmarks
     .Item("Aim").range.Text = Me.Aim
     .Item("ELEMENTS").range.Text = ElLines
     .Item("LEARNINGOUTCOMES").range.Text = LOLines
     .Item("LOvals").range.Text = ValLines
     .Item("Resources").range.Text = Me.resources
     .Item("Duration").range.Text = Me.Duration
     .Item("CourseVal").range.Text = Me.Validation
     .Item("TITLE").range.Text = Me.Name
     .Item("AIM").range.Text = Me.Aim
     .Item("GROUP").range.Text = Me.Target_Group
     'Populate BCF details
        ' Deliver our Business section
        If Not IsNull(bcf1(0, 0)) Then
            .Item("BCF1TITLE").range.Text = bcf1(0, 0)
            .Item("BCF1TXT").range.Text = bcf1(1, 0)
        End If
          If Not IsNull(bcf1(0, 1)) Then
            .Item("BCF2TITLE").range.Text = bcf1(0, 1)
            .Item("BCF2TXT").range.Text = bcf1(1, 1)
        End If
          If Not IsNull(bcf1(0, 2)) Then
            .Item("BCF3TITLE").range.Text = bcf1(0, 2)
            .Item("BCF3TXT").range.Text = bcf1(1, 2)
        End If
          If Not IsNull(bcf1(0, 3)) Then
            .Item("BCF4TITLE").range.Text = bcf1(0, 3)
            .Item("BCF4TXT").range.Text = bcf1(1, 3)
        End If
        ' Relationships with People
         If Not IsNull(bcf2(0, 0)) Then
            .Item("BCF5TITLE").range.Text = bcf2(0, 0)
            .Item("BCF5TXT").range.Text = bcf2(1, 0)
        End If
          If Not IsNull(bcf2(0, 1)) Then
            .Item("BCF6TITLE").range.Text = bcf2(0, 1)
            .Item("BCF6TXT").range.Text = bcf2(1, 1)
        End If
          If Not IsNull(bcf2(0, 2)) Then
            .Item("BCF7ITLE").range.Text = bcf2(0, 2)
            .Item("BCF7TXT").range.Text = bcf2(1, 2)
        End If
          If Not IsNull(bcf2(0, 3)) Then
            .Item("BCF8TITLE").range.Text = bcf2(0, 3)
            .Item("BCF8TXT").range.Text = bcf2(1, 3)
        End If
        'Developing our Organisation
         If Not IsNull(bcf3(0, 0)) Then
            .Item("BCF9TITLE").range.Text = bcf3(0, 0)
            .Item("BCF9TXT").range.Text = bcf3(1, 0)
        End If
          If Not IsNull(bcf3(0, 1)) Then
            .Item("BCF10TITLE").range.Text = bcf3(0, 1)
            .Item("BCF10TXT").range.Text = bcf3(1, 1)
        End If
          If Not IsNull(bcf3(0, 2)) Then
            .Item("BCF11TITLE").range.Text = bcf3(0, 2)
            .Item("BCF11TXT").range.Text = bcf3(1, 2)
        End If
          If Not IsNull(bcf3(0, 3)) Then
            .Item("BCF12TITLE").range.Text = bcf3(0, 3)
            .Item("BCF12TXT").range.Text = bcf3(1, 3)
        End If
    End With
        
    'Wrd.ActiveDocument.PrintOut
        
    Wrd.ActiveDocument.close (wdDoNotSaveChanges)
    Wrd.Quit
End Sub

Access however keeps giving me a 3061 error - "Too Few parameters. Expected 1." at the first instance of rs.Openrecordset()

I have read a few other threads related to this but they focus on the SQL Statements that are used and as I have said using the Message boxes I can't really see a problem with them.

Any help is gratefully recieved

The Start of wisdom is to realise you know nothing. I'll be a genius then!
 
Sort by date Sort by votes
The first instance, that's the

[tt]sqlEL = "SELECT Element.El_Id,Element.Content FROM Element WHERE Element.Course_id ='" & Me.Course_ID & "' ORDER BY [EL_Id];"[/tt]

Are the field names correct - check the spelling - do they all exist in the Element table? Does the Course_ID control contain a value? What datatype is the Course_ID field - if it is numeric, drop the single quotes. Using the same name on controls on form and fields can sometimes give anomalities.

To test, do a

[tt]debug.print sqlEL[/tt]

in stead of msgbox, then hit ctrl+g (immediate pane) and study the sql - or rather copy it and paste into the SQL view of the query builder. It should run.

Roy-Vidar
 
Upvote 0 Downvote
Oh - and there might be some challenges related to using reserwed words as field names. Name is one of them, I think maybe content is another. Reserwed word shouldn't be used as names of any objects in Access, but you may get around it surrounding them in [brackets].

Be careful when working with dao recordcount, it is not reliable before fully populated (use rs.movelast, and perhaps rs.movefirst prior to getrows)

Perhaps also when cretaing an instance of Word, in stead of implicitly referencing the document, use:

[tt]dim oDoc as object
set oDoc=Wrd.Documents.Add("D:\PACK.dot")

' Populate Training Pack Template for this course
With oDoc.Bookmarks
...[/tt]

Roy-Vidar
 
Upvote 0 Downvote
What is Me.Course_ID ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
Upvote 0 Downvote
Thanks Roy, it was some errors in the SQL and the field names. I am now getting a Type Mismatch on the code below
on bcf1
Code: 
Set rs = db.OpenRecordset(sqlBcf1)
    arrVars = rs.GetRows(rs.RecordCount)
    For i = 0 To rs.RecordCount
        bcf1(0, i) = arrVars(0, i)
        bcf1(1, i) = arrVars(1, i)
    Next i
Set rs = db.OpenRecordset(sqlBcf2)
    arrVars = rs.GetRows(rs.RecordCount)
    For i = 0 To rs.RecordCount - 1
        bcf2(0, i) = arrVars(0, i)
        bcf2(1, i) = arrVars(1, i)
    Next i
Set rs = db.OpenRecordset(sqlBcf3)
    arrVars = rs.GetRows(rs.RecordCount)
    For i = 0 To rs.RecordCount - 1
        bcf3(0, i) = arrVars(0, i)
        bcf3(1, i) = arrVars(1, i)
    Next i

The Start of wisdom is to realise you know nothing. I'll be a genius then!
 
Upvote 0 Downvote
PHV,

Me.Course_ID is the control on the form whihc contains the course ID

The Start of wisdom is to realise you know nothing. I'll be a genius then!
 
Upvote 0 Downvote
[tt]sqlBcf2 = "SELECT Bcf_info.Name, Bcf_Info.Content FROM Bcf_info, Link_Bcf WHERE Bcf_info.Bcf_Id= Link_Bcf.Bcf_Id AND Link_Bcf.Course_id='" & Me.Course_ID & "' AND Bcf_info='Relationships with People' ORDER BY "[/tt]

Data type mismatch means the data type of the field and what's passed as criterion doesn't match. Again, if the course_ID field is numeric, drop the single quotes (goes for all instances of the criterion - I see you treat it different, but it is probably (or should be) the same datatype in all tables), also add [brackets] around [Name]. You are starting an order by clause, but not specifying any order. Either specify an order by clause, or remove it.

It is the current SQL that's interesting now, post that for further questions. Also again, the datatype of the course_ID field.

I'm also questioning the rather long text you're querying on. Two things, you seem to be using the same name on both the table and a field. That's not encouraged. You may perhaps get around being a bit more explicit:

[tt]...AND Bcf_info.Bcf_info='Relationships with People'...[/tt]

Is the Bcf_info field also text? Or could it perhaps be that you're using some kind of lookup there? Then you'd need the actual (numeric) value (you aren't by any chance using some of those table level lookups, are you? The Evils of Lookup Fields in Tables)

Roy-Vidar
 
Upvote 0 Downvote
Sorry, should have posted code in full. I have ammended the SQL Statements, the full code is below. The datatype of the course_ID is varchar not numeric so I thought I should use ' at either end of it in the SQL statement

Code: 
Private Sub Sheets_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer
Dim bcf1 As Variant
Dim bcf2 As Variant
Dim bcf3 As Variant
Dim arrVars As Variant
Dim sqlEL As String
Dim sqlLO As String
Dim sqlBcf1 As String
Dim sqlBcf2 As String
Dim sqlBcf3 As String
Dim ElLines As String
Dim ValLines As String
Dim LOLines As String
Dim bcfLines As String
Set db = CurrentDb()
'Delivering our Business
'Relationships with People
'Developing our Organisation
'define sql statements
sqlLO = "SELECT LOuts.LOId, LOuts.Name, LOuts.Content, LOuts.Validation, LOuts.CorId FROM LOuts WHERE LOuts.CorId = '" & Me.Course_ID & "' ORDER BY LOuts.LOId;"
MsgBox sqlLO
sqlEL = "SELECT Element.El_Id,Element.Content FROM Element WHERE Element.CourseId ='" & Me.Course_ID & "' ORDER BY [EL_Id];"
'MsgBox sqlEL
sqlBcf1 = "SELECT Bcf_info.Name, Bcf_info.content, Bcf_info.Bcf_Id, Link_Bcf.Course_id, Bcf_info.type FROM Bcf_info INNER JOIN Link_Bcf ON Bcf_info.Bcf_Id = Link_Bcf.Bcf_id WHERE Bcf_info.Bcf_Id = Link_Bcf.Bcf_Id And Link_Bcf.Course_ID = '" & Me.Course_ID & "' And Bcf_info.type = 'Delivering our Business' ORDER BY Bcf_info.Bcf_Id;"
'MsgBox sqlBcf1
sqlBcf2 = "SELECT Bcf_info.Name, Bcf_info.content, Bcf_info.Bcf_Id, Link_Bcf.Course_id, Bcf_info.type FROM Bcf_info INNER JOIN Link_Bcf ON Bcf_info.Bcf_Id = Link_Bcf.Bcf_id WHERE Bcf_info.Bcf_Id = Link_Bcf.Bcf_Id And Link_Bcf.Course_ID = '" & Me.Course_ID & "' And Bcf_info.type = 'Relationships with People' ORDER BY Bcf_info.Bcf_Id;"
'MsgBox sqlBcf2
sqlBcf3 = "SELECT Bcf_info.Name, Bcf_info.content, Bcf_info.Bcf_Id, Link_Bcf.Course_id, Bcf_info.type FROM Bcf_info INNER JOIN Link_Bcf ON Bcf_info.Bcf_Id = Link_Bcf.Bcf_id WHERE Bcf_info.Bcf_Id = Link_Bcf.Bcf_Id And Link_Bcf.Course_ID = '" & Me.Course_ID & "' And Bcf_info.type = 'Developing our Organisation' ORDER BY Bcf_info.Bcf_Id;"
'MsgBox sqlBcf3
' Retreive the Element Details  / Format them - ELnumber. EL Content


Set rs = db.OpenRecordset(sqlEL)
    arrVars = rs.GetRows(rs.RecordCount)
    For i = 0 To rs.RecordCount - 1
        ElLines = arrVars(0, i) & ".  " & arrVars(1, i) & vbCrLf
    Next i
'Retreive the LO Details / Format them thus -> LO Number. LO Name  & VBcrlf & "      " & LO Content
'Retreive the LO Validation Details / Format them  thus -> LO Number.  LO Validation
    
Set rs = db.OpenRecordset(sqlLO)
    arrVars = rs.GetRows(rs.RecordCount)
    For i = 0 To rs.RecordCount - 1
        LOLines = arrVars(0, i) & ".  " & arrVars(1, i) & "    " & vbCrLf & "     " & arrVars(2, i) & vbCrLf
        ValLines = arrVars(0, i) & ".  " & arrVars(1, i) & "    " & vbCrLf & "     " & arrVars(3, i) & vbCrLf
    Next i
Set rs = db.OpenRecordset(sqlBcf1)
    arrVars = rs.GetRows(rs.RecordCount)
    For i = 0 To rs.RecordCount
        bcf1(0, i) = arrVars(0, i)
        bcf1(1, i) = arrVars(1, i)
    Next i
Set rs = db.OpenRecordset(sqlBcf2)
    arrVars = rs.GetRows(rs.RecordCount)
    For i = 0 To rs.RecordCount - 1
        bcf2(0, i) = arrVars(0, i)
        bcf2(1, i) = arrVars(1, i)
    Next i
Set rs = db.OpenRecordset(sqlBcf3)
    arrVars = rs.GetRows(rs.RecordCount)
    For i = 0 To rs.RecordCount - 1
        bcf3(0, i) = arrVars(0, i)
        bcf3(1, i) = arrVars(1, i)
    Next i
    
    Dim Wrd As Word.Application
    Set Wrd = CreateObject("Word.Application")
    Wrd.Documents.Add "D:\PACK.dot"
        
    Wrd.Visible = True
        
    'Populate Training Pack Template for this course
    With Wrd.ActiveDocument.Bookmarks
     .Item("Aim").range.Text = Me.Aim
     .Item("ELEMENTS").range.Text = ElLines
     .Item("LEARNINGOUTCOMES").range.Text = LOLines
     .Item("LOvals").range.Text = ValLines
     .Item("Resources").range.Text = Me.resources
     .Item("Duration").range.Text = Me.Duration
     .Item("CourseVal").range.Text = Me.Validation
     .Item("TITLE").range.Text = Me.Name
     .Item("AIM").range.Text = Me.Aim
     .Item("GROUP").range.Text = Me.Target_Group
     'Populate BCF details
        ' Deliver our Business section
        If Not IsNull(bcf1(0, 0)) Then
            .Item("BCF1TITLE").range.Text = bcf1(0, 0)
            .Item("BCF1TXT").range.Text = bcf1(1, 0)
        End If
          If Not IsNull(bcf1(0, 1)) Then
            .Item("BCF2TITLE").range.Text = bcf1(0, 1)
            .Item("BCF2TXT").range.Text = bcf1(1, 1)
        End If
          If Not IsNull(bcf1(0, 2)) Then
            .Item("BCF3TITLE").range.Text = bcf1(0, 2)
            .Item("BCF3TXT").range.Text = bcf1(1, 2)
        End If
          If Not IsNull(bcf1(0, 3)) Then
            .Item("BCF4TITLE").range.Text = bcf1(0, 3)
            .Item("BCF4TXT").range.Text = bcf1(1, 3)
        End If
        ' Relationships with People
         If Not IsNull(bcf2(0, 0)) Then
            .Item("BCF5TITLE").range.Text = bcf2(0, 0)
            .Item("BCF5TXT").range.Text = bcf2(1, 0)
        End If
          If Not IsNull(bcf2(0, 1)) Then
            .Item("BCF6TITLE").range.Text = bcf2(0, 1)
            .Item("BCF6TXT").range.Text = bcf2(1, 1)
        End If
          If Not IsNull(bcf2(0, 2)) Then
            .Item("BCF7ITLE").range.Text = bcf2(0, 2)
            .Item("BCF7TXT").range.Text = bcf2(1, 2)
        End If
          If Not IsNull(bcf2(0, 3)) Then
            .Item("BCF8TITLE").range.Text = bcf2(0, 3)
            .Item("BCF8TXT").range.Text = bcf2(1, 3)
        End If
        'Developing our Organisation
         If Not IsNull(bcf3(0, 0)) Then
            .Item("BCF9TITLE").range.Text = bcf3(0, 0)
            .Item("BCF9TXT").range.Text = bcf3(1, 0)
        End If
          If Not IsNull(bcf3(0, 1)) Then
            .Item("BCF10TITLE").range.Text = bcf3(0, 1)
            .Item("BCF10TXT").range.Text = bcf3(1, 1)
        End If
          If Not IsNull(bcf3(0, 2)) Then
            .Item("BCF11TITLE").range.Text = bcf3(0, 2)
            .Item("BCF11TXT").range.Text = bcf3(1, 2)
        End If
          If Not IsNull(bcf3(0, 3)) Then
            .Item("BCF12TITLE").range.Text = bcf3(0, 3)
            .Item("BCF12TXT").range.Text = bcf3(1, 3)
        End If
    End With
        
    'Wrd.ActiveDocument.PrintOut
        
    Wrd.ActiveDocument.close (wdDoNotSaveChanges)
    Wrd.Quit
End Sub


The Start of wisdom is to realise you know nothing. I'll be a genius then!
 
Upvote 0 Downvote
Full code? I said "It is the current SQL that's interesting now, post that for further questions."

I don't know much about SQL-server, but single quotes seems correct for nvarchar, try with or without.

If none of this works, start with simpler SQL. Collecting some fields from only one table, then incrementally add the the rest of the fields, some criteria, next table...

BRACKETS AROUND THE RESERWED WORDS - THIS INCLUDES "NAME" AND "TYPE" AND PERHAPS "CONTENT"

[tt]sqlBcf1 = "SELECT Bcf_info.[Name], Bcf_info.[content], Bcf_info.Bcf_Id, Link_Bcf.Course_id, Bcf_info.[type] FROM Bcf_info INNER JOIN Link_Bcf ON Bcf_info.Bcf_Id = Link_Bcf.Bcf_id WHERE Bcf_info.Bcf_Id = Link_Bcf.Bcf_Id And Link_Bcf.Course_ID = '" & Me.Course_ID & "' And Bcf_info.[type] = 'Delivering our Business' ORDER BY Bcf_info.Bcf_Id;"[/tt]

Which SQL is now giving which error? We can't see your table structure and data types from here...

Roy-Vidar
 
Upvote 0 Downvote
Roy,

The SQL strings for BCF are identical apart from the type field I have tried the SQL Statement below in the query window (substiting a value for Me.Course_id) and it seems to work fine.

sqlBcf1 = "SELECT Bcf_info.[Name], Bcf_info.[content], Bcf_info.Bcf_Id, Link_Bcf.Course_id, Bcf_info.[type] FROM Bcf_info INNER JOIN Link_Bcf ON Bcf_info.Bcf_Id = Link_Bcf.Bcf_id WHERE Bcf_info.Bcf_Id = Link_Bcf.Bcf_Id And Link_Bcf.Course_ID = '" & Me.Course_ID & "' And Bcf_info.[type] = 'Delivering our Business' ORDER BY Bcf_info.Bcf_Id;"

The Start of wisdom is to realise you know nothing. I'll be a genius then!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Kennelbloke
  • Locked
  • Question
TransferSpreadsheet 3125 Error
Replies
5
Views
218
Andrzejek
Andrzejek
Moss100
  • Locked
  • Question
Find error in currency feild 2
Replies
8
Views
199
Andrzejek
Andrzejek
Kim296
  • Locked
  • Question
Sending Gmail using vba Send error
Replies
6
Views
231
strongm
strongm
VicM
  • Locked
  • Question
Error when using Immediate window-recent happening 2
Replies
4
Views
107
VicM
VicM
lameid
  • Locked
  • Question
ADO UpdateBatch SQL Server Example?
Replies
2
Views
91
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top