"new record" button dimmed, and suddenly my forms lists over70,000 records...???

[Erin]

Hi, I am an intern reaching the close of the summer. My project was to
create a database and suddenly i am having these issues with the form
that I am worried I won't figure out how to fix before the internship
ends!

In experimenting with "Order By," I seem to have messed something up
badly. My form now says there are 72,900 records (instead of the
actual 270) and it won't let me add new records (the "new record"
button is dimmed).

Any ideas at all?

Please help!

Erin

 

[ChrisM]

Hi, I am an intern reaching the close of the summer. My project was to
create a database and suddenly i am having these issues with the form
that I am worried I won't figure out how to fix before the internship
ends!

In experimenting with "Order By," I seem to have messed something up
badly. My form now says there are 72,900 records (instead of the
actual 270) and it won't let me add new records (the "new record"
button is dimmed).

Any ideas at all?

Please help!

Erin

- Is the form based on a query, or a table?
I'll assume it is a query.
- Does the query (run on its own) show the 72,900 rows?
Have you 'joined' a new table into the query recently? Maybe you have linked
by a non-unique key?
Have you changed something that has stopped the query being 'updatable'
(if you run the query on its own, are you able to add records to the end of
the query 'grid').

If you can also give me some idea of the data (or just the table structures
if the actual data is confidential) and the SQL code that makes up the query
it would be really useful...

Cheers,

Chris.

 

[Erin]

Chris,

The database is meant to facilitate data entry and review of
information pertaining to facilities that have violated air emissions
standards.

It has three tables - Facility Information, Enforcement Summary, and
Assessments.
Then there are two other tables for the subforms - SEP Summary, and
Compliance Actions.

My form is based on a SELECT statement that has fields from each of my
three tables.
I inserted the two subforms by dragging them from the Database Window
into the main form. Should the form be based on a query instead? I am
starting to think, from all the reading I've been doing, that perhaps
it should.

Do you have any ideas on why it would have suddenly stopped new entries
and be showing so many records? (The tables each still show the actual
270 records).

Thanks so much for your help! I really appreciate it!

Erin

 

[Brendan Reynolds]

270 * 270 = 72,900. This sounds like the result of a Cartesian join, e.g.
you have something like ...

SELECT * FROM Table1, Table2

.... instead of ...

SELECT * FROM Table1 INNER JOIN Table2 ON Table1.SomeField =
Table2.SomeField

 

[Erin]

I'm not sure I understand. Where do I need to edit this? In the Form
Source property?

I hadn't tumbled to the 270^2 thing - very nice!

Thanks for the additional help!

Erin

 

[Erin]

Get ready, this is ridiculous:

In the Record Source property for the main form, it says:

SELECT [Tbl Facility Information].Address, [Tbl Facility
Information].[Phone Number], [Tbl Facility Information].County, [Tbl
Facility Information].[BAR Source ID], [Tbl Facility Information].[SIC
Code], [Tbl Facility Information].[Class Code], [Tbl Facility
Information].[BAR Permit Contact], [Tbl Facility Information].[BAR
Permit Contact Phone], [Tbl Facility Information].[Facility Contact],
[Tbl Facility Information].[Facility Contact Phone], [Tbl Facility
Information].[Facility Comments], [Tbl Enforcement Summary].[Case ID],
[Tbl Enforcement Summary].Year, [Tbl Enforcement Summary].[Facility
Enforcement Contact], [Tbl Enforcement Summary].[Facility Enforcement
Contact Phone], [Tbl Enforcement Summary].Violation, [Tbl Enforcement
Summary].Lead, [Tbl Enforcement Summary].Type, [Tbl Enforcement
Summary].[Appealed?], [Tbl Enforcement Summary].[Appeal Date], [Tbl
Enforcement Summary].[Appeal Comments], [Tbl Enforcement Summary].[Date
Lodged (Source Signed)], [Tbl Enforcement Summary].[Modifications to
CAO?], [Tbl Enforcement Summary].[Modification Comments], [Tbl
Assessments].[Initial Civil Penalty Assessed], [Tbl
Assessments].[Initial Payment Comments], [Tbl Assessments].[SEPs
Involved?], [Tbl Assessments].[Abeyance Length], [Tbl
Assessments].[Abeyance Fee?], [Tbl Assessments].[Abeyance Fee Amount],
[Tbl Assessments].[Date Abeyance Fee Paid], [Tbl Assessments].[Abeyance
Comments], [Tbl Enforcement Summary].[Compliance Requirement?], [Tbl
Enforcement Summary].[Termination Date], [Tbl Enforcement
Summary].[Termination Comments], [Tbl Facility Information].[Facility
Name], [Tbl Facility Information].[Facility ID], [Tbl Enforcement
Summary].[Enforcement Action Date], [Tbl Assessments].[Due Date for
Initial Penalty Payment], [Tbl Assessments].[Abeyance Closure Date],
[Tbl Enforcement Summary].[Compliance Actions Completed?], [Tbl
Assessments].[Date Initial Penalty Paid], [Tbl Enforcement
Summary].[Compliance Actions Completed?] FROM [Tbl Facility
Information], [Tbl Enforcement Summary] INNER JOIN [Tbl Assessments] ON
[Tbl Enforcement Summary].[Case ID] = [Tbl Assessments].[Case ID];

Please help!

Thanks,
Erin

 

[Brendan Reynolds]

Look at the last part of the SQL statement you posted elsewhere in this
thread ...

FROM [Tbl Facility
Information], [Tbl Enforcement Summary] INNER JOIN [Tbl Assessments] ON
[Tbl Enforcement Summary].[Case ID] = [Tbl Assessments].[Case ID]

You've specified the join condition between Tbl Enforcement Summary and Tbl
Assessments, but you have not specified any join condition between Tbl
Facility and either of the other two tables.

 

[ChrisM]

Hi Erin,

You've based your form on a Select statement (which IS a query). That's
fine.
I see that you have linked the EnforcementSummary table to the Assessments
table on the [Case Id] field.
There is nothing linking the FacilityInformation table to anything, so you
have what is called a cartesian join. Which effectively means you will get a
row in the SELECT query result for every row in one table against every row
in the second one.
ie

TableA
=====
Name Code
Chris A
Erin B

TableB
=====
Code Shape
A Circle
B Triangle

SELECT TableA.Name, TableA.Code, TableB.Code, TableB.Shape
FROM TableA, TableB;

Result:

Name TableA.Code TableB.Code Shape
Chris A A Circle
Chris A B Triangle
Erin B A Circle
Erin B B Triangle

See what I mean?

So in your design screen, you need to link a field in the
FacilityInformation table with a corresponding field in one of the other
tables. [FacilityId] would seem favourite...

Make Sense??

Cheers,

Chris.

 

[ChrisM]

Another thought...

What is the relationship between FacilityInformation, EnforcementSummary and
Assessments?

Could one Facility have more than one EnforcementSummary or Assessment(for
example).



Chris.