Forms

[Frazzled]

I have created a form that consists of data from 5 separate tables (form is
therefore based on a query). The data for each table is placed on a single
tab on the one form. After inputting, the data is placed into the tables but
after saving and opening again, the form shows no records (data has been
placed into appropriate tables). I need to be able to open the form and allow
users to search for their data using the form. What do I need to do?

Thanks.

 

[Frazzled]

I have created a form that consists of data from 5 separate tables (form is
therefore based on a query). The data for each table is placed on a single
tab on the one form. After inputting, the data is placed into the tables but
after saving and opening again, the form shows no records (data has been
placed into appropriate tables). I need to be able to open the form and allow
users to search for their data using the form. I should add that I have gone to Record, Remove Filter/Sort to see if this would change things and I have also gone to Data Entry to change that option.... Help!

Thanks.

 

[Rick Brandt]

I have created a form that consists of data from 5 separate tables (form
is therefore based on a query). The data for each table is placed on a
single tab on the one form. After inputting, the data is placed into the
tables but after saving and opening again, the form shows no records
(data has been placed into appropriate tables). I need to be able to
open the form and allow users to search for their data using the form.
What do I need to do?

Thanks.

Just a guess, but it sounds like your query used inner joins. In that
case if even one of the tables has no matching data them you get nothing
back.

I actually find it hard to believe that you created a query with 5 tables
that still allowed data editing. Two is the usual limit with three
working in a pinch. More than that is very unusual.

 

[Frazzled]

Okay, I'm open to suggestions. We have an engineering database that has some
mechanical systems with over 500 pieces that need to be measured and
recorded. Since the table limit is 255, I broke it down by components (fans,
pumps, etc). The system is therefore comprised of several components
(tables). However, the engineer needs to see the one system onscreen so he
can enter all the data for that particular system. Some systems have 15
components (fan table, pump table) etc.

Of course, to add to my misery, there needs to be a calculation included in
there and they want to be able to export the data to a MS Word merge. They
don't want a report because the they need to be able to type several pages of
data within the final document. I'm limited by query fields (255) and table
fields so I'm stumbling through this and keep hitting road blocks.

PS: Thanks Rick, for your quick response. Can you help me with this?

 

[Frazzled]

Sorry, I neglected to mention I'm working in Acces 2003.

 

[Rick Brandt]

Okay, I'm open to suggestions. We have an engineering database that has
some mechanical systems with over 500 pieces that need to be measured
and recorded. Since the table limit is 255, I broke it down by
components (fans, pumps, etc). The system is therefore comprised of
several components (tables). However, the engineer needs to see the one
system onscreen so he can enter all the data for that particular system.
Some systems have 15 components (fan table, pump table) etc.

Of course, to add to my misery, there needs to be a calculation included
in there and they want to be able to export the data to a MS Word merge.
They don't want a report because the they need to be able to type
several pages of data within the final document. I'm limited by query
fields (255) and table fields so I'm stumbling through this and keep
hitting road blocks.

PS: Thanks Rick, for your quick response. Can you help me with this?

Your design is incorrect. You should not have a field-per-measurement.
You should have a record-per-measurement. Then you end up with a narrow
and tall table.

The old axiom is "rows are cheap, columns are expensive". Any time a new
measurement being added to the process would require you to make design
changes to the tables then the design is incorrect.

Yes, this does make building the form a bit more challenging, but believe
me, the other way will cause you no end in problems.

 

[Frazzled]

I am obviously new at doing Access to this extent so my response to your
suggestion is, "huh?". Does this alleviate my issue of trying to bring the
tables together into one form?

 

[Rick Brandt]

I am obviously new at doing Access to this extent so my response to your
suggestion is, "huh?". Does this alleviate my issue of trying to bring
the tables together into one form?

You use as many forms as it takes. Don't let forms drive your design.

I assume you have seen a basic Sales Order form. Main form showing one
order at a time. Within that is a continuous subform showing as many
items purchased on that order as required. That is a good model for your
situation. One main record for a "system" and a subform showing all of
the measurements.

With this setup a system can have 1 measurement or 1000 measurements and
the design of the table and forms is the same.