Update a Field with a New Value.

[TimWillDoIt]

Hello all,

I'm still struggling to get a handle on VB programming within Access. I
received some fantastic advice on my previous question so I'm back to get
some help with another challenge.

I'm using the following line in a VB Procedure in Access 2003:

Me!CustomerInterfaceCost.Value = 2200

This successfully updates field with the new value...but not unless I close
the form.

My searching has come up with the Update Method, which looks like it will
immediately reflect the change in my field. Is this the right way to do what
I need? Or is there another one-line command that will immediately update
the contents of the field? Thanks.

Tim

 

[Klatuu]

I am surprised it isn't working. I don't know if this has anything to do
with it, but you really don't need to use the Value property. It is the
default value for a control, but I am wondering if behind the scenes it does
something different. Try

Me!CustomerInterfaceCost = 2200

If that doesn't do it, try a repaing right after the assignment:

Me!CustomerInterfaceCost = 2200
Me.Repaint

 

[TimWillDoIt]

You're right about the Value property. I had read that it was the default
property. I was using dozens of different syntax and only recently added the
Value property.

I modified the code as you suggested:

Me!CustomerInterfaceCost = 2200
Me.Repaint

It still updates the field...but only after I close the form. I've also
noticed that it will update the field if I scroll to display the next record
in the form. Any other thoughts?

 

[TimWillDoIt]

I've made one additional observation.

For testing purposes, I added a temporary Textbox on the form to display the
contents of the field, so I can see what the contents are and if it has
changed without having to go and open the table each time.

The Textbox is bound to the field. What I noticed is that the contents of
the field is being changed. But when I go and open the database, it still
shows the old contents. So, in effect, I'm looking at the table and seeing
the old (incorect) number and then I'm looking at the bound Textbox that is
showing me the new (correct) number. Obviously, the Textbox is showing
what's buffered.

Do I just call that good? Or can I do something programmatically that will
update the table with the contents of the buffer? Thanks.

 

[fredg]

You're right about the Value property. I had read that it was the default
property. I was using dozens of different syntax and only recently added the
Value property.

I modified the code as you suggested:

Me!CustomerInterfaceCost = 2200
Me.Repaint

It still updates the field...but only after I close the form. I've also
noticed that it will update the field if I scroll to display the next record
in the form. Any other thoughts?

You haven't told us where you placed your code, however try this:

Me!CustomerInterfaceCost = 2200
DoCmd.RunCommand acCmdSaveRecord

 

[Klatuu]

This is all very strange.
Is the original control you were having problems with a bound control?
Normally, when you assign a value to a bound control, the assigned value
will show in the control and when the record is updated, it will update the
bound table field.

Either there is something about the control that is not apparent or you have
some kind of corruption going on in your mdb.

 

[TimWillDoIt]

Fred,

Yes. This was the line I was looking for. Thank you for your suggestion.

Tim

 

[TimWillDoIt]

Dave,

No, the original control was Unbound. When I initially tried this with a
Bound control, I was getting an error message telling me that I couldn't
change the contents of a Bound control so I made it an Unbound control.

Perhaps i was getting that message for a different reason. I'll try
setting it back to a bound control. But in the meantime, a suggestion was
offered with a statement that forced the record to update. What I see on the
form and in the table is now sync'd. Thanks a lot for your suggestions and
assistance.

Tim

 

[John W. Vinson]

I modified the code as you suggested:

Me!CustomerInterfaceCost = 2200
Me.Repaint

It still updates the field...but only after I close the form. I've also
noticed that it will update the field if I scroll to display the next record
in the form. Any other thoughts?

Instead of Me.Repaint you can force the record to be written to disk by adding
a line

Me.Dirty = False

This will require that all Required fields have data and that there isn't any
other validation rule which would prevent the record from being written.