By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,526 Members | 2,207 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,526 IT Pros & Developers. It's quick & easy.

Programmatically changing RecordSource - too risky?

P: n/a
I have a popup form with a textbox that is bound to a memo field. I've been
warned about memo fields so I'm wondering if I should use this code. Is
there any risk with changing the form's RecordSource like this? Am I asking
for trouble doing this with a memo field?

Thanks in advance.

Private Sub cmdNextNote_Click()
Dim lngNid As Long
If Me.Dirty Then DoCmd.RunCommand acCmdSaveRecord
lngNid = Nz(DMin("Note_ID", "tblNotes", "Entity_ID = " & Me!Entity_ID &
_
" And Note_ID > " & Me!Note_ID))
If lngNid > 0 Then Call SetRecordSource(lngNid)
End Sub

Private Sub cmdPreviousNote_Click()
Dim lngNid As Long
If Me.Dirty Then DoCmd.RunCommand acCmdSaveRecord
lngNid = Nz(DMax("Note_ID", "tblNotes", "Entity_ID = " & Me!Entity_ID &
_
" And Note_ID < " & Me!Note_ID))
If lngNid > 0 Then Call SetRecordSource(lngNid)
End Sub

Private Sub SetRecordSource(lngNid As Long)
Dim strSql As String
Dim db As DAO.Database
Dim qdfs As DAO.QueryDefs
Dim qdf As DAO.QueryDef
If lngNid > 0 Then
'selecting only the note in question controls the behavior of the
'spell checker: only the selected note is checked, then the check
completes.
'When the RecordSource contains more than one note, the spell
checker
'proceeds to the next note, and then the next before completing -
which
'results in an unselected note appearing in the form and causing
'the code in Form_Unload to fail.
strSql = "SELECT [NoteBrief], [NoteDate], [NoteText], [Note_ID],
Entity_ID " & _
"FROM tblNotes WHERE ([Note_ID] = " & lngNid & ");"
Else
'when frm0.tglNewNote is clicked, the OpenForm method is called
'with 'acFormAdd' as the Data Mode argument, so we immediately
'arrive at a new record; thus we need only select the top 1
'record for the purpose of getting an updatable RecordSource
strSql = "SELECT TOP 1 [NoteBrief], [NoteDate], [NoteText],
[Entity_ID], " & _
"[Note_ID] FROM tblNotes;"
End If
Set db = CurrentDb
Set qdfs = db.QueryDefs
Set qdf = qdfs("qryNotesDetail")
qdf.SQL = strSql
Me.RecordSource = qdf.Name
Call SetNoteHeader
Set qdf = Nothing
Set qdfs = Nothing
Set db = Nothing
End Sub

Jan 17 '06 #1
Share this Question
Share on Google+
32 Replies


P: n/a
deko, I'm not the most experienced or best person on this newsgroup to
answer your question, but it looks like no one else is going to. I
don't know what you might run into with the memo fields, but I would
rather change my RecordSource programmatically than create a new
form/report for each category or type of data. As long as your field
names are the same in the tables/queries, I don't see what the problem
would be.

Jan 17 '06 #2

P: n/a
deko wrote:
I have a popup form with a textbox that is bound to a memo field. I've been
warned about memo fields ....


I've been warned about strawberries!

Jan 17 '06 #3

P: n/a
If all of that code is required just to avoid a bound
memo field, then dump the code and use a bound memo
field.

Note: memo fields were frequently corrupted 8 years ago.
Don't let that define your coding practice today.

If it is worth using an unbound form, or a form bound to
a local table, do it for all fields, not just the memo
field.

(david)
"deko" <de**@nospam.com> wrote in message
news:YO******************************@comcast.com. ..
I have a popup form with a textbox that is bound to a memo field. I've
been warned about memo fields so I'm wondering if I should use this code.
Is there any risk with changing the form's RecordSource like this? Am I
asking for trouble doing this with a memo field?

Thanks in advance.

Private Sub cmdNextNote_Click()
Dim lngNid As Long
If Me.Dirty Then DoCmd.RunCommand acCmdSaveRecord
lngNid = Nz(DMin("Note_ID", "tblNotes", "Entity_ID = " & Me!Entity_ID &
_
" And Note_ID > " & Me!Note_ID))
If lngNid > 0 Then Call SetRecordSource(lngNid)
End Sub

Private Sub cmdPreviousNote_Click()
Dim lngNid As Long
If Me.Dirty Then DoCmd.RunCommand acCmdSaveRecord
lngNid = Nz(DMax("Note_ID", "tblNotes", "Entity_ID = " & Me!Entity_ID &
_
" And Note_ID < " & Me!Note_ID))
If lngNid > 0 Then Call SetRecordSource(lngNid)
End Sub

Private Sub SetRecordSource(lngNid As Long)
Dim strSql As String
Dim db As DAO.Database
Dim qdfs As DAO.QueryDefs
Dim qdf As DAO.QueryDef
If lngNid > 0 Then
'selecting only the note in question controls the behavior of the
'spell checker: only the selected note is checked, then the check
completes.
'When the RecordSource contains more than one note, the spell
checker
'proceeds to the next note, and then the next before completing -
which
'results in an unselected note appearing in the form and causing
'the code in Form_Unload to fail.
strSql = "SELECT [NoteBrief], [NoteDate], [NoteText], [Note_ID],
Entity_ID " & _
"FROM tblNotes WHERE ([Note_ID] = " & lngNid & ");"
Else
'when frm0.tglNewNote is clicked, the OpenForm method is called
'with 'acFormAdd' as the Data Mode argument, so we immediately
'arrive at a new record; thus we need only select the top 1
'record for the purpose of getting an updatable RecordSource
strSql = "SELECT TOP 1 [NoteBrief], [NoteDate], [NoteText],
[Entity_ID], " & _
"[Note_ID] FROM tblNotes;"
End If
Set db = CurrentDb
Set qdfs = db.QueryDefs
Set qdf = qdfs("qryNotesDetail")
qdf.SQL = strSql
Me.RecordSource = qdf.Name
Call SetNoteHeader
Set qdf = Nothing
Set qdfs = Nothing
Set db = Nothing
End Sub

Jan 17 '06 #4

P: n/a

"Lyle Fairfield" <ly***********@aim.com> wrote
deko wrote:
I have a popup form with a textbox that
is bound to a memo field. I've been
warned about memo fields ....


I've been warned about strawberries!


Lyle, I'll bet that was "strawberry blondes" that your Mama warned you
about. <GRIN> They can be very dangerous, I've heard.
Jan 18 '06 #5

P: n/a

"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
news:43***********************@lon-reader.news.telstra.net...
If all of that code is required just to avoid a bound
memo field, then dump the code and use a bound memo
field.

Note: memo fields were frequently corrupted 8 years ago.
Don't let that define your coding practice today.

If it is worth using an unbound form, or a form bound to
a local table, do it for all fields, not just the memo
field.


10-4. I guess I just had a bit of paranoia there...

Jan 18 '06 #6

P: n/a
"deko" <de**@nospam.com> wrote in
news:fc********************@comcast.com:
"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in
message
news:43***********************@lon-reader.news.telstra.net...
If all of that code is required just to avoid a bound
memo field, then dump the code and use a bound memo
field.

Note: memo fields were frequently corrupted 8 years ago.
Don't let that define your coding practice today.

If it is worth using an unbound form, or a form bound to
a local table, do it for all fields, not just the memo
field.


10-4. I guess I just had a bit of paranoia there...


Memo fields can *still* corrupt.

There are two basic techniques that lower the risk almost to zero:

1. keep your memo fields in a separate table. If they get corrupted,
you won't lose anything *but* the memo. If you put the memos inline
in the tables, you can lose other data, as well.

2. edit the memos unbound only. It is not difficult and does not
require an unbound form, just that you not bind the memo field(s).

I've seen lost memo fields in Access 2000, but I can't say that I've
seen them since the relese of Jet 4 SP6. On the other hand, in the
apps where I've seen them, I've unbound the editing, so I might not
be seeing the real frequency of problems.

In my experience, memos (and all data) are more fragile in Access 2K
and later than they ever were in A97 or A2.0.

Oh, and one other problem is that if you're using replication and
you have a synchronization happen on the back end while a user is
actually editing a memo, it can corrupt the memo pointer. At the
very least, any memos in a replicated database should be edited
unbound.

