Value and Text Properties of controls

[Ronald Dodge]

How do you have a value put into the Value property of a control on the form
via VBA code?

Situation:

User determine what to do with the record after the fields been filled as
opposed to Access wanting to know which edit mode to go into prior to
filling in the fields. This is the real stickler as I'm attempting at
keeping the database as the same feel as JDE to the extent reasonably
possible.

Question: In order to set the value or text on these controls with the
"RecordSource" on the form set to "", do I need to *UNBIND* the controls,
set the value of the control, then *BIND* the controls again for this to
work?

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[Dirk Goldgar]

In

How do you have a value put into the Value property of a control on
the form via VBA code?

Situation:

User determine what to do with the record after the fields been
filled as opposed to Access wanting to know which edit mode to go
into prior to filling in the fields. This is the real stickler as
I'm attempting at keeping the database as the same feel as JDE to the
extent reasonably possible.

Question: In order to set the value or text on these controls with
the "RecordSource" on the form set to "", do I need to *UNBIND* the
controls, set the value of the control, then *BIND* the controls
again for this to work?

I'm not sure I follow you. (And what is JDE?) If the form has no
recordsource, then it is unbound, and the controlsources of all the
controls should be blank. You can, if you want, use an unbound form in
this way to enter data that won't be saved until/unless the user
requests it somehow. Then your code can build and execute an append or
update query on the fly, or execute a stored query that references the
form's controls for values.

But I'm not sure if this is what you're trying to accomplish. And bound
forms are much easier to work with, if your requirements permit it.

 

[Ronald Dodge]

JDE is our main data base program, which users fills in the data and then
determines rather to add it or modify the current record, if the record was
previously inquired. Records must be previously inquired before it can be
editted.

Now the problem with bound forms in Access, it's immediately either adding
to a new record or editing the current record, and when it leaves that
record, it's updated by default, which doesn't work for the scenerio that
I'm having to work with. Users are use to determing after putting in the
values, not before putting in the values, and I rather not face too much
resistance from the users.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[Dirk Goldgar]

In

JDE is our main data base program, which users fills in the data and
then determines rather to add it or modify the current record, if the
record was previously inquired. Records must be previously inquired
before it can be editted.

Now the problem with bound forms in Access, it's immediately either
adding to a new record or editing the current record, and when it
leaves that record, it's updated by default, which doesn't work for
the scenerio that I'm having to work with. Users are use to
determing after putting in the values, not before putting in the
values, and I rather not face too much resistance from the users.

Even with a bound form, there are ways to prevent a record from being
saved until the user specifically requests it. However, it sounds like
an unbound form may be the way for you to go. You can use the approach
I described previously for that.

 

[Ronald Dodge]

Thank you for the update, which more or less confirmed my suspicions. That
means in many regards, I'm back to square 1 as I'm back to using the forms
as unbound, but only as a different reason as before.

However, I already know I will have to treat this project similar to
ADO.NET's disconnected model, but only without the enhanced features for the
following reasons:

As much as I was hoping to be able to use, bound forms don't seem to be the
answer given the mode is determined prior to filling in the fields as
opposed to the preferred afterwards.

DAO coding has a real problem with the "EditMode" property not working
properly at times (there's no official documentation on this bug, but I have
ran into it numerous times), which means I had to have a work around in
place to address this issue.

ADO coding doesn't allow for dynamic cursor keyset recordsets against a Jet
Engine as documented.

Another thing I don't like about unbound controls, certain properties aren't
updated *AND* readable by VBA until *AFTER* that control has lost focus,
which doesn't help me out one bit in many cases. Listboxes has this problem
and impacted me so great to the point that I had to create my own custom
listbox class object to emulate the actual listbox on the form, which then I
ran into another bug dealing with listbox selections via keyboard method.
That's cause I was like, if I'm going to emulate the listbox, I might as
well go full out and get everything including mouse movement, how selections
are done (Keyboard and mouse methods) under the 3 different selection mode
options, and what it will take, just as I emulated VB6's CausesValidation
property and Validate event to get around the fact in Access, using the
default error checking stuff on a control by control basis, it either always
or never gets checked, which you would want checks to take place most of the
time prior to a control losing it's focus, but not everytime in a pretty big
majority of the cases.

One of the reasons why I put in checks on a control by control basis, not
just record by record basis, I did data entry for a time and if an error was
made that could reasonably be caught right there on the spot without slowing
things up, I would want to have it caught right away and allow me to go back
just briefly, and make the adjustment as needed. The other thing, at the
time when I did do this at the IRS, it was all text based, no mouse.
However, even with having the mouse available, if you doing nothing but
strictly data entry (either numeric, straight, or alphanumeric), that
constant switching back and forth between the keyboard and mouse is a huge
time waster from the point of view how much time it takes to go back and
forth as opposed to just sticking to the keyboard in most cases. Along
those same lines, I also like to avoid alphanumeric data entry as much as
reasonably possible and have it either as strictly straight typing or
strictly as numeric as that constant switching back and forth between the
QWETRY keyboard and numpad (MS calls it keypad) is also a time waster. Of
course, some give and take will have to be done.

