Results 1 to 2 of 2
  1. #1

    How can tell recordset exists?

    Okay, how can you tell if the recordset is being built? I keep getting an error of "...too few parameters, expected 5..." (the exact amount of fields in the rs recordset!).

    Here is my code:

    <font color=blue>
    Sub LanguageRecordset()
    Dim strLang As String
    Dim rs As Recordset
    Dim rs2 As Recordset
    Dim rsTemp As Recordset
    Dim sqlTable As String
    Dim sqlTable2 As String

    'Dump the old table with this function

    ' Create New Table

    ' Update the table with distinct data but no languages (yet)
    sqlTable = "SELECT DISTINCT tblCDAssemblyandLangJoin.partno as [Part Number], " _
    & "tblCDAssemblyandLangJoin.base as [Base], " _
    & "tblCDAssemblyandLangJoin.projectname as [Project Name], " _
    & "tblCDAssemblyandLangJoin.cdassembly as [CD Assembly], " _
    & "tblCDAssemblyandLangJoin.component as [Component]," _
    & Chr(34) & Chr(34) & " as [Lang] into [tblTemp] FROM tblCDAssemblyandLangJoin;"

    ' Run that SQL!
    DoCmd.SetWarnings False
    DoCmd.RunSQL sqlTable
    DoCmd.SetWarnings True

    ' Create recordset to use to update tmptable
    Set rs = CurrentDb.OpenRecordset("SELECT [part Number], [base], [project name], [cd assembly], [component] FROM tblTemp;")

    ' SQL for second recordset to loop languages
    sqlTable2 = "SELECT language FROM tblCDAssemblyandLangJoin " _
    & "WHERE (partno = rs![part number]) AND (projectname = rs![project Name]) AND (base = rs![base]) AND " _
    & "(cdassembly = rs![cd assembly]) AND (component = rs![component]);"

    Do While Not rs.EOF
    ' Create the second recordset which will have only languages go with first record of rs
    Set rs2 = CurrentDb.OpenRecordset(sqlTable2)

    <font color=red>Debug.Print "If this doesn't show, error happens just before this line."</font color=red>


    Do While Not rs2.EOF
    strLang = strLang & rs2!lang & ", "
    strLang = Left(strLang, Len(strLang) - 2)
    rs!lang = strLang

    End Sub
    </font color=blue>
    I placed the DEBUG.PRINT LINE right after the point it breaks.

    Driving me nuts!

    Mary <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  2. #2

    Re: How can tell recordset exists?

    If anyone wants to know, my problem was that I didn't break the recordset reference out of the SQL string. You know, the quote ampersand quote sort of thing. Didn't realize that recordsets were objects needing to be reference differently in a SQL statement.

    Mary <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts