Intermittant VBA Code Issue

[eking002]

Hello all.

I am new here. This is my first post and my first real issue that I’ve been unable to resolve without a simple search.

I am new to VBA, SQL and this stuff in general. I have a little experience, but am still learning as I go.

I tend to be long-winded, so I apologize in advance. I try to be clear and descriptive. Sorry if I did not word my issue correctly, or if I am confusing. I would be happy to clarify anything further.




The background:

I inherited a legacy MS Access database. It was originally a single file that was later split to a BE/FE on a shared network drive. We can have between three and thirty people online at a time.

We kept having issues (as you can imagine) and we transitioned to using SQL 2008 R2 and a MS Access Front End. I am trying to clean up and improve the entire thing, but in steps.



The Front End:

The main form is a basic ‘Search Form’, which we use to find and load job information. Depending on job status itself, a ‘pre’ or a ‘post’ form will load with the jobs information.

You can use the Search Form and search by an account number, project name, site address etc. Each search type has a corresponding button, each button searches only within the desired column (address, account number etc) and only within either the “pre” or “post”. I can use the same account number, and load the ‘pre’ form and modify data, and also load the same account in the sold form, and modify data.

It is also coded so that you can only ever have one instance of a form open, at once. You must close the ‘pre’ form, before loading a different account in the ‘pre’ form.



The problem:

I seem to be having an intermittent issue with loading the ‘post’ form after searching. I search for account number 123456789, and the List box populates the data on the search form as expected. The double click on the list box item loads either the pre or post forms, depending on which of the buttons you used. I can double click and load the data if the search needed to use the pre form, but when I attempt to load the same account in the post form, I get an error. (by design, see code sample below)

It works fine, until it stops. It works for some users, but not all users. If/when it breaks, the only thing I can do right now is replacing the existing Front End (with the issue) with a backed up version of the front end. It works until it stops, and I just re-replace the broken front end. The search form uses the results displayed in the listbox to load pre or post results. List0 populates with every button click. Double clicking on a search result from List0 either loads a pre/post form, or generates an error message. List0 seems to remain ‘blank’ when I select something that is ‘post’ but works okay when it is ‘pre’.


“ MsgBox "Please select an account", vbOKOnly, "WARNING!" ” is generated when clicking on results sometimes, but not always.



This just started happening. It has been okay for months, and just the past few days, it is sporadic. I didn’t write the search code, and have been trying to digest it and fix it. There is a lot there, but have a feeling it is located in this section, because of the error/messages:






Some Code:

Public Sub List0_DblClick(Cancel As Integer)
' If an error occurs, this will trigger the error sub
On Error GoTo Err_List0_DblClick

' Define the string to be used to open the form by name
' that we wish to be opened in the DoCmd.OpenForm Statement
' Dim stDocName As String

' Define the string that will contact the record to be opened in the
' record that is selected in List0
Dim stLinkCriteria As String
' stDocName = "Survey Entry Form New"
If CurrentProject.AllForms(stDocName).IsLoaded Then
MsgBox "The form you are attempting to open is already open." & _
vbNewLine & "Please save what you are working on then" & _
vbNewLine & "close the form. Then try this operation again.", vbOKOnly, "Form Already Open"
Else


'This will assign Survey Entry Form New name to the stDocName string
' Assigns the current record from the one selected in the List30 listbox.

stLinkCriteria = "[ID]=" & Me!
[List0]
' stLinkCriteria = "[ID]='1283'"
' MsgBox "Link Criteria is: " & stLinkCriteria, vbOKOnly
' This opens the Survey Entry Form New with the current selected record
If IsNull(Me!
[List0]) = False Then
If Me!
[List0] <> 0 Then

DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
MsgBox "Please select an account", vbOKOnly, "WARNING!"
End If
End If
End If



Exit_List0_DblClick:
Exit Sub

' On an error, this will display a message box that will give instructions to select Project ID
' when clicking on the open button before selecting a Project ID.
Err_List0_DblClick:

Response = MsgBox("Please select an account", vbOKOnly, "WARNING!", "DEMO.HLP", 1000)
Resume Exit_List0_DblClick

