Database quits for no apparent reason! (1 Viewer)

[Cavman]

Morning!

I'm having a weird problem in VBA on a button click. For no apparent reason the database closes itself. No error, no crash - it just closes. I've compacted and repaired it, recompiled it, but still exactly the same. The strange thing is, if I insert a blank message box at the point in the code where it quits, everything then works perfectly! Also, if you leave Text4 empty so that it displays the message box telling you to enter a code to start, when you then enter something the next time it also works perfectly. Somehow the process of displaying the message box seems to fix whatever the problem is.
Can anyone suggest what might be happening? I've tried pausing the code at that point, I've tried changing the focus to a different control or another form. I've run out of ideas!

Here's the code:

 Private Sub cmdReqLookup_Click()

Dim strTaleoReq As String
Dim strTaleoID As String
Dim intIDMatches As Integer
Dim strContractID As String
Dim strEmployeeName As String
Dim vbRed As Double
Dim vbBlack As Double

vbRed = RGB(255, 0, 0)
vbBlack = RGB(0, 0, 0)

strTaleoReq = Nz(Me.Text4, "")
If strTaleoReq = "" Then
    MsgBox "Please Enter a Taleo Req to start!", vbCritical, "No ID"
    Exit Sub
End If

'This is where the database quits. If I add a message box here, it then runs perfectly!

'Disable button
Me.cmdReqLookup.Caption = "Please wait..."
Me.cmdReqLookup.ForeColor = vbRed
Me.cmdReqLookup.Enabled = False
Me.Refresh

ScriptHold (1)

' Count how many ID's there are match the Req entered
intIDMatches = DCount("[ContractID]", "QuerySECIDa")

If intIDMatches < 1 Then
    MsgBox "There are currently no matches for this Req in the database. Please check it carefully and ensure there are no extra spaces. If it still isn't recognised, please check the request has been processed correctly."
    Me.cmdReqLookup.Caption = "Lookup"
    Me.cmdReqLookup.ForeColor = vbBlack
    Me.cmdReqLookup.Enabled = True
    Exit Sub
End If
If intIDMatches > 1 Then
    ' Get from ID selector
    strTaleoID = IDSelector
Else
    ' Lookup Taleo ID and display
    strTaleoID = DLookup("[TaleoID]", "QuerySECIDa")
End If

Me.Text6 = strTaleoID

' Lookup Contract ID and display
strContractID = DLookup("ContractID", "QuerySECID")
Me.Text9 = strContractID

' Lookup Employee Name and display
strEmployeeName = DLookup("Fullname", "QuerySECID")
Me.Text11 = strEmployeeName

Me.cmdReqLookup.Caption = "Lookup"
Me.cmdReqLookup.ForeColor = vbBlack
Me.cmdReqLookup.Enabled = True

End Sub

If anyone could make any suggestions I'd be really grateful.

Thanks,

Cavman

 

[pr2-eugin]

What is the function ScriptHold do? Probably the function is causing the problem not the code you have presented, have a look there. Try Stepping through the Code.

 

[Cavman]

Scripthold is a simple timer routine to slow down the code and allow the form to update. If I remove the Scripthold line it doesn't make any difference. Here's the code:

Private Sub ScriptHold(PauseTime)

Dim Start
Start = Timer
Do While Timer < Start + PauseTime
DoEvents
Loop

End Sub

It's used right through the database and it's never caused problems anywhere else, so I can't see why it would here. Must be something else...

 

[JHB]

Do what pr2-eugin suggest.
Else try to import all into a new created database.

 

[Cavman]

I'll try moving everything into a new database. I'm using Access 2010. Is there an easy way to import everything at once, or do I need to import everything individually?

 

[CJ_London]

Trying to understand what you are saying. Are you saying the lines

strTaleoReq = Nz(Me.Text4, "")
If strTaleoReq = "" Then
MsgBox "Please Enter a Taleo Req to start!", vbCritical, "No ID"
Exit Sub
End If

is only there for testing purposes and the code fails if that code is not there?

 

[pr2-eugin]

Create a new DB, then use the External Data tab, in which you have the option to import.

Before that, have you tried Compact & Repair, if failed, a Decompile then a Compact & Repair?

 

[Cavman]

Hi CJ_London,