To reiterate: I think it's highly unwise to minimize the danger of
memo fields. That danger is still there and quite easily minimized
without needing to completey re-engineer your applications.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jan 18 '06 #7

P: n/a
Memo fields themselves are corruptible, as David stated. I've seen it
in the work I've done in Access XP and 2003 - they have a tendency of
causing data loss and magically create bad characters. However, the OP
asked about problems that might arise from changing the form's
RecordSource on the fly when the RecordSource has a memo field in it.
Outside of the difficulties that memo fields have by themselves, what
problems might occur as a result of doing this?

Jan 18 '06 #8

P: n/a
> Memo fields themselves are corruptible, as David stated. I've seen it
in the work I've done in Access XP and 2003 - they have a tendency of
causing data loss and magically create bad characters. However, the OP
asked about problems that might arise from changing the form's
RecordSource on the fly when the RecordSource has a memo field in it.
Outside of the difficulties that memo fields have by themselves, what
problems might occur as a result of doing this?


Thanks for clarifying my question. My concern is that I might exacerbate
the inherent known fragility of the memo filed by slamming the recordsource
around. I used to follow the second suggestion in David Fenton's post -
edit in an unbound text box - but I'm not sure why this is purportedly
safer. I would think letting Jet do it's job when the record is updated is
no worse than using code like this:

Me!NoteText = Me!txtNoteText

As for keeping all memos in a separate table, that's not really practical
for this app.

I dunno... maybe I'd refactor again...

Jan 19 '06 #9

P: n/a
"deko" <de**@nospam.com> wrote in
news:Tu********************@comcast.com:
Memo fields themselves are corruptible, as David stated. I've seen it
in the work I've done in Access XP and 2003 - they have a tendency of
causing data loss and magically create bad characters. However, the
OP asked about problems that might arise from changing the form's
RecordSource on the fly when the RecordSource has a memo field in it.
Outside of the difficulties that memo fields have by themselves, what
problems might occur as a result of doing this?


Thanks for clarifying my question. My concern is that I might
exacerbate the inherent known fragility of the memo filed by slamming
the recordsource around. I used to follow the second suggestion in
David Fenton's post - edit in an unbound text box - but I'm not sure
why this is purportedly safer. I would think letting Jet do it's job
when the record is updated is no worse than using code like this:

Me!NoteText = Me!txtNoteText

As for keeping all memos in a separate table, that's not really
practical for this app.

I dunno... maybe I'd refactor again...


Every person who ate strawberries in the year 1873 is dead (well - maybe
not Larry Linson)! This is pretty conclusive evidence of the dangers of
strawberries. You think I'm gonna risk eating them? No way!

--
Lyle Fairfield
Jan 19 '06 #10

P: n/a
If that's the case, I'll take two pounds of strawberries :) I'll
eventually die after eating them, but I'll enjoy it while you look on
and sigh in envy :)

Jan 19 '06 #11

P: n/a
"deko" <de**@nospam.com> wrote in
news:Tu********************@comcast.com:
Memo fields themselves are corruptible, as David stated. I've
seen it in the work I've done in Access XP and 2003 - they have a
tendency of causing data loss and magically create bad
characters. However, the OP asked about problems that might arise
from changing the form's RecordSource on the fly when the
RecordSource has a memo field in it. Outside of the difficulties
that memo fields have by themselves, what problems might occur as
a result of doing this?
Thanks for clarifying my question. My concern is that I might
exacerbate the inherent known fragility of the memo filed by
slamming the recordsource around. . . .


Changing the recordsource will save any pending edits on the
previous recordsource. But I would save the record before changing
the recordsource, just to be sure I didn't run up against any
unknown bugs in this regard. We already know about the problem with
data edits potentially being lost when the form's close button is
clicked, so I think it's better that if you want something to happen
(i.e., the record to be saved), then you should explicitly do it.
. . . I used to follow the second suggestion in David Fenton's
post - edit in an unbound text box - but I'm not sure why this is
purportedly safer. I would think letting Jet do it's job when the
record is updated is no worse than using code like this:

Me!NoteText = Me!txtNoteText
Well, it's quite obvious why it's safer.

If it takes someone 5 minutes to write a memo, then there's 5
minutes of vulnerability for the memo. If, on the other hand, they
take 5 minutes to edit the unbound control, it has no impact
whatsoever on the memo field, since it's not being edited.

Remember: the problem with memos is that the main record holds a
pointer to a different data page, where the actual memo data is
stored. This is because memos can be of variable size. If you're
editing the memo bound, you're locking both the main record and the
memo page. If you're editing it unbound, you're only locking the
main record. And, if you're editing only the memo, the main record
isn't locked at all.

This is the principle behind all unbound forms, that you are locking
the back end record only while the update from the unbound form is
happening.

One other thing, after estting the underlying recordsource's memo
field to the value from the unbound edit box, you should save the
record, or you're correct that you're not getting the full benefit
out of unbinding the memo field (you're still getting some of the
benefit, just not all of it).
As for keeping all memos in a separate table, that's not really
practical for this app.

I dunno... maybe I'd refactor again...


I have lost the original context of the question. I don't see that
changing the recordsource has any effects on memos that it wouldn't
also have on all other fields -- you should save the record *before*
switching the recordsource, in order to force any validation to
occur and to allow any errors to bubble up. Not doing so risks
missing those errors.

But that has zilch to do with memo fields.

So far as I know, the issues with memo fields are addressed only by
the two methods I've outlined.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jan 19 '06 #12

P: n/a
>>> Memo fields themselves are corruptible, as David stated. I've
seen it in the work I've done in Access XP and 2003 - they have a
tendency of causing data loss and magically create bad
characters. However, the OP asked about problems that might arise
from changing the form's RecordSource on the fly when the
RecordSource has a memo field in it. Outside of the difficulties
that memo fields have by themselves, what problems might occur as
a result of doing this?


Thanks for clarifying my question. My concern is that I might
exacerbate the inherent known fragility of the memo filed by
slamming the recordsource around. . . .


Changing the recordsource will save any pending edits on the
previous recordsource. But I would save the record before changing
the recordsource, just to be sure I didn't run up against any
unknown bugs in this regard. We already know about the problem with
data edits potentially being lost when the form's close button is
clicked, so I think it's better that if you want something to happen
(i.e., the record to be saved), then you should explicitly do it.
. . . I used to follow the second suggestion in David Fenton's
post - edit in an unbound text box - but I'm not sure why this is
purportedly safer. I would think letting Jet do it's job when the
record is updated is no worse than using code like this:

Me!NoteText = Me!txtNoteText


Well, it's quite obvious why it's safer.

If it takes someone 5 minutes to write a memo, then there's 5
minutes of vulnerability for the memo. If, on the other hand, they
take 5 minutes to edit the unbound control, it has no impact
whatsoever on the memo field, since it's not being edited.

Remember: the problem with memos is that the main record holds a
pointer to a different data page, where the actual memo data is
stored. This is because memos can be of variable size. If you're
editing the memo bound, you're locking both the main record and the
memo page. If you're editing it unbound, you're only locking the
main record. And, if you're editing only the memo, the main record
isn't locked at all.

This is the principle behind all unbound forms, that you are locking
the back end record only while the update from the unbound form is
happening.

One other thing, after estting the underlying recordsource's memo
field to the value from the unbound edit box, you should save the
record, or you're correct that you're not getting the full benefit
out of unbinding the memo field (you're still getting some of the
benefit, just not all of it).
As for keeping all memos in a separate table, that's not really
practical for this app.

I dunno... maybe I'd refactor again...


I have lost the original context of the question. I don't see that
changing the recordsource has any effects on memos that it wouldn't
also have on all other fields -- you should save the record *before*
switching the recordsource, in order to force any validation to
occur and to allow any errors to bubble up. Not doing so risks
missing those errors.

But that has zilch to do with memo fields.

So far as I know, the issues with memo fields are addressed only by
the two methods I've outlined.


Thanks, David. That was helpful info...
Jan 19 '06 #13

P: n/a
> If that's the case, I'll take two pounds of strawberries :) I'll
eventually die after eating them, but I'll enjoy it while you look on
and sigh in envy :)


But those were 1873 strawberries! If you eat 2006 strawberries, you will
not surely die. When you eat them your eyes will be opened, and you will be
like Lyle Fairfield, knowing good memo fields from evil memo fields.

Jan 19 '06 #14

P: n/a
>> Me!NoteText = Me!txtNoteText


