GetLine Number and .ADP

[Stephen]

I have an .ADP database and I am having trouble getting the GetLineNumber
function working...

The code is as follows:

On Continuous FormA, I have a field called "LineNumber" with the following
Control Source:
=GetLineNumber([Forms],"OrderID",[OrderID])

Within the code of the form, I have the following function:

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

Dim CountLines

On Error GoTo Err_GetLineNumber
Dim rs As DAO.Recordset
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


------- QUESTION: I am getting a"0" for every line. Is this because the
recordset is "DAO.Recordset"? Shuold it be something else with an .ADP
file?

Any ideas?

-Stephen

 

[John Spencer (MVP)]

If you are actually getting a record, then why not use the AbsolutePosition
Property (Zero to one less than the number of records in the recordset)

Also, I don't see where you confirm that you've found a matching record.

If rs.NoMatch = false then
GetLineNumber = rs.AbsolutePosition + 1
Else
GetLineNumber = 0
End if

 

[Dirk Goldgar]

I have an .ADP database and I am having trouble getting the
GetLineNumber function working...

The code is as follows:

On Continuous FormA, I have a field called "LineNumber" with the
following Control Source:
=GetLineNumber([Forms],"OrderID",[OrderID])

Within the code of the form, I have the following function:

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

Dim CountLines

On Error GoTo Err_GetLineNumber
Dim rs As DAO.Recordset
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


------- QUESTION: I am getting a"0" for every line. Is this because
the recordset is "DAO.Recordset"? Shuold it be something else with
an .ADP file?

Any ideas?

-Stephen

I think in an ADP, the form's Recordset and RecordsetClone are always
ADO recordsets. So your assignment statement

Set rs = F.RecordsetClone

