Best practice - Adding vs Editing (1 Viewer)

mcalex

Registered User.
Local time
Today, 18:33
Joined
Jun 18, 2009
Messages
141
Is the best way:
  • create one form and then open it differently for adding new vs editing existing records
  • create one form to insert and one form to edit, where the edit form is bound to a query, and the insert form saves data following a button's click event.
  • some other method?
I guess I need some Access 'best practice' info. Having a bit of experience with databases, but none with Access, I initially started Access development by dumping some controls on a form and then writing an Insert query to save the data once the form had been fully completed and the Save button clicked. I'm starting to think this is not the best way to use Access.

I have a large form that contains about 200 fields. These are a mix of fields keyed in directly from source documents and fields that are calculated (to check the correctness of provided data or add extra information).

All the data were originally stored in one main table (not including look-up info), but Access was having problems with the table definition having too many characters (I try to use meaningful descriptions, and I think these might have made the issue worse). I have now moved the information into separate tables, linked through a main ID.

The forms now have issues with not being able to go to selected records, database crashes (frequent and appear to be random), and other painfulness :banghead: and I am seriously considering starting from scratch.

If anyone can provide some pointers in this area, I would be most grateful.

Many thanks

mcalex
 

RainLover

VIP From a land downunder
Local time
Today, 21:33
Joined
Jan 5, 2009
Messages
5,041
create one form to insert and one form to edit, where the edit form is bound to a query, and the insert form saves data following a button's click event.
All forms should be bound if possible.

I have a large form that contains about 200 fields.
Appears excesive. I hope there is more than one table to make up the 200.
 

mcalex

Registered User.
Local time
Today, 18:33
Joined
Jun 18, 2009
Messages
141
Appears excesive. I hope there is more than one table to make up the 200.

As mentioned:
All the data were originally stored in one main table (not including look-up info), but Access was having problems with the table definition having too many characters (I try to use meaningful descriptions, and I think these might have made the issue worse). I have now moved the information into separate tables, linked through a main ID.

The data relates to information in annual financial reports. There are about 200 pieces of information collected from each report, so in theory, only one table is needed to keep it at 3NF. I've now split the data into four tables, each with like data (operating revenue info, operating expenditure info, capital expenditure info etc), but that's only to keep Access happy.

So binding the form is correct. Do I bind one form to a SELECT query, and one form to an INSERT query? If so, does Access take care of the UPDATE query when changing the data in the SELECT form?

Thanks again

mcalex
 

RainLover

VIP From a land downunder
Local time
Today, 21:33
Joined
Jan 5, 2009
Messages
5,041
So binding the form is correct. Do I bind one form to a SELECT query, and one form to an INSERT query? If so, does Access take care of the UPDATE query when changing the data in the SELECT form?
A select Query does allow Edits and Appends, so use that.
If you need to Update many Records use an Update Query or if you wish to Append many records from a different source use an Append Query. I believe a Select Query will do what you need. I do not see a reason for using an unbound Form.

Hope this Helps.
 

Users who are viewing this thread

Top Bottom