none
DoCmd.OpenForm using where condition no longer works after upsizing database to SQL RRS feed

  • Question

  • I'm upsizing an Access database to an SQL backend (2008 R2) with an Access Data Project front-end.  After multiple attempts, I finally got everything fixed to the point that all of my tables and queries migrate (the latter as either views or table valued functions).  However, of course, nothing is perfect, and I find myself with a form that no longer opens to the correct record.  This form is called from another form (opened) with the DoCmd.OpenForm method from the On Dbl Click event in the calling form (which calls another subroutine to actually perform the DoCmd.OpenForm method, as this strategy is repeated for every field/textbox in the row being clicked).  Here's the relevant code:

    ' Every text box in the calling form's row, such as Ticket below, calls the GoToTask procedure to open form TicketFrm with the appropriate ticket displayed
    	Private Sub Ticket_DblClick(Cancel As Integer)
    		GoToTask
    	End Sub
    
    	Private Sub GoToTask()
    		FormStack.FormShown Me.Parent
    		DoCmd.OpenForm "TicketFrm", , , "CallTicketNum = " & Me!Ticket
    		
    	End Sub
    When form TicketFrm opens, it is (now) always showing the first record in the recordset attached to TicketFrm, rather than the record with column CallTicketNum equal to the value passed via Me!Ticket.  The called form's record source is a table valued function named AllCalls, which is defined as follows:
    	USE [HelpDesk2]
    	GO
    	/****** Object:  UserDefinedFunction [dbo].[AllCalls]    Script Date: 6/29/2017 2:45:52 PM ******/
    	SET ANSI_NULLS ON
    	GO
    	SET QUOTED_IDENTIFIER ON
    	GO
    	ALTER FUNCTION [dbo].[AllCalls] () 
    	RETURNS TABLE 
    	AS RETURN (SELECT TOP 100 PERCENT CallLog.CallTicketNum, CallLog.UserID, UserData.LastName, UserData.FirstName, FirstName + '  ' + LastName AS FullName, UserData.Location, UserData.Department, UserData.TelephoneNum, UserData.Extension, CallLog.CallType, CallLog.RequestType, CallLog.Status, CallLog.OpenDate, CallLog.OpenBy, CallLog.CallDescription, CallLog.AssignTo, CallLog.AssignDate, CallLog.Area, CallLog.CloseDescription, CallLog.EstimatedCloseDate, CallLog.CloseDate, CallLog.CloseBy, CallLog.CAR, CallLog.PONum, CallLog.ApprovedBy
    	FROM UserData RIGHT JOIN CallLog ON (UserData.UserID=CallLog.UserID)
    	ORDER BY CallLog.CallTicketNum)
    Any ideas as to what needs to be fixed to get this working?

    Thursday, June 29, 2017 6:54 PM

Answers

  • > I'm upsizing an Access database to an SQL backend (2008 R2) with an Access Data Project front-end

    That is a bad idea. MSFT abandoned ADP 2 versions ago. Don't invest in dead technology. Rather use an ACCDB with DSN-less connection to SQL Server.


    -Tom. Microsoft Access MVP

    • Marked as answer by Tim Peters Friday, June 30, 2017 7:05 PM
    Friday, June 30, 2017 3:04 AM

