Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    opening Recordset issue (XP/2K)

    I have the following code that is giving me 2 strange errors: (The SQL string is copied straight from a qbe grid that was turned to SQL by access, I just added the last part to be able to add a variable there.)
    The messages I get are:"No value given for given parameter"- the form referred to is open!
    Then I sometimes get after that an error that it is calling wrong DLL code.
    Thanks

    Public Function fGetBtlStats(strAgeType As String) As Integer
    Dim intResult As Integer
    Dim rst As Recordset
    Dim strSql As String
    On Error GoTo fGetBtlStats_Error

    strSql = "SELECT tblKever.MainID, tblKever.dtNiftar, Age([tblKever]![dtBirth],[tblKever]![dtNiftar]) AS age FROM tblKever WHERE (((tblKever.dtNiftar) Between [Forms]![frmBtlStats]![Text0] And [Forms]![frmBtlStats]![Text2]) AND ((Age([tblKever]![dtBirth],[tblKever]![dtNiftar]))=" & strAgeType & "));"

    Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseClient
    rst.Open strSql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

    If Not rst.BOF Or Not rst.EOF Then
    rst.MoveLast
    intResult = rst.RecordCount

    Else
    intResult = 0
    End If

    fGetBtlStats = intResult

    On Error GoTo 0
    Exit Function
    fGetBtlStats_Error:
    Call LogError(Err.Number, Err.Description, "fGetBtlStats")
    End Function

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: opening Recordset issue (XP/2K)

    Try concatenating the values of the form fields to the SQL instead of including them. If the field you are filtering is a date field, you may need to explicitly add the # delimiters around the values as well. Also, SQL requires dates in US format (mm/dd/yy), and it doesn't recognize anything but a / or a - as a delimiter within the formatted date string.
    Charlotte

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: opening Recordset issue (XP/2K)

    If you want to open a recordset with parameters (such as reference to open form) in code, you have to specify the value of the parameter or parameters. This applies to both DAO and ADO recordsets. Just having the form open doesn't work. The syntax is a bit different for ADO (I'm more familiar with the DAO syntax). To pass a parameter to a query in ADO you have to use the Command object (which has a Parameters collection) to open a recordset. As noted in Help, "To execute a query without using a Command object, pass a query string to the Open method of a Recordset object. However, a Command object is required when you want to persist the command text and re-execute it, or use query parameters." Here is example from Northwind:

    Public Sub TestParameterRecordsetADO()
    On Error GoTo Err_Handler

    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim cmd As ADODB.Command
    Dim prm As ADODB.Parameter
    Dim strCmd As String
    Dim strMsg As String

    Set cnn = CurrentProject.Connection
    Set rst = New ADODB.Recordset
    Set cmd = New ADODB.Command

    ' strSQL = "SELECT CustomerID, Country " & _
    ' "FROM Customers " & _
    ' "WHERE Country=<!t>[Forms]<!/t>!<!t>[Form1]<!/t>!<!t>[Text1]<!/t>;"

    strCmd = "qryTestParameter"
    cmd.CommandText = strCmd
    cmd.CommandType = adCmdTable

    ' Set prm = cmd.CreateParameter("<!t>[Forms]<!/t>!<!t>[Form1]<!/t>!<!t>[Text1]<!/t>", adBSTR, adParamInput)
    ' cmd.Parameters.Append prm
    ' prm.Value = "France"

    Set cmd.ActiveConnection = cnn
    Set rst = cmd.Execute(, "UK")

    ' Will cause error:
    ' Set rst = New ADODB.Recordset
    ' rst.Open strCmd, cnn, adOpenDynamic, adLockOptimistic
    ' Test Purposes:
    strMsg = rst.RecordCount & " records."
    MsgBox strMsg, vbInformation, "RECORD COUNT"

    Do While Not rst.EOF
    Debug.Print rst!CustomerID, rst!Country
    rst.MoveNext
    Loop

    rst.Close

    Exit_Sub:
    Set rst = Nothing
    Set cnn = Nothing
    Set cmd = Nothing
    Set prm = Nothing
    Exit Sub
    Err_Handler:
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    Beep
    MsgBox strMsg, vbExclamation, "TestParameterRecordsetADO ERROR"
    Resume Exit_Sub

    End Sub

    In this example, the recordset returns 7 records in NW Customers table where Country = UK. Note you can use CreateParameter method to append Parameter to Parameters collection (commented out above), though in this example you can simply supply parameter value as second argument for Command Execute method (UK would override France if 3 lines above command uncommented.) In some cases you will need to use the CreateParameter method. Unlike DAO, you do not have to explicitly declare the parameters in query's SQL statement using PARAMETERS keyword - see query SQL. For more info on using parameters in ADO recommend consult ADO Help files.

    HTH

  4. #4
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: opening Recordset issue (XP/2K)

    Thanks everyone.

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: opening Recordset issue (XP/2K)

    I" had read the stuff about a command parameter. But can I get around that by using a variable that would store the value of the text box on the form and just concatenating the string?

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: opening Recordset issue (XP/2K)

    Possibly, but you would have to specify the commandtype as cmdText, not cmdTable.
    Charlotte

Posting Permissions

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