Using tab off of ComboBox causes concurrent users to crash

I'm new here, but I've been researching this problem over the Internet for about a week now and haven't found any solutions.

Windows Server 2003 with Gigabit connection to network.
Office 2003

Windows XP SP2 with 100MBit connection to network
Office 2003
Some have Office 2003 SP2 installed

Database setup:
1 Table with 77 columns.  225,000 rows
32 total users: up to 20 concurrent

Database FE and BE info:
FE is on network share G:  accessed by a shortcut on each desktop
BE is on a network share G: in a different folder.
FE is about 2MB in size
BE goes up to 800 MB in size

When a user is entering a new record, there is a combobox that provides a "lookup" function for previous entries for that record in the database.  The user, if a record is found, would hit "tab" off of the combobox to continue entering the relevant information for the new record.  The problem occurs that if the user does NOT use the mouse scroll wheel to change record either up or down by one, and then go back to the new record prior to tabbing off.  The database locks and all other users MS Access crash.  Not just the database, All other users in that database have Access crash and want to send a crash report to MS.  The databases are compacted regularly, and are not corrupt.  Shortcuts and permissions are all set correctly to allow full access.  Until a user misses the scroll wheel, everything works perfectly.  Once a user does that, everyone has to exit (including the user that missed the scroll wheel) before anyone can get back into the database.

There is an error code that is brought up on all of the machines that have Access crash, but I do not have that number currently, however I was unable to find any relevant information on it online when I did have it a few days ago.

I have read that having the FE DBs on the local machines is recommended, That is not possible in this situation because of the nature of the network and user file permissions on local machine.  We tried moving to that previously, and the troubles happened in that environment just the same as in the shortcut to network share environment.  Because any changes to the mde file must be propagated quickly to all clients, we decided to keep the centralized mde.  Made updates and other maintenance easier and quicker with fewer bugs and missed updates.
Who is Participating?

This code is typical for "unbound" forms. Is that the case? I suspect not, because for unbound forms, the mouse wheel has no effect. What you probably have is a bound form that you treat like an unbound form, if that makes sense.

What is the record source of the form? It seems to me that the only possible effect of moving to the "next" record (whatever that means in this setup) would be to save the form. In other words, for now, this line should do the same:

    ' your code...
    Me.Dirty = False   ' <---- this line added
End If

    ' etc...

Hope this helps. We can then discuss other methods both faster and more reliable than the list of DLookup() functions...

20 concurrent users of a shared front end??  Just so we are all clear, describe what you mean by '...would hit "tab" off of the combobox to continue entering...'.
As Ray hinted, 20 users is above what we normally recommend as the upper limit. However, this is not the problem here.

The form provides a combo showing a list of "previous" records. What is the source of that combo? Is it a query of a dynamically assigned recordset? Presumably, something happens to the form when the user selects one record in the combo. What happens? change the form's filter, surf to the corresponding record using code?

At that point, the user can use the mouse wheel or the navigation buttons, and then all is fine. If he/she skipps that step, everything is locked...

In my opinion, it has something to do with your code. Could you post here all event procedures used to manage the combo, including the Row Source string or any initialization code for the combo's row source?

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

VenoymAuthor Commented:
To clarify, to move to the next field after the initial combo (which is not always needed), the user uses the "TAB" key on the keyboard.  Fairly standard tabstop setup.
-There is a single checkbox that causes the combo box to requery using a different value, either 0 or -1 (see query below).
-The Combo box, if a record is selected, will populate between 20 and 30 fields using the code in the "Patient_AfterUpdate" sub below.  The entire contents of the VBA is 1756 lines, and the majority (>1000 lines) is dedicated to error checking prior to insertion in the database, per regulations for what should and should not be allowed into the database.
-There is a checkbox that enabled, and shows, the combo box, and also disables the same combo box.  That is what the "PatientEnabled = false" if statement is checking.

**The Combo is called Patient, and once a user selects the record from it, it populates several other fields in the form.  The Patient combo is populated from two queries, one putting a filter on the other.  The code for that is at the bottom.  

Private Sub Patient_AfterUpdate()
If PatientEnabled = False Then
    GoTo ExitGracefully
End If
Dim strhcfaid As Variant
Dim temp As Variant
strhcfaid = "[HCFAAutoID] =" & Me!Patient

