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?
