Intermittent #Type! error

[BeeJayEff]

This is an error which arises from time to time, but not consistently. Within a main form I have a continuous subform which displays a number of detailed records. The subform includes a number of fields whose contents are concatenations of other fields within the subform. Occasionally, one of these concatenated fields displays #Type!, even though the source fields' data is fine and other visible detailed records show their data correctly - and when viewed on another PC, the same detailed record is also displayed correctly.

If I scroll through the subform and come back to the offending detailed record, it will be fine.

So I think there is nothing inherently wrong with the field's data, but there is some sort of race condition going on.

Wrapping the concatenation up in an IsError call to force a refresh wouldn't help as that would jump back to the first detailed record.

Any ideas ?

 

[JHB]

What is the control source for that control showing the #Type?
Do you've any code running when you cycle through the records?

 

[arnelgp]

You know that there is only 1 textbox for that unbound textbox.

You may try thIs
Remove the expression from the textbox control source.
Create a Query for your subform and add your expression as new column.
Make this query the recordsource of the subform.
add the new column to your subform.

 

[BeeJayEff]

The control source is :

=IIf([Forms]![frmSaleEventListing]![Concatenations],Concatenation([Forms]![frmSaleEventListing]![fkPlatformID],"Title"))

... where frmSaleEventListing is the parent form, [Concatenations] and [fkPlatformID] are fields on the parent form and Concatenation is a private function.
No code runs as a result of scrolling through the subform.

arnelgp1 : Can you clarify what you mean in your first sentence ?
The subform's record source is already a rather complicated query (47 fields from 10 tables), and there are six of these concatenated fields on the subform - so yes, I could add them as new columns, but would rather keep it simple - if I can find out what's causing the occasional #Type! error.

 

[JHB]

It may be a time issue where data isn't ready, when you're trying to use the data in the function, mostly a DoEvents placed in a correct place helps.
But how often do you get the #Type?
How does the code look like in your function?

 

[BeeJayEff]

Thanks for the idea - I have never come across DoEvents before so I put one in at the start of the Concatenation routine but that hasn't fixed it.
The occurrence of the #Type is very random - maybe up to 10% of the time, mostly when jumping through several detail records at once (using the scroll bar).
The routine code is very lengthy, with nested Case blocks - though having said that the actual path followed in each case is straightforward.
Is there any defined order in which Access populates fields within a (sub)form ?

 

[Minty]

I'm always under the impression that IIf() statements are a bit slow to process, could that not be incorporated into your function?
At the moment I suspect your function is being called for every record anyway, as I think access will preempt the function call in the IIf statement.

Depending on how many records are involved this may be one of those rare cases where a temp table with the results in it might be better?

 

[BeeJayEff]

The [Concatenations] field is a simple checkbox indicating whether concatenations are required on none or all detailed records. I could pass that in to the routine (or refer to it from within the routine), though that seems a bit inefficient. When the Checkbox is True, the routine is called six times for each detailed record.

The example I'm looking at right now has 206 detailed records, of which about two and a half can be seen within the subform control. If I skip on 5-10 records and come back, the field will usually be shown correctly.

I don't like the idea of a temporary table, not least because of the time taken to generate it and the hassle involved with ensuring it is always deleted cleanly after use.

It's one for pondering over the weekend.

 

[JHB]

Thanks for the idea - I have never come across DoEvents before so I put one in at the start of the Concatenation routine but that hasn't fixed it.

Placing DoEvents in the start of the routine would have been a fluke if it should have helped.
You must place DoEvent's in strategically correct location, where time-consuming calculations may occur, where there are data / results obtained from a query / DLookup etc.
And it does not have to be in the actual code, but also in the control source for controls.
Therefore I asked for the code (even if it is is straightforward), then maybe I might have a suspicion of where that could be.
What supports my theory that it's a matter of time is what you write about: "If you ship on 5-10 records and come back, the field will usually be displayed correctly."
This indicates that the program needs time to perform the "calculations" and when it get that, the "calculations" is correct, (so the failure is not caused by bad data like Null values etc.).