Function freezes Dbase (1 Viewer)

[Ron_dK]

I have converted a macro to a VB function.
I call the fuction by means of clicking on a lable in a form ( on click = event procedure) and this seems to run fine.
However, once the function has finished, the Dbase freezes. I can’t open or close forms, reports whatsoever and have to force the Dbase to close to start all over.
Anyone has any clue as why this happens ?

 

[Rich]

Post the function

 

[RuralGuy]

What version of Access and what OS? Can you post the complete code for the function?

 

[Ron_dK]

I'm running Access 2003 in Windows XP.

Here's the function procedure :

Function Import_schedreq()
On Error GoTo Import_schedreq_Err

    DoCmd.Echo False, ""
    DoCmd.SetWarnings False
    DoCmd.OpenTable "TbSchedREQ-import", acViewNormal, acEdit
    DoCmd.RunCommand acCmdSelectAllRecords
    DoCmd.RunCommand acCmdDeleteRecord
    DoCmd.Close acTable, "TbSchedREQ-import"
    DoCmd.OpenQuery "test22link Query", acViewNormal, acEdit
    DoCmd.RunCommand acCmdSelectAllRecords
    DoCmd.RunCommand acCmdCopy
    DoCmd.Close acQuery, "test22link Query"
    DoCmd.OpenTable "TbSchedREQ-import", acViewNormal, acEdit
    DoCmd.RunCommand acCmdPasteAppend
    DoCmd.Close acTable, "TbSchedREQ-import"
    DoCmd.OpenQuery "QrySchedreq-SchedImport", acViewNormal, acEdit
    DoCmd.RunCommand acCmdSelectAllRecords
    DoCmd.RunCommand acCmdCopy
    DoCmd.OpenTable "TbSchedRequest", acViewNormal, acEdit
    DoCmd.RunCommand acCmdPasteAppend
    DoCmd.Close acTable, "TbSchedRequest"
    DoCmd.Close acQuery, "QrySchedReq-SchedImport"
    Beep
    MsgBox "You have transfered all new records from Sched Import table to the SchedReq table", vbInformation, ""


Import_schedreq_Exit:
    Exit Function

Import_schedreq_Err:
    MsgBox Error$
    Resume Import_schedreq_Exit

End Function

and this is the event procedure on the label :

Private Sub Label0_Click()
Call Import_schedreq
End Sub

 

[RuralGuy]

Since your "Function" does not return a value, it should really be a Sub. I would comment out the last MsgBox. Create a new one with just a message as a test. I take it you get that message which makes you feel the function completed? Have you single stepped the code to see if it still hangs up? Put a breakpoint toward the end of the code and finish the sub by hand with F8.

 

[WayneRyan]

RAK,

I'd try it with the warnings on (plus you don't reset to True).
Do you get all expected data into [TbSchedREQ-import]?

Another way of looking at it ...

Function Import_schedreq()
On Error GoTo Import_schedreq_Err

    DoCmd.SetWarnings False
    DoCmd.RunSQL "Delete From [TbSchedREQ-import];"
    DoCmd.RunSQL "Insert Into [TbSchedREQ-import] " & _
                 "Select * From [test22link Query];"
    DoCmd.RunSQL "Insert Into [TbSchedREQ-import] " & _
                 "Select * From [QrySchedreq-SchedImport];"
    MsgBox "You have transfered all new records from Sched Import table to the SchedReq table", vbInformation, ""
    DoCmd.SetWarnings True

Import_schedreq_Exit:
    Exit Function

Import_schedreq_Err:
    MsgBox Error$
    Resume Import_schedreq_Exit

End Function

Wayne

 

[ChrisO]

Import_schedreq_Exit:
DoCmd.Echo True, ""
Exit Function

 

[RuralGuy]

Way to go Chris. Of course! Never turned the Echo back on! <slaps forehead>

 

[ChrisO]

Naughty Macro.

 

[RuralGuy]

It needs to go stand in the corner for a while.

 

[Ron_dK]

Since your "Function" does not return a value, it should really be a Sub.

Sorry for my ignorance, but why would it be a sub and how would I change
the function to a sub ?

Have you single stepped the code to see if it still hangs up? Put a breakpoint toward the end of the code and finish the sub by hand with F8.

Have done that, but didn't get any errors or strange returns.

Another way of looking at it ...

Function Import_schedreq()
On Error GoTo Import_schedreq_Err

    DoCmd.SetWarnings False
    DoCmd.RunSQL "Delete From [TbSchedREQ-import];"
    DoCmd.RunSQL "Insert Into [TbSchedREQ-import] " & _
                 "Select * From [test22link Query];"
    DoCmd.RunSQL "Insert Into [TbSchedREQ-import] " & _
                 "Select * From [QrySchedreq-SchedImport];"
    MsgBox "You have transfered all new records from Sched Import table to the SchedReq table", vbInformation, ""
    DoCmd.SetWarnings True

Import_schedreq_Exit:
    Exit Function

Import_schedreq_Err:
    MsgBox Error$
    Resume Import_schedreq_Exit

End Function

Wayne

I tried this Wayne, but getting loads of errors , e.g. fields in TbSchedREQ-import are not recognized. However I will test your code once more and see if I can get it working.

Import_schedreq_Exit:
DoCmd.Echo True, ""
Exit Function

I added this to the initial function and the whole thing is running like a charm.

Thanks to all of you.

 

[RuralGuy]

Sorry for my ignorance, but why would it be a sub and how would I change the function to a sub ?

Simply changing the word Function to Sub will change it to a SubRoutine. A Function returns a value to the calling routine and a SubRoutine does not. Since your code does not return a value, it should be a Sub Routine rather than a Function. It is now left up to Access to adjust the stack for the expected returned value. Glad you got it working. Chris has some sharp eyes!

 

[Ron_dK]

Simply changing the word Function to Sub will change it to a SubRoutine. A Function returns a value to the calling routine and a SubRoutine does not. Since your code does not return a value, it should be a Sub Routine rather than a Function. It is now left up to Access to adjust the stack for the expected returned value.

RG,

I changed the routine from function to Sub and tested it. There is no significant difference in the performance of the procedure.
I think I understand what you're saying about returning value ( or not) and relation to function or Sub, but I'm not sure about :

It is now left up to Access to adjust the stack for the expected returned value.

Could you elaborate on that.

Cheers, Ron

 

[RuralGuy]

I would not have expected any difference in performance by changing your code to a Sub. It is simply the proper coding technique. Functions in Access return values so Access sets them up to do so: [YourVariable = Int("1234")] Since Access is aware that the Int() function returns a value, it generates an error if you just put Int("1234") without the assignment function "=". This is not the case with User Defined Functions (UDFs). Access tries to help here by "absorbing" any extra returned values. I suspect that if you put Access in a loop, you could get it to choke in this case. In any event, using the correct procedure name for you code is simply proper coding. Much easier to understand years later when the code is being maintained by someone else.

 

[Ron_dK]

In any event, using the correct procedure name for you code is simply proper coding. Much easier to understand years later when the code is being maintained by someone else.

Makes sense. Thanks for that.