Why can access only display my forms in design view?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to build what I thought was a very simple database. I have 4
tables and three querys, one of the tables and two of the querys are directly
linked to seperate forms. For some reason although the files are fine and
access has no trouble saving them, only the table form and ONE of the Query
forms are able to open in Form view. THere is no difference in the way the
data has been set up. Any ideas?
Renwick
 
Renwick said:
I am trying to build what I thought was a very simple database. I
have 4 tables and three querys, one of the tables and two of the
querys are directly linked to seperate forms. For some reason
although the files are fine and access has no trouble saving them,
only the table form and ONE of the Query forms are able to open in
Form view. THere is no difference in the way the data has been set
up. Any ideas?
Click to expand...

Any error messages? What happens when you try to open (in form view)
one of the forms that are giving you problems? Can you open the query
the form is based on directly, in datasheet view? If you do, does it
contain any records? Can you edit and add records through the query?
That may be relevant, depending on what exactly is going wrong.
 
Thanks for looking on this post. When I open the form in form view I just get
a blank sheet, the things are there when I check in design view though. I can
open the quiery with no problem, and I did get it working by altering the
relationships slightly. I changed the Key identifier and the forms would open
but the data I could save was useless because I couldn't duplicate the ID
number that I was trying to link so I added an auto number to all of the
tables to id them as the Key but when I try to turn them into a relationship
the way the previous one was, I get an error message saying about it not
being spelled correctly or being the wrong OLE. I have double checked
everything and can find no reason for this error. I have never had this
problem before and I'm a bit confused.
Renwick
 
Renwick said:
Thanks for looking on this post. When I open the form in form view I
just get a blank sheet, the things are there when I check in design
view though. I can open the quiery with no problem, and I did get it
working by altering the relationships slightly. I changed the Key
identifier and the forms would open but the data I could save was
useless because I couldn't duplicate the ID number that I was trying
to link so I added an auto number to all of the tables to id them as
the Key but when I try to turn them into a relationship the way the
previous one was, I get an error message saying about it not
being spelled correctly or being the wrong OLE. I have double checked
everything and can find no reason for this error. I have never had
this problem before and I'm a bit confused.
Click to expand...

I'm not sure what's going on with that error message, especially since
you didn't quote it exactly and it doesn't make sense as you wrote it in
your post. Here's a hint: often you can press Ctrl+C when an error
message is displayed, to copy the message to the clipboard, and then
paste it into a message. It doesn't always work, but when it does it
saves a lot of trouble.

But setting that aside, a form that opens completely blank is generally
due to a combination of two circumstances: (1) there are no existing
records to display, and (2) no records can be added because either the
form doesn't permit it, or the query is not updatable. I think this is
what is happening in your case. There are various reasons why a query
may be non-updatable. One of them is that the query doesn't include the
primary keys of the necessary tables, and I think that may be relevant
to your case. There's a help topic on the subject; if you type
"troubleshoot queries" in the help search box, you'll probably be able
to locate the topic. Or, depending on which version of Access you are
running, you may get there by searching for "When can I update data from
a query?"

If possible, do the following:

1. Make sure that every table has a primary key field.

2. In your query, make sure the appropriate fields that link the tables
are joined.

After modifying your query's design, open it directly as a datasheet and
see if the "new record" line appears. if so, it's updatable; if not,
and if you can't figure out from the help topic what is wrong with it,
post the SQL of the query in a reply to this message, and maybe we can
figure it out.
 
I have now fixed all of it bar one section. The last query based form is
still just a grey sheet. I am not getting a new record line so I gather that
means that it is unupdateble. I would love to post a sql version of the query
here but I have no idea how to go about it. Please let me know how to post a
copy. Is it just a copy and paste of the query? Your help is being very much
apprecciated.
Renwick
 
Renwick said:
I have now fixed all of it bar one section. The last query based form
is
still just a grey sheet. I am not getting a new record line so I
gather that means that it is unupdateble. I would love to post a sql
version of the query here but I have no idea how to go about it.
Please let me know how to post a copy. Is it just a copy and paste of
the query? Your help is being very much apprecciated.
Click to expand...

Just flip the query into SQL view, copy the SQL statement, and paste it
into your message. That may (or may not) be enough to tell why the
query isn't updatable.
 
OK, Maybe I am still not doing this right. THe query is still one of my
problems, but I also cant get the forms to desplay the data I need it to even
although the fields are there. This is a data base to store emotional
intelligance and self esteem scores of children for a research project. I
have one table that holds the Pre data and the name and school and basic
data, then I have a query that supposedly pulles together the basic data from
the Pre table together with the content for the Post.But although the fields
are there the data is not being displayed in the post table. I have to re
enter it and it becomes another reccord in the Pre data table. I need the
table to consistantly display the data for the same child without having
their name up so they have ID numbers. I have fixed the query, it was a Key
problem, but I still cant get the forms to display the right data from the
tables. I need each table to have the school, and id number on them, but I
don't know how, although its in the table.
Please help.
Renwick
 
Oh and here is the last query that wont update.

SELECT [Number display Totals].[Junior ID], [Number display Totals].[Pre
Total Self Percentile], [Number display Totals].[Pre Total Self T Score],
[Number display Totals].[Post Total Self Percentile], [Number display
Totals].[Post Total Self T Score], [Post Marsh Cals].[Total Self], [Marsh Pre
Calc].[Total Self]
FROM [Marsh Pre Calc], [Post Marsh Cals], [Number display Totals];

