Concatenating Fields + #Size! Error

[jeran042]

Good morning all,

I have 4 unbound controls on a subform. The first control tests and strips away unwanted leading characters:
Name: txtADJ_POLICY

=Nz(IIf(Left([POLICY],1)="R",Mid([POLICY],3,Len([POLICY])),IIf(Left([POLICY],1)=0,Mid([POLICY],2,Len([POLICY])),[POLICY])),"")

Name: txtTEMP_DESC
The second does a lookup to a table based on the field [POLICY]

=Nz(DLookUp("DESCRIPTION","TBL_TEMP_MONTH_TO_DATE","[VOUCHER_NUMBER]= '" & [POLICY] & "'"),"")

Name: txtINVOICE_NOTES
And the third does a lookup on a linked table. It removes the carriage returns from within the [NOTES] field (This table is the reason why the [POLICY] number needs to be formatted:

=Replace(Nz(DLookUp("NOTES","tblInvoiceLog","[VOUCHER_NUMBER]= " & [txtADJ_POLICY] & ""),""),Chr(13) & Chr(10)," ")

And all 3 of these fields work correctly. Each pulling (or formatting) the piece of information I need.



My question is this, in the 4th unbound textbox I concatenate txtTEMP_DESC and txtINVOICE_NOTES with a line break:

=Trim(Nz([txtTEMP_DESC])) & Chr(13) & Chr(10) & Trim(Nz([txtINVOICE_NOTES]))

And my problem is this, most time this works correctly. Combining the 2 fields, with a line break. However on some records, the fields txtTEMP_DESC and txtINVOICE_NOTES will return the correct answer, but the field txtINVOICE_NOTES gives a "#Size!" error? I should note the formulas for each of these unbound controls is in the Control Source of the control. And none of these fields are formatted (by the format property of the control) as anything.

And suggestions on what may be causing this?
Very much appreciated,

PS: I realize that this is definitely not the best way to go about this, but in this instance (if I can get this to work correctly), I would accept it

 

[MarkK]

Why is it important, on a form with three unbound textboxes, to have a fourth unbound textbox that shows exactly the same thing as two of the other textboxes?
Mark

 

[jeran042]

Mark,

Good question, the first three text boxes are hidden, and the 4th just makes sure that all the data in the 2 lookup text boxes are captured, and in a uniform format.

What I mean is, if I just had the first two lookup boxes on top of each other, there would be a chance that some info may be cut off. Concatenating them (I suppose) is uniform

 

[MarkK]

...there would be a chance that some info may be cut off...

How much data are we talking about here? Maybe add another textbox, not hidden, with a control source like this...

=Len(Replace(Nz(DLookUp("NOTES","tblInvoiceLog","[VOUCHER_NUMBER]= " & [txtADJ_POLICY] & ""),""),Chr(13) & Chr(10)," "))

...and report back on how much data you are trying to display. Maybe data is going to be cut off anyway.
I have never seen a #Size! error. I don't know the exact constraints or limits on what a textbox can display or under what conditions it will fail like that. I would test the size of the data I am pushing into it, and see if that has a bearing on the failure.
Mark

 

[jeran042]

MarkK,

On the long side, the text length is 273 characters. (And in this instance, the text box displays it all)

And on the short size, the length is 7 characters, and the text box is throwing up the error.

I've run into this error before, but in that case it was a text box name clash, I do not believe that is the problem here

 

[MarkK]

I would be tempted to do this in code, like...

Private Sub Form_Current()
    Dim pol As String
    Dim tmp As String
    Dim nte As String
    
    pol = Nz(IIf(Left([policy], 1) = "R", Mid([policy], 3, Len([policy])), IIf(Left([policy], 1) = 0, Mid([policy], 2, Len([policy])), [policy])), "")
    tmp = Nz(DLookup("DESCRIPTION", "TBL_TEMP_MONTH_TO_DATE", "[VOUCHER_NUMBER]= '" & [policy] & "'"), "")
    nte = Replace(Nz(DLookup("NOTES", "tblInvoiceLog", "[VOUCHER_NUMBER]= " & pol & ""), ""), Chr(13) & Chr(10), " ")

    Me.txtFinal = tmp & vbCrLf & nte

End Sub

...and you could likely refactor those IIF() functions to be more readable in VBA too. It is also easier to debug, since you can step thru execution and determine the value of each incremental evaluation.
hth
Mark

 

[jeran042]

This code is giving me a run time error 3075:
Syntax error (missing operator) in query expression [VOUCHER_NUMBER] = '?

Not sure why, looks exactly like the formula (which is working)?

 

[jeran042]

I would be tempted to do this in code, like...

Private Sub Form_Current()
    Dim pol As String
    Dim tmp As String
    Dim nte As String
    
    pol = Nz(IIf(Left([policy], 1) = "R", Mid([policy], 3, Len([policy])), IIf(Left([policy], 1) = 0, Mid([policy], 2, Len([policy])), [policy])), "")
    tmp = Nz(DLookup("DESCRIPTION", "TBL_TEMP_MONTH_TO_DATE", "[VOUCHER_NUMBER]= '" & [policy] & "'"), "")
    nte = Replace(Nz(DLookup("NOTES", "tblInvoiceLog", "[VOUCHER_NUMBER]= " & pol & ""), ""), Chr(13) & Chr(10), " ")

    Me.txtFinal = tmp & vbCrLf & nte

End Sub

...and you could likely refactor those IIF() functions to be more readable in VBA too. It is also easier to debug, since you can step thru execution and determine the value of each incremental evaluation.
hth
Mark

Also, I had basically this same code working yesterday, but I put it in the On Load event, what would be the difference between that and the On Current?

 

[MarkK]

This code is giving me a run time error 3075:
Syntax error (missing operator) in query expression [VOUCHER_NUMBER] = '?

What is the value of [policy] when you get that error? Possibly it is null?

Form_Current fires every time the form loads a new record, so if you navigate to a new record perhaps the value of the field [policy] changes. At that point the Current event fires, and you can run code that updates visual elements of the form that depend on the data in that changed record. Load, of course, only fires once in the entire lifetime of the form.

Mark