That field is bound to the form. You are going
to get locks on everything in the recordsouce,
bind the memo field to a control or not.

If the recordsouce include a memo field, you
will get page locks. You also get read locks
because the memo field counts as a separate
table.

There are no obvious advantages to that code
sample at all.

If you want to use a properly unbound memo field,
the extra effort to make the form fully unbound
from any remote table is small.

(david)

"deko" <de**@nospam.com> wrote in message
news:Tu********************@comcast.com...
Memo fields themselves are corruptible, as David stated. I've seen it
in the work I've done in Access XP and 2003 - they have a tendency of
causing data loss and magically create bad characters. However, the OP
asked about problems that might arise from changing the form's
RecordSource on the fly when the RecordSource has a memo field in it.
Outside of the difficulties that memo fields have by themselves, what
problems might occur as a result of doing this?


Thanks for clarifying my question. My concern is that I might exacerbate
the inherent known fragility of the memo filed by slamming the
recordsource around. I used to follow the second suggestion in David
Fenton's post - edit in an unbound text box - but I'm not sure why this is
purportedly safer. I would think letting Jet do it's job when the record
is updated is no worse than using code like this:

Me!NoteText = Me!txtNoteText

As for keeping all memos in a separate table, that's not really practical
for this app.

I dunno... maybe I'd refactor again...

Jan 19 '06 #15

P: n/a
> That field is bound to the form. You are going
to get locks on everything in the recordsouce,
bind the memo field to a control or not.

If the recordsouce include a memo field, you
will get page locks. You also get read locks
because the memo field counts as a separate
table.

There are no obvious advantages to that code
sample at all.

If you want to use a properly unbound memo field,
the extra effort to make the form fully unbound
from any remote table is small.


So you disagree with the point 2 below:
There are two basic techniques that lower the risk almost to zero:

1. keep your memo fields in a separate table. If they get corrupted,
you won't lose anything *but* the memo. If you put the memos inline
in the tables, you can lose other data, as well.

2. edit the memos unbound only. It is not difficult and does not
require an unbound form, just that you not bind the memo field(s).


How about this:

First, I make form completely unbound (no RecordSource).

Then, I create a query on the Form_Open event like this:

Private Sub Form_Open(Cancel As Integer)
Dim strSql As String
Dim lngNid as Long
Dim db As DAO.Database
Dim qdfs As DAO.QueryDefs
Dim qdf As DAO.QueryDef
lngNid = Me.OpenArgs

'NoteText is the Memo Field
strSql = "SELECT [NoteDate], [NoteText], " & _
[Note_ID] FROM tblNotes WHERE ([Note_ID] = _
" & lngNid & ");"

Set db = CurrentDb
Set qdfs = db.QueryDefs
Set qdf = qdfs("qryNotes")
qdf.SQL = strSql
End Sub

Then, I populate unbound TextBoxes on Form_Load like this:

Private Sub Form_Load()
Me!txtNoteText = DLookup("NoteText", "qryNotes")
Me!txtNote_ID = DLookup("Note_ID", "qryNotes")
'and so on for the other fields I may want
End Sub

And save any changes on Form_Close like this:

Private Sub Form_Close()
Dim strSql As String
Dim db As DAO.Database
strSql = "UPDATE tblNotes SET [NoteText] = " & Me!txtNoteText & _
" WHERE ([Note_ID] = " & Me!txtNote_ID & ");"
db.Execute strSql, dbFailOnError
End Sub

??????????????
Jan 20 '06 #16

P: n/a
Most of my day is spent with unbound forms,
or forms bound to a local table, so it's
not that I disagree with unbinding. It is
not difficult, and does not require an additional
form, just that you not bind to a select
query.

The code you have below for the unbound memo field
can be easily extended for a totally unbound form,
or you can create a local table and use delete-
append-update to load and update your data.

I also have memo fields in tables bound
directly to forms. In those cases, the forms
are not used often, and there is no transactional
data. The memo fields were put there by an idiot,
and are never used. I would also use a bound memo
field in a low volume, single user database - which
is most of the Access databases in the world -
if required, but in most of these cases an even
better alternative is to use a text field, not
a memo field, and add structured data to capture
additional information.

I do also have a separate table for a bound memo
field, left over from A97 SR2, 1998. It was easier
to support, because when I got memo corruption,
I only had to delete the memo record, not reconstruct
a damaged record. Since A97 SR3, it has never had
memo corruption.

I say, horses for courses. Even though the memo
corruption problem is gone, first consider
not using a memo field. Then consider using
a bound field. Then consider using an unbound
form.

I just can't imagine going to the trouble of
using an unbound memo field, and not completing
the step and adding the lines for the other
controls.

Worse, I have seen the locking problems when
I try to update the same record on two different
connections, from the bound form and from an
update query.
(david)
"deko" <de**@nospam.com> wrote in message
news:7d********************@comcast.com...
That field is bound to the form. You are going
to get locks on everything in the recordsouce,
bind the memo field to a control or not.

If the recordsouce include a memo field, you
will get page locks. You also get read locks
because the memo field counts as a separate
table.

There are no obvious advantages to that code
sample at all.

If you want to use a properly unbound memo field,
the extra effort to make the form fully unbound
from any remote table is small.


So you disagree with the point 2 below:
There are two basic techniques that lower the risk almost to zero:

1. keep your memo fields in a separate table. If they get corrupted,
you won't lose anything *but* the memo. If you put the memos inline
in the tables, you can lose other data, as well. 2. edit the memos
unbound only. It is not difficult and does not
require an unbound form, just that you not bind the memo field(s).


How about this:

First, I make form completely unbound (no RecordSource).

Then, I create a query on the Form_Open event like this:

Private Sub Form_Open(Cancel As Integer)
Dim strSql As String
Dim lngNid as Long
Dim db As DAO.Database
Dim qdfs As DAO.QueryDefs
Dim qdf As DAO.QueryDef
lngNid = Me.OpenArgs
'NoteText is the Memo Field
strSql = "SELECT [NoteDate], [NoteText], " & _ [Note_ID] FROM tblNotes
WHERE ([Note_ID] = _ " & lngNid & ");"
Set db = CurrentDb
Set qdfs = db.QueryDefs
Set qdf = qdfs("qryNotes")
qdf.SQL = strSql
End Sub

Then, I populate unbound TextBoxes on Form_Load like this:

Private Sub Form_Load()
Me!txtNoteText = DLookup("NoteText", "qryNotes")
Me!txtNote_ID = DLookup("Note_ID", "qryNotes")
'and so on for the other fields I may want
End Sub

And save any changes on Form_Close like this:

Private Sub Form_Close()
Dim strSql As String
Dim db As DAO.Database
strSql = "UPDATE tblNotes SET [NoteText] = " & Me!txtNoteText & _ "
WHERE ([Note_ID] = " & Me!txtNote_ID & ");"
db.Execute strSql, dbFailOnError
End Sub

??????????????

Jan 20 '06 #17

P: n/a
> Most of my day is spent with unbound forms,
or forms bound to a local table, so it's
not that I disagree with unbinding. It is
not difficult, and does not require an additional
form, just that you not bind to a select
query.
At the risk of overload, I've posted the complete code below for the newly
refactored form - completely unbound.
The code you have below for the unbound memo field
can be easily extended for a totally unbound form,
or you can create a local table and use delete-
append-update to load and update your data.
Could not do that - see comments in code below.
(second to last sub)
I also have memo fields in tables bound
directly to forms. In those cases, the forms
are not used often, and there is no transactional
data. The memo fields were put there by an idiot,
and are never used. I would also use a bound memo
field in a low volume, single user database - which
is most of the Access databases in the world -
if required, but in most of these cases an even
better alternative is to use a text field, not
a memo field, and add structured data to capture
additional information.
10-4
I do also have a separate table for a bound memo
field, left over from A97 SR2, 1998. It was easier
to support, because when I got memo corruption,
I only had to delete the memo record, not reconstruct
a damaged record. Since A97 SR3, it has never had
memo corruption.

I say, horses for courses. Even though the memo
corruption problem is gone, first consider
not using a memo field. Then consider using
a bound field. Then consider using an unbound
form.

I just can't imagine going to the trouble of
using an unbound memo field, and not completing
the step and adding the lines for the other
controls.
That makes sense.
Worse, I have seen the locking problems when
I try to update the same record on two different
connections, from the bound form and from an
update query.


