Results 1 to 7 of 7
  1. #1
    Newby is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Posts
    60

    Returning a recordset for a form

    I am setting up a form using a couple combo boxes. I want to populate a text box or 2 from the item record selected from one of the comboBox. Below is the code I am using. I have commented out where I am trying to transfer the value to the form, and try focusing on the query. When I run this code I get a type mismatch. Thanks in advance for your help.

    John

    rivate Sub cbx_SlideNo_Change()
    Me.Refresh
    Dim StringSQL As String


    Dim rs As Recordset
    StringSQL = "SELECT Slides.[SlideTitle] "
    StringSQL = StringSQL & "FROM Slides "
    StringSQL = StringSQL & "WHERE (((Slides.[Deck])=""" & cbx_Deck & """) AND ((Slides.[SlideNo])=" & cbx_SlideNo & "));"
    Debug.Print StringSQL
    'rs.OpenRecordset(StringSQL) as Recordset
    'rs.OpenRecordset
    DoCmd.RunCommand "StringSQL"
    'If rs.EOF Then
    ' MsgBox "No Data"
    ' Else
    ' rs.MoveFirst

    ' txt_SlideTitle.Value = rs!SlideTitle
    'End If

    End Sub

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Well, I'm not sure you can test the SQL that way. This would help:

    http://www.baldyweb.com/ImmediateWindow.htm

    What are the data types of the field in the WHERE clause?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Are values of cbx_Deck and cbx_SlideNo actually text? I suspect not. Step debug. Review link at bottom of my post for guidance on debug techniques.

    Ooops, now see what pbaldy caught. RunCommand method is not relevant to an SQL statement.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    Newby is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Posts
    60

    Thank you it helped, but I have a follow up

    I incorporated changes based on your link, and it did the job. When I extended it to the next procedure it errors out. "Too few parameters. Expected 3" and "Object invalid or no longer set".
    Below is my code. ????


    Private Sub cbx_SlideNo_Change() ' Runs as expected
    Me.Refresh
    cbx_SlideObj.Value = ""
    Dim stringSQL As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    stringSQL = "SELECT Slides.[SlideTitle] "
    stringSQL = stringSQL & "FROM Slides "
    stringSQL = stringSQL & "WHERE (((Slides.[Deck])=""" & cbx_Deck & """) AND ((Slides.[SlideNo])=" & cbx_SlideNo & "));"
    'Debug.Print stringSQL
    Set rs = db.OpenRecordset(stringSQL, dbOpenSnapshot)
    If rs.EOF Then
    MsgBox "No Data"
    Else
    rs.MoveFirst

    Me.txt_SlideTitle.Value = rs!SlideTitle
    End If
    'ErrorHandler:
    ' Select Case Err
    End Sub


    Private Sub cbx_SlideObj_Change() 'Errors out on bolded underscore line
    Me.Refresh

    Dim stringSQL As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    stringSQL = "SELECT SlideObject.[Object_Title] "
    stringSQL = stringSQL & "FROM SlideObject "
    stringSQL = stringSQL & "WHERE SlideObject.[Deck]=[forms]![fSlides].[cbx_Deck] AND SlideObject.[Slide]=[forms]![fSlides].[cbx_SlideNo] AND SlideObject.[SlideObject]=[forms]![fSlides].[cbx_SlideObj];"
    Debug.Print stringSQL
    Set rs = db.OpenRecordset(stringSQL, dbOpenSnapshot)
    If rs.EOF Then
    MsgBox "No Data"
    Else
    rs.MoveFirst

    Me.txt_SlideTitle.Value = rs!SlideTitle
    End If
    End Sub


    Quote Originally Posted by pbaldy View Post
    Well, I'm not sure you can test the SQL that way. This would help:

    http://www.baldyweb.com/ImmediateWindow.htm

    What are the data types of the field in the WHERE clause?

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You have to concatenate the form references as you were before.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    Newby is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Posts
    60

    thanks. I feel like someone should put a kick me sign on my back.

    I feel like someone should put a kick me sign on my back.
    Quote Originally Posted by pbaldy View Post
    You have to concatenate the form references as you were before.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You can borrow mine.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Difficulty returning ADODB recordset from function
    By randman1 in forum Programming
    Replies: 4
    Last Post: 07-19-2012, 01:07 PM
  2. Replies: 2
    Last Post: 03-08-2012, 12:59 PM
  3. Form returning last blank table row
    By Dannat in forum Forms
    Replies: 5
    Last Post: 01-30-2012, 08:36 AM
  4. Recordset returning blank value
    By Mohamed in forum Access
    Replies: 1
    Last Post: 10-24-2011, 09:31 AM
  5. Recordset not returning records
    By TinaCa in forum Programming
    Replies: 3
    Last Post: 08-03-2011, 09:26 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums