2 users can't add records at same time

[Connie]

I have a secured db (Access 2002). I've tested it having someone log on as 1
user while I log on under another user. Both users belong to the same group
which has permission to add/edit data (I assign permissions to groups not
users). I have a form where they're able to add/edit records. On this form
they can double click a textbox & a popup form with 2 subforms will open.
This popup & the subforms are all the many sides of the record on the main
form. While testing I had the user add a new record on the main form at the
same time I was adding a record ... no problem. Then I had her dbl click to
bring up the popup - she had no problem, but when I tried to open it, while
she was still in it, it took a very long time to display & then the 2
subforms didn't show ... only 2 white boxes. I've tested using other forms
& no problem .. we both can add at the same time, just this one with the
popup is causing a problem. What am I doing wrong?

TIA for any suggestions or ideas!
Connie

 

[Joan Wild]

I have a secured db (Access 2002). I've tested it having someone log on as
1
user while I log on under another user. Both users belong to the same
group
which has permission to add/edit data (I assign permissions to groups not
users). I have a form where they're able to add/edit records. On this
form
they can double click a textbox & a popup form with 2 subforms will open.
This popup & the subforms are all the many sides of the record on the main
form. While testing I had the user add a new record on the main form at
the
same time I was adding a record ... no problem. Then I had her dbl click
to
bring up the popup - she had no problem, but when I tried to open it,
while
she was still in it, it took a very long time to display & then the 2
subforms didn't show ... only 2 white boxes. I've tested using other
forms
& no problem .. we both can add at the same time, just this one with the
popup is causing a problem. What am I doing wrong?

If you are going to have multiple users (even if not really), you should
split the database. Put the backend mdb(tables/relationships) on the
server. Give each user a copy of the frontend (all other objects) mdb on
their computer. The frontend will contain links to the tables in the
backend.

Since your database is secured, don't use the database splitter wizard, as
the backend will end up unsecured. Do the split manually. See
http://www.jmwild.com/SplitSecure.htm

 

[Connie]

thanks for your reply Joan. I should have stated that the db is split.
Both FE & BE are on the server. However, users will be logging on using
remote access. Does that have any bearing on this? Another person has set
the remote access up which I'm not familiar with. He stated that all users
logging in will be using the same file ... can this cause problems? I know
with other clients I have them set up as you stated.

 

[Joan Wild]

thanks for your reply Joan. I should have stated that the db is split.
Both FE & BE are on the server. However, users will be logging on using
remote access. Does that have any bearing on this? Another person has
set
the remote access up which I'm not familiar with. He stated that all
users
logging in will be using the same file ... can this cause problems? I
know
with other clients I have them set up as you stated.

Even using a remote access program like Terminal Services, each user should
have a separate copy of the frontend - it can be put into their profile.

If/when you have to update the frontend you can use something like Tony
Toews frontend updater. See
http://www.granite.ab.ca/access/autofe.htm

His utility works well in a TS environment. Also have a look at
http://www.granite.ab.ca/access/terminalserver.htm

 

[Connie]

ok I'll make sure a copy is placed in their profiles. Thanks for the links,
I'll check them out.

Back to my immediate problem, any idea what would cause the popup form to
take so long to come up & then only show white boxes where the subforms
should be ... which only happens to the 2nd user going in after the 1st user
has already opened the popup. Once the 1st user closes the popup the 2nd
user can go in with no problem .. & to extend this, if the 1st user then
tries to go back in they have the same problem ... slow & white boxes. To
add to this frustration, last Wed when I did a test ... this problem didn't
exist. The only thing that I recall working on Thurs is adding code to the
switchboard that would disable/enable command buttons based on user login
and assign a number to a global var (based on user) which is then used by a
couple of other forms. I've checked all of that out & can't find anything.
Also on Thurs I finished setting permissions for other forms (not the ones
with problems) & reports. Also, as a reminder, if it makes a difference,
these users I'm testing with are assigned to the same group & I assign
permissions to the group not users. Can you suggest anything that might be
causing this problem, as I'm totally stumped & don't know what else to
check.

Thanks for all your help!

 

[TC]

I'd suggest throwing a few MsgBox'es around in the code of the main- &
both sub- forms. I just use numbers, nothing fancy:

msgbox 11
....
msgbox 22
....
etc.

You should soon find where the holdup is. Then post back with more
information.

HTH,
TC

 

[Joan Wild]

Back to my immediate problem, any idea what would cause the popup form to
take so long to come up & then only show white boxes where the subforms
should be ... which only happens to the 2nd user going in after the 1st
user
has already opened the popup.

I think you'll find this problem will disappear if they do not share the
same frontend.

Also think about the global variable you assign based on the current user;
think about the effect with multiple users in the same frontend.

 

[Connie]

TC I've replied to this, plus more, in the reply to Joan. Any add'l
suggestions you might have are definitely appreciated!!

 

[Connie]

