Writing to table: code clashes with form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form with two sets of text boxes.
Boxes A-G
Boxes 1-2

Boxes A-G feed numbers straight into a table.
Boxes 1-2, make calculations (via a function) based on valued in Boxes A-G,
but pulls values from the table.

Used some basic code from a WROX book to populate values from boxes 1-2 into
a table.

Problem: Form clashes with code for write permissions.

Suspect: as values are entered into boxes A-G, they're not immediately in
the table. They're in some temporary storage place but can be plucked from
the table as if they were. Code is triggered by a change in values in boxes
1-2.

When form is closed, the form and code compete to save the changes to tbl,
it actaully triggers this weird loop.

Need: some code to execute the forms action ahead of the code's (but in the
code), I hope it will cut the loop.

I simplified the problem; this is seemingly the solution. There is one other
ultra simple solution but it does not address other problems down the road
under the time constraint.

Thanks,
Pepe
 
Hi Pepe

As soon as you start to change the value in a bound control on a form, it
makes the form "dirty". This puts an optimistic lock on the underlying
record so, as you have discovered, you cannot update the same record from
code.

There are two possible solutions:
1. Save the changed form record before you run the code that updates the
record.
This is done with DoCmd.RunCommamd acCmdSaveRecord
2. Include the fields you want your code to change in the form's
recordsource,
and change them in the form instead of opening a separate recordset.
 
Graham,

Many thanks, I will give this a shot. Sounds like the solution that I seek.

I swear this site rocks.

Cheers! I'll let you know how it works out.

Pepe



Graham Mandeno said:
Hi Pepe

As soon as you start to change the value in a bound control on a form, it
makes the form "dirty". This puts an optimistic lock on the underlying
record so, as you have discovered, you cannot update the same record from
code.

There are two possible solutions:
1. Save the changed form record before you run the code that updates the
record.
This is done with DoCmd.RunCommamd acCmdSaveRecord
2. Include the fields you want your code to change in the form's
recordsource,
and change them in the form instead of opening a separate recordset.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

pepenacho said:
I have a form with two sets of text boxes.
Boxes A-G
Boxes 1-2

Boxes A-G feed numbers straight into a table.
Boxes 1-2, make calculations (via a function) based on valued in Boxes
A-G,
but pulls values from the table.

Used some basic code from a WROX book to populate values from boxes 1-2
into
a table.

Problem: Form clashes with code for write permissions.

Suspect: as values are entered into boxes A-G, they're not immediately in
the table. They're in some temporary storage place but can be plucked from
the table as if they were. Code is triggered by a change in values in
boxes
1-2.

When form is closed, the form and code compete to save the changes to tbl,
it actaully triggers this weird loop.

Need: some code to execute the forms action ahead of the code's (but in
the
code), I hope it will cut the loop.

I simplified the problem; this is seemingly the solution. There is one
other
ultra simple solution but it does not address other problems down the road
under the time constraint.

Thanks,
Pepe
Click to expand...
Click to expand...
 
Back
Top