Need help with a random error

[Zaeed]

Hi,

I have a load module which pulls a recordset, then loads a form from.

Every now and then, very randomly, when i launch my db (still in development) it gives me an error saying that there are no records.

This a portion of the code

 Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
    
    With rst
        .MoveLast                           
        If .RecordCount > 1 Then
            .MoveFirst

The .MoveLast is the line that gets highlighted by the debugger.

This is quite strange, as this module isn't even being called on launch.

Any help would be appreciated

*EDIT* Ok, i've just put a msgbox at the begining of the module, and it is being run whenever i launch my main menu. (The begining of my db) Is there some way to stop this? The problem is that the rst in the above code is created by a sql statement that has a WHERE statement which shouldn't be set yet, and I have initialation code setting that value to 0. So when the module runs without being called it is setting the recordset to NULL essentially.

so basically i need to stop the module running unless it is actually being called

 

[KeithG]

The problem is that you can not move to the last record because there are no records in the recordset. I would add a check to see if records exist.

If rc.Eof =true and rc.BOf= true then exit sub

 

[Zaeed]

Yeah, your right keith, that is the issue. However it would be easier to fix this if I was able to force the module to load ONLY when it is called. The issue is that it is loading by itself initially, which is an issue since not all values have been set yet. Kind of like a car trying to drive itself without the wheels attatched (or something like that)

*EDIT* btw your solution works well.

 

[boblarson]

The big question is how are you setting the values that the recordset is dependent upon?

 

[Zaeed]

Via a sql statement.

The function is called in a form, with the required parameters being passed into the function call.

This is the function header.

Public Function formLoad( _
                        strSQL As String, _
                        rst As DAO.Recordset, _
                        frmTarget As Form, _
                        fldAbrv As String _
                           )

This is going to be long.

Ok, whats happening is that the value of strSQL is being passed in somehow, but with a WHERE reference that sets the recordset to NULL.. What i can't figure out is why the module is running by itself.

It seems to be holding on to the value of strSQL, which i again cant figure out.

Here is the code located in my form that loads the form.

Private Sub Form_Load()
    
    If Not newChange Then
        Dim strSQL As String
        strSQL = "SELECT tbl_Change.Title, tbl_Change.Initiator, "
        strSQL = strSQL & "tbl_Change.Date_Start , tbl_Change.Date_Finish, tbl_Change.Description, "
        strSQL = strSQL & "tbl_Change.Change_Nature , tbl_Change.Change_Type, tbl_Change.Status, "
        strSQL = strSQL & "tbl_Change.Primary_Location , tbl_Change.Secondary_Location, "
        strSQL = strSQL & "tbl_Change.Safety_Impact , tbl_Change.FoodSafety_Impact, tbl_Change.Quality_Impact, "
        strSQL = strSQL & "tbl_Change.Environment_Impact , tbl_Change.HR_Impact, tbl_Change.Inform_STC, "
        strSQL = strSQL & "tbl_Change.Inform_Environment , tbl_Change.[Inform_Q&FS], "
        strSQL = strSQL & "tbl_Change.Inform_HR_Advisor , tbl_Change.Inform_Project_Management, "
        strSQL = strSQL & "tbl_Change.Inform_Technical_Manager , tbl_Change.Inform_Operations_Manager, "
        strSQL = strSQL & "tbl_Change.Inform_Maintenance , tbl_Change.Inform_FLL, tbl_Change.Inform_PL, "
        strSQL = strSQL & "tbl_Change.Inform_EHS_Manager, tbl_Change.Inform_Manufacturing_Area_Manager"
        strSQL = strSQL & " FROM tbl_Change WHERE (tbl_Change.ChangeNumber)=" & GetChangeNumber() & ""
    
        If Not formLoad(strSQL, rs_Change, Me, "frm_Change_") Then
        
        'form not loaded
    
         End If
         
         Me.RecordSource = strSQL
        
         Me.Requery
         
    Else
        Me.RecordSource = ""
        Me.frm_Change_Initiator.Value = UserName
    End If
    
    Me.frm_Change_ChangeNumber.Caption = GetChangeNumber()

End Sub

this would be so much easier to convey if i could talk

 

[boblarson]

What I was meaning was What EVENT are you calling this on?

 

[Zaeed]

The function gets called on the Change Forms load sub i.e. Private Sub Form_Load()

This is the line of code which runs my generic load/save module

If Not formLoad(strSQL, rs_Change, Me, "frm_Change_") Then

formLoad is a function in the load/save module

it returns a boolean

 

[boblarson]

Sorry to be such a bother, but can you post the entire formLoad() code?

 

[Zaeed]

not a problem.

Public Function formLoad( _
                        strSQL As String, _
                        rst As DAO.Recordset, _
                        frmTarget As Form, _
                        fldAbrv As String _
                           ) As Boolean
    
    On Error GoTo err_formLoad
    
    Dim fld As DAO.Field
    Dim strLog As String
    Dim fieldValue As String
    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
    
    If rst.EOF = True And rst.BOF = True Then Exit Function  'current fix to problem
    
    With rst
        .MoveLast                           'populate recordset

        If .RecordCount < 1 Then               'verify ONE record

            formLoad = False                 'and if not,  prepare error return
            MsgBox ("NO RECORDS")
            rst.Close                           'and close the recordset
            GoTo exit_formLoad               'and exit
        End If                              'otherwise
        For Each fld In .Fields             'fill the form
           fieldValue = fldAbrv & fld.Name  'append identification to control
           
            frmTarget.Controls.Item(fieldValue) = fld.Value

        Next
    End With
    
    formLoad = True                      'return the recordset to caller
    
    
exit_formLoad:                   'housekeeping
    On Error Resume Next
    Set fld = Nothing
    Exit Function
    
err_formLoad:
    formLoad = False             'the error return
    strLog = "formLoad " & Err.Number & ": " & Err.Description

    Resume exit_formLoad
    
End Function

*EDIT* um, just noticed it now.. would removing the .MoveLast fix the issue? since i already have code checking for no records..

i feel stupid

**EDIT** Ok, so i think i have that figured.. However, there still remains the issue of the module running itself.. I do not beleive that this is the modules fault. When i first posted, I put a msg box in the module, and every time i started my main menu the message box came up, followed by the error. The message box doesnt come up now, even though its before the no record checks. Meaning the module isn't being run like it was earlier.. Nothing else has changed.

I once had a similar problem when coding in java, where the issue was how the operating system handled the code. It jumped into the code at random points, which sometimes caused errors, is this the same sort of thing?

 

[boblarson]

**EDIT** Ok, so i think i have that figured.. However, there still remains the issue of the module running itself.. I do not beleive that this is the modules fault. When i first posted, I put a msg box in the module, and every time i started my main menu the message box came up, followed by the error. The message box doesnt come up now, even though its before the no record checks. Meaning the module isn't being run like it was earlier.. Nothing else has changed.

I once had a similar problem when coding in java, where the issue was how the operating system handled the code. It jumped into the code at random points, which sometimes caused errors, is this the same sort of thing?

Not sure what could be happening there but that's why I wanted it all so I could see if something was doing it. What you might do is to also use the FIND feature in the VBA window and input formLoad and select ENTIRE PROJECT for the search option and then see if it is being called, by accident, from somewhere you aren't expecting.