Form Wizard

  • Thread starter Thread starter Yolanda Buckles
  • Start date Start date
Y

Yolanda Buckles

I am trying to build a form, with records from 3 tables. After completing
the form, I get "Wizard can't create form". If I drop off a few fields, it
works, but I need all the fields in the form. I am sure the simple answer
is subform, but why will the wizard not add all the records I want to a
form?

TIA

TB
 
Yolanda

If you are "sure the simple answer is [a] subform", have you tried that
approach?

First create a form that will only hold the related ("subform") rows,
including the ID field(s) that will link back to the main form info.

Then create a main form that hold all the higher level info, including the
ID field that links to the related (subform) rows.

Then add the subform to the mainform in design mode.

An example of this would be the OrderDetails (subform) for a given Order
(main form). Since you didn't provide an example of your data, it's a
little tough to offer any more specific suggestions.
 
Jeff,

Thanks for your reply.

I didn't want to use a subform, as the data always seems to display as a
table, rather than just form field input boxes like the main form. Maybe
there is a way around that.

I still would like to know why I can't add all the fields to a form. I
have tried to find a limitation to the number of fields allowed, but no luck
yet.

Here is the premise. I have several hundred construction projects. Each is
tracked from concept through design, permitting, ROW acquisition,
construction and turnover to operations. I started (like a newbie) with one
table, as no info will be cross referenced, just stored and retrieved. That
didn't last long though. It seems there is a limit to the number of fields
in a table ("property value to large" error.) Then split them into seperate
tables. Now, I have to create a form (to fill in the data) based on several
tables, instead of just one. For example, the project number, contract
number & project name all come from the ProjectData table. The design
Project Manager, task assignment, 30% plan review date, and so on, come from
the DesignData table. Still yet, the 30% invoice amount, the design budget
and survey invoice come from the ProjectFunding table. All of this info,
however, is required on the "Design" form. So there is the dilema.

Any help is appreciated.

TIA

TB
 
Yolanda

Folks moving over from Excel often try making an Access table work the way
they're comfortable with. As you are finding, Access is NOT a spreadsheet!

Before you go any further, I suggest stepping back from the computer and
using paper/pencil to sketch out the "entities" and "relationships" you are
interested in recording/report data about. You didn't mention what
guidelines you used to break your large, all-in-one table apart. Any chance
you looked into Access HELP on "normalization"?

Any chance you used the Analyzer tool to help?

What are the relationships among Project, Design and Funding tables? How
many records would you expect to see in, say Design, for every record in
Project?

More info, please...

Jeff Boyce
<Access MVP>
 
Jeff,
I have beaten access help to death, and I am sure my answer is there, I just
don't really understand it maybe. Analyzer was no help. I have already
split the database into seperate tables, so Table Analyzer wouldn't do.
Performance Analyzer showed no problems. I literally have several hundred
fields, so to list them here would be over the top, but as I explained, the
information is very straightforward in that for each project, all the data
is unique. Originally, I had one table, with ProjectNumber as the key
field. So ProjectName is always unique to Project Number, and
ContractNumber is (almost) always unique to ProjectNumber (in a few cases,
we may let two projects go in one contract, but usually not.)
DesignEngineer field will have many Projects, but that is mearly a dropdown
list, as we will have no need to sort by DesignEngineer.

I realize this is sketchy information, but I am not sure just what is needed
to paint the entire picture. If it helps, I have stepped away, and written
down what is needed, but it only takes a few seconds when a dataset is this
straighforward. 1 Project has 1 ProjectNumber & has 1 ContractNumber & has
1 30%DueDate & so on. The tables are as follows:

