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
|