CODEWITHSUNDEEP
vbams-accesscomboboxms-access-2007ms-access-2010
Rizwan Safdar
Rizwan Safdar

Reputation: 135

How can I update any field value through VBA code in ms access?

How can I update any field value through VBA code in ms access? When I enter values in combobox, some of the fields in the relevant tables are updated as I enter the data. Whereas others do not. What should I do to solve this problem?

In a combobox I used me.refresh command and it is updating data as I enter. Whereas in another combobox I did the same, but I got no result. Where I am making mistakes?

Further is unregistered software did such problems so that they behave different at different times.

Upvotes: 2

Views: 67206

Answers (2)

Linger
Linger

Reputation: 15048

When you change a value of a textbox/combo box/etc on a form the record in the table is not immediately updated. The default way Access handles it is to wait until the record no longer has focus and then it updates the record in the table with any changes you made.

If you want to, you can force an update to the record in the table via the After Update event by using the following:

Private Sub txtMyFieldName_AfterUpdate()
  Me.Dirty = False
End Sub

However, I would only do this when editing an existing record. If you are entering a new record then you don't want to trigger Me.Dirty = False after every control has been updated. If you do trigger Me.Dirty = False on new record entry and you have required fields that haven't been filled in yet, you will get an error stating that a required field cannot contain a null value.

Upvotes: 3

Philippe Grondier
Philippe Grondier

Reputation: 11138

There is a slight conceptual difference between the change of a (bound) control's value on a form and the update of the underlying field's value. The underlying field's value might not be updated before the 'update' event is fired.

And, of course, if the control you are dealing with is unbound, there cannot be any field update ...

Edit:

If you want to change an unbound control value programmatically:

myForm.controls(myControl).value = "whatever"

If you want to change a bound control and its underlying field, working on the field side

myForm.recordset.fields(myField).value = "whatever" 
myForm.recordset.update

You might then need to refresh your control on the screen so it displays the updated value

And on the control side 

myForm.controls(myControl).value = "whatever"

You might then need to fire the update programmatically (recordset.update)event on your underlying control

Upvotes: 3

Related Questions