Solved

Same VBA Randomly Causing Run Time Error In Microsoft Access

Posted on 2013-02-04
16
439 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
  • 7
  • 6
  • 3
16 Comments
 
LVL 84
Comment Utility
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)
Comment Utility
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
Comment Utility
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
"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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 84
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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)
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now