I ended up copying my mdb file (I use MDE files for my clients) & I put
breaks in certain areas of code to see if I could see why subforms not
opening ...... all my code seems to be working .. except they don't show. I
placed this mdb file in "My Docs" under my profile & then copied the MDE
file into another shared folder for the user that's helping me test the db.
Problem still exists even tho we are using different FEs.

I also was concerned with the global var, but that part is working fine
(even with 1 fe). This global var holds the region which is based on user's
login .. it's then used by certain combos on some forms. The combos on the
form in question is working fine. It's the popup that's not.

Is there anything in permissions I should be looking at that could do this.
Maybe if I explain the structure, that might trigger something: tblLogs is
the main tbl. tblSites is the many side of tblLogs. tblServices is the many
side of tblSites & tblTimedServ is also the many side of tblSites. All my
forms uses queries not tables. The main form is based on tblLogs (which
works fine), the user then double clicks a txtbox that opens the popup form.
The popup form is based on the tblSites with 2 subforms (1 based on
tblServices & 1 based on tblTimedServ). The 1st user into this popup has no
problem .. for the 2nd user it takes a long time to open ... however, this
form (using tblSites) shows everything correctly ... the 2 subforms however
does not open therefore only shows 2 white boxes. I know they're not open
because when I enter something in a textbox where the AfterUpdate event uses
data from 1 of them I receive an err stating that object is not open or
doesn't exist.

I have set permissions on all existing & new tables as well as all existing
& new queries to: Open/Run, Read Design, Read Data, Update Data, Insert Data
& Delete Data. I had secured the BE, but after experiencing this problem I
tried it with an unsecured BE, but still the problem exists.

Sorry to be so lengthy here, but I figure the more info the better. I'm
running out of ideas here. I'm thinking I just may have to replace the
popup form with a tab control on the main form, but there is so much going
on in that popup that I dread this ... but it may be my only recourse.
However, I'd hate to spend all that time only to find out it still doesn't
work!

BTW Joan thanks for those links ... very, very informative!!!

Again thanks for all your help!
Connie

 

[TC]

I still say you should put some msgbox'es in your code, so you can
isolate the problem to a smaller area of your code.

You said originally: "On this form they can double click a textbox & a
popup form with 2 subforms will open". So you need a msgbox:

- in the textbox'es double-click event, immediately before and after
the openform of the popup form;

- in the popup form's open & load events;

- in each subform's open, load & current events.

ie. 7 msgbox'es /at least/. (Or you could use debug.print statements
for the same result.)

Then double-click the textbox, do nothing else, & note down the
sequence of msgbox or debug.print outputs. This will help you locate
where the problem is. For example, if the subforms look fine at message
#5 (to pick a number at random), but they go white at message #7, then
hopefully, the cause is somewhere after messages #5 and before message
#7.

Another debugging approach, which works very well when you are out of
other ideas, is the good ol' "divide & conquor" technique. Take parts
of your code out (or disable them) until the problem goes away. Then,
the cause of the problem is somehow related to the last part you took
away.

For example, on a /copy/ of your database, you might try:

- removing one of the subforms from it's subform control;

- disabling the remaining subform's code module by placing the line:
#IF FALSE at the top, and: #ENDIF at the bottom. (Include the
crosshatch symbols - those are compile-time statements, not runtime
ones);

- undoing those changes & repeating them on the /other/ subform;

and so on.

HTH,
TC

 

[Connie]

OK TC I did exactly as you said. The msgboxes that I didn't receive were
the subform's OnOpen, OnLoad & OnCurrent ... everything else works. This is
exactly what I saw when I was stepping thru my code ... it never hit those
events & never opened the subforms ... but did open the popup (which of
course is opened after the subforms) & it read all the code & executed all
of it correctly.

I also went back & tested with the unsecured db & that works fine. 2 users
can go in & the popup comes up fine with both subforms for both users ... no
problem. BTW, I have other popup forms with subforms & they work fine in
the secured db. I have no idea how Access works with multiple users but all
my forms can be opened by more than 1 user at the same time in the unsecured
db .... but in the secured database all my forms can be opened by more than
1 user at the same time except for these 2 subforms. So I would think there
must be a setting or something I need to change for these 2 forms .... but
what?? Any thing with permissions .. or table properties ... or query
properties that would cause this to happen in the secured db?? I'm at the
end of my rope as I've checked these things out & they look fine to me, but
I must be overlooking something.

Thanks again for all your help & suggestions!
Connie

 

[Connie]

I don't believe it ... the subforms both had the Record Lock property set to
All Records. I have no idea how that got changed as I generally have that
property set to Edited Records. But that's what was causing all my
problems!!! Unbelievable!!! I lost a whole weekend .... but at least it's
resolved & I just wanted to post my findings so if anyone else comes across
this same problem at least they can check this property out 1st.

I want to thank both TC & Joan for all your help & suggestions! I really
shouldn't say I lost a whole weekend, as I certainly did learn a lot & again
Joan thank you for those very informative links!

Connie

 

[TC]

Just read this.

Well done for fixing your problem & posting back the cause thereof !!

Cheers,
TC