Sounds like if you're really paranoid (I am), the best bet is to use an
unbound form. Here's what I came up with:

Option Compare Database
Option Explicit
Private m_lngNid As Long 'Note ID
Private m_lngEid As Long 'Entity ID
Private m_blnChanges As Boolean

Private Sub cmdDeleteNote_Click()
On Error GoTo HandleErr
Dim intResponse As Integer
Dim db As DAO.Database
Dim strSql As String
If m_lngNid = 0 Then Me!txtNoteText = Null
If IsNull(Me!txtNoteText) Then
intResponse = vbYes
ElseIf m_lngNid > 0 Then
intResponse = MsgBox("Are you sure you want to delete this note?
", _
vbYesNo + vbQuestion, " Confirm Delete")
End If
If intResponse = vbYes Then
If m_lngNid > 0 Then
strSql = "DELETE * FROM tblNotes WHERE ([Note_ID] = " & _
m_lngNid & ");"
Set db = CurrentDb
db.Execute strSql, dbFailOnError
If DLookup("Ct", "qryCtNotes") = 0 Then
If modHandler.IsOpen("frmBig") Then DoCmd.Close acForm,
"frmBig"
End If
End If
End If
DoCmd.Close acForm, "frmNotesDetail"
Exit_Here:
On Error Resume Next
Set db = Nothing
Exit Sub
HandleErr:
Select Case Err.Number
Case Else
modHandler.LogErr "frmNotesDetail", "cmdDeleteNote_Click"
End Select
Resume Exit_Here
End Sub

Private Sub cmdMoveNote_Click()
On Error GoTo HandleErr
DoCmd.OpenForm "frmNotesMove", , , , , , m_lngEid
Exit_Here:
Exit Sub
HandleErr:
Select Case Err.Number
Case Else
modHandler.LogErr "frmNotesDetail", "cmdMoveNote_Click"
End Select
Resume Exit_Here
End Sub

Private Sub cmdNextNote_Click()
On Error GoTo HandleErr
If GetNextNoteID("Next") Then Call GoToNote
Exit_Here:
Exit Sub
HandleErr:
Select Case Err.Number
Case Else
modHandler.LogErr "frmNotesDetail", "cmdNextNote_Click"
End Select
Resume Exit_Here
End Sub

Private Sub cmdPreviousNote_Click()
On Error GoTo HandleErr
If GetNextNoteID("Previous") Then Call GoToNote
Exit_Here:
Exit Sub
HandleErr:
Select Case Err.Number
Case Else
modHandler.LogErr "frmNotesDetail", "cmdPreviousNote_Click"
End Select
Resume Exit_Here
End Sub

Private Function GetNextNoteID(strDirection As String) As Boolean
On Error GoTo HandleErr
Dim lngNid As Long
Select Case strDirection
Case "Next"
lngNid = Nz(DMin("Note_ID", "tblNotes", "Entity_ID = " &
m_lngEid & _
" And Note_ID > " & m_lngNid))
Case "Previous"
lngNid = Nz(DMax("Note_ID", "tblNotes", "Entity_ID = " &
m_lngEid & _
" And Note_ID < " & m_lngNid))
End Select
If lngNid > 0 Then
m_lngNid = lngNid
GetNextNoteID = True
Else
GetNextNoteID = False
End If
Exit_Here:
Exit Function
HandleErr:
Select Case Err.Number
Case Else
modHandler.LogErr "frmNotesDetail", "GetNextNoteID"
End Select
Resume Exit_Here
End Function
Private Sub GoToNote()
On Error GoTo HandleErr
If m_lngNid > 0 Then
Call GetNote
Call LoadNote
m_blnChanges = False
End If
Exit_Here:
Exit Sub
HandleErr:
Select Case Err.Number
Case Else
modHandler.LogErr "frmNotesDetail", "GoToNote"
End Select
Resume Exit_Here
End Sub

Private Sub cmdPrint_Click()
On Error GoTo HandleErr
If Not IsNull(Me!txtNoteText) Then
If m_blnChanges Then Call SaveNote
If m_lngNid > 0 Then
Me!txtNoteID = m_lngNid
Else
Me!txtNoteID = DMax("Note_ID", "tblNotes")
End If
'qryrptNotes (record source for rptNotesDetail) looks for txtNoteID
DoCmd.OpenReport "rptNotesDetail", acViewPreview
End If
Exit_Here:
Exit Sub
HandleErr:
Select Case Err.Number
Case 2212 'Couldn't print your object
'(occurs when cancelling from pdfWriter)
Case Else
modHandler.LogErr "frmNotesDetail", "cmdPrint_Click"
End Select
Resume Exit_Here
End Sub

Private Sub cmdOK_Click()
On Error GoTo HandleErr
DoCmd.Close acForm, "frmNotesDetail"
Exit_Here:
Exit Sub
HandleErr:
Select Case Err.Number
Case Else
modHandler.LogErr "frmNotesDetail", "cmdOK_Click"
End Select
Resume Exit_Here
End Sub

Private Sub cmdSpellCheck_Click()
On Error GoTo HandleErr
If Not IsNull(Me!txtNoteText) Then DoCmd.RunCommand acCmdSpelling
Exit_Here:
Exit Sub
HandleErr:
Select Case Err.Number
Case Else
modHandler.LogErr "frmNotesDetail", "cmdSpellCheck"
End Select
Resume Exit_Here
End Sub

Private Sub Form_Load()
On Error GoTo HandleErr
Dim blRet As Boolean
blRet = MouseWheelOFF(False)
Me!cmdMoveNote.Enabled = m_lngNid
Me!cmdNextNote.Enabled = m_lngNid
Me!cmdPreviousNote.Enabled = m_lngNid
Call LoadNote
Exit_Here:
Exit Sub
HandleErr:
Select Case Err.Number
Case Else
modHandler.LogErr "frmNotesDetail", "Form_Load"
End Select
Resume Exit_Here
End Sub

Private Sub LoadNote()
On Error GoTo HandleErr
Me!txtEntityID = Forms("frm0")!Entity_ID
m_lngEid = Me!txtEntityID
'we'll compare txtEntityID and m_lngEid later
If m_lngNid > 0 Then
Me!txtNoteText = Nz(DLookup("NoteText", "qryNotesDetail"))
End If
Call SetNoteHeader
Exit_Here:
Exit Sub
HandleErr:
Select Case Err.Number
Case Else
modHandler.LogErr "frmNotesDetail", "LoadNote"
End Select
Resume Exit_Here
End Sub

Private Sub SetNoteHeader()
On Error GoTo HandleErr
Dim dtmNoteDate As Date
Dim strNoteDateHeader As String
Dim strNoteEntityHeader As String
If m_lngNid > 0 Then
dtmNoteDate = Nz(DLookup("NoteDate", "qryNotesDetail"))
strNoteDateHeader = Format(dtmNoteDate, "dddd") & " " & dtmNoteDate
strNoteDateHeader = "Note dated " & strNoteDateHeader & _
" for Entity ID " & m_lngEid
Me.Caption = "Edit Note"
Else
strNoteDateHeader = "New note for Entity ID " & m_lngEid
Me.Caption = "New Note"
End If
Me!lblNoteDateHeader.Caption = strNoteDateHeader
Me!txtNoteEntityHeader = modHandler.NameHeader(m_lngEid)
Exit_Here:
Exit Sub
HandleErr:
Select Case Err.Number
Case Else
modHandler.LogErr "frmNotesDetail", "SetNoteHeader"
End Select
Resume Exit_Here
End Sub

Private Sub Form_Open(Cancel As Integer)
On Error GoTo HandleErr
m_lngNid = Nz(Me.OpenArgs)
If m_lngNid > 0 Then Call GetNote
Exit_Here:
Exit Sub
HandleErr:
Select Case Err.Number
Case Else
modHandler.LogErr "frmNotesDetail", "Form_Open"
End Select
Resume Exit_Here
End Sub

