GetLineNumberFunction() is not working

[blur]

Hi,

Good Day!

I have a form with a subform and I want to have a line number for each of the records that will be displayed.

Here is my setup:


I have a table with the field "ID" with the setup "Integer".

I have a module called GetLNumber and put this code

Function GetLineNumber(F As Form, KeyName As String, KeyValue)

Dim RS As DAO.Recordset
Dim CountLines

On Error GoTo Err_GetLineNumber

Set RS = F.RecordsetClone

' Find the current record.
Select Case RS.Fields(KeyName).Type
' Find using numeric data type key value.
Case dbInteger, dbLong, dbCurrency, dbSingle, dbDouble, dbByte
RS.FindFirst "[" & KeyName & "] = " & KeyValue
' Find using date data type key value.
Case dbDate
RS.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
' Find using text data type key value.
Case dbText
RS.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"
Exit Function
End Select

' Loop backward, counting the lines.
Do Until RS.BOF
CountLines = CountLines + 1
RS.MovePrevious
Loop

Bye_GetLineNumber:
' Return the result.
GetLineNumber = CountLines

Exit Function

Err_GetLineNumber:
CountLines = 0
Resume Bye_GetLineNumber
End Function

and then in the textbox called txtID has a Control Source =GetLineNumber([Forms]![frmEarlyWarning System].[Form].[frmEarlyWarning SystemSubform],"ID",[ID])

but when I click Form View, the field for txtID contains #Type!

I tried this code also but still no luck.

Function GetLineNumber(F As Form, KeyName As String, KeyValue)
Dim RS As Object
Dim CountLines

On Error GoTo Err_GetLineNumber

Set RS = F.Recordset.Clone

RS.Find "[" & KeyName & "] = " & KeyValue

' Loop backward, counting the lines.
Do Until RS.BOF
CountLines = CountLines + 1
RS.MovePrevious
Loop

Bye_GetLineNumber:
' Return the result.
GetLineNumber = CountLines

Exit Function

Err_GetLineNumber:
CountLines = 0
Resume Bye_GetLineNumber

End Function

what did I missed?

Your help is very much appreciated!

tia

[NeoPa]

If it allows you to pass a form reference then you could try :

=GetLineNumber([Forms]![frmEarlyWarningSystem]![frmEarlyWarningSystemSubform]![Form],"ID",[ID])

Your reference to the subform object was incorrect (wrong order of elements) and SQL doesn't recognise dots (.), so this is worth a try.

See Referring to Items on a Sub-Form for correct structure of references from VBA.

[blur]

Thanks NeoPa for your quick response. I've tried it but still not working. it displays "#Name?"

here is the new value of control source of my textbox

=GetLineNumber([Forms]![frmEarlyWarningSystem]![Form]![frmEarlyWarningSystemSubform]![Form],"ID",[ID])

[NeoPa]

That won't work. If you look at the linked article you'll clearly see why.

[Forms]![frmEarlyWarningSystem]

Is a reference to a form object. Form objects don't have Form properties (After all - what would be the point?).

Subform controls, however, are not Form objects at all, but contain a Form. That is why it makes sense that they would have a Form property.

It's all in the linked article.

You may well find, however, that there are limits to what you can refer to at all from within SQL. That's not something I can help with I'm afraid.