Form not applying new record source after requery

Hi Experts,

I have a form in ADP project based on a stored proc with a param.

Trying to chance forms record source by code and for some reason nothing takes affect..

See attached form data property.

This is the code I have for changing the property, anything missing?
    Me.RecordSource = "proc_FilterOrientationNotesUniqueEmp"
    Me.Requery

Open in new window

Untitled.png
LVL 6
bfuchsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
My first thought would be that the SP is not returning records, or is not properly formed. Can you show the SP?

Does your SP have a Parameter named "spWhere Text"? If so, and if you have a space, you should enclose that in square brackets.
bfuchsAuthor Commented:
Hi,

This is the stored proc.

USE [PlacementNP]
GO
/****** Object:  StoredProcedure [dbo].[proc_FilterOrientationNotesUniqueEmp]    Script Date: 01/17/2018 15:06:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[proc_FilterOrientationNotesUniqueEmp] (
 @strWhere varchar(5000) ,
 @strRetColums varchar(500) = null

 )
 with recompile
AS 
declare @tmp varchar(5000)
declare @strWhere1 varchar(5000) = ''
declare @strWhere2 varchar(5000)

--set @strWhere1 = (select [value] from ProgrammingSettings where [Code] = 'UniqueFilterOrientationNotesSql')

if @strRetColums is null

SET @tmp = ';with CTE_Orientations As
( select   O.EmployeeID
		 , max(O.ID) as LastOrientationID
   From    Orientations O 
   Group by O.EmployeeID
), CTE_Notes as
(  Select  EmployeeID
		 , LastOrientationID
         , max(N.id) as LastNoteID 
   from CTE_Orientations
   Left Outer Join OrientationNotes N on LastOrientationID = N.OrientationID
   Group by EmployeeID, LastOrientationID
)
   SELECT  --TOP (99.9999) PERCENT 
             CAST(CASE WHEN Last_Orient_Note_Day IS NULL OR Last_Orient_Note_Day < GETDATE() - 60 THEN 1 ELSE 0 END AS bit) AS Red
		   , N.ID
		   , O.EmployeeID 
           , O.FacilityID
		   , O.Active
		   , N.Note
		   , COALESCE (N.Day, O.DateEntered) AS Day
		   , N.Initial
		   , O.ID AS OrientationID
		   , O.DateEntered
		   , O.Initial AS OrientationInitial 
           , O.Traveler
		   , T.TovInfoLastDay AS LastDay
		   , O.DueDate
		   , N.Mailing
		   , O.DueDateInitial
		   , T.Last_Orient_Note_Day
		   , E.AvailibilityPDays
		   , E.AvailibilityPShifts
		   , E.City
		   , E.LastName
		   , E.FirstName
		   , E.Title
		   , E.AddressLine1
		   , F.Name AS FacilityName
		   , N.ID as LastNoteID
		   , T.TovInfoFacility1 AS LastFacility
		   , E.Degree
		   , E.NotSendTextMsg
		   , E.NotSendEmail
		   , E.Email
		   , E.Beeper
		   , E.SMSProvider 
		   , E.ExpectedGraduation

 ,E.LastName + '' '' + E.FirstName As EmpName

FROM         CTE_Notes C
INNER JOIN   Orientations O on C.LastOrientationID = O.ID 
INNER JOIN   Employeestbl E ON C.EmployeeID = E.ID 
LEFT OUTER JOIN   Facilitiestbl F ON O.FacilityID = F.ID 
LEFT OUTER JOIN  OrientationNotes N on C.LastNoteID = N.ID
LEFT OUTER JOIN  EmployeesDocsTov T ON C.EmployeeID = T.EmployeeID  
'

IF @strWhere IS not NULL and @strwhere <> 'ALL' and @strwhere <> 'Long' set @tmp = @tmp + ' where ' + @strWhere 

IF @strWhere IS NULL set @tmp = @tmp + ' where O.EmployeeID = 3' 

if @strWhere = 'Long'
begin
	set @strWhere2 = (select [value] from ProgrammingSettings where [Code] = 'UniqueFilterOrientationNotesSql')
	set @tmp = @tmp + ' where ' + @strWhere2 
end
--print @tmp
exec (@tmp)

Open in new window

when I execute the following in SSMS it does return records.
exec [proc_FilterOrientationNotesUniqueEmp] 'e.State=''ny'''

Open in new window

Thanks,
Ben
bfuchsAuthor Commented:
Does your SP have a Parameter named "spWhere Text"? If so, and if you have a space, you should enclose that in square brackets
The same works for the existing proc as shown on attached.

BTW, is there a way I can view thru SSMS if the stored proc is being executed?
looking for sort of a way to debug it, the way I would put a break point in code if this would be VBA.

Thanks,
Ben
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Mark WillsTopic AdvisorCommented:
>> BTW, is there a way I can view thru SSMS if the stored proc is being executed?

Not easily... You can get some info (but only while in cache)
select OBJECT_NAME(object_id, database_id),*  
FROM sys.dm_exec_procedure_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)

Open in new window

but pretty much useless...
In visual studio you should be able to step into the stored proc if running in debug, but also misses out with 'user' testing.
Best bet is to include some internal code to effectively trace your own events into your own 'debug' table

something like
if object_id('tbl_debug_procedures','U') is not null drop table tbl_debug_procedures

create table tbl_debug_procedures 
(id int identity primary key,
 spid int,
 procName varchar(100),
 rundate datetime default getdate(),
 stage varchar(100),
 content varchar(max),
 result varchar(max)     )

Open in new window

Then add an 'optional' param to your stored proc  @Debug char(1) = NULL

And while editing your stored proc, check if @debug = 'Y' and log your own 'events'

e.g
create proc usp_example_for_debug (@strWhere varchar(max), @debug char(1) = NULL)
as
BEGIN
   IF @debug = 'Y' -- some housekeeping first
   BEGIN
      if object_id('tbl_debug_procedures','U') is NOT null
	  BEGIN
	     Insert tbl_debug_procedures values (@@SPID,object_name(@@procid),getdate(),'Started',@strwhere,'0')
      END  
	  ELSE set @debug = 'N'
   END

   SET NOCOUNT ON  -- etc
   declare @rowcount int
   declare @tmp varchar(5000)
   SET @tmp = '; with CTE_Orientations As
( select   O.EmployeeID
		 , max(O.ID) as LastOrientationID
   From    Orientations O 
   Group by O.EmployeeID
), CTE_Notes as
(  Select  EmployeeID
		 , LastOrientationID
         , max(N.id) as LastNoteID 
   from CTE_Orientations
   Left Outer Join OrientationNotes N on LastOrientationID = N.OrientationID
   Group by EmployeeID, LastOrientationID
)
   SELECT  --TOP (99.9999) PERCENT 
             CAST(CASE WHEN Last_Orient_Note_Day IS NULL OR Last_Orient_Note_Day < GETDATE() - 60 THEN 1 ELSE 0 END AS bit) AS Red
		   , N.ID
		   , O.EmployeeID 
           , O.FacilityID
		   , O.Active
		   , N.Note
		   , COALESCE (N.Day, O.DateEntered) AS Day
		   , N.Initial
		   , O.ID AS OrientationID
		   , O.DateEntered
		   , O.Initial AS OrientationInitial 
           , O.Traveler
		   , T.TovInfoLastDay AS LastDay
		   , O.DueDate
		   , N.Mailing
		   , O.DueDateInitial
		   , T.Last_Orient_Note_Day
		   , E.AvailibilityPDays
		   , E.AvailibilityPShifts
		   , E.City
		   , E.LastName
		   , E.FirstName
		   , E.Title
		   , E.AddressLine1
		   , F.Name AS FacilityName
		   , N.ID as LastNoteID
		   , T.TovInfoFacility1 AS LastFacility
		   , E.Degree
		   , E.NotSendTextMsg
		   , E.NotSendEmail
		   , E.Email
		   , E.Beeper
		   , E.SMSProvider 
		   , E.ExpectedGraduation
		   , E.LastName + '' '' + E.FirstName As EmpName 

FROM         CTE_Notes C
INNER JOIN   Orientations O on C.LastOrientationID = O.ID 
INNER JOIN   Employeestbl E ON C.EmployeeID = E.ID 
INNER JOIN   Facilitiestbl F ON O.FacilityID = F.ID 
LEFT OUTER JOIN  OrientationNotes N on C.LastNoteID = N.ID
LEFT OUTER JOIN  EmployeesDocsTov T ON C.EmployeeID = T.EmployeeID 
'

   IF @debug = 'Y' Insert tbl_debug_procedures values (@@SPID,object_name(@@procid),getdate(),'Built @tmp',@tmp,'0')
  
   EXEC (@tmp)
   SET @rowcount = @@ROWCOUNT  
    
   IF @debug = 'Y' Insert tbl_debug_procedures values (@@SPID,object_name(@@procid),getdate(),'Exec @tmp',@tmp,@rowcount)

END

GO
      
-- now test

exec usp_example_for_debug 'e.State=''ny''',@debug = 'Y'

-- then check debug table

select * from tbl_debug_procedures
order by 2,3,4,1

Open in new window

bfuchsAuthor Commented:
Hi Experts,

You will not going to believe but I was simply not connected to the right database..

Appreciate your help, especially Mark for the debugging hints.

Thanks,
Ben

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
:) classic, and we have all been there / done that before...

Can also add errors to your debug table by collecting @@error
declare @error int

select 1 / 0

set @error = @@ERROR

select @error,text FROM sys.messages WHERE message_id = @error and language_id = 1033

Open in new window

bfuchsAuthor Commented:
Problem solved.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.