Solved

Using tab off of ComboBox causes concurrent users to crash

Posted on 2006-07-05
10
446 Views
Last Modified: 2008-01-09
Hi,
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.

Server:
Windows Server 2003 with Gigabit connection to network.
Office 2003

Clients:
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

Problem:
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.
0
Comment
Question by:Venoym
  • 4
  • 4
  • 2
10 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 17045338
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...'.
0
 
LVL 58

Expert Comment

by:harfang
ID: 17045473
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?

Cheers!
(°v°)
0
 

Author Comment

by:Venoym
ID: 17045649
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
Else
    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
    Else
        Check348.Value = -1
    End If
    'Check348_Click
    Me!Text354.Value = DLookup("[AmountPaid]", "HCFAs", strhcfaid)
    'changed here by Kevin, was combo.value
    Me!Combo344.Value = DLookup("[Resubmit]", "HCFAs", strhcfaid)
    Combo344_AfterUpdate
End If

SkipRoutine:
    Me!Combo25.SetFocus

ExitGracefully:
End Sub


** Row Source Queries
"SELECT * FROM PatientBoxMenuQuery WHERE [SecondaryIns]=0"
or
"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
FROM HCFAs
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];
0
 
LVL 58

Accepted Solution

by:
harfang earned 500 total points
ID: 17046840
Venoym

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...
    Combo344_AfterUpdate
    Me.Dirty = False   ' <---- this line added
End If

SkipRoutine:
    ' etc...

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

(°v°)
0
 

Author Comment

by:Venoym
ID: 17052462
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.
0
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!

 
LVL 58

Expert Comment

by:harfang
ID: 17052833
Well...

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
    Combo344_AfterUpdate
End If

ExitGracefully:
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,
(°v°)
0
 

Author Comment

by:Venoym
ID: 17055481
Harfang,
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?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17069410
Harfang:  Any comments on 20 users of a shared FrontEnd?  I can see 20 users of the BackEnd but...?
0
 
LVL 58

Expert Comment

by:harfang
ID: 17071584
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.

Cheers!
(°v°)
0
 

Author Comment

by:Venoym
ID: 17074372
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.

Venoym
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

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

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

14 Experts available now in Live!

Get 1:1 Help Now