Opening of a Data Entry Form

[dpye]

This may be a simple question but it would really help speed things up with my database.

The form I have now uses a table as it control source. Several users are entering information into this form at the same time and thus this table grows very rapidly. As the table grows the amount of time to load the form also increases each time.

I tried filtering the form based on date so that it will only show records for that day. However, there are a lot of records just in one day so that isn't helping.

What I want to do is have the form open the so that it looks like it is starting at record 0 but is actually adding to the table. If you close the form and reopen it your back at record 0 again. I should mention that this form has a "current user" field on it, perhaps I can use this in addition to the date filter.

If anyone has an idea of how to open a form in a clean state each time despite the number of records in the table it would be greatly appreciated.

 

[humbleprogrammer]

Change your form to an unbound form. Then add code behind your forms submit buttons 'On Click' event that adds the record to the table. This way it will not cyle through the table records when the form is opened and will only connect to the table when the record is actually added.

 

[dpye]

Thanks,

I'll give this a shot tomorrow when I get to work. However, there is one question I have. If the form is unbound won't the fields on the form display as #name# now since it doesn't now where to store the data?

Three of the fields are drop down boxes where I specify the control source as the table with the list of names etc. The other ares are text boxes/memo fields, can the same be done these?

Thanks.

 

[humbleprogrammer]

If the form is unbound, you will need to clear out the fields control source. Also clear the control source for the dropdowns but set the 'Row Source Type' and the 'Row Source', so the dropdowns know where to get their data.

Changing the form to unbound bypasses the Access stuff that automatically allows you to save the record to the DB. You will have to create an SQL statement in your submit buttons 'OnClick' event that inserts all of the fields. Initially this is more work but it will be worth it when you see the huge improvement on performance.


Below is a sample SQL insert:
Dim Zone
Dim Shelf
Dim Bin
Zone = Me.Zone
Shelf = Me.Shelf
Bin = Me.Bin

CurrentDb.Execute ("INSERT INTO Locations (Zone, Shelf, Bin) VALUES ('" & Zone & "', '" & Shelf &"', '" & Bin & "')&quot
'Displays a message after the record is added
MsgBox("The record was added!&quot


Give a holler tomorrow if you get stuck coding the SQL and I will provide you with a sample.

 

[BobAtHome]

You can try this.

In the properties for the form, in the "Data" tab, set the following properties:

Allow Edits - set to "No"
Allow Deletions - set to "No"
Allow Additions - set to "Yes"
Data Entry - set to "Yes"

The form will open to a new record and keep adding records if "Default View" property is set to "Continuous Forms". If the user closes the form and opens it again, it will start at a single blank record.

If the form "Default View" property is set to "Single Form", it will open on a new blank record and show only the one record if the user continues to add records.