CODEWITHSUNDEEP
ms-accessvba
Tom H
Tom H

Reputation: 3

Access sActualFieldValue

I am working on a database that was created years ago by a colleague and updated most recently by a software designer who in his words has "bowed out" of servicing it as our database has become too complex.

I've received the following Run-time error '13": Type Mismatch.

The hi-lighted error is:

Me.Controls("Label" & i & "Desc").Caption = DLookup("[Description]", "tbl_Contract_Items", "[Mat_Item_No]='" & sActualFieldValue & "'")

It is a billing database, we bill our customer with item numbers like 1.23, 2.31, 24.02.03 and 21.03.15. Items with the single decimal work fine but it is the items with two decimals that cause the error. ie 24.02.03. The issue is new and was not a problem until recently.

Private Sub Report_Open(Cancel As Integer) Dim db As DAO.Database Dim rs As DAO.Recordset Dim i As Integer Dim fld As Field Dim sActualFieldValue

Set db = CurrentDb()
Set rs = db.OpenRecordset("qxt_tbl_WEC_Qty_Crosstab1")

For i = 1 To rs.Fields.Count - 1 'rs(0) is Date

    If i > 14 Then Exit For

    sActualFieldValue = Replace(rs(i).Name, "_", ".", , 1)

    Me.Controls("Text" & i).ControlSource = rs(i).Name
    Me.Controls("Label" & i).Caption = sActualFieldValue
    Me.Controls("Label" & i & "Desc").Caption = DLookup("[Description]", "tbl_Contract_Items", "[Mat_Item_No]='" & sActualFieldValue & "'")
    Me.Controls("Text" & i & "Tot").ControlSource = "=sum([" & rs(i).Name & "])"
Next

'Stop

End Sub

Upvotes: 0

Views: 66

Answers (1)

Gustav
Gustav

Reputation: 55816

Property Caption must be a string, but DLookup returns Null for not found. So wrap in Nz():

Me.Controls("Label" & i & "Desc").Caption = Nz(DLookup("[Description]", "tbl_Contract_Items", "[Mat_Item_No]='" & sActualFieldValue & "'"))

Upvotes: 0

Related Questions