If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Hi Ken,
Don't know if you're still out there, but... I thought that the report writing would be the easy bit, but I'm getting an odd error... "The database engine could not lock table 'tblJobs' because it is already in use by another person or process" Basically what I'm trying to do is from my main form click a button to open up a seperate form which contains all of the details for the job; that works fine. From this new form, I want to add a button to run a report that prints out the details. If I use a button on the new form to launch the report, I receive the above error; if I launch it from the main form, I can see the report ('though it shows all records, not just the one that I want to see). I guess that it's because the new form is open and has locked the records, so I tried adding a "DoCmd.Close" bit of code to run before the prining code, however, this just closes the new form & gives the same error. Any suggestions? Regards Colin "Ken Snell [MVP]" wrote in message ... Glad to hear of your success.. I wish you well! and, you're welcome! -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Ken, Once again thanks for your detailed assitance - not just in working out the code, but also the explanations that go with it; it is greatly appreciated In fact this solution is a help to a similar problem that I had on another database (once again someone had asked for changes that would have been better resolved in the original build!!) Having solved this problem (with your help, guidance and code - in fact I seemed to do very little!), I realise that I had another where the name of the contact for the quote kept changing each time that I changed the contact. I then realised that the reason for this is that I was "just" holding the name of the contact within the form as far as the quote was concerned, so each time that the contact changed (even for a new quote), it changed within the quote, too. So what I've now done is to asdd these details into the quote table and set up a check box which, if checked, pulls through the latest contact details, if not allows the user to manually input. So, I think that's it, now (except that I've got to pull it all together in a report!), so once again thanks enormously for your help, support & guidance. Regards Colin Foster |
#12
|
|||
|
|||
Further...
managed to get the report working from my main form :-) but still wonder on the code for resolving the problem, but much less urgent, now... more of a "like to know" rather than "need to know" Regards Colin "Colin Foster" wrote in message ... Hi Ken, Don't know if you're still out there, but... I thought that the report writing would be the easy bit, but I'm getting an odd error... "The database engine could not lock table 'tblJobs' because it is already in use by another person or process" Basically what I'm trying to do is from my main form click a button to open up a seperate form which contains all of the details for the job; that works fine. From this new form, I want to add a button to run a report that prints out the details. If I use a button on the new form to launch the report, I receive the above error; if I launch it from the main form, I can see the report ('though it shows all records, not just the one that I want to see). I guess that it's because the new form is open and has locked the records, so I tried adding a "DoCmd.Close" bit of code to run before the prining code, however, this just closes the new form & gives the same error. Any suggestions? Regards Colin "Ken Snell [MVP]" wrote in message ... Glad to hear of your success.. I wish you well! and, you're welcome! -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Ken, Once again thanks for your detailed assitance - not just in working out the code, but also the explanations that go with it; it is greatly appreciated In fact this solution is a help to a similar problem that I had on another database (once again someone had asked for changes that would have been better resolved in the original build!!) Having solved this problem (with your help, guidance and code - in fact I seemed to do very little!), I realise that I had another where the name of the contact for the quote kept changing each time that I changed the contact. I then realised that the reason for this is that I was "just" holding the name of the contact within the form as far as the quote was concerned, so each time that the contact changed (even for a new quote), it changed within the quote, too. So what I've now done is to asdd these details into the quote table and set up a check box which, if checked, pulls through the latest contact details, if not allows the user to manually input. So, I think that's it, now (except that I've got to pull it all together in a report!), so once again thanks enormously for your help, support & guidance. Regards Colin Foster |
#13
|
|||
|
|||
Same problem, just different symptom.
Without knowing all the details, I'm assuming that your new form allows editing of tblJobs data. And that you're clicking a button whose location doesn't force a save of the data. Thus, when the query for your report's recordsource tries to get tblJobs table's data, that record is still locked because it's still being edited on the form. You'll need to modify your setup to ensure that the record in the new form is saved before the report is run. Assuming that the Print Report button on the new form is in the form's Header section, just add these lines of code Me.Dirty = False DoEvents as the first steps of that button's Click code. -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Hi Ken, Don't know if you're still out there, but... I thought that the report writing would be the easy bit, but I'm getting an odd error... "The database engine could not lock table 'tblJobs' because it is already in use by another person or process" Basically what I'm trying to do is from my main form click a button to open up a seperate form which contains all of the details for the job; that works fine. From this new form, I want to add a button to run a report that prints out the details. If I use a button on the new form to launch the report, I receive the above error; if I launch it from the main form, I can see the report ('though it shows all records, not just the one that I want to see). I guess that it's because the new form is open and has locked the records, so I tried adding a "DoCmd.Close" bit of code to run before the prining code, however, this just closes the new form & gives the same error. Any suggestions? Regards Colin "Ken Snell [MVP]" wrote in message ... Glad to hear of your success.. I wish you well! and, you're welcome! -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Ken, Once again thanks for your detailed assitance - not just in working out the code, but also the explanations that go with it; it is greatly appreciated In fact this solution is a help to a similar problem that I had on another database (once again someone had asked for changes that would have been better resolved in the original build!!) Having solved this problem (with your help, guidance and code - in fact I seemed to do very little!), I realise that I had another where the name of the contact for the quote kept changing each time that I changed the contact. I then realised that the reason for this is that I was "just" holding the name of the contact within the form as far as the quote was concerned, so each time that the contact changed (even for a new quote), it changed within the quote, too. So what I've now done is to asdd these details into the quote table and set up a check box which, if checked, pulls through the latest contact details, if not allows the user to manually input. So, I think that's it, now (except that I've got to pull it all together in a report!), so once again thanks enormously for your help, support & guidance. Regards Colin Foster |
#14
|
|||
|
|||
Hi Ken,
Thanks for the further support - I realise that you are working with both hands behind your back, one eye tightly shut and the other squinting as you've not seen the other parts of the DB! You're almost correct in your assumptions, however, I did set up a button to Save the Record so that (in my view) should have forced the save of the data. But it doesn't appear to. In addition, I've tried attaching your extra code to get the following... Private Sub PrintJobSheet_Click() On Error GoTo Err_PrintJobSheet_Click Me.Dirty = False DoEvents Dim stDocName As String Dim strfilter As String stDocName = "RptJobSheet" strfilter = "tbljobs.jobno = forms![frmjobsheet]!jobno" DoCmd.OpenReport stDocName, acPreview Exit_PrintJobSheet_Click: Exit Sub Err_PrintJobSheet_Click: MsgBox Err.Description Resume Exit_PrintJobSheet_Click End Sub But I still end up with the same answer. Grrr!! Any suggestions? Regards Colin "Ken Snell [MVP]" wrote in message ... Same problem, just different symptom. Without knowing all the details, I'm assuming that your new form allows editing of tblJobs data. And that you're clicking a button whose location doesn't force a save of the data. Thus, when the query for your report's recordsource tries to get tblJobs table's data, that record is still locked because it's still being edited on the form. You'll need to modify your setup to ensure that the record in the new form is saved before the report is run. Assuming that the Print Report button on the new form is in the form's Header section, just add these lines of code Me.Dirty = False DoEvents as the first steps of that button's Click code. -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Hi Ken, Don't know if you're still out there, but... I thought that the report writing would be the easy bit, but I'm getting an odd error... "The database engine could not lock table 'tblJobs' because it is already in use by another person or process" Basically what I'm trying to do is from my main form click a button to open up a seperate form which contains all of the details for the job; that works fine. From this new form, I want to add a button to run a report that prints out the details. If I use a button on the new form to launch the report, I receive the above error; if I launch it from the main form, I can see the report ('though it shows all records, not just the one that I want to see). I guess that it's because the new form is open and has locked the records, so I tried adding a "DoCmd.Close" bit of code to run before the prining code, however, this just closes the new form & gives the same error. Any suggestions? Regards Colin "Ken Snell [MVP]" wrote in message ... Glad to hear of your success.. I wish you well! and, you're welcome! -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Ken, Once again thanks for your detailed assitance - not just in working out the code, but also the explanations that go with it; it is greatly appreciated In fact this solution is a help to a similar problem that I had on another database (once again someone had asked for changes that would have been better resolved in the original build!!) Having solved this problem (with your help, guidance and code - in fact I seemed to do very little!), I realise that I had another where the name of the contact for the quote kept changing each time that I changed the contact. I then realised that the reason for this is that I was "just" holding the name of the contact within the form as far as the quote was concerned, so each time that the contact changed (even for a new quote), it changed within the quote, too. So what I've now done is to asdd these details into the quote table and set up a check box which, if checked, pulls through the latest contact details, if not allows the user to manually input. So, I think that's it, now (except that I've got to pull it all together in a report!), so once again thanks enormously for your help, support & guidance. Regards Colin Foster |
#15
|
|||
|
|||
First, you're never passing the filter to the report. (This isn't the issue
with the "locking" problem, but I noted it in your code.) Change these lines: strfilter = "tbljobs.jobno = forms![frmjobsheet]!jobno" DoCmd.OpenReport stDocName, acPreview to these: strfilter = "tbljobs.jobno = " & forms![frmjobsheet]!jobno DoCmd.OpenReport stDocName, acPreview, , strfilter If this "Me.Dirty = False" code isn't working, then there seems to be another lock on the table somewhere else. Any other forms open at this same time? -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Hi Ken, Thanks for the further support - I realise that you are working with both hands behind your back, one eye tightly shut and the other squinting as you've not seen the other parts of the DB! You're almost correct in your assumptions, however, I did set up a button to Save the Record so that (in my view) should have forced the save of the data. But it doesn't appear to. In addition, I've tried attaching your extra code to get the following... Private Sub PrintJobSheet_Click() On Error GoTo Err_PrintJobSheet_Click Me.Dirty = False DoEvents Dim stDocName As String Dim strfilter As String stDocName = "RptJobSheet" strfilter = "tbljobs.jobno = forms![frmjobsheet]!jobno" DoCmd.OpenReport stDocName, acPreview Exit_PrintJobSheet_Click: Exit Sub Err_PrintJobSheet_Click: MsgBox Err.Description Resume Exit_PrintJobSheet_Click End Sub But I still end up with the same answer. Grrr!! Any suggestions? Regards Colin "Ken Snell [MVP]" wrote in message ... Same problem, just different symptom. Without knowing all the details, I'm assuming that your new form allows editing of tblJobs data. And that you're clicking a button whose location doesn't force a save of the data. Thus, when the query for your report's recordsource tries to get tblJobs table's data, that record is still locked because it's still being edited on the form. You'll need to modify your setup to ensure that the record in the new form is saved before the report is run. Assuming that the Print Report button on the new form is in the form's Header section, just add these lines of code Me.Dirty = False DoEvents as the first steps of that button's Click code. -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Hi Ken, Don't know if you're still out there, but... I thought that the report writing would be the easy bit, but I'm getting an odd error... "The database engine could not lock table 'tblJobs' because it is already in use by another person or process" Basically what I'm trying to do is from my main form click a button to open up a seperate form which contains all of the details for the job; that works fine. From this new form, I want to add a button to run a report that prints out the details. If I use a button on the new form to launch the report, I receive the above error; if I launch it from the main form, I can see the report ('though it shows all records, not just the one that I want to see). I guess that it's because the new form is open and has locked the records, so I tried adding a "DoCmd.Close" bit of code to run before the prining code, however, this just closes the new form & gives the same error. Any suggestions? Regards Colin |
#16
|
|||
|
|||
Hi Ken,
Thanks for this extra bit. My main form is open. If I run the report from a button within that form, it works ok (I just have to tell the user to input the specific job number), then it runs. However is it likely that leaving this form open could be causing the problem as I don't think that it refers to the "TblJobs" table ('though as I'm having to "bolt on" bits to this database - it really needs a rebuild, but there's not the budget! - it might have happened). Assuming that this is the case then, when the new form opens also tries to link to this table, there is a locking problem. Presumably one way around this would be to close the forms, run the report & then reopen them. However, I tried that & it didn't seem to help. Regards Colin "Ken Snell [MVP]" wrote in message ... First, you're never passing the filter to the report. (This isn't the issue with the "locking" problem, but I noted it in your code.) Change these lines: strfilter = "tbljobs.jobno = forms![frmjobsheet]!jobno" DoCmd.OpenReport stDocName, acPreview to these: strfilter = "tbljobs.jobno = " & forms![frmjobsheet]!jobno DoCmd.OpenReport stDocName, acPreview, , strfilter If this "Me.Dirty = False" code isn't working, then there seems to be another lock on the table somewhere else. Any other forms open at this same time? -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Hi Ken, Thanks for the further support - I realise that you are working with both hands behind your back, one eye tightly shut and the other squinting as you've not seen the other parts of the DB! You're almost correct in your assumptions, however, I did set up a button to Save the Record so that (in my view) should have forced the save of the data. But it doesn't appear to. In addition, I've tried attaching your extra code to get the following... Private Sub PrintJobSheet_Click() On Error GoTo Err_PrintJobSheet_Click Me.Dirty = False DoEvents Dim stDocName As String Dim strfilter As String stDocName = "RptJobSheet" strfilter = "tbljobs.jobno = forms![frmjobsheet]!jobno" DoCmd.OpenReport stDocName, acPreview Exit_PrintJobSheet_Click: Exit Sub Err_PrintJobSheet_Click: MsgBox Err.Description Resume Exit_PrintJobSheet_Click End Sub But I still end up with the same answer. Grrr!! Any suggestions? Regards Colin "Ken Snell [MVP]" wrote in message ... Same problem, just different symptom. Without knowing all the details, I'm assuming that your new form allows editing of tblJobs data. And that you're clicking a button whose location doesn't force a save of the data. Thus, when the query for your report's recordsource tries to get tblJobs table's data, that record is still locked because it's still being edited on the form. You'll need to modify your setup to ensure that the record in the new form is saved before the report is run. Assuming that the Print Report button on the new form is in the form's Header section, just add these lines of code Me.Dirty = False DoEvents as the first steps of that button's Click code. -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Hi Ken, Don't know if you're still out there, but... I thought that the report writing would be the easy bit, but I'm getting an odd error... "The database engine could not lock table 'tblJobs' because it is already in use by another person or process" Basically what I'm trying to do is from my main form click a button to open up a seperate form which contains all of the details for the job; that works fine. From this new form, I want to add a button to run a report that prints out the details. If I use a button on the new form to launch the report, I receive the above error; if I launch it from the main form, I can see the report ('though it shows all records, not just the one that I want to see). I guess that it's because the new form is open and has locked the records, so I tried adding a "DoCmd.Close" bit of code to run before the prining code, however, this just closes the new form & gives the same error. Any suggestions? Regards Colin |
#17
|
|||
|
|||
I must admit that I do not "see" the setup correctly in my head, I fear, for
me to provide additional diagnoses at the moment. The error indicates a form or query has a table locked, and the report's query cannot gain access to the table. But from your description, I'm not "seeing" what the source of the lock might be. If you'd like to zip up the database with the objects that you're using, and email it to me with instructions on how to reproduce the situation, I'll take a look and see if I can find the problem (time permitting). My email address can be obtained from the reply address by removing this is not real from the address. -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Hi Ken, Thanks for this extra bit. My main form is open. If I run the report from a button within that form, it works ok (I just have to tell the user to input the specific job number), then it runs. However is it likely that leaving this form open could be causing the problem as I don't think that it refers to the "TblJobs" table ('though as I'm having to "bolt on" bits to this database - it really needs a rebuild, but there's not the budget! - it might have happened). Assuming that this is the case then, when the new form opens also tries to link to this table, there is a locking problem. Presumably one way around this would be to close the forms, run the report & then reopen them. However, I tried that & it didn't seem to help. Regards Colin "Ken Snell [MVP]" wrote in message ... First, you're never passing the filter to the report. (This isn't the issue with the "locking" problem, but I noted it in your code.) Change these lines: strfilter = "tbljobs.jobno = forms![frmjobsheet]!jobno" DoCmd.OpenReport stDocName, acPreview to these: strfilter = "tbljobs.jobno = " & forms![frmjobsheet]!jobno DoCmd.OpenReport stDocName, acPreview, , strfilter If this "Me.Dirty = False" code isn't working, then there seems to be another lock on the table somewhere else. Any other forms open at this same time? -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Hi Ken, Thanks for the further support - I realise that you are working with both hands behind your back, one eye tightly shut and the other squinting as you've not seen the other parts of the DB! You're almost correct in your assumptions, however, I did set up a button to Save the Record so that (in my view) should have forced the save of the data. But it doesn't appear to. In addition, I've tried attaching your extra code to get the following... Private Sub PrintJobSheet_Click() On Error GoTo Err_PrintJobSheet_Click Me.Dirty = False DoEvents Dim stDocName As String Dim strfilter As String stDocName = "RptJobSheet" strfilter = "tbljobs.jobno = forms![frmjobsheet]!jobno" DoCmd.OpenReport stDocName, acPreview Exit_PrintJobSheet_Click: Exit Sub Err_PrintJobSheet_Click: MsgBox Err.Description Resume Exit_PrintJobSheet_Click End Sub But I still end up with the same answer. Grrr!! Any suggestions? Regards Colin "Ken Snell [MVP]" wrote in message ... Same problem, just different symptom. Without knowing all the details, I'm assuming that your new form allows editing of tblJobs data. And that you're clicking a button whose location doesn't force a save of the data. Thus, when the query for your report's recordsource tries to get tblJobs table's data, that record is still locked because it's still being edited on the form. You'll need to modify your setup to ensure that the record in the new form is saved before the report is run. Assuming that the Print Report button on the new form is in the form's Header section, just add these lines of code Me.Dirty = False DoEvents as the first steps of that button's Click code. -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Hi Ken, Don't know if you're still out there, but... I thought that the report writing would be the easy bit, but I'm getting an odd error... "The database engine could not lock table 'tblJobs' because it is already in use by another person or process" Basically what I'm trying to do is from my main form click a button to open up a seperate form which contains all of the details for the job; that works fine. From this new form, I want to add a button to run a report that prints out the details. If I use a button on the new form to launch the report, I receive the above error; if I launch it from the main form, I can see the report ('though it shows all records, not just the one that I want to see). I guess that it's because the new form is open and has locked the records, so I tried adding a "DoCmd.Close" bit of code to run before the prining code, however, this just closes the new form & gives the same error. Any suggestions? Regards Colin |
#18
|
|||
|
|||
Hi Ken,
Thanks for the offer. I'll organise that tomorrow (it's 23:40 here & I'm just of to "the land of nod"! As I said, I've managed to make the report work from a different route, but it would be nice if we can spot the problem ('though don't spend too much time on it!) Regards Colin "Ken Snell [MVP]" wrote in message ... I must admit that I do not "see" the setup correctly in my head, I fear, for me to provide additional diagnoses at the moment. The error indicates a form or query has a table locked, and the report's query cannot gain access to the table. But from your description, I'm not "seeing" what the source of the lock might be. If you'd like to zip up the database with the objects that you're using, and email it to me with instructions on how to reproduce the situation, I'll take a look and see if I can find the problem (time permitting). My email address can be obtained from the reply address by removing this is not real from the address. -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Hi Ken, Thanks for this extra bit. My main form is open. If I run the report from a button within that form, it works ok (I just have to tell the user to input the specific job number), then it runs. However is it likely that leaving this form open could be causing the problem as I don't think that it refers to the "TblJobs" table ('though as I'm having to "bolt on" bits to this database - it really needs a rebuild, but there's not the budget! - it might have happened). Assuming that this is the case then, when the new form opens also tries to link to this table, there is a locking problem. Presumably one way around this would be to close the forms, run the report & then reopen them. However, I tried that & it didn't seem to help. Regards Colin "Ken Snell [MVP]" wrote in message ... First, you're never passing the filter to the report. (This isn't the issue with the "locking" problem, but I noted it in your code.) Change these lines: strfilter = "tbljobs.jobno = forms![frmjobsheet]!jobno" DoCmd.OpenReport stDocName, acPreview to these: strfilter = "tbljobs.jobno = " & forms![frmjobsheet]!jobno DoCmd.OpenReport stDocName, acPreview, , strfilter If this "Me.Dirty = False" code isn't working, then there seems to be another lock on the table somewhere else. Any other forms open at this same time? -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Hi Ken, Thanks for the further support - I realise that you are working with both hands behind your back, one eye tightly shut and the other squinting as you've not seen the other parts of the DB! You're almost correct in your assumptions, however, I did set up a button to Save the Record so that (in my view) should have forced the save of the data. But it doesn't appear to. In addition, I've tried attaching your extra code to get the following... Private Sub PrintJobSheet_Click() On Error GoTo Err_PrintJobSheet_Click Me.Dirty = False DoEvents Dim stDocName As String Dim strfilter As String stDocName = "RptJobSheet" strfilter = "tbljobs.jobno = forms![frmjobsheet]!jobno" DoCmd.OpenReport stDocName, acPreview Exit_PrintJobSheet_Click: Exit Sub Err_PrintJobSheet_Click: MsgBox Err.Description Resume Exit_PrintJobSheet_Click End Sub But I still end up with the same answer. Grrr!! Any suggestions? Regards Colin "Ken Snell [MVP]" wrote in message ... Same problem, just different symptom. Without knowing all the details, I'm assuming that your new form allows editing of tblJobs data. And that you're clicking a button whose location doesn't force a save of the data. Thus, when the query for your report's recordsource tries to get tblJobs table's data, that record is still locked because it's still being edited on the form. You'll need to modify your setup to ensure that the record in the new form is saved before the report is run. Assuming that the Print Report button on the new form is in the form's Header section, just add these lines of code Me.Dirty = False DoEvents as the first steps of that button's Click code. -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Hi Ken, Don't know if you're still out there, but... I thought that the report writing would be the easy bit, but I'm getting an odd error... "The database engine could not lock table 'tblJobs' because it is already in use by another person or process" Basically what I'm trying to do is from my main form click a button to open up a seperate form which contains all of the details for the job; that works fine. From this new form, I want to add a button to run a report that prints out the details. If I use a button on the new form to launch the report, I receive the above error; if I launch it from the main form, I can see the report ('though it shows all records, not just the one that I want to see). I guess that it's because the new form is open and has locked the records, so I tried adding a "DoCmd.Close" bit of code to run before the prining code, however, this just closes the new form & gives the same error. Any suggestions? Regards Colin |
#19
|
|||
|
|||
Colin -
I've reviewed your database and found the problem. The error occurs because your "job sheet" form (the second form) is using QryJobs as its recordsource. And the report that you're trying to open also uses that same query. Because the "job sheet" form is open at the same time, that query cannot be run by two separate objects. One workaround would be to use a different query for the report. Then your code should work as written. However, if you want to keep the same query for both the form and the report, you can rewrite your code to delete the form's Recordsource, run the query, and then restore the Recordsource. Here is sample code: ------code start--------------- Private Sub PrintJobSheet_Click() Dim strRecordSource As String, strFormFilter As String Dim stDocName As String Dim strfilter As String On Error GoTo Err_PrintJobSheet_Click Me.Dirty = False DoEvents stDocName = "RptJobSheet" strfilter = "jobno = " & Me.JobNo.Value ' Store the current recordsource and filter strings strRecordSource = Me.RecordSource strFormFilter = Me.Filter ' Freeze screen so that the data do not appear to change DoCmd.Echo False ' Remove the current recordsource so that the report can use it Me.RecordSource = "" DoCmd.OpenReport stDocName, acPreview, , strfilter Exit_PrintJobSheet_Click: ' Restore the recordsource to what it was Me.RecordSource = strRecordSource ' Restore the filter to what it was Me.Filter = strFormFilter ' Turn filter back on Me.FilterOn = True ' Return to the record that was active when the button was clicked Me.RecordsetClone.FindFirst strfilter Me.Bookmark = Me.RecordsetClone.Bookmark ' Unfreeze the screen DoCmd.Echo True Exit Sub Err_PrintJobSheet_Click: MsgBox Err.Description Resume Exit_PrintJobSheet_Click End Sub ----code end------- -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Hi Ken, Thanks for the offer. I'll organise that tomorrow (it's 23:40 here & I'm just of to "the land of nod"! As I said, I've managed to make the report work from a different route, but it would be nice if we can spot the problem ('though don't spend too much time on it!) Regards Colin "Ken Snell [MVP]" wrote in message ... I must admit that I do not "see" the setup correctly in my head, I fear, for me to provide additional diagnoses at the moment. The error indicates a form or query has a table locked, and the report's query cannot gain access to the table. But from your description, I'm not "seeing" what the source of the lock might be. If you'd like to zip up the database with the objects that you're using, and email it to me with instructions on how to reproduce the situation, I'll take a look and see if I can find the problem (time permitting). My email address can be obtained from the reply address by removing this is not real from the address. -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Hi Ken, Thanks for this extra bit. My main form is open. If I run the report from a button within that form, it works ok (I just have to tell the user to input the specific job number), then it runs. However is it likely that leaving this form open could be causing the problem as I don't think that it refers to the "TblJobs" table ('though as I'm having to "bolt on" bits to this database - it really needs a rebuild, but there's not the budget! - it might have happened). Assuming that this is the case then, when the new form opens also tries to link to this table, there is a locking problem. Presumably one way around this would be to close the forms, run the report & then reopen them. However, I tried that & it didn't seem to help. Regards Colin "Ken Snell [MVP]" wrote in message ... First, you're never passing the filter to the report. (This isn't the issue with the "locking" problem, but I noted it in your code.) Change these lines: strfilter = "tbljobs.jobno = forms![frmjobsheet]!jobno" DoCmd.OpenReport stDocName, acPreview to these: strfilter = "tbljobs.jobno = " & forms![frmjobsheet]!jobno DoCmd.OpenReport stDocName, acPreview, , strfilter If this "Me.Dirty = False" code isn't working, then there seems to be another lock on the table somewhere else. Any other forms open at this same time? -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Hi Ken, Thanks for the further support - I realise that you are working with both hands behind your back, one eye tightly shut and the other squinting as you've not seen the other parts of the DB! You're almost correct in your assumptions, however, I did set up a button to Save the Record so that (in my view) should have forced the save of the data. But it doesn't appear to. In addition, I've tried attaching your extra code to get the following... Private Sub PrintJobSheet_Click() On Error GoTo Err_PrintJobSheet_Click Me.Dirty = False DoEvents Dim stDocName As String Dim strfilter As String stDocName = "RptJobSheet" strfilter = "tbljobs.jobno = forms![frmjobsheet]!jobno" DoCmd.OpenReport stDocName, acPreview Exit_PrintJobSheet_Click: Exit Sub Err_PrintJobSheet_Click: MsgBox Err.Description Resume Exit_PrintJobSheet_Click End Sub But I still end up with the same answer. Grrr!! Any suggestions? Regards Colin "Ken Snell [MVP]" wrote in message ... Same problem, just different symptom. Without knowing all the details, I'm assuming that your new form allows editing of tblJobs data. And that you're clicking a button whose location doesn't force a save of the data. Thus, when the query for your report's recordsource tries to get tblJobs table's data, that record is still locked because it's still being edited on the form. You'll need to modify your setup to ensure that the record in the new form is saved before the report is run. Assuming that the Print Report button on the new form is in the form's Header section, just add these lines of code Me.Dirty = False DoEvents as the first steps of that button's Click code. -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Hi Ken, Don't know if you're still out there, but... I thought that the report writing would be the easy bit, but I'm getting an odd error... "The database engine could not lock table 'tblJobs' because it is already in use by another person or process" Basically what I'm trying to do is from my main form click a button to open up a seperate form which contains all of the details for the job; that works fine. From this new form, I want to add a button to run a report that prints out the details. If I use a button on the new form to launch the report, I receive the above error; if I launch it from the main form, I can see the report ('though it shows all records, not just the one that I want to see). I guess that it's because the new form is open and has locked the records, so I tried adding a "DoCmd.Close" bit of code to run before the prining code, however, this just closes the new form & gives the same error. Any suggestions? Regards Colin |
#20
|
|||
|
|||
I forgot to mention also that your job sheets form currently has its Record
Locks property set to All Records. This property should be set to No Locks, as your current setting is locking the entire table when this form is open. Many of your other forms and subforms have a similar setting for this property. All should be changed to No Locks. -- Ken Snell MS ACCESS MVP "Ken Snell [MVP]" wrote in message ... Colin - I've reviewed your database and found the problem. The error occurs because your "job sheet" form (the second form) is using QryJobs as its recordsource. And the report that you're trying to open also uses that same query. Because the "job sheet" form is open at the same time, that query cannot be run by two separate objects. One workaround would be to use a different query for the report. Then your code should work as written. However, if you want to keep the same query for both the form and the report, you can rewrite your code to delete the form's Recordsource, run the query, and then restore the Recordsource. Here is sample code: ------code start--------------- Private Sub PrintJobSheet_Click() Dim strRecordSource As String, strFormFilter As String Dim stDocName As String Dim strfilter As String On Error GoTo Err_PrintJobSheet_Click Me.Dirty = False DoEvents stDocName = "RptJobSheet" strfilter = "jobno = " & Me.JobNo.Value ' Store the current recordsource and filter strings strRecordSource = Me.RecordSource strFormFilter = Me.Filter ' Freeze screen so that the data do not appear to change DoCmd.Echo False ' Remove the current recordsource so that the report can use it Me.RecordSource = "" DoCmd.OpenReport stDocName, acPreview, , strfilter Exit_PrintJobSheet_Click: ' Restore the recordsource to what it was Me.RecordSource = strRecordSource ' Restore the filter to what it was Me.Filter = strFormFilter ' Turn filter back on Me.FilterOn = True ' Return to the record that was active when the button was clicked Me.RecordsetClone.FindFirst strfilter Me.Bookmark = Me.RecordsetClone.Bookmark ' Unfreeze the screen DoCmd.Echo True Exit Sub Err_PrintJobSheet_Click: MsgBox Err.Description Resume Exit_PrintJobSheet_Click End Sub ----code end------- -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Hi Ken, Thanks for the offer. I'll organise that tomorrow (it's 23:40 here & I'm just of to "the land of nod"! As I said, I've managed to make the report work from a different route, but it would be nice if we can spot the problem ('though don't spend too much time on it!) Regards Colin "Ken Snell [MVP]" wrote in message ... I must admit that I do not "see" the setup correctly in my head, I fear, for me to provide additional diagnoses at the moment. The error indicates a form or query has a table locked, and the report's query cannot gain access to the table. But from your description, I'm not "seeing" what the source of the lock might be. If you'd like to zip up the database with the objects that you're using, and email it to me with instructions on how to reproduce the situation, I'll take a look and see if I can find the problem (time permitting). My email address can be obtained from the reply address by removing this is not real from the address. -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Hi Ken, Thanks for this extra bit. My main form is open. If I run the report from a button within that form, it works ok (I just have to tell the user to input the specific job number), then it runs. However is it likely that leaving this form open could be causing the problem as I don't think that it refers to the "TblJobs" table ('though as I'm having to "bolt on" bits to this database - it really needs a rebuild, but there's not the budget! - it might have happened). Assuming that this is the case then, when the new form opens also tries to link to this table, there is a locking problem. Presumably one way around this would be to close the forms, run the report & then reopen them. However, I tried that & it didn't seem to help. Regards Colin "Ken Snell [MVP]" wrote in message ... First, you're never passing the filter to the report. (This isn't the issue with the "locking" problem, but I noted it in your code.) Change these lines: strfilter = "tbljobs.jobno = forms![frmjobsheet]!jobno" DoCmd.OpenReport stDocName, acPreview to these: strfilter = "tbljobs.jobno = " & forms![frmjobsheet]!jobno DoCmd.OpenReport stDocName, acPreview, , strfilter If this "Me.Dirty = False" code isn't working, then there seems to be another lock on the table somewhere else. Any other forms open at this same time? -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Hi Ken, Thanks for the further support - I realise that you are working with both hands behind your back, one eye tightly shut and the other squinting as you've not seen the other parts of the DB! You're almost correct in your assumptions, however, I did set up a button to Save the Record so that (in my view) should have forced the save of the data. But it doesn't appear to. In addition, I've tried attaching your extra code to get the following... Private Sub PrintJobSheet_Click() On Error GoTo Err_PrintJobSheet_Click Me.Dirty = False DoEvents Dim stDocName As String Dim strfilter As String stDocName = "RptJobSheet" strfilter = "tbljobs.jobno = forms![frmjobsheet]!jobno" DoCmd.OpenReport stDocName, acPreview Exit_PrintJobSheet_Click: Exit Sub Err_PrintJobSheet_Click: MsgBox Err.Description Resume Exit_PrintJobSheet_Click End Sub But I still end up with the same answer. Grrr!! Any suggestions? Regards Colin "Ken Snell [MVP]" wrote in message ... Same problem, just different symptom. Without knowing all the details, I'm assuming that your new form allows editing of tblJobs data. And that you're clicking a button whose location doesn't force a save of the data. Thus, when the query for your report's recordsource tries to get tblJobs table's data, that record is still locked because it's still being edited on the form. You'll need to modify your setup to ensure that the record in the new form is saved before the report is run. Assuming that the Print Report button on the new form is in the form's Header section, just add these lines of code Me.Dirty = False DoEvents as the first steps of that button's Click code. -- Ken Snell MS ACCESS MVP "Colin Foster" wrote in message ... Hi Ken, Don't know if you're still out there, but... I thought that the report writing would be the easy bit, but I'm getting an odd error... "The database engine could not lock table 'tblJobs' because it is already in use by another person or process" Basically what I'm trying to do is from my main form click a button to open up a seperate form which contains all of the details for the job; that works fine. From this new form, I want to add a button to run a report that prints out the details. If I use a button on the new form to launch the report, I receive the above error; if I launch it from the main form, I can see the report ('though it shows all records, not just the one that I want to see). I guess that it's because the new form is open and has locked the records, so I tried adding a "DoCmd.Close" bit of code to run before the prining code, however, this just closes the new form & gives the same error. Any suggestions? Regards Colin |
Thread Tools | |
Display Modes | |
|
|