ManningFan - some tips about unbound forms?

[LAS]

I'm not able to do what I want using the pure Access Way (if I understand it
correctly as letting access control the appearance of New rows, letting
deletes be controlled by the left "button" etc.). Mixing bound forms and
program control is making me nuts. I'm going to try a completely unbound
form. What is your favorite way to load the fields in the form as you get
records in your recordset?

TIA
LAS

 

[Tony Toews]

I'm not able to do what I want using the pure Access Way (if I understand it
correctly as letting access control the appearance of New rows, letting
deletes be controlled by the left "button" etc.). Mixing bound forms and
program control is making me nuts. I'm going to try a completely unbound
form. What is your favorite way to load the fields in the form as you get
records in your recordset?

I don't. Live with how Access works or switch to another product
such as C# or Delphi or whatever. Bound forms are one of Access
biggest strengths.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

 

[LAS]

Thanks, very helpful. How do I

" set up a custom collection including only
the fields that will have data loaded from the recordset that I
populate in the form's OnOpen event. " Is a "collection" an "array"?

I'm curious as to what situations caused you to come up with this elegant
way of managing an unbound form since you are such an advocate of using
Access to handle most things?

I'm not able to do what I want using the pure Access Way (if I
understand it correctly as letting access control the appearance
of New rows, letting deletes be controlled by the left "button"
etc.). Mixing bound forms and program control is making me nuts.

Then you're not doing it right -- you're making it too complicated.

I'm going to try a completely unbound
form. What is your favorite way to load the fields in the form as
you get records in your recordset?

Start out with your form bound. Drop all the controls on the form.
This will create controls with the names of the fields they are
bound to. Remove the ControlSource of each of the controls, and the
form's Recordsource.

