Page 2 of 4 FirstFirst 1234 LastLast
Results 16 to 30 of 50
  1. #16
    Brian62 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2009
    Posts
    147
    I can't post the db because the table is linked to SQL Server 2008. Here is a jpg of the VB.


    Click image for larger version. 

Name:	vba.jpg 
Views:	3 
Size:	68.2 KB 
ID:	8276

  2. #17
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Okay, how about trying the technique in post 11 to get the finished SQL?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #18
    Brian62 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2009
    Posts
    147
    I added the debug function, but it didn't do anything when I ran it. Here is the code.

    Private Sub Display()
    Dim strsql As String
    Dim DAO As DAO.Database
    Dim RS As DAO.Recordset
    Dim LngCnt As Long

    LngCnt = 0

    Set DAO = OpenDatabase("W:\RCO\Databases\SQL_RCO_Database.ac cdb")
    Debug.Print strsql
    Set RS = DAO.OpenRecordset("SELECT * FROM dbo_TblRDStudyAuditDocument WHERE ID_D = '" & Me.ID_D & "'", dbOpenDynaset, dbSeeChanges)
    LngCnt = RS.RecordCount

    With RS
    If LngCnt > 0 Then
    .MoveFirst
    .Index = "PK_TblRDStudyAuditDocument"
    .Seek "=", ID_D
    If .NoMatch Then
    MsgBox ("No Match"), vbInformation, "ERROR: Display()"
    Exit Sub

    Else

    Studyid = .Fields(1)
    AuditDate = .Fields(2)
    TxtSectionName = .Fields(3)
    DocName = .Fields(4)
    Comments = .Fields(5)

    End If

    End If
    .Close

    End With

    Set DAO = Nothing
    End Sub

  4. #19
    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 never actually populated the string. Modify or add these lines as appropriate:

    strsql = "SELECT * FROM dbo_TblRDStudyAuditDocument WHERE ID_D = '" & Me.ID_D & "'"
    Debug.Print strsql
    Set RS = DAO.OpenRecordset(strsql, dbOpenDynaset, dbSeeChanges)
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #20
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Shouldn't matter. The Debug.Print would just send empty string to the immediate window. The code should still have done everything else.
    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.

  6. #21
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I realize that, but the result is we don't have the string available to debug why the code doesn't work.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #22
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    True, populating and using the variable could be helpful in debugging. Just wanted OP to be aware that if code is not working there is something more wrong then just the empty variable.
    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.

  8. #23
    Brian62 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2009
    Posts
    147
    I added the updated string as requested but getting a run-time error 91:

    Click image for larger version. 

Name:	VBA.jpg 
Views:	7 
Size:	66.0 KB 
ID:	8281

  9. #24
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What is the error description?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #25
    Brian62 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2009
    Posts
    147
    (Updated) Run-time error 91: Object variable or with block variable not set

  11. #26
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    That looks like a number, not a description.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #27
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Oh, you haven't set the database variable.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #28
    Brian62 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2009
    Posts
    147
    I updated the thread above after I wrote it. Sorry.

  14. #29
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    So where are we when you add back the code to set the database variable?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  15. #30
    Brian62 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2009
    Posts
    147
    Added the scripted as you suggested so that is where we are at. I showed the error so I hope that helps.

Page 2 of 4 FirstFirst 1234 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. "not an index in this table" error
    By everette in forum Access
    Replies: 4
    Last Post: 04-06-2012, 05:03 AM
  2. Replies: 2
    Last Post: 11-04-2011, 02:45 AM
  3. Replies: 1
    Last Post: 04-25-2011, 12:36 PM
  4. "Group By" causes "ODBC--Call Failed" error
    By kaledev in forum Queries
    Replies: 1
    Last Post: 03-09-2011, 02:43 PM
  5. Replies: 4
    Last Post: 06-10-2010, 07:51 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