?
Solved

Same VBA Randomly Causing Run Time Error In Microsoft Access

Posted on 2013-02-04
16
Medium Priority
?
448 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 85
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 48

Expert Comment

by:Dale Fye
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
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
LVL 48

Expert Comment

by:Dale Fye
ID: 38850866
"the form comes up completely blank"

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

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 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 85
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 85
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 85
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 48

Expert Comment

by:Dale Fye
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 85
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 85
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

752 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