If IsNull(Me!Patient) Then
    GoTo SkipRoutine
    Me!cboGroup = Null
    Me!Text2 = DLookup("[PatientLName-2]", "HCFAs", strhcfaid)
    Me!Combo25 = DLookup("[PlanID]", "HCFAs", strhcfaid)
    Me!Text24 = DLookup("[PatientID-1A]", "HCFAs", strhcfaid)
    Me!Text4 = DLookup("[PatientFName-2]", "HCFAs", strhcfaid)
    Me!Text31 = DLookup("[PatientDOB-3]", "HCFAs", strhcfaid)
    Me!Text32 = DLookup("[PatientSex-3]", "HCFAs", strhcfaid)
    Me!Text40 = DLookup("[InsuredLName-4]", "HCFAs", strhcfaid)
    Me!Text41 = DLookup("[InsuredFName-4]", "HCFAs", strhcfaid)
    Me!Text43 = DLookup("[PatientAddress-5]", "HCFAs", strhcfaid)
    Me!Text45 = DLookup("[PatientCity-5]", "HCFAs", strhcfaid)
    Me!Text46 = DLookup("[PatientState-5]", "HCFAs", strhcfaid)
    Me!Text48 = DLookup("[PatientZip-5]", "HCFAs", strhcfaid)
    Me!Text49 = DLookup("[PatientPhone-5]", "HCFAs", strhcfaid)
    Me!Text318 = DLookup("[PatientMI-2]", "HCFAs", strhcfaid)
    Me!Text157 = DLookup("[OtherInsuredLName-9]", "HCFAs", strhcfaid)
    Me!Text158 = DLookup("[OtherInsuredFName-9]", "HCFAs", strhcfaid)
    Me!Text160 = DLookup("[OtherInsuredPolicyNu-9A]", "HCFAs", strhcfaid)
    Me!Text162 = DLookup("[OtherInsuredDOB-9B]", "HCFAs", strhcfaid)
    Me!Text163 = DLookup("[OtherInsuredSex-9B]", "HCFAs", strhcfaid)
    Me!Text165 = DLookup("[OtherEmployerName-9C]", "HCFAs", strhcfaid)
    Me!Text167 = DLookup("[OtherPlanName-9D]", "HCFAs", strhcfaid)
    Me!Text179 = DLookup("[ReferringDocName-17]", "HCFAs", strhcfaid)
    Me!Text188 = DLookup("[DateofCurrentIllness-14]", "HCFAs", strhcfaid)
    Me!Text69 = DLookup("[Relationship-6]", "HCFAs", strhcfaid)
    Me!Text84 = DLookup("[PatientMaritalStatus-8]", "HCFAs", strhcfaid)
    Me!Text85 = DLookup("[PatientWorkStatus-8]", "HCFAs", strhcfaid)
    Me!Text91 = DLookup("[RelatedToEmployment-10A]", "HCFAs", strhcfaid)
    Me!Text168 = DLookup("[RelatedToAutoAccident-10B]", "HCFAs", strhcfaid)
    Me!Text94 = DLookup("[AutoAccidentState-10B]", "HCFAs", strhcfaid)
    Me!Text169 = DLookup("[RelatedToOtherAccident-10C]", "HCFAs", strhcfaid)
    Me!Text181 = DLookup("[ReferringDocID-17A]", "HCFAs", strhcfaid)
    Me!Text71 = DLookup("[InsuredAddress-7]", "HCFAs", strhcfaid)
    Me!Text73 = DLookup("[InsuredCity-7]", "HCFAs", strhcfaid)
    Me!Text74 = DLookup("[InsuredState-7]", "HCFAs", strhcfaid)
    Me!Text76 = DLookup("[InsuredZip-7]", "HCFAs", strhcfaid)
    Me!Text77 = DLookup("[InsuredPhone-7]", "HCFAs", strhcfaid)
    Me!Text171 = DLookup("[InsuredPolicyGroup-11]", "HCFAs", strhcfaid)
    Me!Text173 = DLookup("[EmployersName-11B]", "HCFAs", strhcfaid)
    Me!Text175 = DLookup("[InsurancePlanName-11C]", "HCFAs", strhcfaid)
    Me!Text177 = DLookup("[AnotherBenefit-11D]", "HCFAs", strhcfaid)
    Me!Text183 = DLookup("[PriorAuthNumber-23]", "HCFAs", strhcfaid)
    Me!Text118 = DLookup("[PrimaryICD9-21]", "HCFAs", strhcfaid)
    Me!ChooseProvider = DLookup("[ProviderAutoID]", "HCFAs", strhcfaid)
    temp = DLookup("[SecondaryIns]", "HCFAs", strhcfaid)
    If temp = 0 Then
        Check348.Value = 0
        Check348.Value = -1
    End If
    Me!Text354.Value = DLookup("[AmountPaid]", "HCFAs", strhcfaid)
    'changed here by Kevin, was combo.value
    Me!Combo344.Value = DLookup("[Resubmit]", "HCFAs", strhcfaid)
End If


End Sub

** Row Source Queries
"SELECT * FROM PatientBoxMenuQuery WHERE [SecondaryIns]=0"
"SELECT * FROM PatientBoxMenuQuery WHERE [SecondaryIns]=-1"

depending on a checkbox that causes the filter.