Private Sub GetNote()
On Error GoTo HandleErr
Dim strSql As String
Dim db As DAO.Database
Dim qdfs As DAO.QueryDefs
Dim qdf As DAO.QueryDef
If m_lngNid > 0 Then
strSql = "SELECT [NoteDate], [NoteBrief], [NoteText], [Note_ID], " &
_
"[Entity_ID] FROM tblNotes WHERE ([Note_ID] = " & m_lngNid &
");"
Else
strSql = "SELECT TOP 1 [NoteDate], [NoteBrief], [NoteText],
[Note_ID], " & _
"[Entity_ID] FROM tblNotes;"
End If
Set db = CurrentDb
Set qdfs = db.QueryDefs
Set qdf = qdfs("qryNotesDetail")
qdf.SQL = strSql
Exit_Here:
On Error Resume Next
Set qdf = Nothing
Set qdfs = Nothing
Set db = Nothing
Exit Sub
HandleErr:
Select Case Err.Number
Case Else
modHandler.LogErr "frmNotesDetail", "GetNote"
End Select
Resume Exit_Here
End Sub

Private Sub Form_Close()
'toggles button and requeries frm0Notes
On Error GoTo HandleErr
Dim blRet As Boolean
If IsOpen("frmBig") Then
Forms("frmBig")!tglNewNote.Value = 0
Forms("frmBig")!frmBig_datasheet.Form.Requery
Else
Forms("frm0")!tglNewNote.Value = 0
Forms("frm0")!frm0Notes.Form.Requery
End If
Exit_Here:
blRet = MouseWheelON
Exit Sub
HandleErr:
Select Case Err.Number
Case Else
modHandler.LogErr "frmNotesDetail", "Form_Close"
End Select
Resume Exit_Here
End Sub

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
On Error GoTo HandleErr
Select Case KeyCode
Case vbKeyPageUp, vbKeyPageDown 'disable PageUp and PageDown
KeyCode = 0
End Select
Exit_Here:
Exit Sub
HandleErr:
Select Case Err.Number
Case Else
modHandler.LogErr "frmNotesDetail", "Form_KeyDown"
End Select
Resume Exit_Here
End Sub

Private Sub Form_Unload(Cancel As Integer)
On Error GoTo HandleErr
If m_lngNid > 0 Then
If m_blnChanges Or (Me!txtEntityID <> m_lngEid) Then
Call SaveNote
Debug.Print "changes saved"
End If
Else
If Not IsNull(Me!txtNoteText) Then Call SaveNote
End If
Exit_Here:
Exit Sub
HandleErr:
Select Case Err.Number
Case Else
modHandler.LogErr "frmNotesDetail", "Form_Unload"
End Select
Resume Exit_Here
End Sub

Private Sub SaveNote()
'I initially tried to use an UPDATE and INSERT
'query, but had trouble. Because I never know what
'might be in the memo field I need to use a parameter
'query to get the exact text. But Jet Parameters are
'limited to 255 characters. So I used a recordset.
On Error GoTo HandleErr
Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rst = db.OpenRecordset("qryNotesDetail")
If m_lngNid > 0 Then
rst.Edit
If Me!txtEntityID <> m_lngEid Then
rst!Entity_ID = Me!txtEntityID
End If
Else
rst.AddNew
rst!Entity_ID = m_lngEid
End If
rst!NoteText = Me!txtNoteText
If IsNull(Me!txtNoteText) Then
rst!NoteBrief = Null
Else
rst!NoteBrief = Left$(Me!txtNoteText, 253)
End If
rst.Update
rst.Close
Call modHandler.Modified("frmNotesDetail")
Exit_Here:
On Error Resume Next
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub
HandleErr:
Select Case Err.Number
Case Else
modHandler.LogErr "frmNotesDetail", "SaveNote"
End Select
Resume Exit_Here
End Sub

Private Sub txtNoteText_Change()
m_blnChanges = True
End Sub

Jan 20 '06 #18

P: n/a
This code eliminates any editing of bound memo fields - because the field is
never bound. All editing/spell checking/cuts-and-pastes/etc. are done in an
unbound textbox on an unbound form. A DLookup is used to suck the memo data
in, and recordset operations (AddNew/Edit) are used to spit the data back
into the table.

All this is done with the intention of avoiding any problems memo fields
might cause. The form has buttons that cycle through any number of memo
records displayed on the form, which, with a bound form, required
programmatically changing the recordsource. That made me a bit nervous...
regardless of how may strawberries were eaten in 1873 :)

Things seem to be working okay, but the rst.AddNew being run against
qryNotesDetails is kind of weird (?)

One thing I discovered: there is no way to know whether or not changes were
made after using the spell checker. So the m_blnChanges flag is set to true
whenever the spell checker is launched. This results in re-saving the
record even if there were no changes after spell checking.

Reposted with some minor revisions:

Option Compare Database
Option Explicit
Private m_lngNid As Long
Private m_lngEid As Long
Private m_blnChanges As Boolean

Private Sub cmdDeleteNote_Click()
On Error GoTo HandleErr
Dim intResponse As Integer
Dim db As DAO.Database
Dim strSql As String
If m_lngNid = 0 Then Me!txtNoteText = Null
If IsNull(Me!txtNoteText) Then
intResponse = vbYes
ElseIf m_lngNid > 0 Then
intResponse = MsgBox("Are you sure you want to delete this note?
", _
vbYesNo + vbQuestion, " Confirm Delete")
End If
If intResponse = vbYes Then
If m_lngNid > 0 Then
strSql = "DELETE * FROM tblNotes WHERE ([Note_ID] = " & _
m_lngNid & ");"
Set db = CurrentDb
db.Execute strSql, dbFailOnError
If DLookup("Ct", "qryCtNotes") = 0 Then
If modHandler.IsOpen("frmBig") Then DoCmd.Close acForm,
"frmBig"
End If
End If
End If
DoCmd.Close acForm, "frmNotesDetail"
Exit_Here:
On Error Resume Next
Set db = Nothing
Exit Sub
HandleErr:
Select Case Err.Number
Case Else
modHandler.LogErr "frmNotesDetail", "cmdDeleteNote_Click"
End Select
Resume Exit_Here
End Sub

Private Sub cmdMoveNote_Click()
On Error GoTo HandleErr
DoCmd.OpenForm "frmNotesMove", , , , , , m_lngEid
Exit_Here:
Exit Sub
HandleErr:
Select Case Err.Number
Case Else
modHandler.LogErr "frmNotesDetail", "cmdMoveNote_Click"
End Select
Resume Exit_Here
End Sub

Private Sub cmdNextNote_Click()
On Error GoTo HandleErr
If GetNextNoteID("Next") Then Call GoToNote
Exit_Here:
Exit Sub
HandleErr:
Select Case Err.Number
Case Else
modHandler.LogErr "frmNotesDetail", "cmdNextNote_Click"
End Select
Resume Exit_Here
End Sub

Private Sub cmdPreviousNote_Click()
On Error GoTo HandleErr
If GetNextNoteID("Previous") Then Call GoToNote
Exit_Here:
Exit Sub
HandleErr:
Select Case Err.Number
Case Else
modHandler.LogErr "frmNotesDetail", "cmdPreviousNote_Click"
End Select
Resume Exit_Here
End Sub

Private Function GetNextNoteID(strDirection As String) As Boolean
On Error GoTo HandleErr
Dim lngNid As Long
Select Case strDirection
Case "Next"
lngNid = Nz(DMin("Note_ID", "tblNotes", "Entity_ID = " &
m_lngEid & _
" And Note_ID > " & m_lngNid))
Case "Previous"
lngNid = Nz(DMax("Note_ID", "tblNotes", "Entity_ID = " &
m_lngEid & _
" And Note_ID < " & m_lngNid))
End Select
If lngNid > 0 Then
m_lngNid = lngNid
GetNextNoteID = True
Else
GetNextNoteID = False
End If
Exit_Here:
Exit Function
HandleErr:
Select Case Err.Number
Case Else
modHandler.LogErr "frmNotesDetail", "GetNextNoteID"
End Select
Resume Exit_Here
End Function
Private Sub GoToNote()
On Error GoTo HandleErr
If m_blnChanges Then
Call SaveNote
Me.Requery
End If
If m_lngNid > 0 Then
Call GetNote
Call LoadNote
m_blnChanges = False
End If
Exit_Here:
Exit Sub
HandleErr:
Select Case Err.Number
Case Else
modHandler.LogErr "frmNotesDetail", "GoToNote"
End Select
Resume Exit_Here
End Sub

