Form and subform relationship advice

Two Tables, One Form- Basic Skills - Open Office Database Tutorials - fdb3twotab1frm

form and subform relationship advice

If I go to Record 2 of the main form to create a new sub form record, then I I'm still learning and don't know a lot about the parent/child relationships or how to fix this, but I'm a quick learner and any advice would be helpful!. Need help? Post your question and get tips & solutions from a community of , IT Pros I can not find a simple way to create forms and reports in Access that I would use a subform on the patient table that displays the. Using combo box to populate a sub form which is used to create a new record This form I want to create for adding a new MRR to the table must contain . the parent/child relationship between the unbound main form and the . as well as hypervisor-specific management advice from TechTarget experts.

Access can convert graphics into much smaller PNG formats to significantly reduce the size of your databases. This does not affect graphics already on your forms and reports but helps if you add new graphics or replace existing ones.

To activate this feature, change an Access setting. Preserve source image format smaller file size Compact Your Database Often To Reclaim Space Compacting your database reclaims unused space and makes almost all operations faster.

You should do this on a regular basis. Also, be sure to compact anytime you import or delete objects in your database, or compile and save VBA code. Learn more about Total Visual Agent for a system administrative tool to schedule compact and backup your Access databases on a regular schedule. Make It Look Faster If you have exhausted all other performance optimization techniques, consider making your application "look" faster. Do this by displaying status messages and progress meters as your application loads forms, runs queries, and performs any other operation that may take a bit of time.

While this doesn't make your application run faster, it appears to run faster. When you compact the database, you reorganize records so that they are stored in adjacent spaces, making retrieval faster.

Additionally, compacting a database updates its data statistics, which can be used by a query to run faster. You can force a query to recompile which in turn causes it to use the latest statistics by opening it in design view, saving it, and then running it. You may want to defragment your disk using a program such as the Disk Defragmenter that is part of Windows before compacting your database.

This leaves contiguous free disk space immediately after the database file. In theory, this make future additions to the database occur faster. You may want to experiment with this on your system.

Index the fields on both sides of a join. Alternatively, you can create a relationship between joined fields, in which case an index is automatically created. Search Access Help for: Limit Fields Returned by a Query Where possible, limit the number of fields returned by a query. This results in faster performance and reduced resource usage. Pay special care to avoid the use of immediate If IIF functions in sub-queries.

Don't Use Non-Indexed Fields for Criteria Avoid using non-indexed fields or calculated fields for criteria restrictions. Index Sort Fields Index any fields you use for sorting. Be careful not to over-index. Use Temporary Tables to Eliminate Running the Same Queries Over and Over If you are processing data that's used multiple times for instance on multiple reportsit may be faster to store intermediate results in temporary tables rather than running a series of Select queries many times.

Create a temporary table to store your results. Empty the table and fill it with your data using and Append query. You can then use that table for multiple reports and forms.

Avoid Domain Aggregate Functions on Foreign Tables Do not use domain aggregate functions DLookup for example in queries to access data from a table that is not in the query.

Link to the table and set criteria accordingly, or create a separate aggregate totals query. When you then run a query, these updated statistics are compiled in the query's execution plan. This sequence of events results in the fastest possible query.


Before you deliver your application, compact the database, and then force each query to be recompiled. Other linked data types do not support Rushmore optimization.

To ensure that Rushmore optimizations are used, create indexes on all fields that are used to restrict a query's output.

form and subform relationship advice

If you have queries that don't contain indexes on fields used in the query's restriction clause, Rushmore is not used.

Link on Primary Key Indexes Whenever Possible To make queries run faster, you should have indexes on all fields in the query that join, restrict, or sort the data.

Whenever possible, link on Primary Key fields instead of other fields. Indexes are most critical on tables with large numbers of records, so you may not see a difference on small tables.

Microsoft Access help, tutorials, examples

You also don't need to add secondary indexes on fields that are part of referential integrity. Experiment With One-to-Many Restrictions If you have a one to many join in query with a restriction, try moving the restriction to the other side of the join. For example, if the restriction is on the many side, move it to the one side. Compare performance results for both versions, and choose the fastest one. De-Normalize If Necessary Although you should strive to normalize your data for best performance and future flexibility, you may want to consider denormalizing some of your data if you frequently run queries with joins would benefit from such data restructuring.

Experiment With Sub Queries Instead Of Joins If you have a query with a join that is not performing adequately, consider replacing the join with a sub query. In some cases, the sub query may cause the overall query operation to run faster.

Limit the Number of Fields Returned By Each Query Where possible, queries should use a Where clause to constrain, or limit, the number of records returned. Somehow, saved queries are optimized more than the SQL string behind the report.

Close Unused Forms Close forms that aren't being used. Every form that is open consumes memory that could be used by other parts of your applications. Open Forms Hidden Consider opening your application's most commonly used forms when your application starts.

