Update Open Continuous Form Using Query (Record Locked Error) (1 Viewer)

[slm020]

Hi All,

This is my first time posting but I am having a little trouble with some code that I thought was pretty simple.

I have a continuous form, that I am trying to update using a query. The form is for frmNavigation!frmSubform!frmContinuous.

On the continuous form, there is a tick box to mark that the record has been submitted and a field for the submission date.

I have a VBA sub linked to a button on the Subform "Submit". The VBA does it's stuff and I then need the date boxes populated with Now() which is dealt with through an update query (DoCmd.OpenQuery).

Because the user would tick the tickbox then immediately press the "Submit" button, the last selected record is still open and so cannot be changed so I get a record locked error.

I have tried

me.dirty = false
Me!frmContinuous.Form.Dirty = False
me.dirty = false on a OnClick for the tickbox control
DoCmd.Save
I've even tried closing the Subform record and moving to a new record on the Subform but still get the lock violation.

At the moment my workaround is to pop up a message box with a warning to move to a record without a tickbox before hitting the Submit button to unlock the record but that can't work if the continuous form has only one record.

Thanks for any help you could give me, it would be much appreciated.

 

[XPS35]

First of all I don't think you need a tick box and a date. Just a date will do. If there is a date, the record is submitted.

You do not need a update query. Let the button just update the date on the form (me.SubmissionDate = Now())

 

[XPS35]

Make sure the date is locked on the form.

 

[slm020]

Hey there,

Thanks for coming back so quickly!

In an ideal world yes - I would just have the date which would be the easiest solution, but the date will be locked and will only get populated after the part of the VBA script where the report has been emailed, which is our way of covering ourselves and proving that an email handover was done for those selected record.

