Subform not displaying for new records after upsizing

Discussion:

(too old to reply)

Max Right

2010-03-18 00:42:29 UTC

Hello

I'm having problem with the subform not displaying after upsizing
access backend db to sql 05.

I have an access FE and I upsized my BE to sql 05, the table structure
and data transfered flawlessly.

I have 4 forms referencing 4 tables. the tables have PK autonumbers
and foreing keys relating to each other.

<a href="http://tinypic.com" target="_blank"><img src="http://
i43.tinypic.com/ehkghg.jpg" border="0" alt="Image and video hosting by
TinyPic"></a>

The main(frmMain) form has 1subform(frmLdata), and my secondary
form(frmGdata) also has one subform(frmGCdata).
The secondary(frmGdata) form displays data dependant on the
main(frmMain) form and subform(frmLdata).

after migrating and linking the 4 tables to SQL 05, my main(frmMain)
form and subform(frmLdata) display the data correctly and tables are
updated accordingly in sql. when I open my secondary(frmGdata) form,
the form data shows up and its table reflects the changes/additions,
the subform(frmGCdata) also shows up but only for existing entries.

When I try to make a new record in the secondary form(frmGdata), I'm
able to do that, but the subform(frmGCdata) disappears.

The secondary(frmGdata) form is opened by a cmd button with link
criteria frmMain![Mindex]

my record source for the secondary subform(frmGCdata) is:
SELECT tblGCdata.Mindex, tblGCdata.Gindex, tblGCdata.Lindex,
tblGCdata.aField FROM tblLdata LEFT JOIN tblGCdata ON
tblLdata.Lindex=tblGCdata.Lindex WHERE (((tblLdata.Mindex)=Forms!
frmMain!Mindex));

the child and parent fields linking the subform(frmGCdata):
Gindex;Mindex

my FE works flawlessly if I link the tables back to the access
backend.

could this be because sql has a different way of creating/writting/
updating records???

I am quite new to SQL...so I might be missing something that may bee
quite obvious.

Sylvain Lafontaine

2010-03-19 15:57:33 UTC

Permalink

The first thing to look would be to make sure that the upsized tables have
all a primary key defined on the SQL-Server iftself; as the wizard will
often forgot to set the primary key on one or more tables on the backend.

With some older versions of Access, if you also have other unique indexes,
the name of the primary key should also be alphabetically smaller than the
name of these other unique indexes. (I'm talking about the name of the
primary key constraint itself; not about the name of the field(s) or
columns.). Don't know if this is still relevant.

In the past, I've also noticed some problem with the use of composite
primary keys; ie., primary keys with more than a single field.

Some type of fields like real or float can also be problematic.

You can try adding a timestamp field on these tables. This will often help.

Finally, don't forget to refresh the linked tables every time you will make
a change on the design of any of the backend tables!
--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

Post by Max Right
Hello
I'm having problem with the subform not displaying after upsizing
access backend db to sql 05.
I have an access FE and I upsized my BE to sql 05, the table structure
and data transfered flawlessly.
I have 4 forms referencing 4 tables. the tables have PK autonumbers
and foreing keys relating to each other.
<a href="http://tinypic.com" target="_blank"><img src="http://
i43.tinypic.com/ehkghg.jpg" border="0" alt="Image and video hosting by
TinyPic"></a>
The main(frmMain) form has 1subform(frmLdata), and my secondary
form(frmGdata) also has one subform(frmGCdata).
The secondary(frmGdata) form displays data dependant on the
main(frmMain) form and subform(frmLdata).
after migrating and linking the 4 tables to SQL 05, my main(frmMain)
form and subform(frmLdata) display the data correctly and tables are
updated accordingly in sql. when I open my secondary(frmGdata) form,
the form data shows up and its table reflects the changes/additions,
the subform(frmGCdata) also shows up but only for existing entries.
When I try to make a new record in the secondary form(frmGdata), I'm
able to do that, but the subform(frmGCdata) disappears.
The secondary(frmGdata) form is opened by a cmd button with link
criteria frmMain![Mindex]
SELECT tblGCdata.Mindex, tblGCdata.Gindex, tblGCdata.Lindex,
tblGCdata.aField FROM tblLdata LEFT JOIN tblGCdata ON
tblLdata.Lindex=tblGCdata.Lindex WHERE (((tblLdata.Mindex)=Forms!
frmMain!Mindex));
Gindex;Mindex
my FE works flawlessly if I link the tables back to the access
backend.
could this be because sql has a different way of creating/writting/
updating records???
I am quite new to SQL...so I might be missing something that may bee
quite obvious.

Sylvain Lafontaine

2010-03-19 16:02:56 UTC

Permalink

Another possibility could be the LEFT JOIN or the fact that the source of
the subform is based on two tables. The multi-step process of Access is very
buggy when it comes to SQL-Server.

If I remember correctly (it has been many years since the last time that
I've worked with ODBC Linked Tables), there was a UniqueTable property that
you could set for the subform; this should help for the multi-step process.
--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

Max Right

2010-03-22 18:35:28 UTC

Permalink

Sylvain,

thank you so much for the input, you hit the nail on the head with
your second post.

the record source for the form is based off the form table and another
table, when the rowsource evaluates and has matching records the form
is displayed, but when a new record needs to be created, the subform
does not show because unlike in access, in sql a PK is not created
until a record is commited.

I was unable to find a way through this, but I did find a way around
it by placing the Add New feild on the header of the form, then I have
VBA code Grabing those values writing them to sql db, updating and
recalculating the form fields, this solved my issue.
Thank God that the form has only 2 fields so it was not that big of a
deal, but for future reference is there a way to force sql to create
the new PK field?

here's my code...

Dim tGC

If (Not Combo61) Then
Set tGC = CurrentDb.OpenRecordset("tblGCdata")
tGC.AddNew
tGC!Mindex = Forms!frmMain!Mindex
tGC!Gindex = Forms!frmGdata!Gindex
tGC!Lindex = Me.Combo61 '<--the value would evaluate to the FK Lindex
tGC!Ctension = Me.Combo60
tGC.Update

Me.Combo61.Value = ""
'tGC.Bookmark = tGC.lastmodified
Me.Requery
Me.Recalc
tGC.Close