bound vs unbound fields

[J]

When giving users the option to modify information in a table, is it better
to have the fields on the form bound to the underlying table or to use
unbound fields and then add/modify the data on the click of a 'update' button?

Is there a preferred way of doing this in the programming world??

Thanks!

 

[Jeff Boyce]

"Preference" is so ... personal!

I suspect you'll find that the responses you receive depend on which
development tools the respondent uses, what experience the respondent has
had, and what "problem" is being solved (including the environment).

What works for one person in one circumstance may fail miserably (i.e., poor
performance) in other circumstances or for other folks.

Why do you ask?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Rick Brandt]

When giving users the option to modify information in a table, is it
better to have the fields on the form bound to the underlying table
or to use unbound fields and then add/modify the data on the click of
a 'update' button?

Is there a preferred way of doing this in the programming world??

Thanks!

The vast majority of the advantages in using Access over some other
development tool come from using bound controls on bound forms. There might
be something to be said for using unbound forms and code, but then why use
Access? Just use VB or dot net to build your app.

"Programmers" who come later into using Access will often use unbound forms
because that is more familiar to them since that is the way you are forced
to do things in most other environments. You can obtain a finer level of
"control" in some cases going unbound, but it is a LOT more work.

 

[Klatuu]

I believe Rick's response to be an accurate analysis of how Access is used
depending on prior experience. I came to Access from a long history of
procedural languages (COBOL and varions versions of BASIC), through FoxPro,
and then to Access.

My experience led me to write my first application using unbound forms. As
Rick said, it is a lot of work. I have since changed to using only bound
forms. I am not sure I agree a finer level on control can be achieved with
unbound forms. To me it is more a matter of really understanding the Event
sequence for controls and forms and how they interact. Coming from a
procedural background, this what the hardest part of Access for me to grasp.
It is also a very powerful environment when you get it down.

 

[J]

How would you handle the following situation:

You have a list of contacts and you want to prevent them from entering
duplicate contacts (based on name). I know you can set the property on the
table, but I would rather use a more descriptive/helpful message if they try
to reenter John Doe. If I'm using a form and it's bound, I want to alert
them after they enter John Doe that that record already exists and give them
the option of entering a different name or viewing the John Doe record to
modify.

 

[Brendan Reynolds]

Here's an example from one of my own apps. This is from a Form_BeforeUpdate
event procedure ...

Set db = CurrentDb
strSQL = "SELECT Count(*) AS TheCount FROM " & _
"tblStudents WHERE LastName & " & strEmpty & " = '" & _
Replace(Me!txtFamilyName & vbNullString, "'", "''", 1, -1,
vbBinaryCompare) & _
"' AND FirstName & " & strEmpty & " = '" & _
Replace(Me!txtGivenName & vbNullString, "'", "''", 1, -1,
vbBinaryCompare) & _
"' AND MiddleName & " & strEmpty & " = '" & _
Replace(Me!txtMiddleName & vbNullString, "'", "''", 1, -1,
vbBinaryCompare) & _
"' AND StudentID <> " & Me!StudentID
Set rst = db.OpenRecordset(strSQL)
boolOpen = True
With rst
boolFound = .Fields("TheCount") <> 0
.Close
boolOpen = False
End With
Set rst = Nothing
Set db = Nothing
If boolFound Then
intResult = MsgBox(gstrDuplicateNamePrompt, glngcYesNoQuery,
gstrcAppTitle)
If intResult = vbYes Then
Cancel = True
End If
End If

 

[Klatuu]

