change field properties, reflect in query

[Sue Potter]

I am new to Access-using Access 2002-am not familiar with VBA coding.
I have two "main" tables with a 1-to-1 relationship (couldn't fit all the
fields in one table-property value too large), and several lookup tables. I
created multitable forms from a query that contains both main tables. All was
working well until I had to change/add some lookup tables and establish some
new relationships between some fields in the main two tables and the lookup
tables. The query is no longer recognizing the formatting for the field types
(yes/no check box, lookup combo box, etc) or the default values for the
fields in one of the main tables, although it is recognizing the new
validation rules I have set. And it's not just the fields I changed, but
others that I left as is. I have checked the relationships and tried
recreating the query, but to no avail. Any suggestions?

 

[Steve Schapel]

Sue,

I can't offer an answer on the default values of fields not operating.
However, here is a comment that will hopefully help you get started in
Access in a good way. It is extremely unusual to require more than 30
fields in a table. If you have a table that has more than this, it is
almost certainly an indication of an incorrect design. If you would
care to post back with some more details of the fields you are using,
just some examples, then someone will be able to advise on re-structuring.

 

[Sue Potter]

Thanks for your response. I accidentally put this in 2 discussion forums, but
might as well keep with it for the extra help.

I am using Access for the data entry of surveys--the data will be converted
to SPSS for analysis in flat file format. I'm using lookup tables, validation
rules, etc with forms designed to look exactly like the surveys in order to
minimize human error in data entry. In total, I have 230 distinct fields from
the survey, most of which are linked to other tables for lookups, some are
yes/no check boxes, a few text fields, and a few memo fields. Each respondent
only does a survey once, so I'm not sure it's possible to make the design
more efficient-e.g. there is never a scenario where one case/respondent has
multiple records for one field.

When I was creating the database, I kept getting the error message "Property
value too large." On the MS Help & Support website, there was an article
that suggested this was due to too many fields and/or the Default Value
property was set for memo fields. The article suggested to the split the
tables with a 1-to-1 relationship (which I did figure out) and change the
memo field default value property (which I couldn't figure out). I also
turned off the Track Auto Name feature because it was severely slowing down
the functioning--I would copy and paste fields (then rename new ones) for
survey questions that had the same response options (yes, no, don't know,
etc) to save on time and my own potential to make mistakes.

I ended up restarting from scratch once, and it all worked fine. Then it was
decided by the project group that we needed to change some of the response
options, and when I then modified existing lookup tables and created new ones
(to which some fields are now related), this is when I started to have teh
problem with the query. When in data view for the tables, I see the check
boxes, the drop down boxes, etc. When in the data view for the query, I see
those formats only for some fields.

 

[Tim Ferguson]

On Tue, 9 Nov 2004 09:14:03 -0800, Sue Potter <Sue

I have two "main" tables with a 1-to-1 relationship (couldn't fit all the
fields in one table-property value too large),

Ouch -- as a rule of thumb, more than about twenty fields in a table
starts to suggest that normalisation has not been achieved. Over two
hundred and there is a Definite Major Design Problem.

and several lookup tables.

Double ouch. Get rid of them -- I am assuming that you mean the horrendous
Lookup Fields?

All was
working well until I had to change/add some lookup tables

I'm not surprised.

Any suggestions?

Create the tables and relationships properly... never rely on Microsoft's
wicked and malevolent wizards, which are only out to destroy your castle
and steal your first born child.

HTH


Tim F