End Sub

[eking002]

Also, didn't mention above:


We used to hav this error, which went away when we migrated to SQL 2008 R2. Today/recently, it started occuring again.


The database cannot be opened because the VBA projecy contained in it cannot be read. The database can beopened only if the VBA project is first deleted. Deleting the VBA project removes all code from modules, forms and reports. .... etall
[OK] [Cancel] [Help]


I can bet my pinky toe that User_X is simply hitting OK to proceed, thus causing futher issues. I just have no idea why this started up again all of a sudden.

[Missinglinq]

...I inherited a legacy MS Access database. It was originally a single file that was later split to a BE/FE on a shared network drive...

You have a split app, but does each user have a copy of the Front End on their PC, or are you talking about a single Front End file that everyone is using?

Linq ;0)>

[John_G]

Hi -

On first glance, the only thing I can see is that

' stDocName = "Survey Entry Form New"
If CurrentProject.AllForms(stDocName).IsLoaded Then

won't work because the line assigning a value to stDocName is commented out, and so is the Dim statement for stDocname. The IsLoaded check will be false every time, if is does not give a run-time error caused by blank/Null stDocName.

HTH

John

[eking002]

We still utilize the shared network drive, where the Front End lives.

Some users copy the FE and paste on desktop. Some 'create shortcut'. Some just run the .addcb file from the shared drive itself.

Regarding the error message, in my second post (hit OK to delete VBA code). The only thing I've seen online that makes sense is that a user has a different Access version or VBA version on their machine.

[eking002]

Hi -

' stDocName = "Survey Entry Form New"
If CurrentProject.AllForms(stDocName).IsLoaded Then

won't work because the line assigning a value to stDocName is commented out

Sorry about the confusion here. stDocName is assigned somewhere else actually. The two sets of buttons i mentioned, stDocName is assigned depending on which set of buttons is used. "Pre" will set stDocName to 'pre form' or 'post form'.

[Missinglinq]

We still utilize the shared network drive, where the Front End lives.

Some users copy the FE and paste on desktop. Some 'create shortcut'. Some just run the .addcb file from the shared drive itself.

Having multiple users sharing a single database file, even a Front End file of a split app, sitting on a network drive, is the sure way to repeated episodes of corruption (which is what you're experiencing, here) as well as speed and timing problems! Multiple users simply have to work off of a split database, with each user having their own copy of the Front End, which contains everything except the data/tables, on their respective hard drives, and a Back End with all Tables on a shared drive.

Being in forced retirement, I spend 6-8 hours a day here and on other Access forums/newsgroups, and over the past six years have seen dozens and dozens of reports similar to yours! The really insidious thing is that the app can work for extended periods of time before the troubles begin! But once it does, they become chronic, which is to say they occur over and over and over again!

The record, by the way, was a Non-Split db that had been working, without problems, for 12 years! It then started exhibiting the exact same symptom you describe here and continued to do so until the app was split.

You've already done the heavy lifting, which is to say splitting the app; you now need to put a copy of the Front End on each machine.

Linq ;0)>

[eking002]

You've already done the heavy lifting, which is to say splitting the app; you now need to put a copy of the Front End on each machine.

Linq ;0)>

Thanks for your assistance with this.

I've always hated having the front end shared, in this way, and have assumed it was part of the problem. I 'blamed' the VBA because the VBA was new and not written by me... so it must have been wrong!!! Which it isn't.


My fear now is keeping the 20+ users "up to date" with the new front end releases. It is hard enough to get them to read an email, let alone 'delete this, copy that', but maybe we need a heavy hand approuch in getting this done.

I'll throw the FE on a share point or something, and delete the version we have on the shared network drive.


Thanks again.

[RayMilhon]

I can make 1 suggestion used it successfully a few years ago. Had the front end and back end on the server. When the user clicked on the shortcut on their desktop it ran a batch file that first copied the front end to the local machine and then ran the local front end.

[ssanfu]

And there are several free/low cost "FE Updaters" available. Google "FE Update"......