No, that piece of code is there permanently. It checks to make sure the user has entered a reference code before proceeding. What I'm saying is, if the user doesn't enter a code, that piece of code displays the message, and then exits the click routine. If the user then enters a code and clicks again, the rest of the code then runs perfectly. For some reason it seems that the process of displaying the message box fixes something on the form so that the code can then run. Weird!

Yes, I've run a compact and compare and I've also decompiled the database. It didn't change anything!

I'm just importing everything into a new DB, so let's see if that works...

 

[CJ_London]

Is there an easy way to import everything at once, or do I need to import everything individually?

You can do either - just go to the external data and select Access then choose what you want to import.

If this is a front end, I would relink the tables rather than import linked tables - I tend to do it in stages, compacting each time:

tables
queries
modules
forms

You can also try decompiling your existing db to get rid of any unecessary code - copying the db as above will not do this

To decompile do the following:

1. create a short cut to the MS Access .exe - something like the below

"C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE"

2. then edit the shortcut and add /decompile to the target so you end up with

"C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE" /decompile

Note the space between the final " and the /

3. Now open access by clicking on the shortcut and then open your db. It will be opened in a decompiled state.

4. Finally compact the db

Note: suggest take a copy of your db before decompiling it just to be on the safe side, although I've never had a problem. You will also see how your db is now much smaller (compared with a previously compacted 'old' db)

 

[CJ_London]

It checks to make sure the user has entered a reference code before proceeding. What I'm saying is, if the user doesn't enter a code, that piece of code displays the message, and then exits the click routine. If the user then enters a code and clicks again, the rest of the code then runs perfectly

I'm having one of those mornings So what is not working? Are you saying the msgbox works if it is blank and fails if you have a message? Or are you saying if the user enters something the first time round (so the message is not displayed) it then fails?

 

[Cavman]

Ok, so I've imported everything into a new database, decompiled it, compacted and repaired it and relinked all the tables from the back end.

Still no joy

 

[pr2-eugin]

Just step through the code !

 

[Cavman]

CJ_London - lol - the second thing you said. If no message box is displayed then the database quits. If I add a messagebox in, or a messagebox is displayed by the piece of code, everything then runs fine!

 

[Cavman]

Ok - if I step through, it all works perfectly. If I shift-open the database and manually run the form, it works perfectly. If I run the database and it opens the form itself, it doesn't work.

 

[CJ_London]

might be helpful if you rename your fields something meaningful rather than Text4 etc.

What are you actually entering in the Text4 box? Presumably something that is referenced in QuerySECIDa as a criteria?

It sounds like perhaps a variable has not been initialised - for example IDSelector

 

[JHB]

Try by putting in a DoEvents.

...
'This is where the database quits. If I add a message box here, it then runs perfectly!

'Disable button
[B][COLOR=Red]DoEvents[/COLOR][/B]
Me.cmdReqLookup.Caption = "Please wait..."
Me.cmdReqLookup.ForeColor = vbRed
Me.cmdReqLookup.Enabled = False
Me.Refresh
...

 

[Cavman]

Right, I think I've fixed it. But still not sure why!

Sorry about the nondescript textbox names - I didn't build the original database, I've inherited it - you wouldn't find any Text4's if I'd built it! lol.

I did try a DoEvents right where you suggested, but it didn't work.

What I've just done is removed the me.Refresh after the bit that disables the button. For some reason this fixes the issue and everything now works. But I'm still confused!!! Why would that change anything?

 

[BlueIshDan]

Try this just for my satisfaction:

    If strTaleoReq = "" Then
        MsgBox "Please Enter a Taleo Req to start!", vbCritical, "No ID"
        
        ScriptHold (1)
        Exit Sub
    End If

NVM YOU DON'T NEED ME!

 

[Cavman]

I tried that for you BlueIshDan, doesn't change anything.

Problem I have now is that when the button is disabled and the caption changed to 'please wait...', that doesn't actually display until after the queries have run. That's why I needed the Me.Refresh in there!

 

[Cavman]

I tried using a Me.Repaint instead of Me.Refresh, but that causes the quitting problem again. It has to be some kind of bug within Access, surely? It's really frustrating to get stuck on something so simple when I have a whole load much more complex work to complete on the database!!