Update number value from one tabed subform across 4 tabs

[wader]

I have an on click event that checks if entered number values on my first tabbed subform are not null. I then pass the values to three other fields each on a different tabbed subform using If Then Else syntax that seems to work. When I click through the tabs after the code has finished I can see the updated values on their respective tabbed subforms. However, when I click back through the tabs or requery the record all but the first and last subform values have saved.

I have tried inserting If Me.Dirty Then Me.Dirty = False in the subform after update and on data change events as well as in the on click event. I have also tried RunCommand acCmdSaveRecord and neither seems to work.

Any help/ideas would be appreciated
Ex.
If IsNull(Me.AMT1_AF) Then
Else
Forms![SCH_ENTRY]![ENT1subform]![AMT1] = Me.AMT1_AF
End If
If IsNull(Me.AMT2_AF) Then
Else
Forms![SCH_ENTRY]![ENT2subform]![AMT2] = Me.AMT2_AF
End If
If IsNull(Me.AMT3_AF) Then
Else
Forms![SCH_ENTRY]![ENT3subform]![AMT3] = Me.AMT3_AF
End If
If IsNull(Me.AMT4_AF) Then
Else
Forms![SCH_ENTRY]![ENT4subform]![AMT4] = Me.AMT4_AF
End If

 

[wader]

I'm not sure why the issue was occuring....probably something to do with running through the tabs and subforms so quickly. However, I am running an update query instead with the If Dirty statement before and after the update runs and that seems to be handeling any record locking issues. Not so much solved as using a better method.

 

[Mihail]

So... you think that you are faster than the computer. This is good to know
May I ask why you wish to paste the value to more 3 other fields ???????

 

[wader]

Its so the entry person for the db dosen't have to enter the same value over and over again for the same record. The record has hundreds of columns but thoes all boil down to only a few numberic values. It is a massive dataset from a record attribute perspective. Less than 2K records though.

 

[Mihail]

The record has hundreds of columns

Unfortunately, this is exactly what I suspected.
Take a look here
Database Normalization and Table Structures - Microsoft Access / VBA
and think again and again about this "hudreds of columns". And again.

 

[wader]

I know that access has an issue with saving values through a form if you don't step off the field. I tend to shy away from writing to the record using VBA with blended SQL to the table because of all the little bells and whistles that need to be accounted for even though it is a powereful method. Letting an Access update query do the work seems like the best method. Thank for the criticism but I think the update query is going to work out just fine.

 

[Galaxiom]

Thank for the criticism but I think the update query is going to work out just fine.

You will eventually be back with more problems because your data structure is wrong.

 

[wader]

You guys are funny, you have no idea what my data structure is and you have assumed that my database is not normalized because it has a lot of fields.

 

[Galaxiom]

You guys are funny, you have no idea what my data structure is and you have assumed that my database is not normalized because it has a lot of fields.

Call it intuition.

Many previous posters using repeating field names like yours (AMTnn), especially those with "hundreds of fields", have made similar statements and I have yet to see such a case where we have not ultimately confirmed that their data is inappropriately structured.

 

[wader]

I may be a new member but I have been reading these posts for years and I see that comment about normilization used too much. In many cases I would agree with you, it's mostly true but I would never tell someone that they essentiall need to scrap what they are doing and go back to the drawing board on intuition...its not helpful and in my opinion quite rash.