Private Sub cmdPrint_Click()
On Error GoTo HandleErr
If Not IsNull(Me!txtNoteText) Then
If m_blnChanges Then Call SaveNote
If m_lngNid > 0 Then
Me!txtNoteID = m_lngNid
Else
Me!txtNoteID = DMax("Note_ID", "tblNotes")
End If
'qryrptNotes (record source for rptNotesDetail) looks for txtNoteID
DoCmd.OpenReport "rptNotesDetail", acViewPreview
End If
Exit_Here:
Exit Sub
HandleErr:
Select Case Err.Number
Case 2212 'Couldn't print your object
'(occurs when cancelling from pdfWriter)
Case Else
modHandler.LogErr "frmNotesDetail", "cmdPrint_Click"
End Select
Resume Exit_Here
End Sub

Private Sub cmdOK_Click()
On Error GoTo HandleErr
DoCmd.Close acForm, "frmNotesDetail"
Exit_Here:
Exit Sub
HandleErr:
Select Case Err.Number
Case Else
modHandler.LogErr "frmNotesDetail", "cmdOK_Click"
End Select
Resume Exit_Here
End Sub

Private Sub cmdSpellCheck_Click()
On Error GoTo HandleErr
If Not IsNull(Me!txtNoteText) Then DoCmd.RunCommand acCmdSpelling
m_blnChanges = True
'We don't know if any changes were made, but better safe than sorry.
Exit_Here:
Exit Sub
HandleErr:
Select Case Err.Number
Case Else
modHandler.LogErr "frmNotesDetail", "cmdSpellCheck"
End Select
Resume Exit_Here
End Sub

Private Sub Form_Load()
On Error GoTo HandleErr
Dim blRet As Boolean
blRet = MouseWheelOFF(False)
Me!cmdMoveNote.Enabled = m_lngNid
Me!cmdNextNote.Enabled = m_lngNid
Me!cmdPreviousNote.Enabled = m_lngNid
Call LoadNote
Exit_Here:
Exit Sub
HandleErr:
Select Case Err.Number
Case Else
modHandler.LogErr "frmNotesDetail", "Form_Load"
End Select
Resume Exit_Here
End Sub

Private Sub LoadNote()
On Error GoTo HandleErr
Me!txtEntityID = Forms("frm0")!Entity_ID
m_lngEid = Me!txtEntityID
'we'll compare txtEntityID and m_lngEid later
If m_lngNid > 0 Then
Me!txtNoteText = Nz(DLookup("NoteText", "qryNotesDetail"))
End If
Call SetNoteHeader
Exit_Here:
Exit Sub
HandleErr:
Select Case Err.Number
Case Else
modHandler.LogErr "frmNotesDetail", "LoadNote"
End Select
Resume Exit_Here
End Sub

Private Sub SetNoteHeader()
On Error GoTo HandleErr
Dim dtmNoteDate As Date
Dim strNoteDateHeader As String
Dim strNoteEntityHeader As String
If m_lngNid > 0 Then
dtmNoteDate = Nz(DLookup("NoteDate", "qryNotesDetail"))
strNoteDateHeader = Format(dtmNoteDate, "dddd") & " " & dtmNoteDate
strNoteDateHeader = "Note dated " & strNoteDateHeader & _
" for Entity ID " & m_lngEid
Me.Caption = "Edit Note"
Else
strNoteDateHeader = "New note for Entity ID " & m_lngEid
Me.Caption = "New Note"
End If
Me!lblNoteDateHeader.Caption = strNoteDateHeader
Me!txtNoteEntityHeader = modHandler.NameHeader(m_lngEid)
Exit_Here:
Exit Sub
HandleErr:
Select Case Err.Number
Case Else
modHandler.LogErr "frmNotesDetail", "SetNoteHeader"
End Select
Resume Exit_Here
End Sub

Private Sub Form_Open(Cancel As Integer)
On Error GoTo HandleErr
m_lngNid = Nz(Me.OpenArgs)
If m_lngNid > 0 Then Call GetNote
Exit_Here:
Exit Sub
HandleErr:
Select Case Err.Number
Case Else
modHandler.LogErr "frmNotesDetail", "Form_Open"
End Select
Resume Exit_Here
End Sub

Private Sub GetNote()
On Error GoTo HandleErr
Dim strSql As String
Dim db As DAO.Database
Dim qdfs As DAO.QueryDefs
Dim qdf As DAO.QueryDef
If m_lngNid > 0 Then
strSql = "SELECT [NoteDate], [NoteBrief], [NoteText], [Note_ID], " &
_
"[Entity_ID] FROM tblNotes WHERE ([Note_ID] = " & m_lngNid &
");"
Else
strSql = "SELECT TOP 1 [NoteDate], [NoteBrief], [NoteText],
[Note_ID], " & _
"[Entity_ID] FROM tblNotes;"
End If
Set db = CurrentDb
Set qdfs = db.QueryDefs
Set qdf = qdfs("qryNotesDetail")
qdf.SQL = strSql
Exit_Here:
On Error Resume Next
Set qdf = Nothing
Set qdfs = Nothing
Set db = Nothing
Exit Sub
HandleErr:
Select Case Err.Number
Case Else
modHandler.LogErr "frmNotesDetail", "GetNote"
End Select
Resume Exit_Here
End Sub

Private Sub Form_Close()
'toggles button and requeries frm0Notes
On Error GoTo HandleErr
Dim blRet As Boolean
If IsOpen("frmBig") Then
Forms("frmBig")!tglNewNote.Value = 0
Forms("frmBig")!frmBig_datasheet.Form.Requery
Else
Forms("frm0")!tglNewNote.Value = 0
Forms("frm0")!frm0Notes.Form.Requery
End If
Exit_Here:
blRet = MouseWheelON
Exit Sub
HandleErr:
Select Case Err.Number
Case Else
modHandler.LogErr "frmNotesDetail", "Form_Close"
End Select
Resume Exit_Here
End Sub

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
On Error GoTo HandleErr
Select Case KeyCode
Case vbKeyPageUp, vbKeyPageDown 'disable PageUp and PageDown
KeyCode = 0
Case vbKeyF7
KeyCode = 0
Call cmdSpellCheck_Click
End Select
Exit_Here:
Exit Sub
HandleErr:
Select Case Err.Number
Case Else
modHandler.LogErr "frmNotesDetail", "Form_KeyDown"
End Select
Resume Exit_Here
End Sub

Private Sub Form_Unload(Cancel As Integer)
On Error GoTo HandleErr
If m_lngNid > 0 Then
If m_blnChanges Or (Me!txtEntityID <> m_lngEid) Then
Call SaveNote
Call modHandler.Modified("frmNotesDetail")
End If
Else
If Not IsNull(Me!txtNoteText) Then
Call SaveNote
Call modHandler.Modified("frmNotesDetail")
End If
End If
Exit_Here:
Exit Sub
HandleErr:
Select Case Err.Number
Case Else
modHandler.LogErr "frmNotesDetail", "Form_Unload"
End Select
Resume Exit_Here
End Sub

Private Sub SaveNote()
On Error GoTo HandleErr
Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rst = db.OpenRecordset("qryNotesDetail")
If m_lngNid > 0 Then
rst.Edit
If Me!txtEntityID <> m_lngEid Then
rst!Entity_ID = Me!txtEntityID
End If
Else
rst.AddNew
rst!Entity_ID = m_lngEid
End If
Debug.Print "saving changes..."
rst!NoteText = Me!txtNoteText
If IsNull(Me!txtNoteText) Then
rst!NoteBrief = Null
Else
rst!NoteBrief = Left$(Me!txtNoteText, 253)
End If
rst.Update
Exit_Here:
On Error Resume Next
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub
HandleErr:
Select Case Err.Number
Case Else
modHandler.LogErr "frmNotesDetail", "SaveNote"
End Select
Resume Exit_Here
End Sub

Private Sub txtNoteText_Change()
m_blnChanges = True
End Sub

Jan 20 '06 #19

P: n/a
"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in
news:43***********************@lon-reader.news.telstra.net:
I just can't imagine going to the trouble of
using an unbound memo field, and not completing
the step and adding the lines for the other
controls.
That's ridiculous. Having one single field unbound and populating it
in the OnCurrent event and updating it in the control's AfterUpdate
event is substantially easier to do than making the entire form
unbound.

