Geeks With Blogs

News My Blog has been MOVED to https://mfreidge.wordpress.com
Michael Freidgeim's OLD Blog My Blog has been MOVED to https://mfreidge.wordpress.com

A long time ago I reported the problem with MS Access ADP Reports/Forms with subforms/subreports where Link
Master/Child fields are numeric but not integer (i.e scale greater than zero). 
I had the join field  defined as numeric(28.2).
However Access doesn't recognize scale and generate incorrect SQL
statements, eg
exec sp_executesql N'SELECT  *  FROM "dbo"."PickSlipOverdues" WHERE
((@P1 = "PatronID"))', N'@P1 numeric(28)', 7
instead of correct , N'@P1 numeric(28.2)', 7.01
As a result join doesn't work and subform is not populated.
The parent form is bound to the table with primary field defined as
numeric(28.2), child subforms are bound to the view or select
statements that has the same link field definition -numeric(28.2)

Today I had to fix the report with subreports where again Master/Child fields link fields are numeric (e.g . [BookID] [numeric](28, 2))
The workaround is to add to RecordSource  of each report/subreport string version of the key , e.g. (,cast(BookID as nvarchar(25)) as sBookID )
After this it is possible to specify sBookID as Master/Child fields and Access correctly generates string joins. 

Note: This post originally appeared on an thespoke web site, but site is down at the moment and the text was recovered from G o o g l e's cache

Posted on Wednesday, September 27, 2006 4:08 PM MS Access | Back to top


Comments on this post: Link Child/Master fields in ADP do not work for Numeric key

# re: Link Child/Master fields in ADP do not work for Numeric key
Requesting Gravatar...
I've been racking my brains for several days trying to resolve this problem. I've looked on many sites and found nothing about this SERIOUS problem with Access. MICROSOFT are you listening!
Thanks ,you are a life saver.
PS: I'am an IT professional and have been programming for over 30 years.
Left by Gérard on Feb 24, 2008 2:24 PM

# re: Link Child/Master fields in ADP do not work for Numeric key
Requesting Gravatar...
I am having a problem, i have various forms with a command button to open a subform, the problem though is that even although linking everything correctly (incl. relationships) the data relevant to the specific record does not show, it just keeps showing the same data for all records. What can i do?
Left by Sheldon on Feb 11, 2009 7:12 PM

# re: Link Child/Master fields in ADP do not work for Numeric key
Requesting Gravatar...
I had this problem (Access 2003 ADP/SQL Server 2000). I removed the Master/Child fields and managed my subform with recordsources and filters instead.
Left by Taz73 on Dec 15, 2009 4:55 AM

Your comment:
 (will show your gravatar)


Copyright © Michael Freidgeim | Powered by: GeeksWithBlogs.net