Form is asking for more parameters than I know I have programmed.

[khughes46]

Whenever Iopen the minutes form I am asked for two parameters, which is what I want. Then I am asked for 4 more parameters, probably for ther imbedded subforms, which I don't wnat, but do need the sub forms. How do I stop this. Sanitized DB attached.

[ranman256]

Your query maybe looking for a field that doesnt exist, or a form object spelled wrong or DNE.
I'll look.

[ranman256]

The form is looking for PEIMSId , but its not in the query or mapped.

[khughes46]

I know this. If I removed the sub forms the 4 PEIMS ID requests go away.

[ssanfu]

It appears that you changed the name of one of the fields from "PEIMS ID" to "PEIMSId" (no space) in the "SDE" table.
Therefore, the subforms are not linked to the main form.

I edited the form "Minutes Header Form New" to relink the subforms, added combo boxes to the main form and edited the caption property for the form "Minutes Header Form New".

There are many things that I think are going to bite you in the rear: using look-up FIELDS, using MVFs, using text fields as primary keys.
Why 5 tables for "Minutes"?

But as long as it works for you.....

Good luck with your project....

[khughes46]

The reason for five tables is I need 4 years of history; but this is the first year. I'm not wild about text as a primary key but the data I have to use is imported from a source that leaves something to be desired when it comes to data integrity. Help me here, why is using lookup fields an issue and what are MFV's (Munti Function Values?)?

[June7]

Lookups with alias in table not advised, review: http://access.mvps.org/access/lookupfields.htm

I think Steve meant MVF - Multi-Value Fields. I NEVER use MVF.

Maintaining multiple years of data does not require separate tables and is actually poor design.

[ssanfu]

See: http://access.mvps.org/access/tencommandments.htm
and http://access.mvps.org/access/lookupfields.htm

Look up FIELDS hide the true value. Lets say you have a look up field with a field "ID" that is autonumber and a field "CompanyName" that is text.
When you try and search, you enter the "CompanyName", because that is what you see. But you should be entering the "ID" field - the linking field.

As far as MVFs (multi-value fields), they are very hard to work with (searching, reports). No experienced programmers (that I know of) use MVFs or look up fields.
If you ever decide to up size (SQL Express,SQL server, Oracle, MySQL), you will have to re-write your dB because MVFs are unsupported in the major databases.

The reason for five tables is I need 4 years of history;

So what are you going to do in the 6th year? You are going to have to do a major modification to the dB. It would be better to have 1 table and add a field for the year - maybe name the field "HistoricalYear" or "WhatYear". Then it is a matter of searching 1 table not 5 tables.

I do not have tables as form/report record sources.... I ALWAYS use queries for form/report record sources. It is easier to select the records I want using a query rather than a table. I know that you can set a filter on a form to limit records, but then how do you sort the records? Sooo much easier with queries.

I'm not wild about text as a primary key but the data I have to use is imported from a source that leaves something to be desired when it comes to data integrity.

And??? With an Autonumber PK, the data doesn't change - the Autonumber just ensures each record uniquely identified.
Data integrity can be dealt with - if necessary, I set an index where duplicates are not allowed.


Here are some sites on Autonumbers:
Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers

Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

Use Autonumbers properly
http://access.mvps.org/access/general/gen0025.htm


I would also look at normalizing the tables. The "SDE" table has 91 fields.
For example there are fields "PrimaryDisability", "SecondaryDisability", "TertiaryDisabilityy". These are known as repeating fields. What do you do if there is a 4th disability? These fields should be in their own table.

Same goes for the fields: "RaceCode", "HispanicLatinoFlag", "AmericanIndianAlaskanNativeFlag", "AsianFlag", "BlackAfricanAmericanFlag", "NativeHawaiianPacificIslanderFlag", "WhiteFlag".

And the "Minutes" table has 41 fields. I would look at moving the fields that begin with "TELPAS" to their own table.
I realize that I don't know what90% of the fields mean, but it is easier to manage tables with fewer fields.


BTW, you should only use letters, numbers and the underscore in object names. Never have spaces in names




My $0.02..... for what it is worth...

[khughes46]

Thanks for the tips and web sites.