Unbound Forms, Do I need them?

[HKComputer]

I'm new to programming and new to access, though I have studied
database programming for quite some time now. Pardon me, my questions
might almost be outside the scope of interests and capabilities of this
newsgroup, but I hope not.

I'm building several applications in Access. To help you understand, I
am not building an Access "project". Neither am I connecting to an
outside data source through ODBC or any other technology. We are
working with a single .mdb file here.

I'd disenchanted with the bound forms I get in Access. Records become
records as soon the user starts to enter data. I prefer to have
extensive form-level validation and give the user limited options to
prevent incomplete records and surprise edits or deletes. I'll tell
you what I want to achieve and then I'd like some recommendations.

I'd like my forms (bound or unbound) to display the current record
selected using either my combo box or list box. This I have already
achieved in a bound form.

When the record and it's child records are displayed, edits should be
disallowed to prevent accidental edits or deletion of of data.

I'd like to have the following buttons on my form:

Edit
Allows edits. Enables a cancel button that undoes the edit on this
record and goes back to locked fields. Enables a save button to save
changes to this record. (Seems that to get these options the form will
need to be unbound?) Disables the exit form button. Disables the Add
New button.

Add New
Moves to a new blank record. Disables all other buttons except save
and cancel. Save button and cancel button would act as I described in
my paragraph above for the edit button. Exit Form and Edit buttons
would be disabled.

Cancel
Cancel would only be available during an edit or add new operation.
Whether it undoes something or deletes the current record, in order to
appear canceled, I don't care. I just want to cancel the current edit
or add new operation.

Exit Form
This button's purpose is obvious. This operation would never be
allowed in an edit or add new operation.

Delete
This button would not be enabled in an edit or add new operation. It
would require either a password or clicking on an OK button to actually
delete a record. I'm even considering the idea of marking these
records in a status field with a corresponding letter or number and not
actually deleting them until the end of the month so that the user can
restore a deleted record if ultimately necessary.

First, can my needs be achieved using a traditional Access bound form?
If so, I wouldn't mind some examples for my button code and how to lock
and unlock my controls for edit. I cannot lock my whole form at once
because that disables my combo and list box that is used to find the
correct record.

Should I use an unbound form and then use ADO to open a recordset and
fill in my fields/controls according the current record selected on my
combo or list box? Do I need to use ADO?

Should I use ADO's methods to add new, edit, and delete records? Or do
I use SQL commands to achieve that? Does it matter?

I'm not asking how to program my whole form here. I rather want
opinions on my options to get the bullet-proof form I'm asking for.

 

[Guest]

I have been down the same road. In reality, a bound form can do all you want
and still take advantage of those things an unbound form does not have. See
notes below:

I'm new to programming and new to access, though I have studied
database programming for quite some time now. Pardon me, my questions
might almost be outside the scope of interests and capabilities of this
newsgroup, but I hope not.

I'm building several applications in Access. To help you understand, I
am not building an Access "project". Neither am I connecting to an
outside data source through ODBC or any other technology. We are
working with a single .mdb file here.

I'd disenchanted with the bound forms I get in Access. Records become
records as soon the user starts to enter data. I prefer to have
extensive form-level validation and give the user limited options to
prevent incomplete records and surprise edits or deletes. I'll tell
you what I want to achieve and then I'd like some recommendations.

I'd like my forms (bound or unbound) to display the current record
selected using either my combo box or list box. This I have already
achieved in a bound form.

When the record and it's child records are displayed, edits should be
disallowed to prevent accidental edits or deletion of of data.

Use the form's Current event to Lock your controls.
Me.txtSomeTextBox.Locked = True
Since you will be manipulating these controls repeatedly, you may consider a
Sub that does it for you based on a passed parameter:

Sub SetControls(blnLockUnLock)

Me.txtSomeTextBox.Locked = blnLockUnLock

I'd like to have the following buttons on my form:

Edit
Allows edits. Enables a cancel button that undoes the edit on this
record and goes back to locked fields. Enables a save button to save
changes to this record. (Seems that to get these options the form will
need to be unbound?) Disables the exit form button. Disables the Add
New button.

UnLock the controls as described above. In the Cancel button's Click event,
use the OldValue property to reset the the controls to what they were before
the edit started:
Me.txtSomeTextBox = Me.txtSomeTextBox.OldValue

Add New
Moves to a new blank record. Disables all other buttons except save
and cancel. Save button and cancel button would act as I described in
my paragraph above for the edit button. Exit Form and Edit buttons
would be disabled.

There is a difference in the cancel button between an edit and an addnew.
For the Edits, you would use the example above. For an Add, you would need
to use the Undo:
Me.Undo
You could code your module so that the Cancel button would know whether you
are editing or adding. Set a Module Level varialbe that would allow the
cancel button to know which to use:

If blnIsNewRec Then
Me.Undo
Else
Me.txtSomeTextBox = Me.txtSomeTextBox.OldValue
End if

Cancel
Cancel would only be available during an edit or add new operation.
Whether it undoes something or deletes the current record, in order to
appear canceled, I don't care. I just want to cancel the current edit
or add new operation. See comments above

Exit Form
This button's purpose is obvious. This operation would never be
allowed in an edit or add new operation.

Here you have to cover events that can happen outside your control. For
example, if your form has the standard Close button or the use exits the
application, an imcomplete add or edit would be saved. You should check in
the Close event of the form to be sure these don't catch you by suprise:

If Me.Dirty Then
if MsgBox ("Complete Update Before Exiting", vbQuestion + vbYesNo, _
"Update Pending") = vbYes Then
Me.Dirty = False
Else
If blnIsNewRec Then
Me.Undo
Else
Me.txtSomeTextBox = Me.txtSomeTextBox.OldValue
End If
End IF
End If

Delete
This button would not be enabled in an edit or add new operation. It
would require either a password or clicking on an OK button to actually
delete a record. I'm even considering the idea of marking these
records in a status field with a corresponding letter or number and not
actually deleting them until the end of the month so that the user can
restore a deleted record if ultimately necessary.

If you use a flag to denote a "deleted" record, you will need to use a query
as your recordsource with the "deleted" records filtered out. You would have
to do the same everywhere in your application you use this table. Not a bad
idea for some apps where you may need this functionality.