Record Locking and multiple users within Access Project

[ericpsu32]

Hello,

I have run into an issue with an invoicing system that I am creating. I would like to, on the click of a button, call a stored procedure and send a parameter to that procedure, have it update the record and update the table to reflect it in my database.

Here are the steps to my process. An invoice is approved to be billed. The user clicks a check box and this calls a stored procedure and updates a bit field and a date field. I know the update query is correct because I tested it in analyzer. What is happening is the record is not showing as updated in access project as well as when I go to leave the record, it gives me a record locking error and prompts me to save the changes. Ultimately what happens is when I go to generate the invoice, (which assigns an invoice number, then generates a report with that invoice number on it) it is unable to do so because the pieces within the procedure for that action have not been reflected on the main table (the check box and date from the approval process).

All my pieces work, I just can't get them to work one after another. I have thrown in me.refresh, me.requery, etc, but they end up causing more grief because it reshuffles the records.

Any ideas??

Thanks in advance!!

 

[cmmrfrds]

Do you have the record you are updating bound to the Form? It appears to be the case. If so, there may already be a lock on the record from the Form recordset - if pessimistic. If optimitic lock then, when you tab off the record ADO will detect the record was updated by another user. If you save the changes, it will over write the changes made in the stored procedure.

If my assumptions are correct, why do you need the stored procedure to update the record if it is bound and can be done from the Form?

 

[ericpsu32]

Well, I am not sure. This is my first attempt at creating a multi user application in ADP and wanted to make sure I was doing things right. I was reading all these posts saying it was bad to use a bound table as the record source, so I switched to a Stored Procedure. I thought I couldn't update the record with the sp driving the form. I found this out to not be the case after I made the post. Now I found the only time I get a lock is when I have two applications open on the same records, which should be the case. The problem of having the record be available still persists though. If I check the invoice approval box, it will update the approval date and the approval box, but if I do not refresh the form prior to generating the invoice number and invoice, it does not create an invoice because part of that criteria looks to see if these two fields are populated. Sorry about the lengthiness, but I want to provide as much detail as possible.

Right now my work around was to place refresh buttons on the form and create the sort for the form to be user friendly for finding the records that were being changed. Is there any way to have this happen automatically without causing grief in terms of sorts, etc?? I jump back to the first record of the for if I refresh?

Thanks for the help, I really appreciate it.
Eric

 

[cmmrfrds]

I am not sure of the sequence and whether you are adding a new record or updating an existing record. Maybe you show an example of the record on the Form and the various tables an key fields involved.

 

[ericpsu32]

Here are my steps, Form opens displaying data from a stored procedure that selects all records that have not been invoiced. A user will go through the list and approve records to be invoiced. When they click the check box, the approval date is populated and the check box is as well. At this time, the record is ready for invoicing. (side note...I use the approval method because we single invoice and statement invoice the records) The user would then hit a button that would open a report (the invoice to print) that is run from another stored procedure, this procedure finds the last invoice number in the system, adds 1, and generates a new invoice number for the record, then displays the invoice in print preview. In my app, however, the process runs through, but the new invoice number is never generated because the SQL database is not showing that the record was approved. The print preview shows the correct record since I join on a pkey field, but the invoice number isn't displayed. If I close the report and click the same button again, it works. So I have a refresh button that needs to be pressed before you can produce the invoice. This works, but adds one more step to the user process. I think my main issue is data synching between Access Project and my SQL Dbase.

I hope that clarifies...sorry if I didn't explain it good.

 

[cmmrfrds]

Let me see if I am clear.
1. record on Form has a field for approval and invoice#.
2. approval updated on Form.
3. report runs
4. stored procedure plugs the invoice# in the record.
5. report displays record.
6. want to see updated fields on Form?

 

[ericpsu32]

1. Record approved on form (user clicks check box populating a date)
2. USer clicks button that runs a stored procedure creating an invoice number)
3. Report opens for the record we are working

At this point the report is to have the invoice number that was generated from the procedure. However, the procedure fails to create one because the tbls in SQL never showed that the record was approved (even though it is displayed as approved on my front end).

Right now after the record is approved, I have the user hitting a refresh button, then clicking the generate button which goes on to steps 2 and 3. I tried including code on the check box to refresh the form, but this is a pain because it rearranges the data on each check, so i let the user do it themselves after they have checked a few records.

What do you think?

 

[cmmrfrds]

Has the record been tabbed off of before the report is run? If not try clicking on the update bar if you have it on the left side of the Form just to test.

 

[cjowsey]

I am not sure if I fully understand the problem but it sounds like the data displayed in the form is not being refreshed after you update the individual row. If you are using a stored procedure to populate the form you should refresh or requery your recordset after the update otherwise the data that you are displaying on the client may be out of sync with the server. A view will refresh automatically after the specified time set in Tools, Options. I wouldn't expect a stored procedure to do the same. Be careful with doing a requery it will give you a brand new recordset and you may have to reposition on the appropriate record.

 

[ericpsu32]

Cjowsey,

That is exactly what is happening. I have not come up with a better solution than allowing the user to control when the refresh happens and I have sorted by the information they will be updating so that what they change will be moved to the top of the form.

Thanks goes out to you and to cmmrfdrs for the help, I just wish their was a less meddlsome way to refresh.