All replies

  • Hi Radosvet,

    What does FormStack.FormShown do? I didn't find FormStack object or FormShown method in Access PIA. What is Ticket? Is it a textbox? If you use OpenForm with fixed argument in a standard module, such as DoCmd.OpenForm "TicketFrm", , , "CallTicketNum =3", will you get the same result? Data in TicketFrm is stored in SQL Server, right? If you try to open a Form which source data is in access table, will you get the result?

    Best Regards,

    Terry

    Friday, June 30, 2017 1:51 AM
  • > I'm upsizing an Access database to an SQL backend (2008 R2) with an Access Data Project front-end

    That is a bad idea. MSFT abandoned ADP 2 versions ago. Don't invest in dead technology. Rather use an ACCDB with DSN-less connection to SQL Server.


    -Tom. Microsoft Access MVP

    • Marked as answer by Tim Peters Friday, June 30, 2017 7:05 PM
    Friday, June 30, 2017 3:04 AM
  • That's a good question.  I failed to mention that this is a legacy app that I did not write, and I'm still in the process of getting familiar with Access objects, VB code, and object programming in general.  That said, it looks like FormStack.FormShown is putting the open form into an array and then hiding it, so that the form being opened by the OpenForm call is the only form that appears to the user.  Whether that's a useful thing to do, I don't know.  The GoTask subroutine, called by the click event's subroutine, is in a subform containing detail records that meet the criteria entered on the parent form. I've included the FormStack module with its FormShown method below, so, perhaps you can confirm that my analysis is correct.  In any event, I don't think it's the reason why the OpenForm filter doesn't appear to be working - though, I could be wrong.

    Option Compare Database
    
    Private FormStack() As Access.Form
    Private stackCount As Long
    
    Public Sub FormShown(ByRef formToHide As Access.Form, Optional blnHide As Boolean = True)
        'Push formToHide onto the stack
        stackCount = stackCount + 1
        ReDim Preserve FormStack(stackCount - 1)    'For conversion to .NET, remove '- 1'
        Set FormStack(stackCount - 1) = formToHide
        
        'Hide formToHide if we need to
        If blnHide Then
            formToHide.Visible = False
        End If
    End Sub

    Friday, June 30, 2017 12:11 PM
  • Damn, Tom, we love dead technology at my company!  Seriously, though, if ACCDB is the way to go, can that be accomplished (at least, partially) using the Upsize Wizard in Access 2010 (the version I have available)?  Also, what is a DSN-less connection?
    Friday, June 30, 2017 12:15 PM
  • I created a simple ADP in A2010, then opened a new ACCDB and using Import > Access I was able to import form objects into the new database.

    The tables I would migrate from Access to SQL Server using "SQL Server Migration Assistant".

    You and your search engine won't have trouble finding relevant information about "Access DSN-less connection".


    -Tom. Microsoft Access MVP

    Friday, June 30, 2017 1:49 PM
  • Right after my previous response, with time to kill, I tried converting my un-upsized .mdb to .accdb using Access 2010.  That worked fine, so I then went and ran the upsize wizard against the .accdb, selecting the option to link the new tables to my application rather than creating an .adp; that worked fine, too.  I don't know if the connection the wizard creates to the SQL database is DSN-less, though, so I guess I'll have to do the search you suggest.  In any event, the form I was having trouble with in the original ticket (opened by the DoCmd.OpenForm method) is working fine.  So, progress has been made.

    Aside from still needing to understand DSN-less, I see in my upsized front-end that all of my tables now appear twice: once as SQL tables with the original table names, and once as name_local tables, which appear to be the original tables that were linked to my backend .acccdb (e.g. both Telephone and Telephone_local appear under Tables). Are the "_local" tables merely kept for reference/troubleshooting? Can they be safely removed?

    Friday, June 30, 2017 2:07 PM
  • Those can be removed.

    Good thing you're keeping lots of backups (hint, hint).


    -Tom. Microsoft Access MVP

    Friday, June 30, 2017 2:10 PM
  • Thanks.  Also, I notice that when I hover my cursor over the tables in my upsized application, I see a connection string that starts with "ODBC;...".  Does this mean that the upsize wizard created the DNS-less connections to which you referred?  And, if so, is there anywhere you can view these within the database (aside from hovering the cursor)?
    Friday, June 30, 2017 3:46 PM
  • The hidden system table MSysObjects has the connection string for each linked table.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Friday, June 30, 2017 4:14 PM
  • Thanks for pointing me to hidden tables, Bill.
    Friday, June 30, 2017 7:07 PM
  • Thanks for pointing me to hidden tables, Bill.
    You're welcome, Tim.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Wednesday, July 5, 2017 5:42 PM