Solved

Same VBA Randomly Causing Run Time Error In Microsoft Access

Posted on 2013-02-04
16
444 Views
Last Modified: 2013-02-05
I am using the following code so that a combo box automatically shows the current shift when a form is loaded:

Dim T As Date, T1 As Date, T2 As Date, T3 As Date
T = TimeValue(Now())
T1 = #6:30:00 AM#
T2 = #2:30:00 PM#
T3 = #10:30:00 PM#
If T < T1 Or T >= T3 Then
    Me.Shift = "3rd"
ElseIf T < T2 Then
    Me.Shift = "1st"
Else
    Me.Shift = "2nd"
End If

Most of the time it has worked flawlessly, but in the 7 days that this database has been up and running, there has been three times, including this morning, that a runtime error is generated that says "You cant assign a value to this object". This morning, When I opened the debugger after recieving the runtime error, the line of code that was highlighted was: Me.Shift = "1st".

The last time this happened was last Wed - exact same scenario, and the same line of code was highlighted.

Any ideas what is going on, why it is only happening sporadically, and how I can prevent it from happening at all?
0
Comment
Question by:Drizzt420
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 3
16 Comments
 
LVL 84
ID: 38850724
What is "Me.Shift"? Is it a textbox? If so, is it bound?

If "Shift" is a textbox, and if it's also the name of a Field in the form's underlying recordsource, try name the textbox differently - for example, name the Shift textbox something like "txShift", and then change your code to refer to that textbox:

Me.txShift = "3rd"
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38850739
What event are you using to do this?

I'm guessing you are using the Form_Open event, try using the Form_Load event instead.
0
 
LVL 4

Author Comment

by:Drizzt420
ID: 38850846
Ok, I dont think that the VBA is the problem anymore, it was more like a symptom of something else.

I completely removed the code in question and while I no longer got the runtime error, it still didnt work. The form comes up but it is completly blank.

I have this database split so that the tables are on a network drive, and everyone that needs to use the database has a seperate copy of the front end file, NOT a shortcut to the same front end file. Having it set up this way should eliminate the tables being read only if multiple people have thier files open at the same time shouldnt it?

One copy of the database is always running on a computer out on the production floor, this is where the data entry happens. Several other people have copies of the database so that they can analyze the data and generate reports.
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38850866
"the form comes up completely blank"

What are the settings for AllowAdditions and DataEntry for this form?
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 38850868
Even if the db were readonly, the form should still show the data.

Does the trouble occur on the "other copies", or on the one on the production floor?

Note that all users must have at least Modify permissions on the folder hosting your backend file.

You might also perform maintenance on the database - compact the database and see if that helps. Also Compile the database - from the VBA Editor click Debug - Compile.

Also be sure that all machines are fully updated in regard to Windows and Office.
0
 
LVL 4

Author Comment

by:Drizzt420
ID: 38850888
When I open the back end, It opens as read only, no matter what PC or user I use.

The front end that resides on the production floor is slightly different than the one the rest of us use, the data analysis section has been disabled and the interface opens up full screen for them. The front end that everyone else uses opens up more traditionally.

The thing is, this database has been constanlty in use, 24 hours a day for seven days now and it works fine most of the time. The last issue before this morning was five days ago, and it was the same exact issue. We tried everything last Wednesday to get it working again to no avail, but when we came back into work Thursday morning, it was working just fine again like nothing ever happened and it worked that way up until this morning. No permissions have been altered for any user on either the file or the storage location
0
 
LVL 84
ID: 38850908
When I open the back end, It opens as read only, no matter what PC or user I use.
This would indicate either a problem with the backend, or a problem with user permissions on the folder.
0
 
LVL 4

Author Comment

by:Drizzt420
ID: 38851051
OK -  I compacted and compiled the databases and it is working again for now.

I am going to wait for a few days before I consider this issue solved though, I want to make absolutely sure that it's not going to happen again before I accept a solution

Thank you
0
 
LVL 84
ID: 38851356
If there is corruption, you also need to determine why the corruption occurred. In many cases it's caused by dropped network connections - i.e. a bad cable, or switch/router/NIC. In others it's caused by the user power cycling their machine without closing out of their apps.

Also, if you've had corruption it's a good idea to move everything over to a new, blank database. This starts everything off fresh, which is a good idea.
0
 
LVL 4

Author Comment

by:Drizzt420
ID: 38851420
I figured out the problem - If somebody gets into the database and generates a report, as long as that report is open the tables become read only and the errors that I have been experiencing start.

The problem is that people will pull up a report and forget about it, leaving it open on their PC, locking people out of the database
 is there any way that Access will allow  simultaneous data entry and data analysis?
0
 
LVL 84
ID: 38851552
Access allows this by default, assuming you've set the database to open in Shared mode (which is the default).

What do you mean by "generate a report" - do you mean print/preview a report, or do people actually create or modify existing reports?

If they're creating or modifying reports, then Access will lock the database exclusively when this happens, and there's nothing that can be done about that.

If that's not the case, and if the users are just printing/previewing reports, then you've got something else going on.
0
 
LVL 4

Author Comment

by:Drizzt420
ID: 38851644
They are just viewing reports that are automatically generated.

The reports are based off of queries, users utilize a form with combo boxes, option groups, etc, to choose what information they want to see in the report, they then click a generate report button and the report opens up in print preview mode
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38851876
You might want to consider creating a temp table which contains all of the data from the query that is the basis for your report.  By doing so, then modifying the source of your report to use the temp table, you can avoid this locking problem.

If you are interested in that option, check out my article on using temp tables.
0
 
LVL 84
ID: 38852248
I would strongly suspect a permission issue on the folder hosting the database. All it takes is one user with invalid permissions to lock out the others. We also sometimes find that a specific user will cause this - for example, if the user profile for that user is corrupt, it can cause the database to lock.

Check the permissions on that folder, and then check them again. Then have someone else check them, just to be sure :)
0
 
LVL 4

Author Comment

by:Drizzt420
ID: 38854596
Ok, the problem has been solved and here is what was going on:

Alot of the management folk only have Read access to the folder that the back-end was stored in because they were only going to be viewing reports and not adding or editing data.

Multiple people could use the database without any problems as long as all of those people had Read/Write. If one person that didnt have Write access on the folder containing the tables were to get into the database to view reports, the tables became "Read Only" for everybody for the entire duration that the person had thier copy of the front end open.

Even if someone with "Full Control" already had it open before the Read Only guy opened his, that someone would lose Full Control and effectively have Read Only rights.

It took a grueling couple of hours, and multiple phone calls to India, before our "Help Desk" was able to properly apply the correct permissions for everyone - but everything is golden now.

Thanks to all of you for your help!
0
 
LVL 84
ID: 38854707
Glad we could help. Permissions can be difficult to work with, especially when you have to depend on offsite tech support to manage them.
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question