A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Is it possible to show both "Yes" and "No" answers from a "yes/no" checkbox?



 
 
Thread Tools Display Modes
  #21  
Old March 16th, 2005, 05:16 PM
Ken Snell [MVP]
external usenet poster
 
Posts: n/a
Default

I didn't go through all the forms that have the All Records setting, and
didn't see if any of those subforms are using tblJobs in the forms'
recordsources' queries, but it's possible that, if you change all of them to
No Locks, then the locking problem may go away and your original code will
work ok. (You wouldn't need my workaround procedure.)

Try changing all the Record Locks properties first.
--

Ken Snell
MS ACCESS MVP



"Ken Snell [MVP]" wrote in message
...
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
















  #22  
Old March 16th, 2005, 10:24 PM
Colin Foster
external usenet poster
 
Posts: n/a
Default

Ken,
Once again I thank you for your detailed, helpful explanations.

I'ts one thing to get the answer, far more useful is to get the explanation,
too as this aids understanding.

I will have a go tomorrow with your advice regarding the No Locks first and
then your code if necessary.. I'll post the result.

Very best regards
Colin Foster


"Ken Snell [MVP]" wrote in message
...
I didn't go through all the forms that have the All Records setting, and
didn't see if any of those subforms are using tblJobs in the forms'
recordsources' queries, but it's possible that, if you change all of them
to No Locks, then the locking problem may go away and your original code
will work ok. (You wouldn't need my workaround procedure.)

Try changing all the Record Locks properties first.
--

Ken Snell
MS ACCESS MVP



"Ken Snell [MVP]" wrote in message
...
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


















  #23  
Old March 17th, 2005, 06:24 PM
Colin Foster
external usenet poster
 
Posts: n/a
Default

Hi Ken,

Tried you code and it "almost" worked... the report opens up, however the
sub reports do not show; there only appear placeholders to show where they
should be. I can't see anything in the code that you suggested that would
cause this because although the subreports are all linked by the JobNo, this
is reset within your code.

Did you get this effect?

Regards
Colin

"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














  #24  
Old March 17th, 2005, 07:36 PM
Ken Snell [MVP]
external usenet poster
 
Posts: n/a
Default

It's the subreports in the frmJobSheet form. Although their Record Locks
property is set to No Locks in my copy of the database, so long as they are
on this form, the report will not show the subreports. The subform labeled
"equipment to install" blocks the showing of the "equipment" subreport;
delete that subform and the report will show that subreport. All the other
subforms on frmJobSheet block the other subreports; delete all those other
subforms and the subreports will show.

If you have frmJobSheet open, and then try to open RptJobSheet from the
database window, you'll get a message about "tblJobs" being locked.

There is some type of additional lock in place.

I found one in the query "QryJobs". If you open this query in design view,
and right-click in the area above the grid (where the table shows), and
select Properties, you'll find that the Record Locks property here is set to
All Records. Change that to No Locks.

The other locks are in all your subreports ("RptQuoteDetailCat1" through
"RptQuoteDetailCat5"; and "RptJobAirCon"). Open them in design view, and
you'll see that their Record Locks property is set to All Records for the
reports. Change them to "No Locks".

Do this as well for RptJobSheet.

*NOW* the report will open correctly without the need for my workaround code
in the "Print" button's code for frmJobSheet.

I'm betting that all other reports, forms, etc. in your database have the
Record Locks set to the "All Records" value. Change them!
--

Ken Snell
MS ACCESS MVP


"Colin Foster" wrote in message
...
Hi Ken,

Tried you code and it "almost" worked... the report opens up, however the
sub reports do not show; there only appear placeholders to show where they
should be. I can't see anything in the code that you suggested that would
cause this because although the subreports are all linked by the JobNo,
this is reset within your code.

Did you get this effect?

Regards
Colin

"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
















  #25  
Old March 17th, 2005, 07:48 PM
Ken Snell [MVP]
external usenet poster
 
Posts: n/a
Default

Sorry... first sentence should read:

"It's the subforms in the frmJobSheet form."

--

Ken Snell
MS ACCESS MVP

"Ken Snell [MVP]" wrote in message
...
It's the subreports in the frmJobSheet form. Although their Record Locks
property is set to No Locks in my copy of the database, so long as they
are on this form, the report will not show the subreports. The subform
labeled "equipment to install" blocks the showing of the "equipment"
subreport; delete that subform and the report will show that subreport.
All the other subforms on frmJobSheet block the other subreports; delete
all those other subforms and the subreports will show.

If you have frmJobSheet open, and then try to open RptJobSheet from the
database window, you'll get a message about "tblJobs" being locked.

There is some type of additional lock in place.

I found one in the query "QryJobs". If you open this query in design view,
and right-click in the area above the grid (where the table shows), and
select Properties, you'll find that the Record Locks property here is set
to All Records. Change that to No Locks.

The other locks are in all your subreports ("RptQuoteDetailCat1" through
"RptQuoteDetailCat5"; and "RptJobAirCon"). Open them in design view, and
you'll see that their Record Locks property is set to All Records for the
reports. Change them to "No Locks".

Do this as well for RptJobSheet.

*NOW* the report will open correctly without the need for my workaround
code in the "Print" button's code for frmJobSheet.

I'm betting that all other reports, forms, etc. in your database have the
Record Locks set to the "All Records" value. Change them!
--

Ken Snell
MS ACCESS MVP


"Colin Foster" wrote in message
...
Hi Ken,

Tried you code and it "almost" worked... the report opens up, however the
sub reports do not show; there only appear placeholders to show where
they should be. I can't see anything in the code that you suggested that
would cause this because although the subreports are all linked by the
JobNo, this is reset within your code.

Did you get this effect?

Regards
Colin

"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


















  #26  
Old March 18th, 2005, 12:04 AM
Colin Foster
external usenet poster
 
Posts: n/a
Default

Hi Ken,

Tried this one and, yes, by deleting the "equipment to install" subForm from
frmJobSheet I can then run the report & the subreport showing the "equipment
to install" appears.

However, the problem with this is that the user cannot now select the
equipment that needs to be installed from the face of the frmJobSheet which
is what I wanted to be able to do. One way around this is to try to use a
button to open up each of the subforms in turn, change the relevant
information then close them. However this is less elegant. I think that I I
prefer being able to select on the frmJobSheet, even if this means having to
go a roundabout way to be able to print the report.

This locking & no locking is causing me confusion. It would appear that some
are on by default & others not. Do you always set locks to "no"?

Regards
Colin


"Ken Snell [MVP]" wrote in message
...
It's the subreports in the frmJobSheet form. Although their Record Locks
property is set to No Locks in my copy of the database, so long as they
are on this form, the report will not show the subreports. The subform
labeled blocks the showing of the "equipment" subreport; delete that
subform and the report will show that subreport. All the other subforms on
frmJobSheet block the other subreports; delete all those other subforms
and the subreports will show.

If you have frmJobSheet open, and then try to open RptJobSheet from the
database window, you'll get a message about "tblJobs" being locked.

There is some type of additional lock in place.

I found one in the query "QryJobs". If you open this query in design view,
and right-click in the area above the grid (where the table shows), and
select Properties, you'll find that the Record Locks property here is set
to All Records. Change that to No Locks.

The other locks are in all your subreports ("RptQuoteDetailCat1" through
"RptQuoteDetailCat5"; and "RptJobAirCon"). Open them in design view, and
you'll see that their Record Locks property is set to All Records for the
reports. Change them to "No Locks".

Do this as well for RptJobSheet.

*NOW* the report will open correctly without the need for my workaround
code in the "Print" button's code for frmJobSheet.

I'm betting that all other reports, forms, etc. in your database have the
Record Locks set to the "All Records" value. Change them!
--

Ken Snell
MS ACCESS MVP


"Colin Foster" wrote in message
...
Hi Ken,

Tried you code and it "almost" worked... the report opens up, however the
sub reports do not show; there only appear placeholders to show where
they should be. I can't see anything in the code that you suggested that
would cause this because although the subreports are all linked by the
JobNo, this is reset within your code.

Did you get this effect?

Regards
Colin

"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


















  #27  
Old March 18th, 2005, 03:06 AM
Ken Snell [MVP]
external usenet poster
 
Posts: n/a
Default

You don't need to delete anything from your form or subform or report or
subreport. What you need to do is change the "Record Locks" property in all
the forms and subforms and reports and subreports to No Locks.

Once you do that, you can print the report from the frmJobSheet. I tested
this here before I replied earlier this afternoon. No workaround code is
needed; your current code will work just fine. But you need to change the
record locking in all the forms and reports (open each from the database
window and use Properties to change them.)

I always set my locks to "no locks". This is the default setting that is in
your database as well (Tools | Options | Advanced), so someone had to have
set the record locks in the forms and reports differently.

--

Ken Snell
MS ACCESS MVP


"Colin Foster" wrote in message
...
Hi Ken,

Tried this one and, yes, by deleting the "equipment to install" subForm
from frmJobSheet I can then run the report & the subreport showing the
"equipment to install" appears.

However, the problem with this is that the user cannot now select the
equipment that needs to be installed from the face of the frmJobSheet
which is what I wanted to be able to do. One way around this is to try to
use a button to open up each of the subforms in turn, change the relevant
information then close them. However this is less elegant. I think that I
I prefer being able to select on the frmJobSheet, even if this means
having to go a roundabout way to be able to print the report.

This locking & no locking is causing me confusion. It would appear that
some are on by default & others not. Do you always set locks to "no"?

Regards
Colin


"Ken Snell [MVP]" wrote in message
...
It's the subreports in the frmJobSheet form. Although their Record Locks
property is set to No Locks in my copy of the database, so long as they
are on this form, the report will not show the subreports. The subform
labeled blocks the showing of the "equipment" subreport; delete that
subform and the report will show that subreport. All the other subforms
on frmJobSheet block the other subreports; delete all those other
subforms and the subreports will show.

If you have frmJobSheet open, and then try to open RptJobSheet from the
database window, you'll get a message about "tblJobs" being locked.

There is some type of additional lock in place.

I found one in the query "QryJobs". If you open this query in design
view, and right-click in the area above the grid (where the table shows),
and select Properties, you'll find that the Record Locks property here is
set to All Records. Change that to No Locks.

The other locks are in all your subreports ("RptQuoteDetailCat1" through
"RptQuoteDetailCat5"; and "RptJobAirCon"). Open them in design view, and
you'll see that their Record Locks property is set to All Records for the
reports. Change them to "No Locks".

Do this as well for RptJobSheet.

*NOW* the report will open correctly without the need for my workaround
code in the "Print" button's code for frmJobSheet.

I'm betting that all other reports, forms, etc. in your database have the
Record Locks set to the "All Records" value. Change them!
--

Ken Snell
MS ACCESS MVP


"Colin Foster" wrote in message
...
Hi Ken,

Tried you code and it "almost" worked... the report opens up, however
the sub reports do not show; there only appear placeholders to show
where they should be. I can't see anything in the code that you
suggested that would cause this because although the subreports are all
linked by the JobNo, this is reset within your code.

Did you get this effect?

Regards
Colin

"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




















  #28  
Old March 18th, 2005, 10:20 AM
Colin Foster
external usenet poster
 
Posts: n/a
Default

Hi Ken,
It was the subReports in the JobSheet Report that was causing the problem...
they were all set to "AllRecords", rather than "No Locks". Changed them &
all worked perfectly :-)

Once again, thanks for your patience and time. I really do appreciate it.

Very Best regards
Colin
"Ken Snell [MVP]" wrote in message
...
You don't need to delete anything from your form or subform or report or
subreport. What you need to do is change the "Record Locks" property in
all the forms and subforms and reports and subreports to No Locks.

Once you do that, you can print the report from the frmJobSheet. I tested
this here before I replied earlier this afternoon. No workaround code is
needed; your current code will work just fine. But you need to change the
record locking in all the forms and reports (open each from the database
window and use Properties to change them.)

I always set my locks to "no locks". This is the default setting that is
in your database as well (Tools | Options | Advanced), so someone had to
have set the record locks in the forms and reports differently.

--

Ken Snell
MS ACCESS MVP


"Colin Foster" wrote in message
...
Hi Ken,

Tried this one and, yes, by deleting the "equipment to install" subForm
from frmJobSheet I can then run the report & the subreport showing the
"equipment to install" appears.

However, the problem with this is that the user cannot now select the
equipment that needs to be installed from the face of the frmJobSheet
which is what I wanted to be able to do. One way around this is to try to
use a button to open up each of the subforms in turn, change the relevant
information then close them. However this is less elegant. I think that I
I prefer being able to select on the frmJobSheet, even if this means
having to go a roundabout way to be able to print the report.

This locking & no locking is causing me confusion. It would appear that
some are on by default & others not. Do you always set locks to "no"?

Regards
Colin


"Ken Snell [MVP]" wrote in message
...
It's the subreports in the frmJobSheet form. Although their Record Locks
property is set to No Locks in my copy of the database, so long as they
are on this form, the report will not show the subreports. The subform
labeled blocks the showing of the "equipment" subreport; delete that
subform and the report will show that subreport. All the other subforms
on frmJobSheet block the other subreports; delete all those other
subforms and the subreports will show.

If you have frmJobSheet open, and then try to open RptJobSheet from the
database window, you'll get a message about "tblJobs" being locked.

There is some type of additional lock in place.

I found one in the query "QryJobs". If you open this query in design
view, and right-click in the area above the grid (where the table
shows), and select Properties, you'll find that the Record Locks
property here is set to All Records. Change that to No Locks.

The other locks are in all your subreports ("RptQuoteDetailCat1" through
"RptQuoteDetailCat5"; and "RptJobAirCon"). Open them in design view, and
you'll see that their Record Locks property is set to All Records for
the reports. Change them to "No Locks".

Do this as well for RptJobSheet.

*NOW* the report will open correctly without the need for my workaround
code in the "Print" button's code for frmJobSheet.

I'm betting that all other reports, forms, etc. in your database have
the Record Locks set to the "All Records" value. Change them!
--

Ken Snell
MS ACCESS MVP


"Colin Foster" wrote in message
...
Hi Ken,

Tried you code and it "almost" worked... the report opens up, however
the sub reports do not show; there only appear placeholders to show
where they should be. I can't see anything in the code that you
suggested that would cause this because although the subreports are all
linked by the JobNo, this is reset within your code.

Did you get this effect?

Regards
Colin

"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






















  #29  
Old March 18th, 2005, 03:19 PM
Ken Snell [MVP]
external usenet poster
 
Posts: n/a
Default

Good luck.
--

Ken Snell
MS ACCESS MVP

"Colin Foster" wrote in message
...
Hi Ken,
It was the subReports in the JobSheet Report that was causing the
problem... they were all set to "AllRecords", rather than "No Locks".
Changed them & all worked perfectly :-)

Once again, thanks for your patience and time. I really do appreciate it.

Very Best regards
Colin



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:40 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.