1

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.

peterh
  • 11,875
  • 18
  • 85
  • 108
Rizwan Safdar
  • 125
  • 2
  • 2
  • 11

2 Answers2

3

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

Philippe Grondier
  • 10,900
  • 3
  • 33
  • 72
  • so how do i update any unbound control when lostfocus event fired. – Rizwan Safdar Sep 26 '13 at 17:00
  • As far as I remember, it depends how you are loosing the focus: if it's through ESC key, update might not be fired. If it's through Enter or TAB, it will be fired. Add some debug.print messages to your event and before/after update procedures and check how the different eventas are managed. I think you can also find some help on event hierarchy. – Philippe Grondier Sep 26 '13 at 19:11
2

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.

Linger
  • 14,942
  • 23
  • 52
  • 79
  • i am unable to understand your last sentence please. if i am entering new record then what should i do. – Rizwan Safdar Sep 26 '13 at 19:04
  • ALSO after_update event should work for both bound and unbound controls ? – Rizwan Safdar Sep 26 '13 at 19:07
  • Unbound controls will not have any database fields to update. So, `Me.Dirty = False` does not do anything for unbound controls. But, you wouldn't need it to. Unbound controls are controlled via an action to another control or user interaction. – Linger Sep 26 '13 at 20:31