Add to it that there is virtually never any significant benefit to
unbound forms (except in high concurrency applications, in which
case, you'd be going unbound for everything already), and it makes
no sense whatsoever to go unbound.

I, for one, have a hard time living without the .Dirty property, and
any number of events that don't fire in unbound forms. It requires a
lot of careful coding to get back that functionality, and by
unbinding the memo field alone, you get rid of the danger of
corruption (though you are right, it doesn't remove the read locks)
without much work at all.
Worse, I have seen the locking problems when
I try to update the same record on two different
connections, from the bound form and from an
update query.


I was not recommending updating the underlying memo field via a SQL
statement.

Read what I wrote.

In fact, I was pushed in that direction by MichKa's suggestions on
the subject, and he knows a helluva lot more about Access than you
ever will.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jan 20 '06 #20

P: n/a
"deko" <de**@nospam.com> wrote in
news:Qv******************************@comcast.com:
This code eliminates any editing of bound memo fields - because
the field is never bound. All editing/spell
checking/cuts-and-pastes/etc. are done in an unbound textbox on an
unbound form. A DLookup is used to suck the memo data in, and
recordset operations (AddNew/Edit) are used to spit the data back
into the table.


That is simply more complicated than is necessary to avoid the risk
of memo corruption during editing.

Having a memo field in a bound recordsource that is not edited
through a bound control has a very minimal risk of corruption
because the amount of time when the data is being "edited" is going
to be mere milliseconds.

Going completely unbound as you're doing just makes no sense as it
just requires a whole helluva lot more code for no benefit in data
integrity. All it gets rid of is the read lock, which does not cause
any significant problems in terms of potential corruption or
concurrency.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jan 20 '06 #21

P: n/a
> Going completely unbound as you're doing just makes no sense as it
just requires a whole helluva lot more code for no benefit in data
integrity. All it gets rid of is the read lock, which does not cause
any significant problems in terms of potential corruption or
concurrency.


Well it's at least as good as the alternative. And the thought of users
hitting the next/previous button in rapid-fire succession no longer worries
me.

Jan 20 '06 #22

P: n/a
david epsom dot com dot au wrote:
Most of my day is spent with unbound forms,
or forms bound to a local table, so it's
not that I disagree with unbinding. It is
not difficult, and does not require an additional
form, just that you not bind to a select
query.

The code you have below for the unbound memo field
can be easily extended for a totally unbound form,
or you can create a local table and use delete-
append-update to load and update your data.

I also have memo fields in tables bound
directly to forms. In those cases, the forms
are not used often, and there is no transactional
data. The memo fields were put there by an idiot,
and are never used. I would also use a bound memo
field in a low volume, single user database - which
is most of the Access databases in the world -
if required, but in most of these cases an even
better alternative is to use a text field, not
a memo field, and add structured data to capture
additional information.

I do also have a separate table for a bound memo
field, left over from A97 SR2, 1998. It was easier
to support, because when I got memo corruption,
I only had to delete the memo record, not reconstruct
a damaged record. Since A97 SR3, it has never had
memo corruption.

I say, horses for courses. Even though the memo
corruption problem is gone, first consider
not using a memo field. Then consider using
a bound field. Then consider using an unbound
form.

I just can't imagine going to the trouble of
using an unbound memo field, and not completing
the step and adding the lines for the other
controls.

Worse, I have seen the locking problems when
I try to update the same record on two different
connections, from the bound form and from an
update query.
(david)


I use unbound whenever I can, bound when I must.

James A. Fortune
CD********@FortuneJames.com

And then we have DLinq, which is the code name for a future version of
ADO.NET that supports mapping of your relational databases to objects
and Language Integrated Query on top of that. --Anders Hejlsberg

Jan 20 '06 #23

P: n/a
"deko" <de**@nospam.com> wrote in
news:FZ********************@comcast.com:
Going completely unbound as you're doing just makes no sense as
it just requires a whole helluva lot more code for no benefit in
data integrity. All it gets rid of is the read lock, which does
not cause any significant problems in terms of potential
corruption or concurrency.


Well it's at least as good as the alternative. And the thought of
users hitting the next/previous button in rapid-fire succession no
longer worries me.


Excuse me? That can be handled by managing your recordsource.

When I advocate a bound recordsource, that does not imply binding it
to the whole table, but two one or a small selection of records.

If you're concerned about navigation within a group of records, then
program to handle for it. That is a *much* easier way to deal with
the potential problems than just running off and insanely converting
it to an unbound form.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jan 20 '06 #24

P: n/a
CD********@FortuneJames.com wrote in
news:11*********************@f14g2000cwb.googlegro ups.com:
I use unbound whenever I can, bound when I must.


I use bound whenever I can, unbound only in the tiny number of
situations where I must.

If I didn't, then there wouldn't be much point in using Access at
all.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jan 20 '06 #25

P: n/a
David W. Fenton wrote:
CD********@FortuneJames.com wrote in
news:11*********************@f14g2000cwb.googlegro ups.com:
I use unbound whenever I can, bound when I must.


I use bound whenever I can, unbound only in the tiny number of
situations where I must.

If I didn't, then there wouldn't be much point in using Access at
all.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/


Yes, I agree that we have different views about both these points for
different reasons. I'm glad Access didn't force everyone to use bound
controls.

James A. Fortune
CD********@Fortunejames.com

Jan 21 '06 #26

P: n/a
>> > I use unbound whenever I can, bound when I must.

I use bound whenever I can, unbound only in the tiny number of
situations where I must.


Yes, I agree that we have different views about both these points for
different reasons. I'm glad Access didn't force everyone to use bound
controls.


I'd be interested in your reasons for preferring unbound forms. In the
situation that started this thread, I'm dealing with a popup form used for
editing memo fields. The two suggested options are:

1. Bind the form to a query recordsource that includes the memo field, but
don't bind the memo field to a control.
- use Me!txtMemoText = Me!MemoText to populate (on some event, e.g.
OnCurrent)
- use Me!MemoText = Me!txtMemoText to update (on on some event, e.g.
AfterUpdate of txtMemoText)

2. Don't bind anything
- use DLookup to populate txtMemoText (on Form_Load)
- use DAO recordset operations to update (rst.Edit OnChange event of
txtMemoText)

Option 2 requires more code, but avoids read locks on the memo field. And
in option 1 (correct me if I'm wrong), Jet is doing the updating; in option
2, DAO is doing the updating. I'm not sure that's any advantage, but what
other advantages are there with an unbound form?

Jan 22 '06 #27

P: n/a
"deko" <de**@nospam.com> wrote in
news:ue********************@comcast.com:
1. Bind the form to a query recordsource that includes the memo
field

2. Don't bind anything


I haven't read the whole thread; this may be pertinent and it may not. Of
course, it requires Ac2000 or later, and, perhaps, an up-to-date MDAC,
with whatever changes in references is required.

3.
Option Explicit

Dim c As ADODB.Connection

Private Sub Form_Close()
With c
If MsgBox("Save Changes?", vbYesNo Or vbQuestion) = vbYes Then
.CommitTrans
Else
.RollbackTrans
End If
End With
End Sub

Private Sub Form_Open(Cancel As Integer)
Dim r As ADODB.Recordset

Set c = CurrentProject.Connection
c.BeginTrans

Set r = New ADODB.Recordset

With r
.CursorLocation = adUseClient
.LockType = adLockBatchOptimistic
.CursorType = adOpenStatic
.ActiveConnection = c
.Open "SELECT * FROM Table1"
End With

Set Me.Recordset = r
End Sub

I'm undecided about the necessity of including:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
c.RollbackTrans
End Sub

--
Lyle Fairfield
Jan 22 '06 #28

P: n/a

"deko" <de**@nospam.com> wrote in message
news:Na********************@comcast.com...
Most of my day is spent with unbound forms,
The code you have below for the unbound memo field
can be easily extended for a totally unbound form,
or you can create a local table and use delete-
append-update to load and update your data.


Could not do that - see comments in code below.
(second to last sub)

Private Sub SaveNote()
'I initially tried to use an UPDATE and INSERT
'query, but had trouble. Because I never know what
'might be in the memo field I need to use a parameter
'query to get the exact text. But Jet Parameters are
'limited to 255 characters. So I used a recordset.

What you can do is create a local table which mimics your
original table or query. Then bind the form to the local
table. This gives you all the features of a bond form,
but is not connected to your data tables.

This is good if you want to use the features of a bound
form, like using the table-level data verification, but
if you want to write your own sophisticated data checking
and expansion, then of course you are better off with
a completely unbound form.

When you are bound to a table like MyTable_tmp, then
your load event starts with something like:
"delete * from Mytable_tmp"
"insert into MyTable_tmp select * from MyTable where idxRecord = " & 5

and your close or next event ends with something like
"update MyTable_tmp inner join MyTable
on mytable_tmp.idxrecord = mytable.idxrecord
set mytable.fld_1 = mytable_tmp.fld_1
etc etc"
Jan 22 '06 #29

P: n/a
> Read what I wrote.

I've read what you wrote here, and you haven't
actually advocated anything. I see no sample code
at all.
In fact, I was pushed in that direction by MichKa's suggestions

"Remove the memo fields. That is my suggestion. "

I invite you to review the complete record of MichKa's
writings on the subject, and see if you aren't
engaging in wishful thinking.

As for your published statement in 2000 that

"I found that memo fields could lead to corruption
if someone was editing the memos during a synchronization.
To solve it, I went to an unbound form for the table
that was causing the problem. I've never had memo corruption
since."

Well, I've never had memo corruption since then either,
even in applications written by other people.

But if I was worried about memo corruption, I would
unbind the form that was causing the problem.

(david)
--

"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1... "david epsom dot com dot au" <david@epsomdotcomdotau> wrote in
news:43***********************@lon-reader.news.telstra.net:
I just can't imagine going to the trouble of
using an unbound memo field, and not completing
the step and adding the lines for the other
controls.


That's ridiculous. Having one single field unbound and populating it
in the OnCurrent event and updating it in the control's AfterUpdate
event is substantially easier to do than making the entire form
unbound.

Add to it that there is virtually never any significant benefit to
unbound forms (except in high concurrency applications, in which
case, you'd be going unbound for everything already), and it makes
no sense whatsoever to go unbound.

I, for one, have a hard time living without the .Dirty property, and
any number of events that don't fire in unbound forms. It requires a
lot of careful coding to get back that functionality, and by
unbinding the memo field alone, you get rid of the danger of
corruption (though you are right, it doesn't remove the read locks)
without much work at all.
Worse, I have seen the locking problems when
I try to update the same record on two different
connections, from the bound form and from an
update query.


I was not recommending updating the underlying memo field via a SQL
statement.

Read what I wrote.

In fact, I was pushed in that direction by MichKa's suggestions on
the subject, and he knows a helluva lot more about Access than you
ever will.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Jan 22 '06 #30

P: n/a
I just wanted to return to this point and say that
there is nothing risky about changing a forms
recordsource property.
Me.RecordSource = qdf.Name
The RecordSet properties are a bit risky, not
the RecordSource property.

Changing a form's RecordSet property may be risky.

Using a RecordSetClone when you are changing
a RecordSouce property is risky.

(david)
"deko" <de**@nospam.com> wrote in message
news:YO******************************@comcast.com. ..I have a popup form with a textbox that is bound to a memo field. I've
been warned about memo fields so I'm wondering if I should use this code.
Is there any risk with changing the form's RecordSource like this? Am I
asking for trouble doing this with a memo field?

Thanks in advance.

Private Sub cmdNextNote_Click()
Dim lngNid As Long
If Me.Dirty Then DoCmd.RunCommand acCmdSaveRecord
lngNid = Nz(DMin("Note_ID", "tblNotes", "Entity_ID = " & Me!Entity_ID &
_
" And Note_ID > " & Me!Note_ID))
If lngNid > 0 Then Call SetRecordSource(lngNid)
End Sub

Private Sub cmdPreviousNote_Click()
Dim lngNid As Long
If Me.Dirty Then DoCmd.RunCommand acCmdSaveRecord
lngNid = Nz(DMax("Note_ID", "tblNotes", "Entity_ID = " & Me!Entity_ID &
_
" And Note_ID < " & Me!Note_ID))
If lngNid > 0 Then Call SetRecordSource(lngNid)
End Sub

Private Sub SetRecordSource(lngNid As Long)
Dim strSql As String
Dim db As DAO.Database
Dim qdfs As DAO.QueryDefs
Dim qdf As DAO.QueryDef
If lngNid > 0 Then
'selecting only the note in question controls the behavior of the
'spell checker: only the selected note is checked, then the check
completes.
'When the RecordSource contains more than one note, the spell
checker
'proceeds to the next note, and then the next before completing -
which
'results in an unselected note appearing in the form and causing
'the code in Form_Unload to fail.
strSql = "SELECT [NoteBrief], [NoteDate], [NoteText], [Note_ID],
Entity_ID " & _
"FROM tblNotes WHERE ([Note_ID] = " & lngNid & ");"
Else
'when frm0.tglNewNote is clicked, the OpenForm method is called
'with 'acFormAdd' as the Data Mode argument, so we immediately
'arrive at a new record; thus we need only select the top 1
'record for the purpose of getting an updatable RecordSource
strSql = "SELECT TOP 1 [NoteBrief], [NoteDate], [NoteText],
[Entity_ID], " & _
"[Note_ID] FROM tblNotes;"
End If
Set db = CurrentDb
Set qdfs = db.QueryDefs
Set qdf = qdfs("qryNotesDetail")
qdf.SQL = strSql
Me.RecordSource = qdf.Name
Call SetNoteHeader
Set qdf = Nothing
Set qdfs = Nothing
Set db = Nothing
End Sub

Jan 23 '06 #31

P: n/a
deko wrote:
> I use unbound whenever I can, bound when I must.

I use bound whenever I can, unbound only in the tiny number of
situations where I must.


Yes, I agree that we have different views about both these points for
different reasons. I'm glad Access didn't force everyone to use bound
controls.


I'd be interested in your reasons for preferring unbound forms. In the
situation that started this thread, I'm dealing with a popup form used for
editing memo fields. The two suggested options are:

1. Bind the form to a query recordsource that includes the memo field, but
don't bind the memo field to a control.
- use Me!txtMemoText = Me!MemoText to populate (on some event, e.g.
OnCurrent)
- use Me!MemoText = Me!txtMemoText to update (on on some event, e.g.
AfterUpdate of txtMemoText)

2. Don't bind anything
- use DLookup to populate txtMemoText (on Form_Load)
- use DAO recordset operations to update (rst.Edit OnChange event of
txtMemoText)

Option 2 requires more code, but avoids read locks on the memo field. And
in option 1 (correct me if I'm wrong), Jet is doing the updating; in option
2, DAO is doing the updating. I'm not sure that's any advantage, but what
other advantages are there with an unbound form?


I'm not saying that MS' implementing bound controls was a mistake.
Bound controls solved a specific problem; i.e., concurrency. In about
99% of all Access databases concurrency is not really an issue at all.
For example, if multiple users are updating address data, they want and
expect the latest change to overwrite the previous edit. So the issue
we are really talking about is whether or not to leverage the record
integration of the bound controls for cases where concurrency is not an
issue since unbound controls have to have a mechanism for saving the
changes anyway. I don't think doing that is a mistake either. I don't
have a problem with the currently recommended idea of limiting a form
with bound controls to a single record. I feel that MS has documented
what they did well enough that I could even handle the concurrency case
without too much trouble. So, even though it looks like this is a huge
philosophical issue it's just a minor style issue. In short, there's
really not that big of a difference between the two approaches. You
only have to decide whether or not avoiding the record lock on the
single record is worth having extra code. If you already have extra
code to handle the unbound memo field because of potential corruption
based on the experience of people in this NG then you still have to
make the same decision.

James A. Fortune
CD********@FortuneJames.com

Jan 23 '06 #32

P: n/a
> I'm not saying that MS' implementing bound controls was a mistake.
Bound controls solved a specific problem; i.e., concurrency. In about
99% of all Access databases concurrency is not really an issue at all.
For example, if multiple users are updating address data, they want and
expect the latest change to overwrite the previous edit. So the issue
we are really talking about is whether or not to leverage the record
integration of the bound controls for cases where concurrency is not an
issue since unbound controls have to have a mechanism for saving the
changes anyway. I don't think doing that is a mistake either. I don't
have a problem with the currently recommended idea of limiting a form
with bound controls to a single record. I feel that MS has documented
what they did well enough that I could even handle the concurrency case
without too much trouble. So, even though it looks like this is a huge
philosophical issue it's just a minor style issue. In short, there's
really not that big of a difference between the two approaches. You
only have to decide whether or not avoiding the record lock on the
single record is worth having extra code. If you already have extra
code to handle the unbound memo field because of potential corruption
based on the experience of people in this NG then you still have to
make the same decision.


10-4. Not that big a deal. Sounds like the underlying issue is about how
Jet does locking (which I don't know very much about).

Jan 25 '06 #33

This discussion thread is closed

Replies have been disabled for this discussion.