Update Open Continuous Form Using Query (Record Locked Error)

[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.

 

[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?

 

[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 :/

 

[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?

 

[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!