I need to compare the results of two expresson querys in the smae form and
use the results to enter further data on another table. All I want this query
to do is pull that data together for me. Incidently, I just worked out how to
get the data displayed. Thank you for helping me.
Renwick
 
Maybe i could put the post data into a sub table or something. It still isn't
working, I thought I had it. It was fine untill I Put data into it. would a
subtable enable me to keep the first part of the record the same while
changing the rest, and if it will fix it, could you tell me how to find info
 
Ok, fixed it all now except... theres always one thing. I have my last query
working in the form but it wont let me enter any new data into the table that
is part of the query. It will display itand let me move through the reccords
but I cant enter anything. any ideas.
Renwick
 
THis is the SQL for the queryy as it is working. I need to be able to enter
data into the Number display Table that is part of the query. Nothing is read
only or locked or anything, I checked.

SELECT [Number display Totals].[Pre Total Self Percentile], [Number display
Totals].[Pre Total Self T Score], [Number display Totals].[Post Total Self
Percentile], [Number display Totals].[Post Total Self T Score], [Marsh Pre
Calc].[Total Self] AS [Marsh Pre Calc_Total Self], [Post Marsh Cals].[Total
Self] AS [Post Marsh Cals_Total Self], [Marsh Pre].School, [Marsh Pre].Name,
[Marsh Pre].[Year 5/6], [Marsh Pre].Male, [Marsh Pre].[Junior ID]
FROM (([Marsh Pre] LEFT JOIN [Marsh Pre Calc] ON [Marsh Pre].[Junior ID] =
[Marsh Pre Calc].[Junior ID]) INNER JOIN [Post Marsh Cals] ON [Marsh
Pre].[Junior ID] = [Post Marsh Cals].[Juniour ID]) LEFT JOIN [Number display
Totals] ON [Marsh Pre].[Junior ID] = [Number display Totals].[Junior ID];

Renwick
 
THis is the SQL for the queryy as it is working. I need to be able to enter
data into the Number display Table that is part of the query. Nothing is read
only or locked or anything, I checked.

SELECT [Number display Totals].[Pre Total Self Percentile], [Number display
Totals].[Pre Total Self T Score], [Number display Totals].[Post Total Self
Percentile], [Number display Totals].[Post Total Self T Score], [Marsh Pre
Calc].[Total Self] AS [Marsh Pre Calc_Total Self], [Post Marsh Cals].[Total
Self] AS [Post Marsh Cals_Total Self], [Marsh Pre].School, [Marsh Pre].Name,
[Marsh Pre].[Year 5/6], [Marsh Pre].Male, [Marsh Pre].[Junior ID]
FROM (([Marsh Pre] LEFT JOIN [Marsh Pre Calc] ON [Marsh Pre].[Junior ID] =
[Marsh Pre Calc].[Junior ID]) INNER JOIN [Post Marsh Cals] ON [Marsh
Pre].[Junior ID] = [Post Marsh Cals].[Juniour ID]) LEFT JOIN [Number display
Totals] ON [Marsh Pre].[Junior ID] = [Number display Totals].[Junior ID];
Click to expand...

If [Number Display Totals] - or any of the components of this query -
is itself a Totals query, the resulting query will not be (and cannot
be made) updateable. You really should avoid using complex multitable
queries if you want to update them!

Would it be possible to have a Form (for the main table) with a
Subform? You could either put the totals query or the summary data,
read only, into the subform; or have an updateable subform of a
non-updateable main form. If these are actually tables rather than
queries, the form/subform construct will often allow updating even
when the multitable query will not.

John W. Vinson[MVP]
 
Thanks so much, I thought that was it. You have solved my problem though.
THanks again.
Renwick

John Vinson said:
THis is the SQL for the queryy as it is working. I need to be able to enter
data into the Number display Table that is part of the query. Nothing is read
only or locked or anything, I checked.

SELECT [Number display Totals].[Pre Total Self Percentile], [Number display
Totals].[Pre Total Self T Score], [Number display Totals].[Post Total Self
Percentile], [Number display Totals].[Post Total Self T Score], [Marsh Pre
Calc].[Total Self] AS [Marsh Pre Calc_Total Self], [Post Marsh Cals].[Total
Self] AS [Post Marsh Cals_Total Self], [Marsh Pre].School, [Marsh Pre].Name,
[Marsh Pre].[Year 5/6], [Marsh Pre].Male, [Marsh Pre].[Junior ID]
FROM (([Marsh Pre] LEFT JOIN [Marsh Pre Calc] ON [Marsh Pre].[Junior ID] =
[Marsh Pre Calc].[Junior ID]) INNER JOIN [Post Marsh Cals] ON [Marsh
Pre].[Junior ID] = [Post Marsh Cals].[Juniour ID]) LEFT JOIN [Number display
Totals] ON [Marsh Pre].[Junior ID] = [Number display Totals].[Junior ID];
Click to expand...

If [Number Display Totals] - or any of the components of this query -
is itself a Totals query, the resulting query will not be (and cannot
be made) updateable. You really should avoid using complex multitable
queries if you want to update them!

Would it be possible to have a Form (for the main table) with a
Subform? You could either put the totals query or the summary data,
read only, into the subform; or have an updateable subform of a
non-updateable main form. If these are actually tables rather than
queries, the form/subform construct will often allow updating even
when the multitable query will not.

John W. Vinson[MVP]
Click to expand...
 
Back
Top