Multiple users trying to access reports with append queries ::Please Help::

[gold007eye]

I have a dilema on my hands as I can't seem to figure out the best way to accomplish this. I created a database to handle all of our letters for my department in the company. The problem I am having is that we have 26 people who could possible access the database at the same time. The letters are created using a form. (not a problem with multi-users) When someone wants to print the letter the do a search either by "Letter Date", "Provider Name", "Provider Number", or "Group Name". I set it up so either one of these search queries append data into one table called "Letter Report Information" all 38 letters get there data from this 1 table. The problem I am having is that if John and Mary try to pull different/same letters at the same time or while one is viewing their letters the data is getting pushed onto the others Letter.

Example:

Mary goes to print her Welcome letter
John also goes to print his Denial letter at the same time

When John's letters show up.. the Denial letter shows up with his data, but also with Mary's Welcome letter data.

What can I do or what kind of query can i use to avoid this problem as it is becoming a very prominent issue? :lease Help::

Thanks.

 

[Meltdown]

Is the DB on a network?, have you tried splitting the database and giving each user their own front-end with the forms reports etc. Search the forum for 'Split Database', lot's of posts.

 

[gold007eye]

Yes it is on a company network. I have the database split, but leave the front end on the network and put a shortcut on their desktop. I would have now problems putting the front end on their own computer, but the database is updated constantly and I don't know if there is a way to make their computer update the database to the newest version without me having to do it manually every update. Any suggestions?

 

[KenHigg]

Sounds like you may need to put the 'Letter Report Information' table in the 'FE'...

 

[gold007eye]

That tbl is in the front end, but all the users connect to the same FE that is the problem

 

[KenHigg]

Then the only other thing I can think of is to add some kind of identifier field in table that records where(from who) the data is coming from. Then use that fld to limit the outputs...

Make sense?

 

[gold007eye]

You just gave me another idea.. I recently added a field called [UserID] to capture the persons name when they create the letter. Is there any code I could put on the report that would look at the userID field and only show the letters pertaining to that userID and hide or not show any other users letter?

If [User ID] <> ("Field [User ID] on Report") Then
Somehow only show the letters for the person who created them and not show any letters with a userid different from their own?

 

[stevehooley]

Hi gold007eye,

Firstly I would avoid appending to a table for the generation of a report if possible (rather use a query that filters from an existing table and can be accessed by many users).

If the append method is the only way, have you considered having another, one record table that contains a check box that is updated when a user is printing the report? You could use this to ensure only one person is printing at a time.

Its not an ideal method but I've used it before and it does the job.

 

[KenHigg]

You just gave me another idea.. I recently added a field called [UserID] to capture the persons name when they create the letter. Is there any code I could put on the report that would look at the userID field and only show the letters pertaining to that userID and hide or not show any other users letter?

If [User ID] <> ("Field [User ID] on Report") Then
Somehow only show the letters for the person who created them and not show any letters with a userid different from their own?

I think you just solved your own problem. Or am I missing something? Limit it in the reports underlying query...

 

[gold007eye]

I have the concept figured out, but I am not sure how to implement the code into the report and where on the report I would put the code.. I am thinking I would use the OnLoad event. I have the "If" statement down; just not sure what the "Then" statement should be.

How do I tell access to only show the user ID of the person who submitted the report search?

 

[KenHigg]

Can you simply base the report on a query instead of a table that would do this limiting stuff for you?

 

[gold007eye]

I thought about that except every letter(report) has different fields that are used. I actually did try the query method, but that limits only 1 person at a time being able to access the letters. so if 2 people go in at the same time only 1 will be able to view the letter the other one gets an error message