locked
"GetLineNumber" code on subform not working today RRS feed

  • Question

  • Hi,

    I've been working on different code over the past couple of days, finally figured that code out, compiled my database and solved a few bugs.  Now when I open my main form which has a subform listing rows of products, pricing, etc., the code below that has worked for years, produces an error.   The problem line is marked by "==>" below.  Any suggestions will be greatly appreciated.

    -Colleen

    Option Compare Database
    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 DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, DB_DOUBLE, DB_BYTE
    ==>               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

             ' 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

    Wednesday, August 10, 2016 2:52 PM

Answers

  • I can reproduce the problem. The reason that the error shows on your machine and not on others is that you have set "Break on all errors" in the VBA editor. If you change this to "Break on unhandled errors", it will disappear.

    But instead you could try to fix the problem at the source: Perhaps the fastest way around it is to modify the control source of textbox "lblLineNumber" to

      =GetLineNumber([Form];"OrderItemID";Nz([OrderItemID];0))

    (you may have to replace the semicolons with commas to get it to work)

    > Unfortunately, no, no little green triangle

    That's good, that's not unfortunate. 

    Matthias Kläy, Kläy Computing AG

    P.S. I should ask for "consulting fees" You could click on "Propose as answer" if a reply solves your problem :-)

    • Marked as answer by Kellygirl1533 Wednesday, August 10, 2016 7:14 PM
    Wednesday, August 10, 2016 6:42 PM

All replies

  • What is the exact error number and description?

    I would suspect that KeyValue passed to the function is NULL, because it is a variant.

    Matthias Kläy, Kläy Computing AG

    Wednesday, August 10, 2016 3:01 PM
  • Please elaborate some: What does this "problem line" cause?

    Best regards, George

    Wednesday, August 10, 2016 3:05 PM
  • Hi Matthias, again,

    Error message is:  "Run-time error 3077, Syntax error (missing operator) in expression"

    Yes, when I hover over KeyValue it does show NULL.

    I just opened this db on another computer and I am not getting that error.  On my computer the line number field shows "ERROR", however, on the other computer it shows "0" as it always has in the past.

    -c

    Wednesday, August 10, 2016 3:22 PM
  • Are you for real?

    Best regards, George

    Wednesday, August 10, 2016 3:24 PM
  • Are you saying that you have two distinct copies of the database on two different computers -- one behaving normally and the other suddenly acting funny?   Have you ruled out the possibility of corruption in the one that is showing the error?

    Try compact/repairing the problematic database, or importing all objects into a new database, or replacing the problematic database with a known good back up and see if any of those helps resolve the issue.


    Miriam Bizup Access MVP

    Wednesday, August 10, 2016 4:35 PM
  • Hi Miriam,

    No, the db is run from a server.  I did compact and repair, I did open 3 different backup and they all give the same error (and they did not yesterday).  For now (so I can get some work done),  I removed the control from the subform that contains the call code.  I just cannot for the life of me figure out why it works on my assistant's computer and not mine - both run Office 13.

    Am going to try importing all objects to a new db shortly.

    Thanks for the response.

    -c

    Wednesday, August 10, 2016 4:42 PM
  • This looks like an error in the control source of the control, e.g. the field is not contained in the record source of the form.

    Just to make sure: This is the same application as before, the .accdb file without external data connections that I have downloaded? On which form does the error occur?

    Have you turned on Error Checking in Options / Object Designer?

    If you put the form in design mode, does it show a little green triangle in the upper left corner? If yes, select the field and click on the !-icon to get an explanation for the error.

    Matthias Kläy, Kläy Computing AG

    Wednesday, August 10, 2016 4:45 PM
  • Hi Matthias,

    Yes, same application.  Form is called:  frmPurchaseOrdersSubItems which is a subform in frmPurchaseOrdersFY17.

    From the main screen that opens, click on the "FY17 Purchase Order" button.  If there are rows in the subform, I get the message.  If there no rows in the subform yet, the error doesn't happen.

    Yes, "enable error checking" is selected in Object Designer as well as "Break on all Error's in Tools|Options|General (code window).

    Unfortunately, no, no little green triangle.

    Best-

    Colleen

    Wednesday, August 10, 2016 5:24 PM
  • Be sure both machines are up to date with respect to Windows updates.  Could be that one has an update relevant to Access that the other machine does not.

    -Bruce

    Wednesday, August 10, 2016 6:38 PM
  • I can reproduce the problem. The reason that the error shows on your machine and not on others is that you have set "Break on all errors" in the VBA editor. If you change this to "Break on unhandled errors", it will disappear.

    But instead you could try to fix the problem at the source: Perhaps the fastest way around it is to modify the control source of textbox "lblLineNumber" to

      =GetLineNumber([Form];"OrderItemID";Nz([OrderItemID];0))

    (you may have to replace the semicolons with commas to get it to work)

    > Unfortunately, no, no little green triangle

    That's good, that's not unfortunate. 

    Matthias Kläy, Kläy Computing AG

    P.S. I should ask for "consulting fees" You could click on "Propose as answer" if a reply solves your problem :-)

    • Marked as answer by Kellygirl1533 Wednesday, August 10, 2016 7:14 PM
    Wednesday, August 10, 2016 6:42 PM
  • Oh, wow, now that you say that, my machine updated a bunch of things this morning.  :-(

    The other computer did not.

    I would guess that's probably the problem - or at least part of it. 

    THANKS!

    -c

    Wednesday, August 10, 2016 6:44 PM
  • Did you see my reply above about the Break on all errors option?

    Matthias Kläy, Kläy Computing AG

    Wednesday, August 10, 2016 7:16 PM
  • Hi Matthias,

    Yes, you deserve consulting fees for sure.  And the "unfortunate" part of the little green triangle not being there ... I know how to fix that all by myself. 

    Thanks again for your help.  Works like a dream!

    Wednesday, August 10, 2016 7:16 PM
  • Hi Matthias,

    Yes, I did see it.  I left it on until I fixed the control source as you suggested.  Everything ran without errors (yes, I had to use commas).  Now I've set it back to "Break on unhandled errors".

    Thanks again!

    Wednesday, August 10, 2016 7:19 PM