Weird form behavior AfterUpdate

[hokiewalrus]

I have a complicated form with several subforms, everything works as it should.

I added one field to the table the main field is based on, then I destroyed and recreated the link between the table and the field.

I added the new field to the main form and it shows up as it should, I did nothing else, either in code or anywhere else. Now the strange part, if I change the data in the new field the entire form and all the subforms blank out as if you just opened the program.

I feel like this has to be something stupid as I do this in plenty of other places around the program, but this is the only place where things go pear shaped. I've removed the control and added it twice, always with the same result.

 

[DanWall]

Have you got a filter applied to the form? Or a Where Condition?

 

[hokiewalrus]

There is a WHERE condition in the main form record source, yes:

SELECT * FROM tJobsTotals WHERE [ControlNumber]=1;

Would that screw something up?

 

[Rabbie]

This link may explain what is going on

 

[hokiewalrus]

Hmm, that link feels like the right track, thanks!

 

[Rabbie]

You're welcome. Always happy to help.

 

[hokiewalrus]

Ok, perhaps it's deficiency on my part, but I have not been able to solve the problem.

The thing that is driving me nuts is this form has other controls in it also based on the same table, and I can update any of those just find, it's only with this new one that I have added that I have a problem.

The rest of the controls have some nominal bits of code associated with them so I tried adding a Me.Refresh to the AfterUpdate just to see if that would do anything, no such luck. I also tried putting code in the BeforeInsert area, as in the link provided, but that had no effect as well.

I thought perhaps the control was trying to add a new record, however if I open the job again the new value (which caused the form to blank out in the first place) is in there.

I've enclosed a partial screenshot, the Misc Charge field is the only one with this problem, any other control can be changed without incident.

Why is it always the simple things that are the hardest to get working?

 

[hokiewalrus]

Bit of an update.

I noticed someone that all my properties on the main form got reset back to default (Allow DataSheet View, Allow PivotTable View, etc..) including AllowAdditions.

So I set AllowAdditions back to No but when I tried that I got an error (2455) when trying to reference the subforms:

MaterialsSource = "SELECT * FROM [tJobsMaterials] WHERE [ControlNumber] = 1;"
    Me.MaterialsSubForm.Form.RecordSource = MaterialsSource

The second line now gives me the error: You entered an expression that has an invalid reference to the property Form/Report.

But if I set the AllowAdditions property back to Yes on the main form that error goes away and everything works as it should. Weird.

 

[hokiewalrus]

For anyone following along or for anyone else with the same problem as me, I found the solution in a very old thread:

I had the cycle property (something I had never heard of before) set to the default, which is "all records" so when I tabbed out of the field I had just inserted (which was of course at the bottom of the tab order) it tried to go to the next record, which in my case was a new record, hence the form looked blank.

So all I had to do was change the property to "current record", bingo bango, life is gravy.

I KNEW it had to be something simple, I could feel it.