I just tried locking the date field (I didn't previously for testing purposes) but the record was still locked when the query ran.

I tried "Me!Subform.Form.SubmittedDate = Now()" expecting it to update all records on the continuous form - so then I could put in a if then, but it only populated a date for the selected record.

 

[Pat Hartman]

Do NOT run an update query to update a record that is open on a form!!! Why would you do that? The frm is bound. Just update it directly. If you want to store both the checkbox AND the date - you shouldn't. You should just store the date - then in the AfterUpdate event of the checkbox, simply populate the date:

If Me.chkSubmit = True then
    Me.SubmissionDate = Date()
Else
    Me.SubmissionDate = Null
End If

The date will be saved, when the form is saved.

The better method is to use a button to toggle the date.

If Not IsDate(Me.SubmissionDate) = True then
    Me.SubmissionDate = Date()
Else
    Me.SubmissionDate = Null
End If

 

[theDBguy]

Hi. Welcome to AWF!

Just wanted to clarify something... Is your UPDATE query changing the date for multiple records in the continuous form, or just the current one? Can you post the SQL statement?

 

[Pat Hartman]

Just wanted to clarify something... Is your UPDATE query changing the date for multiple records in the continuous form, or just the current one? Can you post the SQL statement?

Didn't even think of that. That would be a design flaw. The date does not belong in multiple records.

PS, although we do NOT run an update query to update the current bound record, we do open forms and reports that might include the current record. Therefore, before you open any form/report from the current form, you ALWAYS save the current record to ensure that the object you are opening is working with saved data.

 

[slm020]

Hi. Welcome to AWF!

Just wanted to clarify something... Is your UPDATE query changing the date for multiple records in the continuous form, or just the current one? Can you post the SQL statement?

Hi thanks for the welcome.

So, the continuous form can have anywhere between 1 and 30 records, some of which need to be sent to a different department, others may or may not be sent later. The tickbox would be me saying, this record should be part of an emailed report.

The VBA then sends an email with a report of only those records that have a tick to the relevant people, and after that, marks any records that had a tickbox with the current date (not editable by the user) as evidence that my department submitted the email to the relevant people.

The SQL is just check the main form record ID, and update any records with a tickbox with the date.

UPDATE tblStudies INNER JOIN tblServices ON tblStudies.ID = tblServices.StudyID SET tblServices.SubmittedDate = Now()
WHERE (((tblServices.SubmittedDate) Is Null) AND ((tblServices.Ready)=True) AND ((tblStudies.ID)=[tblstudies]![id]));

I totally understand that running an update query on an open form is not the thing to do, however have only been working in Access since July so I don't know of the correct solution :/

 

[Pat Hartman]

I'm confused about the process. You seem to be saying that you scroll though the records and check a record you want to update the date for and then move to the next record.

Maybe you're confused because this data is being viewed in a continuous subform. A form is a form regardless of whether it is single, continuous, or datesheet view. The code that runs in the form affects ONLY the current record.

That is the problem. You should not be using a field for this purpose, you should be using a button. In the click event of the button, you set the date.

Me.SubmittedNate = Now()

Or if you are using a subform in DS view where you can't have a button, you could use the double-click event of the date field. You would still use the If statement I posted earlier so you can toggle the date - ie set it or remove it. I changed the If to make it a positive test and to use Now() rather than Date().

However, I think you should be using Date() rather than Now() I don't know why you would want the time component as part of a Submission Date, I think that using Now will cause you problems later if for example, you wanted to find all the items submitted on 11/20/22. You wouldn't find any because they all have times in the date. Instead you would need to use a range.

If IsDate(Me.SubmissionDate) = True then
    Me.SubmissionDate = Null
Else
    Me.SubmissionDate = Now()
End If

 

[slm020]

Hi Pat, thanks for your suggestion - I completely agree with Date() that is a good call out that I hadn't thought of and something I will change throughout the database!

So the process is that we will be on a call with someone, asking which services they require. We can tick the box or untick the box of the various services (each with it's own record as there is more data we glean from the teleconference) as the conversation develops. If I have a button to set/unset a date then this could work - and I could change my report query to only catch today's date so it ignores the previously submitted services - because we might revisit the required services on later calls, but if we forget to hit the submit button, the VBA code that sends the email wouldn't be run, then my department could be at fault for something not happening, which is why I was hoping to have the VBA add submission dates to the selected records on screen and on requery, this would be shown as done and if the other departments come back to me, I can demonstrate that the dates were automatic, unchangeable and therefore the reports were definitely sent based on the fact that the code only populates the dates once the email has been sent. Do you see a way I could mitigate that?

 

[Pat Hartman]

If you can click on a checkbox, you can double click on a field. The field can be locked, but it has to be enabled. The code will run and update the field. YOU have to decide what to do if the user clicks on a date control that already has a date in it. I said you might want the code to act like a toggle. If it is empty fill it, if it is full, empty it. But that doesn't sound like what you want. But, I can't decide based on what you've told us if it makes sense to allow the double-click to update a field that already has a date.

What submit button? Where is is located? What prompts the user to press the button? Why can you not figure out automatically that a submission i necessary? We are on to a new question with this. UNLESS you want each individual double-click to perform the send.

Does the send need to happen immediately? Can there be a batch process? Can it wait until overnight, does running it in the close event of the mainform make sense?

This ia all pretty easy stuff once you can define when/why. For example, You can have an unbound field on the main form. Each time the code in the subform runs to add the date, the code could also add 1 to to the control in the mainform. Then when the main form closes, that event could send the email selecting all the items with today's date or including all the items if that makes more sense. A bound control is even better. That way if something disrupts the flow, the data will remain in the main form. The email won't have been sent but the next time you go back to the record, and close the form, they would be sent. This is a case where I would run an update query to clear the value because the form is no longer dirty and I don't want to dirty it in the middle of the close process. the query would not interfere with the close process.

 

[slm020]

Hi Pat thanks for your help in this - so I didn't want a batch, as the main form linked to the subforms each need to be submitted as separate emails and reports as and when they happen as there is a second stage to the meeting which has another report populated with dates that gets sent to the caller.

But I did use a mix of your idea and my initial implementation. I maintained the update query and refresh that updates all ticked except the locked record, and used VBA to do the - Box ticked? Date null? - check, Then add Date and dirty=false which gives me the exact outcome I needed which is perfect timing for being able to go on vacation without having to worry about it.

So thank you for all your help, you gave me the exact direction I needed!

 

[Pat Hartman]

I maintained the update query and refresh that updates all ticked except the locked record

You're welcome but bad choice. Apparently I was not emphatic enough I'm sorry I couldn't actually help you. I tried to teach you some important concepts. Instead, I gave you a few bullets that helped you to implement the bad idea and I shouldn't have done that. I seem to have failed to even communicate why it makes no sense to run an update query that updates records bound to the subform from the code of the subform. If you were not running the update query from the code of the subform but instead ran it from code in the main form (perhaps as it was closing as I suggested), you would never have had a problem with it at all. Even though it would still be a bad idea, it would at least work. You also did not understand the code I posted which eliminated the need for the checkbox and for the update query entirely. The date control is locked so the user cannot change it manually. Your code changes it without needing a checkbox. It is also the procedure where you could update a form level variable that you would use to send the email and print the report in the form's After update event. You use this technique because you don't want to send or print before the record is saved but by the time you get to an appropriate form level event, you don't have any way of determining if the send/print is necessary. The problem with sending the email and printing the report in the control's double-click event is that the record might not actually get saved and if it doesn't, you would not want the email to be sent or report to be printed.

and dirty=false

I did not tell you to do that and I don't recommend it. Sounds like you don't have validation code in your form's BeforeUpdate event to prevent saving incomplete records. Access will automatically save the record when you leave it. You should never force the record to be saved from a control event unless it is a button that is pressed to save when you are done. Remember, Access takes it as a personal mission to ALWAYS save dirty records. You NEVER have to force it unless you have some specific reason to do so. Usually, newcomers can't figure out how to stop Access from saving a record. Mostly, because they do not yet understand the purpose of the form's BeforeUpdate event.

I don't know how long you've been working with Access but you have adopted several poor practices. you also don't seem to have understood the concept of having the mainform decide automatically when to "submit", whatever that means. But, when the main form decides this, ALL the rows in the subform are saved. There is no dirty record in the subform, EVER because when focus shifts from the subform back to the mainform, Access is triggered to automatically save the current record if it is dirty.

Technically you can never be sure the emails get sent since once you pass them off to Outlook, they are out of your control. So, you send the email AFTER you KNOW the record has been saved. That means you would use a button on the mainform or code in the AfterUpdate event of the subform.

Perhaps if you ever build another application, you will reflect on the problems you continue to have with this one and understand how you might have prevented some of them by following the expert's advice. Good luck