Access crash

[JennyL]

Hi,

I am experiencing crash after crash, no matter what I do. I recreated a new database from scratch and it still crashes. I have a feeling is due to the form_load vba code before. The following code works but I have a feeling something is causing the database to constantly crash. Does anyone have any recommendations? Thank you!


Private Sub Form_Load()
Dim Security As Integer
Me.txtuser = Environ("Username")
If IsNull(DLookup("userSecurity", "QryUser", "[userLogin] = '" & Me.txtuser & "'")) Then
MsgBox "No User Security is set up for this user. Please contact the Admin", vbOKOnly, "Login Info"
'Disable Adminpage button if the user does not have the security level set up


Me.NavbuttonAdm.Enabled = False
Me.NavbuttonCM.Enabled = False
Me.navbuttonCS.Enabled = False
Me.NavibuttonRpt.Enabled = False

Else
'Assign a security level number to variable Security
Security = DLookup("Usersecurity", "QryUser", "[UserLogin] = '" & Me.txtuser & "'")
If Security = 1 Then
Me.NavbuttonAdm.Enabled = True
End If

If Security = 2 Then
Me.NavbuttonAdm.Enabled = False
End If

If Security = 3 Then
Me.NavbuttonCM.Enabled = False
Me.NavbuttonAdm.Enabled = False
End If

End If
End Sub

[ranman256]

none of this code should crash access.
I had this problem with 1 pc. The PC was the problem, not access.
Compacting, rebuilding, etc, did nothing. Running the exact same db on another PC had NO problems.
Turns out, something on the PC ,Access does not like.
our solutions was to swap PCs. It worked fine after.

[JennyL]

none of this code should crash access.
I had this problem with 1 pc. The PC was the problem, not access.
Compacting, rebuilding, etc, did nothing. Running the exact same db on another PC had NO problems.
Turns out, something on the PC ,Access does not like.
our solutions was to swap PCs. It worked fine after.

Thanks Ranman256.

Are there common issues that could cause access to crash? such as autoname etc, that I should avoid. How about security warnings? Every time after crash it automatically creates a backup copy for me and it always comes up with security warning.

Since now my copies have crashed multiple times, does it mean my database is corrupted now? Do I need to re-create from a new pc and then split it from there? Will I still have this issue after I split the database?

I am starting to get worried that users will experience this issue after I deploy the database.

[RayMilhon]

Jenny I would like to make 1 suggestion on your code. Don't think it will help your crashing but it will make the code more efficient

Change

'Assign a security level number to variable Security
 Security = DLookup("Usersecurity", "QryUser", "[UserLogin] = '" & Me.txtuser & "'")
 If Security = 1 Then
 Me.NavbuttonAdm.Enabled = True
 End If

 If Security = 2 Then
 Me.NavbuttonAdm.Enabled = False
 End If

 If Security = 3 Then
 Me.NavbuttonCM.Enabled = False
 Me.NavbuttonAdm.Enabled = False
 End If

To

'Assign a security level number to variable Security
 Security = DLookup("Usersecurity", "QryUser", "[UserLogin] = '" & Me.txtuser & "'")
  Select Case Security
         Case is = 1
                   Me.NavbuttonAdm.Enabled = True
         Case is = 2 
                  Me.NavbuttonAdm.Enabled = False
         Case is = 3 
                 Me.NavbuttonCM.Enabled = False
                Me.NavbuttonAdm.Enabled = False
   End Select

For what you're doing the Select statement is more efficient.

[CJ_London]

Nothing wrong with your code on the face of it - so a number of thoughts/suggestions

I note you are using 64bit Access - was the db originally written for 32 bit access? and if so are you now a) linking to the relevant 64bit libraries? b) checked for missing libraries? and c) does your code use API's and if so have they been modified to work with 64bit?

Also, when you say it crashes, what does that mean? are you getting any error messages? does access drop through to the code? freeze? closes?

Have you tried stepping through the code to see if it fails on a particular line? Or does it fail before the first line of code is run?

I presume the username does not contain any characters that would cause the dlookup to fail (such as a quotation marks)

Also that in all cases the usersecurity field is populated with 1, 2 or 3 (otherwise none of your ifs will do anything with the buttons which may have a knock on effect and create an error later on)

You dlookup is referencing a query - does that query rely in any way on the form you are loading or perhaps a form that is not open?

Have you tried compiling your code from the VBA debug dropdown?

Do you have Option Explicit at the top of all modules (on the line below Option Compare Database). If not, put it in all modules and compile to detect potential coding errors

Finally have you temporarily disabled any error handling in other events (I can see you don't have any in the load event) so you are not hiding potential error messages?

[JennyL]

I checked my pc's system properties and it says 64 bit operating system. how do I know which library should be checked in the access? I don't use API so don't know how that works. I am building a brand new database off my work pc and it says 64bit, I have tried on a different pc and it still crashes.

the error is "Microsoft has stopped working" and it tries to create a backup copy for me.

yes user security is populated with 1, 2, and 3 as the bound column.

dlookup is strictly to a query, not a form. I guess I could use table as well, but I just thought query might be better as it is faster. not sure if I am wrong on this.

I have tried compiling in vba and no errors.

I don't use modules so not sure how to works. most codes I tried to add on error, just this one happen not to have it yet but I will add it.

I don't know what else could be causing Microsoft to stop working on Access.

[CJ_London]

I checked my pc's system properties and it says 64 bit operating system

the 64bit refers to Office, not the operating system - in any office program, click on options>help, you will see the version and bit type stated there.

some things you missed from my list

I presume the username does not contain any characters that would cause the dlookup to fail (such as a quotation marks)

Have you tried stepping through the code to see if it fails on a particular line? Or does it fail before the first line of code is run?

Do you have Option Explicit at the top of all modules (on the line below Option Compare Database). If not, put it in all modules and compile to detect potential coding errors

Finally have you temporarily disabled any error handling in other events (I can see you don't have any in the load event) so you are not hiding potential error messages?


There is no reason why a query will be quicker (in this situation), but if it has a parameter which refers to a form it will probably fail. Usual basis is to lookup on a table

I don't understand this

user security is populated with 1, 2, and 3 as the bound column.

bound columns are a property of list and combo controls, not recordsets - kind of suggests you are using lookup fields in your table. It may not matter in this case, but just to confirm, are you? and if so what is the rowsource used?

Also, have you tried Ranmans suggestion? just copy the db to another pc and see if it runs there OK