Typing into a field bound to a locked recordset

[Guest]

It seems I am pushing Access far beyond what it was meant for, but...

I have an order detail table that consists of several rows, taken together
they are "an order". I have made a form where a user can see all recent
orders, one line per, and click off a checkbox if the order is "confirmed"
(OK). All I did was wire up the checkbox to the field in the recordset, and
put an event on the mouseUp that goes in and sets that field to "-1" on ALL
the records in question. Presto!

Ok here's the problem. The user also wants to have a notes field... so I did
the same thing, added a field, wired it up to the db field, and wrote an
afterChange handler to write out the change. Nope. It won't even let you type
into the field because the field in the recordset is locked. There doesn't
seem to be some sort of low-level event like mouseUp that hooks in at a
suitable early time.

Suggestions? I'm going to fiddle with the recordset in the meantime...

Maury

 

[Guest]

I'm not sure I understand. If the field is BOUND (that's the term you should
use rather than "wired") to a field in the table, and if the form is bound to
the table, then you don't need ANY event handler to accomplish the change.
Also worthy of note: The OnChange event fires for EVERY CHARACTER TYPED. And
once a change is "in progress" to a record, that record is locked (IIRC).

MHO - remove the event handler and let the form do its job.

 

[Guest]

use rather than "wired") to a field in the table, and if the form is bound to
the table, then you don't need ANY event handler to accomplish the change.

The recordset is non-updatable.

Maury

 

[Guest]

Maury, I guess I just don't understand. Unless someone else has the record
open for pending update, it's available to *you* for update.

 

[Guest]

One thing though:

If your MAIN form is accessing the same table as your SUBform (something
that you should NEVER DO), that can generate the condition you describe.

 

[Albert D. Kallal]

Why is the record set locked?

If the recordset is locked, then why even being to bother trying to edit it?
this makes no sense to me at this point in time...

Why not bind the form to a editable recordset, and give all the time and
money your wasting to feed the poor?

If you have a editable reordset, then the bound controls such as text boxes
etc will be editable.

If you want to have some "button" on the form to open a large form with a
HUGE text box that nearly fills the screen, then simply create a form with a
huge text box, and bind it to the table (pk for the form, and the on large
"comments" box.

Then in code to launch the form (much like the zoom (shift f5). you go:

me.Refresh (you MUST write this current record out, since we are about to
launch *anther* form that will edit the same record.

docmd.OpenForm "EditComments",,,"id = " & me!id

Make the EditComments form model..and thus you can now launch a form that
bound to the same table.

So, in theory, your main form *could* have a locked reocrdset, and you
launch a text box "form" to edit the comments
(but, then you would not need the above refresh to write out pending disk
writes).

As long as the form you launch is bound to the table, and not a locked
reocrdset, you should be fine.

If you trying to create recordsets in code, and bind them to forms, then you
likely should dump ms-access, as vb is much better for this type of
application...

 

[Guest]

Why is the record set locked?

It uses a GROUP BY.

If the recordset is locked, then why even being to bother trying to edit it?

I don't want it to be locked, but ADODB has this limitation (as far as I am
aware) that many SQL statements result in ADODB not being able to edit the
recordset. DISTINCT does it some times, GROUP BY almost always.

Maury

 

[Rick Brandt]

It uses a GROUP BY.


I don't want it to be locked, but ADODB has this limitation (as far
as I am aware) that many SQL statements result in ADODB not being
able to edit the recordset. DISTINCT does it some times, GROUP BY
almost always.

Maury

DISTINCT does it *always* as does Group By.

 

[Guest]

DISTINCT does it *always* as does Group By.

So, any suggestions? Nothing I try seems to work.

Maury

 

[John W. Vinson]

So, any suggestions? Nothing I try seems to work.

Maury

Don't use DISTINCT, don't use GROUP BY, and don't use UNION if you want the
query to be updateable.

Could you perhaps step back and describe the table you're trying to use, and
just what you need to accomplish? Perhaps post the SQL of your current query.

John W. Vinson [MVP]

 

[Guest]

Could you perhaps step back and describe the table you're trying to use, and
just what you need to accomplish? Perhaps post the SQL of your current query.

The table contains "order items". Each order consists of several records,
some for parts, other for taxes, etc. The query uses CASEs to do a rotation
into a single row with multiple columns. I really don't see anything that can
be done here.

But what about fooling it in the form? I don't know, something like an
invisible field pasted over the "real" one?

Maury

 

[John W. Vinson]

The table contains "order items". Each order consists of several records,
some for parts, other for taxes, etc. The query uses CASEs to do a rotation
into a single row with multiple columns. I really don't see anything that can
be done here.

So what's on the screen as a single record does not correspond to a single
record in the table? Naturally this won't be updateable.

But what about fooling it in the form? I don't know, something like an
invisible field pasted over the "real" one?

I don't know if that can be done at all. I think you're trying to blend two
quite different tasks - data summary and presentation, and data entry; they
maybe should best be done separately.

John W. Vinson [MVP]