Set their Visible properties to False, and then make the Visible as needed. This frontloads some performance hits to the application load event, making forms load faster when needed. Use the DataEntry Property of a Form If a form's record source the table or tables accessed by the form's RecordSource property contain a large number of records, and the form is primarily used to add new records, set the DataEntry property of the form to Yes.

This precludes Access from having to retrieve existing records when the form loads. Don't Sort A Form's Recordset Avoid sorting records in a form's underlying record source unless a particular presentation order is absolutely necessary for the form.

This makes the form load faster. By doing this, you can use the query to restrict the number of fields returned, making the form load faster. Use Lightweight Forms Consider replacing Visual Basic code in a form's module with calls to standard modules, or with hyperlink objects. Then set the form's HasModule property to False.

This turns the form into a Lightweight form, making it load faster. Search Access online help for "Lightweight Forms" for more information.

In Accessyou can use embedded macros for simple operations. Also index all fields in the subform that are used for criteria. Or set the RecordsetType property of the subform to Snapshot. In order to find matches, Access needs to convert numeric values to text. If the data type is Text, this conversion can be skipped.

Optimize Bound ComboBoxes If the bound field in a lookup combobox is not the displayed field, don't use expressions for the bound field or the displayed field, don't use restrictions the WHERE clause in the row source, and use single-table row sources wherever possible.

Move Linked Data Local for ComboBox and ListBox Controls If the data that fills a list box or combo box does not change often, and that data comes from a linked table, consider moving that data's table into the local database. This can be a huge performance boost, especially if the linked table is located on a network drive. When the form loads, prepare only the controls on the form's first page.

Defer operations on other page's controls, such as setting the record source until the user moves to that page. Only Load Subforms on Tab Pages when the Page is Selected If a tab control contains several pages tabs with subforms on them, the form will load quicker if the subforms on the tabs that aren't visible aren't loaded right away. Since those tab pages aren't viewed yet, you can defer loading the data until the user clicks on the tab. You'll take a performance hit the first time the user clicks on it, but if they never select the tab, you would never load the data.

For more information, read Microsoft Access Performance Tip: When you activate an unbound OLE objects, the memory used in that operation is not released until the form is closed.

Convert Subforms to Listbox or Combobox Controls Where possible, convert subforms to listbox or combobox controls. It is far quicker to load a control than it is to load an additional form as a subform. Move Form Module Code to a Standard Module You can reduce a form's load time by moving its code from the form module to a standard module. When the form loads, the form's module doesn't need to be loaded. Of course, the standard module needs to be loaded at some point, but once a standard module is loaded, it stays in memory until you close the database.

Avoid Unnecessary Property Assignments Set only the properties that absolutely need to be set. Properties assignments can be relatively expensive in terms of performance. Review your form's startup code to ensure that you are not setting any form or control properties that don't need to be set.

form and subform relationship advice

The method is significantly faster than the action. Give Visual Feedback Give the user some visual feedback during long operations. Consider using status meters to display a task's progress.

At a minimum, use the Hourglass cursor along with a status message. So instead of placing command buttons on your forms to do common operations, investigate the possibility of using a label control with the Hyperlink properties. This approach eliminates the need for a command button, and its associated event code. Split Forms Into Multiple Pages Consider using multi-page forms, separated by the page-break character.

This allows you to present only the controls needed, and can reduce form-load time. For example, if your form has 10 combobox controls that take a long time to fill, split the form into multiple pages using the PageBreak control.

Then, pick the 5 combobox controls the user is most likely to use and place them on the first page. Place the remaining controls on the second page. Load time for the form should be substantially reduced, especially if the queries filling those combo box controls are complex. Minimize the Number of Controls Minimize the number of controls on your form.

Loading controls is the biggest performance hit when loading a form. Avoid Overlapping Controls Avoid overlapping controls. It takes Access more time to render and draw controls that overlap each other than it does non-overlapping controls. Use Graphics Sparingly Use bitmap and other graphic objects sparingly as they can take more time to load and display than other controls. Use the Image Control Use the Image control instead of unbound object frames to display bitmaps.

The Image control is a faster and more efficient control type for graphic images. Access reports use their Sorting and Grouping settings to sort and group records: Avoid Expressions and Functions In Sorting and Grouping Try to avoid reports that sort or group on expressions or functions. By using a query, you can restrict the number of fields returned to the absolute minimum number, making data retrieval faster. This will cause the subreport to link its records faster.

Of course, remember that over-indexing can cause performance bottlenecks when editing, adding and deleting data. This can have a significant performance impact on how long it takes the report to open and display pages.

form and subform relationship advice

You can then display a message and close the report. This is easier than running a separate process to see if data exists for the report.

Eliminate Unnecessary Reports If a sub report is based on the same query as its parent report, or the query is similar, consider removing the sub report and placing its data in the main report. While this is not always feasible, such changes can speed up the overall report. Limit the Number of Controls on Reports Minimize the number of controls on your report.

Loading controls is the biggest performance hit when loading a report. Minimize Bitmap Usage Use bitmap and other graphic objects sparingly as they can take more time to load and display than other controls.