I would use the Before Update event of the text box to test whether the name
exists in the recordset. Here is some example code:

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "[Name] = '" & Me.Name & "'"
If Not rst.NoMatch Then
If MsgBox (Me.txtName & " Is Already in the Table" & vbNewLine &
"Click OK to go to that record", & vbNewLine & "Click Cancel to enter another
name", vbOkCancel + vbQuestion) = vbOk Then
Me.Bookmark = rst.Bookmark
Else
Cancel = True
End If
End If
Set rst = Nothing

 

[twas via AccessMonster.com]

I see potential value for both bound and unbound fields. While bound fields
appear to be far more common, when building a form to search through a
database, it seems that using bound fields could allow the user to
inadvertently change the data rather than searching through it.

Is there an easy (e.g., a few lines of VBA) way to change a field between
bound and unbound?

thanks
Twas

I believe Rick's response to be an accurate analysis of how Access is used
depending on prior experience. I came to Access from a long history of
procedural languages (COBOL and varions versions of BASIC), through FoxPro,
and then to Access.

My experience led me to write my first application using unbound forms. As
Rick said, it is a lot of work. I have since changed to using only bound
forms. I am not sure I agree a finer level on control can be achieved with
unbound forms. To me it is more a matter of really understanding the Event
sequence for controls and forms and how they interact. Coming from a
procedural background, this what the hardest part of Access for me to grasp.
It is also a very powerful environment when you get it down.

When giving users the option to modify information in a table, is it better
to have the fields on the form bound to the underlying table or to use

[quoted text clipped - 3 lines]

Thanks!

 

[Jeff Boyce]

I'd probably approach this by changing the process.

I want my data entry people to add new contacts. I don't want them to add
the same contact twice.

If I force them to add a (potential) new contact before
evaluating/validating, they could to all the work, only to learn that the
person was already a contact.

Or, I could use an unbound combo box in the header of the form. I could
list all of the current contacts' names (LastName, FirstName seems
appropriate). The data entry folks could start out by typing the first part
of the LastName. If you set the LimitToList property to Yes/True, Access
will NOT accept a name that isn't already on the list -- this would trigger
the NotInList event, to which you could add code to allow the addition of
the new contact name.

On the other hand, if the data entry person found a name in the combo box,
s/he could determine if the name they are trying to enter is a new person
with the SAME NAME! If so, I'd probably also have an <Add New Contact>
button that wouldn't light up until after the combo box had been checked.

JOPO - just one person's opinion

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Klatuu]

Possible, but not with a few lines of code, and certainly not practicle.
There is always a danger of data being changed when it is presented in a
bound form whether you are searching through it or not.

Searching is most commonly done using an unbound combo box. You can use a
text box for the field the combo searches on so the user has to actually
place the cursor in the text box to change the data. It is also a good idea
to make the combo unbound because what will happen when you change a bound
combo is you dirty the form. Then when you try to navigate away from the
current record, the form will update the record (or try to) with the new
value in the combo. This will either incorrectly modify your record or if
the search field is required to be unique, raise an error.

The typical way to handle searching using the unbound combo is to use the
After Update event to move to the selected record and the Not In List event
to allow adding a new record.

In the After Update event:

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "[SomeField] = '" & Me.MyCombo & "'" 'Syntax is for a text
field
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing

When the user enters a value in the combo that is not in the combo's row
source and the LimitToList property of the combo is set to Yes, you can use
something like this in the NotInList event to either add the new record or
cancel

Dim rst As Recordset

