Form Problems- First uneditable, now finds no records?

[colin_e]

Hi Guys. new to access and just finding my way around.

I created a (largish) form from a query built on 3 tables using the wizard.
I understand that in Access queries are like Views in other apps, i.e. they
can be updated.

However my form was read-only. The form props weren't set to read-only, but
any attempt to modify a field resulted in an error beep and no change.

While looking at what might be causing this I tweaked the table joins. The
query still runs fine, but this resulted in the form showing no records (even
thought the query has 28?).

Rebuilding the form using the wizard results ina working form, including
update!, but without all my careful tweaks of the layout.

My question is, when a form fails like this, how the hell do you diagnose
the problem? I have not yet found an error log or other tools, just an
defunct form. What's the route to fix this kind of problem without rebuilding
every form from scratch?

Regards: Colin

 

[John Vinson]

Hi Guys. new to access and just finding my way around.

I created a (largish) form from a query built on 3 tables using the wizard.
I understand that in Access queries are like Views in other apps, i.e. they
can be updated.

Some can; some cannot. The more tables the less likely that you can.
See the online help for Updateable for details.

However my form was read-only. The form props weren't set to read-only, but
any attempt to modify a field resulted in an error beep and no change.

While looking at what might be causing this I tweaked the table joins. The
query still runs fine, but this resulted in the form showing no records (even
thought the query has 28?).

If you open the query in Datasheet view do you see records? how about
the *> new record line?

Rebuilding the form using the wizard results ina working form, including
update!, but without all my careful tweaks of the layout.

My question is, when a form fails like this, how the hell do you diagnose
the problem? I have not yet found an error log or other tools, just an
defunct form. What's the route to fix this kind of problem without rebuilding
every form from scratch?

Opening the Recordsource form in datasheet view; reviewing the Form
properties (Allow Edits and Allow Additions, in particular); you're
right, it is obscure and complicated.

Just FWIW a Form based on a three-table Join will be awkward for the
user even if it is updateable. Typically one would use a Form for the
"one" side table of a relationship, with one or more Subforms for the
"many" side table(s).

John W. Vinson[MVP]

 

[colin_e]

Hey John,
Thanks for the reply. Sorry for the delay in getting back to you, Christmas
got in the way ;-)

Some can; some cannot. The more tables the less likely that you can.
See the online help for Updateable for details.

OK, I was searching for "read only" or "writeable". I'm not too surprised if
joined queries are harder to update, however what caught me out was that
faced with two forms, one working (writeable) and one not, I could not see
any difference in the basic data access setup, or see any way to debug the
problem!

If you open the query in Datasheet view do you see records? how about
the *> new record line?

The query showed 28 records in datasheet view, plus one for the extra record
line. Again i gave up and rebuilt it in the end.

Opening the Recordsource form in datasheet view; reviewing the Form
properties (Allow Edits and Allow Additions, in particular); you're
right, it is obscure and complicated.

In the end I had to re-create the form in a wizard then painstakingly re-lay
it out. Not a lot of fun. I would have really liked a "copy form layout to
another form" capability.

Just FWIW a Form based on a three-table Join will be awkward for the
user even if it is updateable. Typically one would use a Form for the
"one" side table of a relationship, with one or more Subforms for the
"many" side table(s).

Understood. However my initial experiments with subforms have found these to
be pretty tricky also. Simple cases created by wizard are fine. But it seems
that if you then edit the table structure or joins you can break the link
between form and subform, and again it's somewhere between hard and
impossible to recreate.

I also found creatng a master form based on a manual join (i.e. not a
standalone query) confused access. It claimed form elements were unbound when
in fact they worked OK, and I could not initially work out how to make the
subform link. I think I have it now, but again only by recreating the forms
from scratch mutiple times looking for a way through.

There doesn't seem to be a simple "redefine subform relationship" tool for
an existing form/subform pair. As far as I can see, if the magic VBA code in
the master form gets lost (which can happen easily) you would have to create
a dummy master/sub form pair just to be able to copy/paste the magic linking
code back into your carefully crafted originals. It can probably work but it
isn't too much fun.

 

[John Vinson]

Hey John,
Thanks for the reply. Sorry for the delay in getting back to you, Christmas
got in the way ;-)

Hope it was merry and that you were able to forget about Access

The query showed 28 records in datasheet view, plus one for the extra record
line. Again i gave up and rebuilt it in the end.

That can happen... and yes, it's annoying as all get out.

In the end I had to re-create the form in a wizard then painstakingly re-lay
it out. Not a lot of fun. I would have really liked a "copy form layout to
another form" capability.

Well.. there is, actually. Create a new form, WITHOUT using a wizard
or selecting a table; set its Recordsource property; open the old form
in design view. Select all the controls, Ctrl-C to copy, select the
new form, Ctrl-V to paste. This MIGHT transfer the same problem but
it's quick and worth trying.

Understood. However my initial experiments with subforms have found these to
be pretty tricky also. Simple cases created by wizard are fine. But it seems
that if you then edit the table structure or joins you can break the link
between form and subform, and again it's somewhere between hard and
impossible to recreate.

I also found creatng a master form based on a manual join (i.e. not a
standalone query) confused access. It claimed form elements were unbound when
in fact they worked OK, and I could not initially work out how to make the
subform link. I think I have it now, but again only by recreating the forms
from scratch mutiple times looking for a way through.

There doesn't seem to be a simple "redefine subform relationship" tool for
an existing form/subform pair. As far as I can see, if the magic VBA code in
the master form gets lost (which can happen easily) you would have to create
a dummy master/sub form pair just to be able to copy/paste the magic linking
code back into your carefully crafted originals. It can probably work but it
isn't too much fun.

I'm confused here, and we may be thinking of different things. A
Form-Subform link requires NO CODE AT ALL - simply setting the Master
and Child Link Field properties. Are you in fact talking about a Popup
form being opened from code, rather than a Form in a Subform control?

John W. Vinson[MVP]

 

[colin_e]

Hey John, thanks for the reply.

:

....

Well.. there is, actually. Create a new form, WITHOUT using a wizard
or selecting a table; set its Recordsource property; open the old form
in design view. Select all the controls, Ctrl-C to copy, select the
new form, Ctrl-V to paste. This MIGHT transfer the same problem but
it's quick and worth trying.

Cool. I'll give it a try.

I'm confused here, and we may be thinking of different things. A
Form-Subform link requires NO CODE AT ALL - simply setting the Master
and Child Link Field properties. Are you in fact talking about a Popup
form being opened from code, rather than a Form in a Subform control?

Hmm, maybe I am getting confused. I thought when I looked at a working
master form I found some fancy event handling code to propagate the changes
in the master form. I must have been looking at something else.

But man, this stuff is shakey. Just now while editing some of the VBA (no
form changes) my subform stopped showing any data. I went and changed the
linking fields from "OrgId" and "OrgID" to "OrgID" and "OrgID" (i.e, exactly
the same) and bingo , it started working again...