Solved First Record In Subform Calculated Control #Type! (2 Viewers)

[dalski]

Sorry to hog forum. In a subform (datasheet) I have a calculated control; which obtains it's vale from a function placed in a global module.

• First value produces #Type! in the calculated control

The calculated control is not processing the first record. Has anyone experienced this sort of thing?

 

[arnelgp]

test the function first and see the result, is it numeric or string or date?

 

[dalski]

Thanks arnel, it's a string which concatenates values. Function works fine on all other records but it skips the very first record. The first record processed is actually the second in the dataset. Can't find anything on Google yeilding same sort of problem.
The dataset is based on a query which is sorted in order.

 

[MajP]

Are you concatenating with a "+" instead of a "&"? Please show the function, if not no one can guess at the cause.

 

[dalski]

I don't think the problem is with the function; it's sorting in order perfectly based on a numeric value. It seems as if it's treating the first record in the recordset as Beginning Of File or something like that.
I had an #Error on the last record but I got around that with a simple If statement; I'll probably remove record creation enabling in the form anyway.
I have an issue with the last ElseIf but that's not the problem; the first record is not a hTypeID=9. Stepping through the code I see the first record is not being passed to the function & I'll be fine fixing this.

Public Function RefDisplayDetermine(ID As Long, hTypeID As Long, ParentID As Long) As String
  Dim rsThisItem As DAO.Recordset
  Dim qDefThisItem As DAO.QueryDef
  Dim strHolder As String
 
  Set qDefThisItem = CurrentDb.QueryDefs("Tender-hRefDisplayQ")
  qDefThisItem.Parameters!ID = ID
  Set rsThisItem = qDefThisItem.OpenRecordset(dbOpenDynaset, dbSeeChanges)
  
  If hTypeID <> 0 Then
    'Not a blank Pg/ record:
    If hTypeID <> 9 Then
      'HEADERS - Selected item was a header
      'RootH selected:
      If IsNull(rsThisItem!ParentHeaderID) Then
        'RootH selected is selected:
        RefDisplayDetermine = rsThisItem!Ref_Header
      ElseIf Not IsNull(rsThisItem!ParentHeaderID) Then
        'SubH selected:
        'Temporarirly hold this ref (second part of ref) to concatenate shortly:
        strHolder = rsThisItem!Ref_Header
        qDefThisItem.Parameters!ID = ParentID
        Set rsThisItem = qDefThisItem.OpenRecordset(dbOpenDynaset, dbSeeChanges)
        'Concatenate Ref:
        RefDisplayDetermine = rsThisItem!Ref_Header & "." & strHolder
      End If
    ElseIf hTypeID = 9 Then
      'BILL-ITEMS - Selected item was a BI:
      'First part of Ref:
      qDefThisItem.Parameters!ID = ParentID
      strHolder = rsThisItem!Ref_Header
      Set qDefThisItem = CurrentDb.QueryDefs("Tender-BillItemDisplayQ")
      qDefThisItem.Parameters!ID = ID
      Set rsThisItem = qDefThisItem.OpenRecordset(dbOpenDynaset, dbSeeChanges)
      RefDisplayDetermine = rsThisItem!Ref_Header & "." & strHolder
    End If
  
  
  Else

  
  End If
 
  rsThisItem.Close
  qDefThisItem.Close
End Function

 

[arnelgp]

are you sure you don't have Null values on some of your Fields used by the function?

 

[dalski]

The arguments passed to the function are fine & the query is being processed in correct order. I think it's event related (which I appreciate I need to post the db but I can't do this [apologies I know what I'm asking is impossible to diagnose on this poor info]), but thought I'd post incase there was a common issue that someone had encountered.

I thought I might've spotted it on an external class reloading the page but that wasn't it as I commented it out. I've messaged Pete the db as he helped me massively previously & is familiar with the project. The db has gotten quite complex for my limited capabilities.

 

[CJ_London]

As a test , suggest reverse your sort order - if the error is now at the bottom of the recordset it implies an issue with the data. If still at the top then the issue is with the function - perhaps a variable has not been initialised for example

 

[dalski]

Thanks CJ, I was not expecting to see an error but it does generate an error. Something is up with the ID not being passed. I'm investigating now. Arnel also on target; thanks, there is a null which kicks the record off. Like MajP's fine treeview I need a null to identify a 'Root' record. Pete's also kindly identified somewhere to look at.

Really appreciate all your help - thank you.

 

[dalski]

Thank you all so much. The problem was caused as Arnel identified a null argument in one of the arguments. It stopped all the other values for that pass of the function. CJ also spot-on identifying an unitialized argument, & MajP has helped me more problems than I can count; beyond Long Integer's data storage capabilities . Really appreciate you all helping me with such bad info.

 

[The_Doc_Man]

Sorry to hog forum.

You have a solution in 10 posts, and you participated in the dialog in ways that showed you actually read the responses you got. Further, you explained the result that led to your solution. This is NOT hogging the forum. It is using it correctly. Therefore, don't be sorry and don't apologize.

We've had some posters who get fixated on a particular way to do something and then go berserk defending an untenable algorithm or schema. Those technical threads go 'round and 'round for hundreds of posts without reaching any resolution whatsoever. We expect Watercooler or Politics threads to go on for a while, but multi-hundred post technical threads? Sheesh!

Don't worry, dalski, you're doing fine! YOUR kind of post is what we all hope to see.

 

[dalski]

Thanks Doc_Man