Hello all. 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. [u][b]The background:[/b][/u] 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. [u][b]The Front End:[/b][/u] 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. [u][b]The problem:[/b][/u] 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
Anyone have any ideas?