As a production statistician and the person responsible for maintaining the
data, I certainly don't want any corrupt data, thus I have to have strict
rules in place for most things to reasonably assure this factor.

Also as a programmer, I'm also don't want to have to spend too much time
having to go back and fix things, but also need to make things as user
friendly as possible while keeping the data validity in place.

I'm now doing the administator type work as well on the data base, which
that also has to be taken into account.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[Dirk Goldgar]

In

DAO coding has a real problem with the "EditMode" property not working
properly at times (there's no official documentation on this bug, but
I have ran into it numerous times), which means I had to have a work
around in place to address this issue.

I don't know what you're referring to, so I can't comment.

ADO coding doesn't allow for dynamic cursor keyset recordsets against
a Jet Engine as documented.

The fact that you're delving into these details suggests to me that
you're either working in an unusual environment, are facing unusual
operational constraints, or are "over-programming". I couldn't say
which.

Another thing I don't like about unbound controls, certain properties
aren't updated *AND* readable by VBA until *AFTER* that control has
lost focus, which doesn't help me out one bit in many cases. Listboxes
has this problem and impacted me so great to the point that
I had to create my own custom listbox class object to emulate the
actual listbox on the form, which then I ran into another bug dealing
with listbox selections via keyboard method. That's cause I was like,
if I'm going to emulate the listbox, I might as well go full out and
get everything including mouse movement, how selections are done
(Keyboard and mouse methods) under the 3 different selection mode
options, and what it will take, just as I emulated VB6's
CausesValidation property and Validate event to get around the fact
in Access, using the default error checking stuff on a control by
control basis, it either always or never gets checked, which you
would want checks to take place most of the time prior to a control
losing it's focus, but not everytime in a pretty big majority of the
cases.

I'm somewhat curious as to what deficiencies of the Access listbox
forced you to go to such lengths. I've found it to be a very functional
control, only occasionally needing to be supplemented by a little bit of
VBA programming.

One of the reasons why I put in checks on a control by control basis,
not just record by record basis, I did data entry for a time and if
an error was made that could reasonably be caught right there on the
spot without slowing things up, I would want to have it caught right
away and allow me to go back just briefly, and make the adjustment as
needed.

There are differing ideas about this. I prefer immediate trapping of
invalid data, but deferred trapping of missing data and inconsistent
data.

The other thing, at the time when I did do this at the IRS,
it was all text based, no mouse. However, even with having the mouse
available, if you doing nothing but strictly data entry (either
numeric, straight, or alphanumeric), that constant switching back and
forth between the keyboard and mouse is a huge time waster from the
point of view how much time it takes to go back and forth as opposed
to just sticking to the keyboard in most cases.

I don't follow you. You can easily create an Access UI that doesn't
require using the mouse at all.

Getting back to the original question, one thing we haven't really
talked about is that you can fairly easily create a form that is
dynamically bound to a single record, and that controls the saving of
that record. So you can use bound forms with a high degree of control.
It's not the default for Access, and requires a bit of programming, but
it can be done.

 

[Ronald Dodge]

So what would be required to go that route (bound to a single record)? I
like to explore my options, especially if one that hasn't been explored is
the one that's going to save me some heart ache and time. As for JDE, it
primarily works on a record by record basis, so it could be quite the way
for me to go in Access.

As you said, it may be some of each, which the first 2 I definitely would
agree with to a large extent. The third one (Over programming) could be a
factor given that I'm detail oriented and there's been occassions when I had
been too deep into the forest and didn't see the overall picture at that
point of time.

As you can probably tell, when I find something not working as it suppose
to, I don't stop there but rather look for ways to either get rid of the
issue (preferred means, but normally not the case when it's not dealing with
my own coding) or getting around the issue.

One of the properties on the listbox that didn't work for me while it still
has the focus was the fact that the Selected Property (I think of it as more
of a collection or an array) wasn't updated for as long as the listbox has
control, which then I been known at times to have other actions performed,
but would be dependent on what is or isn't selected as well as what's added
and/or removed from the list. The Column property seemed to return the
values based on what the listbox had in it prior to either the "Enter" or
"GotFocus" event was on it, which then had to emulate the listbox so as I
could perform certain things based on what's currently in it, not what was
in it prior to making changes to it rather it be items themselves or the
selection.
--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[Jamie Collins]

I will have to treat this project similar to ADO.NET's disconnected model, but only without the enhanced features for the
following reasons...

ADO coding doesn't allow for dynamic cursor keyset recordsets against a Jet
Engine as documented.

Do you need a dynamic cursor? It's not a requirement for working with
disconnection recordsets and Jet. You do need a client side cursor,
which is available when using other cursor types e.g. a keyset
cursor.

Jamie.

--

 

[Ronald Dodge]

It would have been nice to have a dynamic cursor keyset to be able to see
changes within the DB as they take place without having to requery, but
given the issues that I facing, I see that doesn't seem to be a realistic
possibility, which then has forced me to go the disconnected recordset type
model such as what's used in ADO.NET programming, though keeping to within
Access' objects and behavior.

A dynamic cursor keyset allows for pessimistic record locking while a
disconnected recordset model only allows for optimistic record locking,
rather if it's done in batch mode (transactions) or done individually for
updating the database.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000