Here is my vba code I am struggling with...the red DLookup text is where I get a Runtime error 94: invalid use of null. varAgency is a string and varYear is an integer. There is no data for 2019 in the query to start thus I used If varFishCount = "" Then EndNum =1...
I think my issue is with the quotes, singles quotes or AND or all of the above in the DLookup string. Any able to tell me where I'm wrong here?
Dim varYear As Integer
Dim varAgency As String
Dim varFishCount As Integer
Dim EndNum As Integer
varYear = [Forms]![frmFishObservations2]![txtYear]
varAgency = [Forms]![frmFishObservations2]![cmbAgency2]
varFishCount = DLookup("[CountOfFishID]", "qryFishCount", "[AgencyCode] = '" & varAgency & "'" & " And [CollectionYear]=" & varYear)
If Not (IsNull([DBFishID])) Then Exit Sub
If varFishCount = "" Then
EndNum = 1
ElseIf Not (IsNull(varFishCount)) Then
EndNum = varFishCount + 1
End If
[DBFishID] = varYear & "-" & varAgency & "-" & EndNum
End Sub