ProjectData: - (holds ProjectNumber, ContractNumber, ProjectName, Length,
TypicalSection..., about 10 fields)
Design - (holds ProjectNumber(what I use to relate the tables, 1to1),
DesignAgendaItemDate, DesignProjectManager, DesignEngineer, 30%DueDate,
30%Received...,48 fields)
Construction - (holds ProjectNumber(what I use to relate the tables, 1to1),
ConstructionAgendaItemDate, ConstructionProjectManager, ContractDays,
HighBidder...,14 fields)
Funding - (holds ProjectNumber(what I use to relate the tables, 1to1),
TotalBudget, DesignBudget (not duplicated in Design), SurveyBudget..., 87
fileds)
Survey - (holds ProjectNumber(what I use to relate the tables, 1to1),
SurveyCrewChief, DateSurveyOrdered, DateSurveyComplete...,13 fields)
ROW - (holds ProjectNumber(what I use to relate the tables, 1to1),
ROWSpecialist, ROWAcreageNeeded..., 7 fields)
Utilities - (holds ProjectNumber(what I use to relate the tables, 1to1),
Utilities30%Review, Utilities90%Review, UtilitesROWCleared...,23 fields)
Signals - (holds ProjectNumber(what I use to relate the tables, 1to1),
BeginDesign, 30%Review, 90%Review, 100%Complete...,23 fields)
Permitting - (holds ProjectNumber(what I use to relate the tables, 1to1),
StartDate, USFWSPermitApplied, USFWSPermitApproved...,12 fields)

The remaining tables are all lookup tables and function properly, so are
ommited here. They are not related to any other table.
 
It would appear that you have a single table with over 200 fields.

That fact is hidden by what would appear to be the inappropriate use of the
1 to 1 relationship between tables.

I would suggest that you read up on normalisation and then apply that to
your data. You may wish to check the Access 2002 Knowledge Base article
283878 dated 10 October 2003. (Referenced on MSDN Library DVD July 2004) or
online at http://support.microsoft.com/?id=283878
 
Craig,

Thanks for your reply.

Actually, I have a table with 83 fields, and several more smaller tables as
noted in my last post. My table is in third normal form. No redundancy,
and items seperated by structured groups.

I agree, the 1to1 is probably the problem, but with only one related key, I
need to change the Pkey to any other key in the related table, in order to
make a 1-many, is that correct. I will give it a try, and let you know.

My problem, however, is with the Form, as noted in my first post.

Thanks for the help.

TB
 
Yolanda

Based on your description of the tables and your response to Craig's post,
I'm wondering if Access is the proper tool for you to use to manage
projects...?

Another possibility would be to create a tab control on your form, and group
related fields together on tabs. But if the form can't hold all the fields
you have already, I'm not sure it could if you used a tab control.
 
TB

I know that you have a table with 80 or so fields however all the other 1 to
1 tables are in effect part of the main table as they are identified only by
the value of the Primary Key, the same key used in the main table.

The most common, probably only normal use of the 1 to 1 relation is for what
can be referred to as subtyping or subclassing. Another less formal use is
for less frequently recorded information in order to reduce the number of
fields containing a null value.

Subtyping is generally concerned with an entity that can have types about
which different information is to be recorded. An example of this is
Personnel where the main table may contain Name and Address info only. If
Personnel can be employed in different roles they may have data that applies
when they are a Manager and/or when they are Engineer etc. etc.

You would have a Manager and Engineer table and these would contain data
about the Personnel member when they operated in either of these roles.

Your data would appear to contain progress information about each stage of
the Project.

It _may_ be worth considering designing your data to cater clearly for the
distinct Stages in the project.

You would have a table for the Project and you would have a Stage table with
records such as Initiation, Design, Construction, Survey, ROW, Utilities,
Signals, Permits. (or whatever stages are appropriate)

Between the Project and the Stage (which would be a many to many
relationship) you would have a table which has a 1 to many relationship
between Project and Project Stage and a 1 to many between Stage and Project
Stage.

This is only a suggestion and may not be appropriate for your specific
requirements.

As to the Form you would need this is unlikely to be achievable using Form
Wizard although you could try.

You would in effect structure the form in line with the tables. The main
form would be the Project form that would contain the fields about the
project that were a constant irrespective of the stage at which the project
is in. You would then need a series of subforms for each Project Stage. You
could then display these subforms either as one to each tab of a tab control
or you could add a pull-down to the main form that could select the
appropriate Project Stage and then you could display that subform only. With
the last approach I generally change the SourceObject to the subform and
design all the subforms to be of the same physical dimensions.

The Form Wizard does not produce anything really useable especially for more
complex designs.
My problem, however, is with the Form, as noted in my first post.
Click to expand...

Yes that may well be the case but it is the foundations that may need
attention before the walls go up. :-)
 
Back
Top