Popup form

[marcsessoms]

Hey guys.



I am relatively new to Access 2010 and self-taught. I began a project from very few fields in one table in a MS SQL database. It has now grown to several forms and several tables. I am still in the process of "normalizing" the database.

What I have run into is the 255 field limit in a query. The query selects records from different tables to populate a form with tab controls and sub-forms.

After googling and reading I think a way to fix this for now is to use popup forms for specific form access when it is needed and not pull the information into the main form after the records is selected. I had also tried researching to see if a separate query for each tab would work as well.

I am trying a popup form to test this theory. However, I am having an issue of the popup displaying the fields from the record I am currently on.

Parent Form = frmProject
SubForm/PopForm = frmPOC_Popup
Parent/Child ID = ProjectID

Any help would be appreciated.

Thanks.

[June7]

I ran into that same limit (also limit on number of controls on a form) and resolved both with tab controls and subforms on the tab pages.

Popup for new record will be harder to manage than the synchronized form/subform.

[marcsessoms]

You know, I tried that because what I read was that subforms WAS the way to go. However after adding subforms to my tabs. Everything was good until I added a new field to my sql tables and refreshed my links. I then get the "Too Many Fields Defined Error". I assume this is from the query, which populates my main form and subforms and has reached the limit.

Is there a trick to adding the subforms to each tab so that it isn't included in the main query?

[June7]

Use separate queries for main and subforms. Each RecordSource should not have all the fields that are in the other.

Master/Child links properties of the subform container control will synchronize related records.

RecordSource can be set direct to table, doesn't absolutely have to be a query. You have set links to the SQL tables?

[marcsessoms]

Please excuse my ignorance but I use the ODBC database wizard to create links to my tables and views. Once the table is added or refreshed I open my form. I then drag and drop my fields onto the form that I need. If it is a subform them I'll link the parent and child field by ProjectID.

If I originally had the all fields on one form with no subfoms and I move most of my controls/fields to subforms, will Access remove those fields from the datasource or do I need to do that manually?

(Hope that makes sense)

[June7]

Moving the controls will not modify the query or RecordSource. So, yes, you need to edit those. I don't mean edit the 'views' (that is a SQL query?) that are in SQL, unless you have to, I mean queries that are in Access that use the linked tables. My ignorance is showing. I haven't used other db's much.

[marcsessoms]

And just to clarify, each subform will contain it's own query/Recordsource which should lower the number of fields that is in the original query/Recordsource query (after I edit it of course). This should eliminate my error of too many fields.

If this works I'll owe you a steak dinner. I've been looking for a resolution for days with no definitive answer.

[June7]

Yes.

The main form and its subforms should not be bound to the same data. For instance, main form bound to Orders table and subform bound to OrderDetails. The Master/Child links on pk/fk fields will synchronize the related records.

[marcsessoms]

Thanks so much June. I'll try it and see how it goes.