Append Query, Delete Query and a Shared Form

[GussyBoy]

I have a database that is used by multiple users. I've create an MDE database to accomodate this. In this database I have a form that all my users can access, usually at the same time. What this form does is similar to an invoicing system where each user gets a new invoice number when they access this form. This part works fine. On this form is a command button that runs an append query to add the details of each users invoice to an 'Invoice Detail' table. The user keys the details on a subform. What is happening is that when a user hits this command button, the data is duplicated, sometimes triples in the Invoice Detail table. This problem happens 60% of the time and it's baffling me. My append query is standard where its just adding fields from the subform to the Invoice Detail table. To add, I also have a delete/clear table query that clears the data from this subform. What happens in this scenario is that if two users are using this form at the same time and one finishes before the next, user two's info gets clears prematurely. This makes it seems like the database is not really shared. I know that this is a mouth full, but can someone PLEASE shed some light and help me out on this?

Thanks for your help and time.

 

[PHV]

How is the new invoice number handled ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GussyBoy]

Well, I have a loop testing the invoice number field in the database to check for consecutive numbers. It starts from invoice 1 and loops to check for the next unused invoice number and gives it to the user.

 

[PHV]

And how the concurrent user using the same form at the same time is aware that this unused invoice number is no longer unused ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GussyBoy]

I dont think I follow that question but the entire process is transparent to the user. The user does not know which invoice numbers are unused. He/she just accepts that the invoice number is available. I've tested this logic in the database and it works fine. Do you think this is the reason for my issues?

 

[PHV]

So, I rephrase my question:
WHEN is the next unused invoice number given to the user and WHEN is it marked as no longer unused to ensure that another user don't get it ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GussyBoy]

Oh...now I follow....when an unused invoice number is given to the user, a record is written to the with that invoice number. When the user is done entering the details for the invoice number, that record is updated with these details. Hope this helps.

 

[GussyBoy]

Anything else?

 

[GussyBoy]

PHV, are you still there?

 

[PHV]

Have a look here:

http://www.tek-tips.com/faqs.cfm?fid=184

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GussyBoy]

Generation my invoice number is working fine. I don't have a problem with this. I have written code to create my own number. My problem lies with the queries where they duplicate and delete prematurely.