Complicated Help Needed

M

malycom

Hi all.

I have linked an Access Database to our main work database (play version) as
I want to try to create user input forms that allow users to set up clients.
I want to do this with Access as we don't have access to the source code for
the actual work database and there are to many flaws with it in its current
state.

What I would like to know is...

1: How do I create a form that goes straight to a new record ready for user
input and doesn't list records to the linked table as I don't want accidental
deletions.

2: How can I make records already listed non editable or deleteable if the
above is not possible - Actually, even if the above is possible, I would like
to know the answer as it may come in useful elsewhere.

3: I would like to know if it is possible to ensure that all required data
is input and if anything is left out, a complete rollback is performed
clearing all data. As it stands, our system allows partial data to be input
and that's all most users do which causes huge problems down the line.

I want to let them fill required details in the client table followed by
required details in a Trust or Company table depending on the catefgory set
up in the client table, and finally I want them to fill in details in an
officer table.

If all the data is not filled in from one of the tables, I need the
previously entered data to rollback so there is no partial set up.

4: Can this be done on 1 form instead of a form for each table. As
metioned, tables used are
Clients
Trusts
Company
Officer

I will be able to set up the realtionships based on the current system but
as for the rest of it, I need a lot of help. I appreciate this may be to
complex to answer here but any starting points or guidance as to where I can
find the information will be greatly appreciated.

Also, as mentioned, I am linking to a Play Version of the database so errors
are not going to cause any real problems.

Thanks in advance.

Malcolm
 
A

Allen Browne

Answers in-line.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

malycom said:
I have linked an Access Database to our main work database (play version)
as I want to try to create user input forms that allow users to set up
clients.
I want to do this with Access as we don't have access to the source code
for the actual work database and there are to many flaws with it in its
current
state.

What I would like to know is...

1: How do I create a form that goes straight to a new record ready for
user
input and doesn't list records to the linked table as I don't want
accidental
deletions.
Click to expand...

If you don't need to see existing records, just set the form's Data Entry
property to Yes.

If you do need to be able to view existing records as well, add this to the
Load event procedure of your form:
If Not Me.NewRecord Then
RunCommand acCmdRecordsGotoNew
End If
2: How can I make records already listed non editable or deleteable if the
above is not possible - Actually, even if the above is possible, I would
like
to know the answer as it may come in useful elsewhere.
Click to expand...

Set the form's Allow Deletions property to No.

If you don't need to be able to edit anything (not even unbound boxes or
subforms), set the form's Allow Edits property to No.

If you do need to be able to edit the unbound boxes, see:
Locking bound controls on a form and subforms
at:
http://allenbrowne.com/ser-56.html
3: I would like to know if it is possible to ensure that all required data
is input and if anything is left out, a complete rollback is performed
clearing all data. As it stands, our system allows partial data to be
input
and that's all most users do which causes huge problems down the line.

I want to let them fill required details in the client table followed by
required details in a Trust or Company table depending on the catefgory
set
up in the client table, and finally I want them to fill in details in an
officer table.

If all the data is not filled in from one of the tables, I need the
previously entered data to rollback so there is no partial set up.
Click to expand...

To make a specific field required, set the Required property of the field in
table design.

To warn if the user leaves a field blank but allow them to proceed, use the
BeforeUpate event procedure of the form.

You cannot easily force the entry of records into child tables as well.
There is no Undo that applies across forms and subforms.

You can have the user enter the new record and related records into
temporary tables, and then execute a series of append query statements to
write them to the real tables once everything checks out. But you then still
have to put into place the logic to ensure the child records are not edited,
deleted, or reassigned in a way that conflicts with your business rules.
4: Can this be done on 1 form instead of a form for each table. As
metioned, tables used are
Clients
Trusts
Company
Officer
Click to expand...

No. Attempting to insert records into multiple tables simultaneously in a
single form is an exercise in frustration.
 
M

malycom

Hi Allen

Thanks for this - I have somewhere to start now and that was my biggest
problem.
 
Top