The Patient Box Menu Query:
SELECT DISTINCT Max(HCFAs.HCFAAutoID) AS MaxOfHCFAAutoID, [PatientLName-2] & ", " & [PatientFName-2] & " " & [PatientMI-2] & ", " & [PatientID-1A] & ", " & [PatientDOB-3] & ", " & [PatientSex-3] AS Patient, HCFAs.SecondaryIns
GROUP BY [PatientLName-2] & ", " & [PatientFName-2] & " " & [PatientMI-2] & ", " & [PatientID-1A] & ", " & [PatientDOB-3] & ", " & [PatientSex-3], HCFAs.SecondaryIns
ORDER BY [PatientLName-2] & ", " & [PatientFName-2] & " " & [PatientMI-2] & ", " & [PatientID-1A] & ", " & [PatientDOB-3] & ", " & [PatientSex-3];
VenoymAuthor Commented:
Thank you harfang.  I'm going to put that into a testing setup.  It may be a day or two before I hear back from the entry technicians on whether it has corrected the problem.  I'm going to use your answer for right now until I hear back from the technicians.
The form is Bound.  The Record source for the form is the HCFAs table, the one with 77 columns.

As near as I can figure, the act of rolling the scroll wheel saves the current record... without going through the validations.  Though why skipping the save would cause all others (even some using a different MDE on Access XP) to either crash or lockup to where everyone has to exit out is still beyond me.

I also wanted to ask about the DLookup() function.  Would it be better to use a built in query that I filter by the autoid and then retrieve the entries from that?
Like this:
Set MYDB = CurrentDb()
Set myset = MYDB.OpenRecordset("SELECT * FROM QueryPatient WHERE [AutoID]=" & Me!Patient)
Me!Text2 = myset.[PatientLName-2]

The DLookup functions were legacy code that has been present since this was built using Access 2000 (has evolved through Access XP to Access 2003).  I was unsure what exactly they were doing and why, but since it worked I was going to leave it.

If the form is bound to the table HCFAs, you do not need any DLookup. The form itself already has a recordset, so there is no need to create your own ("myset" in your example). There can be two cases here.

1. (quite unlikely) Your *controls* are also bound. This would mean that whatever record is the current record would get overwritten with the one selected from the combo.

2. In the contrary, I would suspect that although the form is bound (and thus has a recordset defined), the controls themselves are not. In that case, use something like this:

Private Sub Patient_AfterUpdate()

Dim strhcfaid As Variant
Dim temp As Variant

If PatientEnabled = False Then GoTo ExitGracefully
If IsNull(Me!Patient) Then GoTo ExitGracefully

strhcfaid = "[HCFAAutoID] =" & Me!Patient

Me!cboGroup = Null
With Me.Recordset
    .FindFirst strhcfaid
    If .NoMatch Then Goto ExitGracefully

    Me!Text2 = ![PatientLName-2]
    Me!Combo25 = ![PlanID]
    Me!Text24 = ![PatientID-1A]
    Me!Text4 = ![PatientFName-2]
    ' etc...
    Me!ChooseProvider = !ProviderAutoID
    Check348.Value = Nz(!SecondaryIns)
    Me!Text354.Value = !AmountPaid
    Me!Combo344.Value = !Resubmit
End If

End Sub

I agree, this does not really explain the crash. It might have to do with your default locking mechanism, or with a mixture of bound and unbound fields...

Hope this helps,
VenoymAuthor Commented:
I tried your code that you gave (with modifications for the missing fields) and every time the code returns a .NoMatch value of True, even when the value is valid.  Also, should the "Me.Dirty=false" be included in that code snippet?
Harfang:  Any comments on 20 users of a shared FrontEnd?  I can see 20 users of the BackEnd but...?
Ray (and Venoym),

If the FE database is in MDE format, 20 users can share it without problem. It's not fast, because each an every object needs to be transferred over the network, but it shoud work.

However, with an MDB front end, you run into problems whenever one of the users saves a form or report. Either after a message "save formatting of..." or silently in the case of embedded OLE objects like charts. Even with just two or three users, this is a real problem, but 20 users allowed to save design changes to forms and reports is a disaster waiting to happen...

So, as you said, sharing tables and queries (a BE basically) is not a problem with two dozen users. But sharing a FE is potentially troublesome, and the best choice is to use the MDE format.

VenoymAuthor Commented:
harfang (and Ray),

I'm using the MDE's for the front end.  So that's not a big problem.  We used to use MDB's, but ran into too many problems with that.  The average number of users on this system is about 10 concurrent, but can peak as high as 18-20 on busy days.

Most of the fields on the form are bound.  only a few are not bound.  The patient combobox is one that is not bound.  it just queries at form load.

I'm trying out the code harfang gave above, but .nomatch always returns true.  not matter what I do to it.  Not sure why yet.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.