I'm getting a type mismatch in the Dlookup below. Note: the ID column in the Results2 Table is formatted as a Number.

If DLookup("[Result" & i & "]", "Results2", "[ID] = '" & newid & "'") <> Me.Controls("C" & 3   column & "R" & i   j).Value Then

I've tried changing the newid from a string to an Integer or a Long, but I still get this error.

Full code for this Sub below, if more info is needed.

Private Sub BtnSave_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim rs3 As DAO.Recordset
    Dim i As Integer
    Dim j As Integer
    Dim ans As Integer
    Dim column As Integer
    Dim colcnt As Integer
    Dim newid As String
    If IsNull(Me.Spindle3.Value) = False Then
        colcnt = 3
    ElseIf IsNull(Me.Spindle2.Value) = False Then
        colcnt = 2
    Else
        colcnt = 1
    End If
    column = 1
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Results")
    Set rs2 = db.OpenRecordset("Results2")
    Set rs3 = db.OpenRecordset("Results3")
Linestart:
    j = 0
    rs.AddNew
    newid = rs![ID].Value
    If Me.Result1.Value = "Fail" Or Me.Result2.Value = "Fail" Or Me.Result1.Value = "Fail" Then
        If column = 1 Then
            ans = MsgBox("This is a FAILING Result.  Do you with to save it?", vbYesNo)
            If ans = 7 Then GoTo Lineend
        End If
    ElseIf Me.Result1.Value = "Incomplete" Or Me.Result2.Value = "Incomplete" Or Me.Result2.Value = "Incomplete" Then
        If column = 1 Then
            ans = MsgBox("Testing is not finished for this part.  Do you with to save and close now?", vbYesNo)
            If ans = 7 Then GoTo Lineend
        End If
    End If
    With rs
        ![PartNum] = Me.FilterPartNumber.Value
        ![INDNum] = Me.INDNum.Value
        ![DateTime] = Me.DateTime.Value
        ![HTLotNum] = Me.HTLotNum.Value
        ![Operator] = Me.Inspector.Value
        ![Spindle] = Me.Controls("Spindle" & column).Value
        ![TypeofCheck] = Me.InspType.Value
        ![OverallResult] = Me.Controls("Result" & column).Value
    End With
        rs2.AddNew
    With rs2
        ![ID] = newid
        ![PartNum] = Me.FilterPartNumber.Value
        ![Plant] = Me.plantnum.Value
        ![DateTime] = Me.DateTime.Value
        ![HTLotNum] = Me.HTLotNum.Value
        ![Notes] = Me.Notes.Value
        ![Spindle] = Me.Spindle.Value
        ![TypeofCheck] = Me.InspType.Value
        ![OverallResult] = Me.Result1.Value
    End With
        rs3.AddNew
    With rs3
        ![ID] = newid
        ![PartNum] = Me.FilterPartNumber.Value
        ![DateTime] = Me.DateTime.Value
    End With
    If IsNull(Me.HTLotNum.Value) = True Then
        rs![HTLotNum] = "(blank)"
        rs![HTLotNum] = "(blank)"
    End If
    For i = 1 To 90 Step 1
        If i   j >= 90 Then
            i = 90
            GoTo Line1
        End If
        If IsNull(Me.Controls("C3R" & i   j).Value) = True Then
            j = j   1
        End If
        If i   j >= 90 Then
            i = 90
            GoTo Line1
        End If
        If IsNull(Me.Controls("C2R" & i   j).Value) = True Then GoTo Line1
        rs("Char" & i) = Me!ListFeatures.column(1, i - 1)
        rs("Desc" & i) = Me!ListFeatures.column(2, i - 1)
        rs("Spec" & i) = Me!ListFeatures.column(3, i - 1) & " " & Me!ListFeatures.column(6, i - 1)
        rs2("SC" & i) = Me!ListFeatures.column(4, i - 1)
        rs2("Location" & i) = Me!ListFeatures.column(5, i - 1)
        rs2("Result" & i) = Me.Controls("C" & 3   column & "R" & i   j).Value
        rs3("Coding" & i) = Me!ListCoding.column(1, i - 1)
Line1:
    Next
    rs.Update
    rs2.Update
    rs3.Update
    For i = 1 To 90 Step 1
        If i   j >= 90 Then
            i = 90
            GoTo Line1
        End If
        If IsNull(Me.Controls("C3R" & i   j).Value) = True Then
            j = j   1
        End If
        If i   j >= 90 Then
            i = 90
            GoTo Line1
        End If
        If DLookup("[Result" & i & "]", "Results2", "[ID] = '" & newid & "'") <> Me.Controls("C" & 3   column & "R" & i   j).Value Then
            MsgBox "Results not saved! Document results on paper and contact the database engineer regarding this error."
            GoTo Lineend:
        End If
    Next
    If column < colcnt Then
        column = column   1
        GoTo Linestart
    End If
Line2:
    Forms![Landing Page]![LIstIncomplete].Requery
    DoCmd.Close
Lineend:
End Sub

CodePudding user response：

Per one of the comments, I updated the trouble line to the line below. I'm almost certain that was how I initially wrote this line and added the apostrophes as an attempt to fix.

If DLookup("[Result" & i & "]", "Results2", "[ID] = " & newid) <> Me.Controls("C" & 3   column & "R" & i   j).Value Then

I had to fix one of my Goto's as well, one of them led to an infinite loop, but now everything is working as intended.

Thanks for the help!