In almost all cases, Visual Basic code runs faster than macros. This obviously doesn't apply if you are using macros for a SharePoint deployment of your Access forms. Additionally, since no source code is stored in the MDE file, the database loads faster and uses less memory.

Creating an Access Form with Subforms

Achieve the Compiled State Module code is saved in two states in your Access database: The source state consists of the contents of your actual modules, with full text including white space, procedure and variable names, and comments. The compiled state is the executable version of your code. All comments and white space have been removed, and a stream of executable instructions has been produced-the code is ready to be run.

The difference between these two states can cause your application to run slower than molasses in January if you don't understand them. When you run a procedure, VBA checks to see if the module containing the procedure is compiled. If it is, VBA simply runs the code. If it is not compiled, VBA compiles it by loading the code into memory, performing a syntax check, and compiling it into an executable format. If all these operations succeed, it can then run the code.

You probably surmised that this process of compiling is not a free lunch-it does take some time. And herein lies the crux of the matter: So if you want your database to run as fast as possible, your task is obviously to reduce the amount of time Access spends compiling your code to a bare minimum. In fact, in an ideal application, all your code should be compiled and saved in the compiled state. So how do you go about this?

Your Access database or project in VBA parlance is said to be in a compiled state when all modules, including form and report modules, are saved in both states in the database.

This means that the original source code is stored, as is the compiled version. In such a state, Access runs much faster, because it can completely bypass the compilation process. Getting your database into the compiled state is actually rather easy: This includes form and report modules called class modules using Access terminology and standard modules. All VBA code that is called by your application is immediately ready for execution. There is no need for compilation. This is all fine and well, but is just as easy for your database to become decompiled.

When you make certain changes to your database, it automatically becomes decompiled, which means that the compiled state that you created using the previous steps no longer exists. How to Avoid Decompilation So how do you avoid decompilation, or loss of the compiled state? Any of the following actions can decompile your database: Modify any module code Make changes to code-bearing objects, such as form, reports and controls, or create such code-bearing objects So the bottom line is: It's not as bad as it seems.

After all, your database does not need to be in a compiled state while you are doing development work on it-it only really requires the performance benefits of the compiled state when it is actually running on your user's workstations. Therefore, if you follow these guidelines, you can enjoy peak performance from your module code: During development, don't use Compile All Modules.

It is a waste of time, because the first time you make any changes to the module, it will decompile, or reverse the effect of Compile All Modules.

Rather, use the Compile Loaded Modules option instead. This action only compiles the modules that are called by the modules you have open. This is a much quicker operation, and results in the same syntax checking that Compile All Modules does.

When you are ready to deliver your database for testing or live use, put it into the compiled state using the steps outlined above. Read this reference for more information on the Microsoft Access decompile feature.

This allows you to use early binding to bind variables to the control's objects, making it load and run faster. In most cases, this is handled for you: Use Option Explicit Always explicitly declare variables. Ensure this happens in every module in your application by using the Option Explicit phrase at the top of each module.

English schools tend to assign each pupil to a "house" The "House" field is not used in the example The second table, "Incident" has the following fields Having set up the tables, I then established a relationship not join between Name.

Name There's more on relationships and joins in a short er page I've done talking about these important concepts in general terms. They are not the same thing, but the differences constantly blur in my mind, and I hope to help you fight that problem.

It has a form with Name. House, and a subform with Incident. Both are displayed as datasheets. Based on "Incident" relationship. The names of pupils known to the system are displayed in the main form. New names can be added. The form is used as follows First the user selects one of the names in the first datasheet, or adds a new name. Once that is done, the second datasheet lists any incidents a teacher has accused that pupil of committing. And new incidents can be added, and as they are, the pupil's name is filled in on the resulting record in Incident.

If you try the form, and have the tables displayed while you do it, do not be alarmed if you don't see things you "have added" appearing in the displays of the underlying tables.

  • MS Access your Data with Sub-Forms
  • Accommodating a many-to-many relationship in Access
  • advice on subform please

If you save the record you've added via the form "EnterIncidents", and then hit the "refresh" button on either table, the display of what's in the table at the moment will be updated. It isn't updated automatically. Go "play" with the database, which you can just download. If things are set up right, you can automatically purge the "Incident" table of all incidents concerning a given pupil simply by deleting the pupil's name That will delete it from the Name table, and all of the incident records for that pupil You can also set things up so that a name can't be removed unless there are no records in the Incident table You can set things so that if you change, say, Fred to Freddie, then all of the incidents that Fred was charged with now relate them to Freddie.

Not only are these tricks of relational integrity tools fun, but they also protect the integrity wonder where the name came from? You can't enter an incident to a "Frde", for instance Without the form, you would have to type each offender's name each time you put in a new incident record. With the form, that is taken care of for you.

Editorial Philosophy I dislike 'fancy' websites with more concern for a flashy appearance than for good content.

form and subform relationship advice

For a pretty picture, I can go to an art gallery. Of course, an attractive site WITH content deserves praise I am trying to present this material in a format which makes it easy for you to USE it.