If MsgBox(NewData & " Is Not In The Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
CurrentDb.Execute ("INSERT INTO MyTable (SomeField) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[SomeField] = '" & NewData & "'"
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Response = acDataErrAdded
Else
Me.cboMyCombo.Undo
Response = acDataErrContinue
End If

If you are using a text box as the bound control the the field the combo is
searching on (in this case SomeField), then it will be populated by the line
"Me.Bookmark = rst.Bookmark"

Believe me, this is the easy way. Being the world's laziest programmer, I
spend a lot of time figuring out the easy way.

I see potential value for both bound and unbound fields. While bound fields
appear to be far more common, when building a form to search through a
database, it seems that using bound fields could allow the user to
inadvertently change the data rather than searching through it.

Is there an easy (e.g., a few lines of VBA) way to change a field between
bound and unbound?

thanks
Twas

I believe Rick's response to be an accurate analysis of how Access is used
depending on prior experience. I came to Access from a long history of
procedural languages (COBOL and varions versions of BASIC), through FoxPro,
and then to Access.

My experience led me to write my first application using unbound forms. As
Rick said, it is a lot of work. I have since changed to using only bound
forms. I am not sure I agree a finer level on control can be achieved with
unbound forms. To me it is more a matter of really understanding the Event
sequence for controls and forms and how they interact. Coming from a
procedural background, this what the hardest part of Access for me to grasp.
It is also a very powerful environment when you get it down.

When giving users the option to modify information in a table, is it better
to have the fields on the form bound to the underlying table or to use

[quoted text clipped - 3 lines]

Thanks!

 

[Klatuu]

I would do it almost exactly the same way. See my previous post.

 

[SF]

In case there are millions of row, bound form tend to work slower than
unbound form?

SF

Possible, but not with a few lines of code, and certainly not practicle.
There is always a danger of data being changed when it is presented in a
bound form whether you are searching through it or not.

Searching is most commonly done using an unbound combo box. You can use a
text box for the field the combo searches on so the user has to actually
place the cursor in the text box to change the data. It is also a good
idea
to make the combo unbound because what will happen when you change a bound
combo is you dirty the form. Then when you try to navigate away from the
current record, the form will update the record (or try to) with the new
value in the combo. This will either incorrectly modify your record or if
the search field is required to be unique, raise an error.

The typical way to handle searching using the unbound combo is to use the
After Update event to move to the selected record and the Not In List
event
to allow adding a new record.

In the After Update event:

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "[SomeField] = '" & Me.MyCombo & "'" 'Syntax is for a
text
field
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing

When the user enters a value in the combo that is not in the combo's row
source and the LimitToList property of the combo is set to Yes, you can
use
something like this in the NotInList event to either add the new record or
cancel

Dim rst As Recordset

If MsgBox(NewData & " Is Not In The Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
CurrentDb.Execute ("INSERT INTO MyTable (SomeField) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[SomeField] = '" & NewData & "'"
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Response = acDataErrAdded
Else
Me.cboMyCombo.Undo
Response = acDataErrContinue
End If

If you are using a text box as the bound control the the field the combo
is
searching on (in this case SomeField), then it will be populated by the
line
"Me.Bookmark = rst.Bookmark"

Believe me, this is the easy way. Being the world's laziest programmer, I
spend a lot of time figuring out the easy way.

I see potential value for both bound and unbound fields. While bound
fields
appear to be far more common, when building a form to search through a
database, it seems that using bound fields could allow the user to
inadvertently change the data rather than searching through it.

Is there an easy (e.g., a few lines of VBA) way to change a field between
bound and unbound?

thanks
Twas

I believe Rick's response to be an accurate analysis of how Access is
used
depending on prior experience. I came to Access from a long history of
procedural languages (COBOL and varions versions of BASIC), through
FoxPro,
and then to Access.

My experience led me to write my first application using unbound forms.
As
Rick said, it is a lot of work. I have since changed to using only
bound
forms. I am not sure I agree a finer level on control can be achieved
with
unbound forms. To me it is more a matter of really understanding the
Event
sequence for controls and forms and how they interact. Coming from a
procedural background, this what the hardest part of Access for me to
grasp.
It is also a very powerful environment when you get it down.

When giving users the option to modify information in a table, is it
better
to have the fields on the form bound to the underlying table or to use
[quoted text clipped - 3 lines]

Thanks!

 

[Rick Brandt]

In case there are millions of row, bound form tend to work slower than
unbound form?

Only if you are silly enough to open a form bound to a millions-of-rows table
without a query or filter limiting the records returned. Forms bound to large
tables should always be set up to return the absolute minimum records to do a
poarticualr job (preferably exactly one records ).