will always raise a type-mismatch error, and thus your error-handler
will get control and set CountLines to 0. To fix that problem (assuing
I'm right), you would need to declare rs like this:

Dim rs As ADODB.Recordset

But like John Spencer, I don't understand why you're going to such
lengths. What's wrong with just using the form's CurrentRecord
property?

 

[Malcolm Cook]

Dirk, John, Stephen, et al,

The CurrentRecord property, when used as the data source of a control on a
form in either datasheet or continuous form view, in an ADP (against MS SQL
Server) will at all times display the exact same number in every row,
namely, the row number of the current record (the one with with record
selection triangle against it). Same if you use the form's .seltop
property.


If the user selects another record, all the rows will have this column
updated.

Definitely not the desired behaviour.

I don't think that any recoding to use ADODB recordsets, absolute position,
bookmarks is going to make GetLineNumber work in an ADP. Becuase, AFAIK,
client side filtering and sorting are not reflected in a forms underlying
recordset and are simply not exposed at all to the programmer.

In particular, if you modify CountLines to use ADODB and use the absolute
position or count rows, you may think you have what you want until you sort
or filter rows, or your data gets rearranged on disk and the order it is
returned in is no longer the order of creation. I've included both rewrites
below. DOH!

I'm struggle with tryinto to provide for the same need - to display a
recordnumber on every row (in a datasheet view). This would allows the user
to scan the screen to find a record they want to edit, hit F5, and type the
record number into the record number box.

My users REALLY want this ability.

Any other ideas? I know Lebans has walked this beat allot, but I think his
solutions are all geared to Jet.

If I figure anything out I'll post it here.

--
Malcolm Cook - (e-mail address removed)
Database Applications Manager - Bioinformatics
Stowers Institute for Medical Research - Kansas City, MO USA


P.S.

here are a few links to this topic, but the solutions don't address adp.

http://www.utteraccess.com/forums/showflat.php?Cat=&Number=350302&Main=350302

Also, here is GetLineNumber recoded for adp but it does not solve the
problem in general.

Function GetLineNumber(F As Form, KeyName As String, KeyValue)
'c.f. http://support.microsoft.com/?kbid=210340 "adapted" for adp but it
still is wrong

Dim RS As adodb.Recordset
Dim CountLines

On Error GoTo Err_GetLineNumber

Set RS = F.Recordset.Clone

' 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.Find "[" & KeyName & "] = " & KeyValue
' Find using date data type key value.
Case dbDate
RS.Find "[" & KeyName & "] = #" & KeyValue & "#"
' Find using text data type key value.
Case dbText
RS.Find "[" & 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

' or, try AbsolutePosition

CountLines = RS.AbsolutePosition - 1

Bye_GetLineNumber:
' Return the result.
GetLineNumber = CountLines

Exit Function

Err_GetLineNumber:
CountLines = 0
Resume Bye_GetLineNumber

End Function

I have an .ADP database and I am having trouble getting the
GetLineNumber function working...

The code is as follows:

On Continuous FormA, I have a field called "LineNumber" with the
following Control Source:
=GetLineNumber([Forms],"OrderID",[OrderID])

Within the code of the form, I have the following function:

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

Dim CountLines

On Error GoTo Err_GetLineNumber
Dim rs As DAO.Recordset
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


------- QUESTION: I am getting a"0" for every line. Is this because
the recordset is "DAO.Recordset"? Shuold it be something else with
an .ADP file?

Any ideas?

-Stephen

I think in an ADP, the form's Recordset and RecordsetClone are always
ADO recordsets. So your assignment statement

Set rs = F.RecordsetClone

will always raise a type-mismatch error, and thus your error-handler
will get control and set CountLines to 0. To fix that problem (assuing
I'm right), you would need to declare rs like this:

Dim rs As ADODB.Recordset

But like John Spencer, I don't understand why you're going to such
lengths. What's wrong with just using the form's CurrentRecord
property?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Malcolm Cook]

Dirk, John, Stephen, Jane, L, et al,

Well, I bit the bullet, and followed an approach discussed in other
newsgroups. It is attached.

It works. The line numbers are correct in my apd application even after
sorting and filtering in the client. The translation from Jet to ADO is a
hack. Improvements welcome.

Regards,

--
Malcolm Cook - (e-mail address removed)
Database Applications Manager - Bioinformatics
Stowers Institute for Medical Research - Kansas City, MO USA



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

' PURPOSE : as the formula to which a control is bound on datasheet or
continuous form to show a record number.

' USAGE: =GetLineNumber([Form], "ID", [ID])

' Based on http://support.microsoft.com/?kbid=210340
' "adapted" for ADP by Malcolm Cook ([email protected])
'
' implementing ideas expressed in:
'
http://groups.google.com/[email protected]&rnum=2
'
' TODO: the translation of the form's filter and OrderBy to the record set's
is pretty much a hack and
' makes assumptions that will not hold up in practice! Improvements
welcome!

On Error GoTo Err_GetLineNumber

If IsNull(KeyValue) Then ' This _may_ be due to being run in Access' New
Record buffer
GetLineNumber = -1
Exit Function
End If

Dim RS As adodb.Recordset
Dim RSFrm As adodb.Recordset
Dim CountLines
Dim strNewFilter As String
Dim strNewSort As String
Static re As Object
If re Is Nothing Then
Set re = CreateObject("VBScript.RegExp")
With re
.Global = True
.IgnoreCase = True
.Multiline = True
.Pattern = "\w+\."
End With
End If

With F
Set RSFrm = .Recordset
With RSFrm
Set RS = .Clone
End With

If .FilterOn Then
strNewFilter = .filter
With re
strNewFilter = .Replace(strNewFilter, "")
End With
strNewFilter = Replace(strNewFilter, " ALIKE ", " LIKE ")
strNewFilter = Replace(strNewFilter, "*", "%")
strNewFilter = Replace(strNewFilter, "?", "_")
strNewFilter = Replace(strNewFilter, Chr(34), "'")
RS.filter = strNewFilter
End If
If .OrderByOn Then
strNewSort = .OrderBy
With re
strNewSort = .Replace(strNewSort, "")
End With
RS.Sort = strNewSort
End If

With RS

.MoveFirst

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

CountLines = RS.AbsolutePosition ' - 1

End With
End With


Bye_GetLineNumber:
' Return the result.
GetLineNumber = CountLines

Exit Function

Err_GetLineNumber:
CountLines = -666
Resume Bye_GetLineNumber

End Function

Dirk, John, Stephen, et al,

The CurrentRecord property, when used as the data source of a control on a
form in either datasheet or continuous form view, in an ADP (against MS SQL
Server) will at all times display the exact same number in every row,
namely, the row number of the current record (the one with with record
selection triangle against it). Same if you use the form's .seltop
property.


If the user selects another record, all the rows will have this column
updated.

Definitely not the desired behaviour.

I don't think that any recoding to use ADODB recordsets, absolute position,
bookmarks is going to make GetLineNumber work in an ADP. Becuase, AFAIK,
client side filtering and sorting are not reflected in a forms underlying
recordset and are simply not exposed at all to the programmer.

In particular, if you modify CountLines to use ADODB and use the absolute
position or count rows, you may think you have what you want until you sort
or filter rows, or your data gets rearranged on disk and the order it is
returned in is no longer the order of creation. I've included both rewrites
below. DOH!

I'm struggle with tryinto to provide for the same need - to display a
recordnumber on every row (in a datasheet view). This would allows the user
to scan the screen to find a record they want to edit, hit F5, and type the
record number into the record number box.

My users REALLY want this ability.

Any other ideas? I know Lebans has walked this beat allot, but I think his
solutions are all geared to Jet.

If I figure anything out I'll post it here.

--
Malcolm Cook - (e-mail address removed)
Database Applications Manager - Bioinformatics
Stowers Institute for Medical Research - Kansas City, MO USA


P.S.

here are a few links to this topic, but the solutions don't address adp.

http://www.utteraccess.com/forums/showflat.php?Cat=&Number=350302&Main=350302

Also, here is GetLineNumber recoded for adp but it does not solve the
problem in general.

Function GetLineNumber(F As Form, KeyName As String, KeyValue)
'c.f. http://support.microsoft.com/?kbid=210340 "adapted" for adp but it
still is wrong

Dim RS As adodb.Recordset
Dim CountLines

On Error GoTo Err_GetLineNumber

Set RS = F.Recordset.Clone

' 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.Find "[" & KeyName & "] = " & KeyValue
' Find using date data type key value.
Case dbDate
RS.Find "[" & KeyName & "] = #" & KeyValue & "#"
' Find using text data type key value.
Case dbText
RS.Find "[" & 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

' or, try AbsolutePosition

CountLines = RS.AbsolutePosition - 1

Bye_GetLineNumber:
' Return the result.
GetLineNumber = CountLines

Exit Function

Err_GetLineNumber:
CountLines = 0
Resume Bye_GetLineNumber

End Function

I have an .ADP database and I am having trouble getting the
GetLineNumber function working...

The code is as follows:

On Continuous FormA, I have a field called "LineNumber" with the
following Control Source:
=GetLineNumber([Forms],"OrderID",[OrderID])

Within the code of the form, I have the following function:

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

Dim CountLines

On Error GoTo Err_GetLineNumber
Dim rs As DAO.Recordset
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


------- QUESTION: I am getting a"0" for every line. Is this because
the recordset is "DAO.Recordset"? Shuold it be something else with
an .ADP file?

Any ideas?

-Stephen

I think in an ADP, the form's Recordset and RecordsetClone are always
ADO recordsets. So your assignment statement

Set rs = F.RecordsetClone

will always raise a type-mismatch error, and thus your error-handler
will get control and set CountLines to 0. To fix that problem (assuing
I'm right), you would need to declare rs like this:

Dim rs As ADODB.Recordset

But like John Spencer, I don't understand why you're going to such
lengths. What's wrong with just using the form's CurrentRecord
property?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Dirk, John, Stephen, et al,

The CurrentRecord property, when used as the data source of a control
on a form in either datasheet or continuous form view, in an ADP
(against MS SQL Server) will at all times display the exact same
number in every row, namely, the row number of the current record
(the one with with record selection triangle against it). Same if
you use the form's .seltop property.

If the user selects another record, all the rows will have this column
updated.

Definitely not the desired behaviour.

Ah. I see what you're after now.

I don't think that any recoding to use ADODB recordsets, absolute
position, bookmarks is going to make GetLineNumber work in an ADP.
Becuase, AFAIK, client side filtering and sorting are not reflected
in a forms underlying recordset and are simply not exposed at all to
the programmer.

I'm not that familiar with ADPs, I have to admit. I'd have thought that
*client* side filtering and sorting would be exposed, but I'll take your
word for it.

In particular, if you modify CountLines to use ADODB and use the
absolute position or count rows, you may think you have what you want
until you sort or filter rows, or your data gets rearranged on disk
and the order it is returned in is no longer the order of creation.
I've included both rewrites below. DOH!

I'm struggle with tryinto to provide for the same need - to display a
recordnumber on every row (in a datasheet view). This would allows
the user to scan the screen to find a record they want to edit, hit
F5, and type the record number into the record number box.

My users REALLY want this ability.

Silly users! ;-)

Any other ideas? I know Lebans has walked this beat allot, but I
think his solutions are all geared to Jet.

If I figure anything out I'll post it here.

Your ultimate solution looks interesting. My first reaction, reading
it, was "Surely he doesn't need all this! Can't he do something with
bookmarks?" But I'd have to set up an ADP and fool around with it to
find out. I don't know when I'll have time to try that -- I'm kind of
busy right now.

 

[Malcolm Cook]

Dirk,

....

I'm not that familiar with ADPs, I have to admit. I'd have thought that
*client* side filtering and sorting would be exposed, but I'll take your
word for it.

Well, DOH, of course you're right, it _is_ exposed, only as attributes of
the form (.OrderBy, .Filter), not of the underlying recordset. My above
message shows how I sussed out transferring these to an clone of the
recordset.

Silly users! ;-)

Say no more!

Your ultimate solution looks interesting. My first reaction, reading
it, was "Surely he doesn't need all this! Can't he do something with
bookmarks?" But I'd have to set up an ADP and fool around with it to
find out. I don't know when I'll have time to try that -- I'm kind of
busy right now.

I don't think bookmarks will help. Reason? Because AFAIK the solution MUST
have a datastructure which knows the records as they appear on the screen
and the order in which they appear. The only way I think this is possible
is to combine the serverside and clientside filters and sorts/orderbys into
a single recordset. However, the bookmarks in the resulting combined
recordset would not be comparable with those in the original underlying
recordset. Why? Well, From the ADO 2.8 help, I read:
Except for the case of cloned Recordsets, bookmarks are unique to the
Recordset in which they were created, even if the same command is used. This
means that you cannot use a Bookmark obtained from one Recordset to move to
the same record in a second Recordset opened with the same command.

....and if I try and compare them, I get an error to the effect that they are
not comparable.

Any other enlightened observations?

Regards,

Malcolm Cook