Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Dear Loungers,

    I am swimming in unknown waters and attempting to use ADO. I get this error "No value given for one or more parameters" the debug cursor is set at the .Open line. This is the code (with error handling removed):

    • Dim rsPersonDetails As ADODB.Recordset
      Dim cn As ADODB.Connection
      Dim strSQL As String

      strSQL = _
      • " SELECT " _
        & "[tbl_Person].[PER_ID] as Id, " _
        & "[tbl_Person].[PER_FirstName], " _
        & "[tbl_Person].[PER_LastName], " _
        & "[tbl_Person].[PER_Id], " _
        & "[tbl_Person].[PER_Address] ," _
        & "[tbl_Person].[PER_PostCode] " _
        & "FROM tbl_Person " _
        & "WHERE [tbl_Person].[PER_ID] = [TempVars]![tv_Select_PER_Id];"


      Set cn = CurrentProject.AccessConnection
      Set rsPersonDetails = New ADODB.Recordset

      With rsPersonDetails
      ' Open the Recordset
      • Set .ActiveConnection = cn
        .CursorType = adOpenForwardOnly
        .LockType = adLockOptimistic
        .Source = strSQL
        .Open

      End With
      ' Set the tempvars
      [TempVars]![tv_PER_Id].Value = rsPersonDetails![PER_Id]
      [TempVars]![tv_Name].Value = rsPersonDetails![PER_FirstName] & " " & rsPersonDetails![PER_LastName] & " (" & rsPersonDetails![PER_Id] & ")"
      [TempVars]![tv_Address].Value = rsPersonDetails![PER_Address]
      [TempVars]![tv_Postcode].Value = rsPersonDetails![PER_PostCode]

      ' Close the Recordset & connection
      rsPersonDetails.Close
      Set rsPersonDetails = Nothing
      cn.Close
      Set cn = Nothing


    If anyone can see any obvious flaws I'd be grateful. And, I haven't yet got to the bit of code that sets the TempVars but I assume that I refer to them like this?

    many thanks...................... liz

  2. #2
    New Lounger
    Join Date
    Jun 2010
    Location
    UK
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Liz,

    No value given for one or more parameters is usually when you refer to a field in an SQL statement that doesn't exist, normally due to a spelling mistake.

    In your case it looks like [TempVars]![tv_Select_PER_Id] doesn't exist where you are trying to use it in the WHERE part of your SQL statement.

    Hope i'm pointing you in the right direction.

    Mark

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    My tactic for dealing with problems like this is to put in

    Code:
    Debug.print strSQL
    after you have compiled the sql statement.
    Debug.print writes the value to the immediate window. Often you can see the problem on inspection. If not, copy the SQL and paste it into a new query in SQL View, and switch to Design View. Many errors can be resolved here.

    Also
    Code:
    & "WHERE [tbl_Person].[PER_ID] = [TempVars]![tv_Select_PER_Id];"
    needs to be
    Code:
    & "WHERE [tbl_Person].[PER_ID] = " & [TempVars]![tv_Select_PER_Id]
    Any references to variables need to be outside the double quotes, so the value of the variable is used.
    Regards
    John



Posting Permissions

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