Results 1 to 6 of 6
  1. #1
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496

    ought to be simple loor / query

    ought to be simple, but i clearly have something wrong
    if you could take a quick look and let me know if you see something obvious:

    me.type is a valid value


    qryTypeDetail_InstallationNotes_EOS_text is a valid query (despite the ridiculously long name)
    and
    InstallationNoteTitle is a valid field in qryTypeDetail_InstallationNotes_EOS_text

    Code:
    vCriteria = "Type ='" & Me.Type & "'"""
                Dim Db As DAO.Database
                Dim rs As DAO.Recordset
                Set Db = CurrentDb
                Set rs = Db.OpenRecordset("SELECT InstallationNoteTitle " & _
                    "FROM qryTypeDetail_InstallationNotes_EOS_text " & _
                    "WHERE vCriteria;")
                vCountNotes_EOS = DCount("InstallationNoteTitle", "qryTypeDetail_InstallationNotes_EOS_text", vCriteria)
                If vCountNotes_EOS > 0 Then
                    vEOSList = ""
                    With rs
                        .MoveLast
                        .MoveFirst
                        Do While Not .EOF
                            vEOSList = vEOSList & .InstallationNoteTitle & ", "
                            .MoveNext
                        Loop
                        .Close
                    End With
                End If
                Set rs = Nothing
                Set Db = Nothing
    stumped
    with thanks in advance,
    m.

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Maybe this would help..
    Code:
    Set rs = Db.OpenRecordset("SELECT InstallationNoteTitle " & _ "FROM qryTypeDetail_InstallationNotes_EOS_text " & _ "WHERE " & vCriteria)
    Last edited by davegri; 08-12-2019 at 09:30 PM. Reason: format

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Also, too many quote marks in expression setting vCriteria, drop the last 2:

    vCriteria = "Type ='" & Me.Type & "'"
    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
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The ".Close" should be moved down to just above "Set rs = Nothing" to ensure the record set is closed if "vCountNotes_EOS<= 0".
    Code:
                            .MoveNext
                        Loop
                        
                    End With
                End If
    
                rs.Close
                Set rs = Nothing
                Set Db = Nothing

    Code:
    vCriteria = "Type ='" & Me.Type & "'"
    Be aware that "TYPE" is a reserved word and shouldn't be used for object names.

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    with all the errors, I'm surprised you didn't get any error messages to resolve this.

    In addition to the other corrections, you also need to use !InstallationNoteTitle, not .InstallationNoteTitle. Type is a reserved word but can usually be prevented from generating misleading errors by enclosing with square brackets. Best to change to something more meaningful such as NoteType

    you also don't need the dcount or movefirst, movelast lines

    Incorporating the corrections already advised


    Code:
    vCriteria = "[Type] ='" & Me.Type & "'"
                
    Dim Db As DAO.Database
    Dim rs As DAO.Recordset
                
    Set Db = CurrentDb
    Set rs = Db.OpenRecordset("SELECT InstallationNoteTitle " & _
                    "FROM qryTypeDetail_InstallationNotes_EOS_text " & _
                    "WHERE " & vCriteria)
    
    vEOSList = ""
    With rs
        while not .EOF
            vEOSList = vEOSList & !InstallationNoteTitle & ", "
            .MoveNext
        wend
        .Close
    End With

  6. #6
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496
    it must have been a moment of sheer "time to call it quits for the night";
    its all good direction, and it worked !

    thnx so much

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

Similar Threads

  1. Replies: 8
    Last Post: 10-17-2018, 11:52 AM
  2. Replies: 2
    Last Post: 11-10-2017, 07:52 PM
  3. Replies: 8
    Last Post: 03-16-2016, 10:11 AM
  4. need help with this simple query
    By dada in forum Programming
    Replies: 3
    Last Post: 08-20-2010, 07:08 AM
  5. Help with a simple query
    By JohnnyO in forum Queries
    Replies: 1
    Last Post: 02-11-2009, 09:43 PM

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