Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2014
    Posts
    37

    Unanswered: Adding Row Numbers to Subform problems.

    Thanks for everyone's help on past issues, but I am beating my head against the wall on this one now...

    THE GOAL: To add row numbers to a txtfield on a subform.

    The Method: using function GetLineNumber() originally published by MS here: http://support.microsoft.com/kb/120913

    On a txtbox control's control source property, call the function =GetLineNumber([Form],"RecNo",[RecNo]) as described in the KB article.

    I have added my module (LineNumbering) code for the GetLineNumber() function below:

    Code:
    Function GetLineNumber(F As Form, KeyName As String, KeyValue)
    
    'This article shows you how to create and use a procedure to display the current line or row number in a subform.
    'source: http://support.microsoft.com/kb/120913
    
    
    Dim RS As DAO.Recordset
    Dim CountLines
    
    On Error GoTo Err_GetLineNumber
    
    Set RS = F.RecordsetClone
    Debug.Print "Form name: " & F.Name
    Debug.Print "RecordSource: " & F.RecordSource
    Debug.Print "KeyValue: " & KeyValue
    
     
     ' Find the current record.
     If Not IsNull(KeyValue) Then
       
       Select Case RS.Fields(KeyName).Type
          ' Find using numeric data type key value.
          Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, DB_DOUBLE, DB_BYTE
             
             Debug.Print ".Findfirst: " & "[" & KeyName & "] = " & KeyValue
             RS.FindFirst "[" & KeyName & "] = " & KeyValue
    
          ' Find using date data type key value.
          Case DB_DATE
             RS.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
          ' Find using text data type key value.
          Case DB_TEXT
             RS.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
          Case Else
             MsgBox "ERROR: Invalid key field data type!"
             Exit Function
       End Select
    
        Debug.Print "Abs. Pos.: " & RS.AbsolutePosition
        
    
       ' Loop backward, counting the lines.
       Do Until RS.BOF
          CountLines = CountLines + 1
          RS.MovePrevious
       Loop
    Else
        CountLines = "+1"
    End If
    
    
    Bye_GetLineNumber:
       ' Return the result.
       Debug.Print "CountLines: " & CountLines
       GetLineNumber = CountLines
       
       Set RS = Nothing
    
       Exit Function
    
    Err_GetLineNumber:
        Debug.Print "error position AP: " & RS.AbsolutePosition
       CountLines = "+"
       Debug.Print "Error " & Err.Number & " " & Err.Description, vbCritical, "ERROR!"
       Resume Bye_GetLineNumber
    
    End Function
    The Problems:

    On a Form Record with only one subrecord:
    1) The function appears to be called three times for the first record, but generally works (see debug output 1 from immediate window)

    a) Why would this function be called multiple times, and is this related to the next problem?

    DEBUG OUTPUT 1

    Form name: SubsampleDataSubModFrm
    RecordSource: SELECT subsampleData.* FROM subsampleData;
    KeyValue: 303640
    .Findfirst: [RecNo] = 303640
    Abs. Pos.: 0
    CountLines: 1 <<<< correct

    Form name: SubsampleDataSubModFrm
    RecordSource: SELECT subsampleData.* FROM subsampleData;
    KeyValue: 303640
    .Findfirst: [RecNo] = 303640
    Abs. Pos.: 0
    CountLines: 1 <<<< correct again (round 2)

    Form name: SubsampleDataSubModFrm
    RecordSource: SELECT subsampleData.* FROM subsampleData;
    KeyValue: 303640
    .Findfirst: [RecNo] = 303640
    Abs. Pos.: 0
    CountLines: 1 <<<< Correct a third time??

    Form name: SubsampleDataSubModFrm
    RecordSource: SELECT subsampleData.* FROM subsampleData;
    KeyValue:
    CountLines: +1 <--- this shows on the NEW RECORD

    On a Form Record with multiple subrecords:

    The function appears to be called generally three times (sometimes more) and always fails if called more than three times for the first record. EVERY other record fails with an error 3059. (See partial debug output below)




    Form name: SubsampleDataSubModFrm
    RecordSource: SELECT subsampleData.* FROM subsampleData;
    KeyValue: 303567
    .Findfirst: [RecNo] = 303567
    Abs. Pos.: 0
    CountLines: 1 <<<< row 1

    Form name: SubsampleDataSubModFrm
    RecordSource: SELECT subsampleData.* FROM subsampleData;
    KeyValue: 303567
    .Findfirst: [RecNo] = 303567
    Abs. Pos.: 0
    CountLines: 1 <<< row 1 two times

    Form name: SubsampleDataSubModFrm
    RecordSource: SELECT subsampleData.* FROM subsampleData;
    KeyValue: 303567
    .Findfirst: [RecNo] = 303567
    Abs. Pos.: 0
    CountLines: 1 <<< Row 1, third time is a charm?

    Form name: SubsampleDataSubModFrm
    RecordSource: SELECT subsampleData.* FROM subsampleData;
    KeyValue: 303567
    .Findfirst: [RecNo] = 303567
    error position AP: 0
    Error 3059 Operation canceled by user. 16 ERROR!
    CountLines: + <<< ROW 1 Error on the 4th go round.

    Form name: SubsampleDataSubModFrm
    RecordSource: SELECT subsampleData.* FROM subsampleData;
    KeyValue: 303568
    .Findfirst: [RecNo] = 303568
    error position AP: 0
    Error 3059 Operation canceled by user. 16 ERROR!
    CountLines: + <-------- ALL THE REMAINING RECORDS FAIL - CANCELED BY USER???? REALLY???

    Form name: SubsampleDataSubModFrm
    RecordSource: SELECT subsampleData.* FROM subsampleData;
    KeyValue: 303569
    .Findfirst: [RecNo] = 303569
    error position AP: 0
    Error 3059 Operation canceled by user. 16 ERROR!
    CountLines: +

    Form name: SubsampleDataSubModFrm
    RecordSource: SELECT subsampleData.* FROM subsampleData;
    KeyValue: 303570
    .Findfirst: [RecNo] = 303570
    error position AP: 0
    Error 3059 Operation canceled by user. 16 ERROR!
    CountLines: +

    OTHER ODD BEHAVIOR:
    When in any row and I change the value in another field, the row number calculates correctly and the function runs once. however it errors on other rows (except row 1).

    -- change another field value in Row 3 ---
    Form name: SubsampleDataSubModFrm
    RecordSource: SELECT subsampleData.* FROM subsampleData;
    KeyValue: 305436
    .Findfirst: [RecNo] = 305436
    Abs. Pos.: 2
    CountLines: 3 <-- calculates correctly

    -- Move to another field in row 5 --- (errror when leaving record)
    Form name: SubsampleDataSubModFrm
    RecordSource: SELECT subsampleData.* FROM subsampleData;
    KeyValue: 305436
    .Findfirst: [RecNo] = 305436
    error position AP: 0
    Error 3059 Operation canceled by user. 16 ERROR!
    CountLines: +


    --- change another field value in row 5 --- (correct when changing values in record)
    Form name: SubsampleDataSubModFrm
    RecordSource: SELECT subsampleData.* FROM subsampleData;
    KeyValue: 305438
    .Findfirst: [RecNo] = 305438
    Abs. Pos.: 4
    CountLines: 5

    ---- Move to row 7---- (error when leaving row 5!)
    Form name: SubsampleDataSubModFrm
    RecordSource: SELECT subsampleData.* FROM subsampleData;
    KeyValue: 305438
    .Findfirst: [RecNo] = 305438
    error position AP: 0
    Error 3059 Operation canceled by user. 16 ERROR!
    CountLines: +

    --- change value in another field in row 7 --- (correct when changing value in row 7)
    Form name: SubsampleDataSubModFrm
    RecordSource: SELECT subsampleData.* FROM subsampleData;
    KeyValue: 305440
    .Findfirst: [RecNo] = 305440
    Abs. Pos.: 6
    CountLines: 7

    What am I missing here??? Any suggestions on where to look?

    Best

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Not sure why the function is called more than once. This is perhaps due to the way Access initializes a Form and its controls.

    What you could try would be to dynamically set the ControlSource property:
    Code:
    Private Sub Form_Current()
    
        If Me.txtfield.ControlSource = "" Then Me.txtfield.ControlSource = "GetLineNumber([Form],"RecNo",[RecNo])
    
    End Sub
    As far as the Function GetLineNumber() is concerned, I tested it in a different way:
    1. A parent Form + a SubForm.

    2. On the Parent form, a TextBox: Text1

    3. In the module of the SubForm:
    Code:
    Private Sub Form_Current()
    
        Me.Parent.Controls("Text1").Value = GetLineNumber(Me, "Numero", Me.NUMERO.Value)
        
    End Sub
    This works correctly provided that Me.NUMERO.Value Is Not Null, otherwise the returned value is meaningless.
    Have a nice day!

  3. #3
    Join Date
    Sep 2014
    Posts
    37
    Thanks!

    I tried your suggestion, and while I like it, it unfortunately left me with the exact same results. If it functioned well on your forms, then I'm guessing that this means I should be looking for the culprit that is causing it to re-evaluate the first record multiple times.

    suggestions?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    No suggestions at first sight. However, depending on why you need to call the function (i.e. for which purpose), you can perhaps use a workaround such as:

    1. Leave the TextBox unbound (ControlSource = "").

    2. Use this code:
    Code:
    Private Sub Form_Current()
    
        Me.txtfield.Value = GetLineNumber([Form],"RecNo",[RecNo])
    
    End Sub
    Have a nice day!

  5. #5
    Join Date
    Sep 2014
    Posts
    37
    I am not sure the order of events in this scenario, but when i move from one record to the next the function is triggered, and when i change the count field the function is triggered.

    Main form
    - ctl1
    - ctl2
    ...etc
    - subform ctl
    -subform - form view continuous record
    Row1
    - subctl1 - txtRowNumber
    - subctl1 - txtCount
    - subctl1 - TxtLS
    Row2
    - subctl1 - txtRowNumber
    - subctl1 - txtCount
    - subctl1 - TxtLS
    Row3
    - subctl1 - txtRowNumber
    - subctl1 - txtCount
    - subctl1 - TxtLS

    What are the order of events that happen when i change a value in the txtCount field that would trigger the txtRowNumber to reevaluate.?

  6. #6
    Join Date
    Sep 2014
    Posts
    37
    Quote Originally Posted by Sinndho View Post
    No suggestions at first sight. However, depending on why you need to call the function (i.e. for which purpose), you can perhaps use a workaround such as:

    1. Leave the TextBox unbound (ControlSource = "").

    2. Use this code:
    Code:
    Private Sub Form_Current()
    
        Me.txtfield.Value = GetLineNumber([Form],"RecNo",[RecNo])
    
    End Sub
    In this case... every row gets the same value, that of the current row that has focus.

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by slynde View Post
    In this case... every row gets the same value, that of the current row that has focus.
    True ! That's why I wrote "depending on why you need to call the function"

    As for the order of events, Access help provides the following information:
    KeyDown --> KeyPress --> BeforeInsert --> Change --> KeyUp --> BeforeUpdate --> AfterUpdate
    And the GotFocus and LostFocus events must happen somewhere too. That's the problem when you assign a function to the RecordSource property of a control: you cannot control how and when (i.e. following which event(s)) Access will call the function.

    If you're using the function on a (sub)form in continuous or datasheet view, you could perhaps use a query, in which you add an extra column which makes calls to the function, as the RecordSource of the form. All you need is to place the function in a standard module, declare it Public, use only Variant values and make it able to handle Null parameters (it must return Null in such a case):
    Code:
    Public Function GetLineNumber(F As Form, KeyName As Variant, KeyValue As Variant) As Variant
    Have a nice day!

  8. #8
    Join Date
    Sep 2014
    Posts
    37

    UPDATE...sort of fixed??

    I appreciate all the help. The problem seems to have "kind of" corrected itself.

    from original post:
    On a Form Record with multiple subrecords:

    The function appears to be called generally three times (sometimes more) and always fails if called more than three times for the first record. EVERY other record fails with an error 3059. (See partial debug output below)
    Here's what I learned.... restart access.

    After a reboot and restart, this function appears to be both working and much faster. YEAH!

    However, if I go into edit the module, or open VB at all, then the function fails going forward.

    It requires a complete shutdown/restart of Access to function properly (I have to make sure there are no other access processes running).

    It breaks at the first calling of .FindFirst

    Any ideas on why this might be happening?

    Thanks

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you have odd unexplainable errors it could be a sign of corruption within the db
    what you could do is ON A COPY of the db do a compact and repair, see if that makes any difference.
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If it's not already don, I would first decompile the project, then recompile it:
    1. Backup.
    2. Exit Access.
    3. From the command line or a batch file:
    Code:
    <path to Access.exe> <Path to the Access project (.mdb or .accdb)> /decompile
    e.g.
    Code:
    "C:\Program Files\Microsoft Office\OFFICE14\msaccess.exe" "C:\SinnDHo\Access\Reclamations\Credentials.mdb" /decompile
    4. Open the VBA IDE (Ctrl+G) then Compile all.
    5. Compact and repair.
    6. Backup.

    If it does not solve the problem, you can create a new project and import all objects from the existing one.

    If it does not solve the problem, you can also try to export then re-import all objects in the project usin the Application.SaveAsText and Application.LoadFromText undocumented methods.

    Example for exporting:
    Code:
    Private Sub dExportForms()
    
        Const c_SQL As String = "SELECT [Name] FROM MSysObjects WHERE [Type]=-32768;"
        
        Dim rst As DAO.Recordset
        
        Set rst = CurrentDb.OpenRecordset(c_SQL, dbOpenSnapshot)
        With rst
            Do Until .EOF
                Application.SaveAsText acForm, !Name, CurrentProject.Path & "\Documentor\" & !Name & ".txt"
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
    
    End Sub
    
    Private Sub dExportModules()
    
        Const c_SQL As String = "SELECT [Name] FROM MSysObjects WHERE [Type]=-32761;"
        
        Dim rst As DAO.Recordset
        
        Set rst = CurrentDb.OpenRecordset(c_SQL, dbOpenSnapshot)
        With rst
            Do Until .EOF
                Application.SaveAsText acModule, !Name, CurrentProject.Path & "\Documentor\" & !Name & ".txt"
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
        
    End Sub
    
    Sub ExportObjects()
    
        dExportForms
        dExportModules
        dExportReports
        
    End Sub
    
    Private Sub dExportReports()
    
        Const c_SQL As String = "SELECT [Name] FROM MSysObjects WHERE [Type]=-32764;"
        
        Dim rst As DAO.Recordset
        
        Set rst = CurrentDb.OpenRecordset(c_SQL, dbOpenSnapshot)
        With rst
            Do Until .EOF
                Application.SaveAsText acReport, !Name, CurrentProject.Path & "\Documentor\" & !Name & ".txt"
                .MoveNext
            Loop
            .Close
        End With
    
    End Sub
    For re-importing, you'll need to create a list of the objects that were exported or, if everything was exported to a single folder, use the Dir() function to sequentially re-import everything.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •