On Combo Change - If New Record - Save In Place?

[misscrf]

I have form, where there is a combo that is first in line, to be set, on a new record. When that combo is changed (Change event), I want to check if we are on a new record. If we are, I want to save this record, and refresh some unbound textboxes, which are set on the form's Current code. To have that happen, it would need to see that the current record is no longer a new record, as the current code checks for that.

This is what I have in the Change code, for the combo:

Private Sub FKMyType_Change()
     If Me.NewRecord Then
        If Me.Dirty Then
            Me.Dirty = False
        End If
    End If
    Me.MyName.Requery
    Me.MyNo.Requery
End Sub

It seems that me.dirty = false doesn't change the current record from being seen as a new record. I check the back-end table and it inserts the record, assigning the PK (SQL identity).

Anyone know how to accomplish what I'm trying to do?

Thanks!

 

[bastanu]

I would use the AfterUpdate event instead of the OnChange as that will trigger for every single keystroke.

Also, it seems that you have a SQL table linked in using ODBC. Do you have a timestamp field in the table and is that part of the recordsource of the form?
Finally I don't think you really need to test for Dirty, just set it =False.

Cheers,
Vlad

 

[misscrf]

it is odbc sql linked tables. I don't have a timestamp. I'd rather not add one. I have lots of tables in this schema, and really don't want to add timestamp fields to all of them. I do want to be consistent. Is that needed?

 

[misscrf]

nevermind. I moved it to after update and now it works. Thanks!

 

[bastanu]

Glad it works. I do believe that having timestamps in your SQL server tables is very important when using them with an Access front-end.

Here are some links to read:

https://accessexperts.com/blog/2013...ess-developer-who-works-with-sql-should-know/

https://social.msdn.microsoft.com/F...tting-write-conflict?forum=sqlservermigration

 

[misscrf]

Thanks. I used to use timestamps. From what I have experienced, they are only really necessary, when you attempt to do things you shouldn't do. As long as you do things in the right order, they are superfluous.

 

[Pat Hartman]

Why do you want to save the record IMMEDIATELY after changing the value of this combo? As long as the form is bound, Access will save it when it needs to be saved. You can still use this event to set the unbound fields and whatever else you want to do.

BTW, your original code was not working because the on change event runs once for EVERY keystroke. The on change event for a control is only used when you have a need to check character by character as the user is typing in the entry.