To load data, you open a recordset on the former Recordsource
(though filterd to a single record, of course, and do this:

Dim rs As DAO.Recordset
Dim ctl As Control

Set rs = CurrentDB.OpenRecordset("[your former recordsource]")
rs.MoveFirst

For Each ctl in Me.Controls
ctl = rs(ctl.Name)
Next ctl
Set ctl = Nothing

rs.Close
Set rs = Nothing

Since the controls have the names of the fields they were formerly
bound to, this will load the data very easily.

Now, it can't be quite that simple, as you very often have controls
on your form that are not for editing the data (such as command
buttons), but that's easily handled in a number of ways. My
preferred approach is to set up a custom collection including only
the fields that will have data loaded from the recordset that I
populate in the form's OnOpen event. It's much more efficient than
testing for a .Tag or testing ControlType each time you load/save
data.

Now, for saving data, you reverse the process:

rs.Edit
For Each ctl in Me.Controls
rs(ctl.Name) = ctl.Value
Next ctl
rs.Update

You may, if you're cautious like me, choose to test whether the
values differ before assigning the value. I do that because I have a
lot of replicated applications, and I don't want to change fields
whose actual data is the same (this can result in unnecessary
conflicts, even with field-level conflict resolution, as is the norm
in Jet 4).

But if you do all of this, you lose much, much more than you gain.
You'll have to program every single behavior, including navigation
and all the data-based events (there's no .Dirty property in an
unbound form, for instance, and thus, no OnDirty event, just to name
one of the most important losses).

From what I've seen you have attempted to program all sorts of
things that I would never write a single line of code for, and each
time you've chosen the least appropriate method for programming what
you're trying to do. I think you'd be much better served by trying
to do things the easy way, rather than going unbound and losing all
the advantages of Access.

Put another way, if you're going to go unbound for all your data
editing, there's really no point in using Access in the first place,
as you'll now be on par with programming environments that don't
have all the RAD tools that come in Access from using bound forms.

 

[LAS]

To load data, you open a recordset on the former Recordsource

(though filterd to a single record, of course

I've written a function to handle the code you describe below, but my SQL
might retrieve multiple records (I have NEXT logic in the form). Is there a
way to tell the function which record to act on? In a function I got from
the web it looks like this will tell the function what the current record
is, but I must confess that I don't understand what it's doing. It's the
bookmark stuff that you said was unnecessary in a different thread.

.Bookmark = frmSomeForm.Bookmark 'Move to the current record of the
passed form

Do you have a different method to handle this?

TIA
LAS

I'm not able to do what I want using the pure Access Way (if I
understand it correctly as letting access control the appearance
of New rows, letting deletes be controlled by the left "button"
etc.). Mixing bound forms and program control is making me nuts.

Then you're not doing it right -- you're making it too complicated.

I'm going to try a completely unbound
form. What is your favorite way to load the fields in the form as
you get records in your recordset?

Start out with your form bound. Drop all the controls on the form.
This will create controls with the names of the fields they are
bound to. Remove the ControlSource of each of the controls, and the
form's Recordsource.

To load data, you open a recordset on the former Recordsource
(though filterd to a single record, of course, and do this:

Dim rs As DAO.Recordset
Dim ctl As Control

Set rs = CurrentDB.OpenRecordset("[your former recordsource]")
rs.MoveFirst

For Each ctl in Me.Controls
ctl = rs(ctl.Name)
Next ctl
Set ctl = Nothing

rs.Close
Set rs = Nothing

Since the controls have the names of the fields they were formerly
bound to, this will load the data very easily.

Now, it can't be quite that simple, as you very often have controls
on your form that are not for editing the data (such as command
buttons), but that's easily handled in a number of ways. My
preferred approach is to set up a custom collection including only
the fields that will have data loaded from the recordset that I
populate in the form's OnOpen event. It's much more efficient than
testing for a .Tag or testing ControlType each time you load/save
data.

Now, for saving data, you reverse the process:

rs.Edit
For Each ctl in Me.Controls
rs(ctl.Name) = ctl.Value
Next ctl
rs.Update

You may, if you're cautious like me, choose to test whether the
values differ before assigning the value. I do that because I have a
lot of replicated applications, and I don't want to change fields
whose actual data is the same (this can result in unnecessary
conflicts, even with field-level conflict resolution, as is the norm
in Jet 4).

But if you do all of this, you lose much, much more than you gain.
You'll have to program every single behavior, including navigation
and all the data-based events (there's no .Dirty property in an
unbound form, for instance, and thus, no OnDirty event, just to name
one of the most important losses).

From what I've seen you have attempted to program all sorts of
things that I would never write a single line of code for, and each
time you've chosen the least appropriate method for programming what
you're trying to do. I think you'd be much better served by trying
to do things the easy way, rather than going unbound and losing all
the advantages of Access.

Put another way, if you're going to go unbound for all your data
editing, there's really no point in using Access in the first place,
as you'll now be on par with programming environments that don't
have all the RAD tools that come in Access from using bound forms.

 

[David W. Fenton]

How do I

" set up a custom collection including only
the fields that will have data loaded from the recordset that I
populate in the form's OnOpen event. " Is a "collection" an
"array"?

See this StackOverflow.com post where I discuss setting up and using
custom collections:

http://stackoverflow.com/q/1917981/

I'm curious as to what situations caused you to come up with this
elegant way of managing an unbound form since you are such an
advocate of using Access to handle most things?

I read it in a book.

 

[David W. Fenton]

I've written a function to handle the code you describe below, but
my SQL might retrieve multiple records (I have NEXT logic in the
form).

I don't think you're doing it right. If you're going unbound, you
can load the data of only one record at a time, so I see no point in
loading more than one record in your recordset.

So, it would seem to me you need to refine your navigation. Perhaps
you need a listbox to list the records that match the desired
criteria, and then have the AfterUpdate of the listbox load the
individual record selected in the listbox.

Is there a
way to tell the function which record to act on? In a function I
got from the web it looks like this will tell the function what
the current record is, but I must confess that I don't understand
what it's doing. It's the bookmark stuff that you said was
unnecessary in a different thread.

.Bookmark = frmSomeForm.Bookmark 'Move to the current record
of the
passed form

Do you have a different method to handle this?

I would never do anything but retrieve one record at a time in the
recordset.

 

[David W. Fenton]

I don't. Live with how Access works or switch to another
product such as C# or Delphi or whatever. Bound forms are one of
Access biggest strengths.

I have never understood why LAS needs to do the things she's doing.
They are things I've never once been tempted to try, and I'm not
getting what's causing her to repeatedly go down the wrong path.

 

[Rick Brandt]

Start out with your form bound. Drop all the controls on the form.
This will create controls with the names of the fields they are
bound to. Remove the ControlSource of each of the controls, and the
form's Recordsource.

To load data, you open a recordset on the former Recordsource
(though filterd to a single record, of course, and do this:

Dim rs As DAO.Recordset
Dim ctl As Control

Set rs = CurrentDB.OpenRecordset("[your former recordsource]")
rs.MoveFirst

For Each ctl in Me.Controls
ctl = rs(ctl.Name)
Next ctl
Set ctl = Nothing

rs.Close
Set rs = Nothing

Since the controls have the names of the fields they were formerly
bound to, this will load the data very easily.

Now, it can't be quite that simple, as you very often have controls
on your form that are not for editing the data (such as command
buttons),

When I have done similar to this I do the for-each on the fields collection
of the recordset instead of on the controls collection of the form. Then
you automatically ignore controls that do not have the same name as a field.

 

[David W. Fenton]

Start out with your form bound. Drop all the controls on the
form. This will create controls with the names of the fields they
are bound to. Remove the ControlSource of each of the controls,
and the form's Recordsource.

To load data, you open a recordset on the former Recordsource
(though filterd to a single record, of course, and do this:

Dim rs As DAO.Recordset
Dim ctl As Control

Set rs = CurrentDB.OpenRecordset("[your former recordsource]")
rs.MoveFirst

For Each ctl in Me.Controls
ctl = rs(ctl.Name)
Next ctl
Set ctl = Nothing

rs.Close
Set rs = Nothing

Since the controls have the names of the fields they were
formerly bound to, this will load the data very easily.

Now, it can't be quite that simple, as you very often have
controls on your form that are not for editing the data (such as
command buttons),

When I have done similar to this I do the for-each on the fields
collection of the recordset instead of on the controls collection
of the form. Then you automatically ignore controls that do not
have the same name as a field.

You're right, of course, though that works only as long as you don't
have fields in your recordset that you don't have controls for
(quite common in that in a lot of forms you won't display an
Autonumber PK, for instance). I might do that, because I'll often
leave the recordset open after loading the form, and write back to
it, and might want to look at fields that aren't on the form.

Of course, if you do what I just describe, your form is basically
bound to a recordsource, just with no controls bound, so there's no
real difference between just using the form's RecordSource property.

But I'm hostile to using unbound forms for data editing in
general...