Subform not updating

[Guest]

I have what I believe is an update problem that I cannot figure out. I have
a main form (fGeneralInfo) with an autonumber field JobNumber. There is a
subform (fStatus) with a date field (Incoming). Once user leaves main form,
the first field is the Incoming field where a date is entered. After
entering date, a report should be generated based on the JobNumber of the
main form. It worked for a short while, but then began printing blank pages
on the report. Here is the code for the Incoming field:
Private Sub Incoming_AfterUpdate()
On Error GoTo Err_Incoming_AfterUpdate


If [Forms]![fGeneralInformation]![CustomerName] = "Lyondell" Then
DoCmd.OpenReport "rRMABatchRegularLyondell", , , "JobNumber=" &
[Forms]![fGeneralInformation]![JobNumber]
Else
DoCmd.OpenReport "rRMABatchRegular", , , "JobNumber=" &
[Forms]![fGeneralInformation]![JobNumber]

End If

Exit_Incoming_AfterUpdate:
Exit Sub

Err_Incoming_AfterUpdate:
MsgBox Err.Description
Resume Exit_Incoming_AfterUpdate
End Sub

Here is requery code from subform - I've tried both and it still won't work

Private Sub Form_Activate()
[Forms]![f*GeneralInformationWITHQUOTE]![JobNumber].Requery
End Sub

Private Sub Form_AfterUpdate()
[Forms]![f*GeneralInformationWITHQUOTE]![JobNumber].Requery
End Sub

I've changed this around so much trying to get it to work and am very
frustrated. Any help is greatly appreciated!!
Thanks,
Phisaw

 

[Steve Schapel]

Phisaw,

First thing I noticed is that in your code, you refer to the
fGeneralInformation form, but in your description of the problem, it is
called fGeneralInfo. What's going on here?

In any case, the code is being called from an event on the subform, and
the JobNumber on the subform should be the same as the JobNumber on the
main form, so I would do it like this:

If Me.Parent!CustomerName = "Lyondell" Then
DoCmd.OpenReport "rRMABatchRegularLyondell", , , "JobNumber=" &
Me.JobNumber
Else
DoCmd.OpenReport "rRMABatchRegular", , , "JobNumber=" & Me.JobNumber
End If

I can't figure out what you are trying to do with the Requery stuff.
What is f*GeneralInformationWITHQUOTE? And isn't JobNumber a textbox?
So Requery doesn't really apply? What are you trying to achieve here?

 

[Pam]

Steve,
Thanks for replying. I'm sorry about the confusion. In describing my
problem, I used "info" instead of "information" and in the requery code
"f*GeneralInformationWITHQUOTE" is the actual name of the form, I just try
to shorten it. I started this db a long time ago and named this form with
the asterik and caps and now wish I hadn't.

I've tried requery on both forms in AfterUpdate, OnExit, OnCurrent, etc and
couldn't get anything to work, so I tried putting in the JobNumber field to
requery. I had this set up with macros and it would ask for the job number
before printing reports. I have been trying to do more with code and used
it in other places to automate form opening and report printing without the
job number prompt based on the current job number open. I don't understand
why it quit working. We did have a glitch (or something) run thru our db and
found that all users were not using the same version of Access. That has
been corrected.

Anyway, I tried the code you gave. I changed the Me.JobNumber to
Me.Parent!JobNumber - it is on the main form the same as CustomerName. It
still prints blank pages. I would appreciate any additional help or ideas
you may have on this. Thanks, Phisaw

Private Sub Incoming_AfterUpdate()
On Error GoTo Err_Incoming_AfterUpdate

If Me.Parent!CustomerName = "Lyondell" Then
DoCmd.OpenReport "rRMABatchRegularLyondell", , , "JobNumber=" &
Me.Parent!JobNumber
Else
DoCmd.OpenReport "rRMABatchRegular", , , "JobNumber=" &
Me.Parent!JobNumber

End If

Exit_Incoming_AfterUpdate:
Exit Sub

Err_Incoming_AfterUpdate:
MsgBox Err.Description
Resume Exit_Incoming_AfterUpdate
End Sub

Phisaw,

First thing I noticed is that in your code, you refer to the
fGeneralInformation form, but in your description of the problem, it is
called fGeneralInfo. What's going on here?

In any case, the code is being called from an event on the subform, and
the JobNumber on the subform should be the same as the JobNumber on the
main form, so I would do it like this:

If Me.Parent!CustomerName = "Lyondell" Then
DoCmd.OpenReport "rRMABatchRegularLyondell", , , "JobNumber=" &
Me.JobNumber
Else
DoCmd.OpenReport "rRMABatchRegular", , , "JobNumber=" & Me.JobNumber
End If

I can't figure out what you are trying to do with the Requery stuff. What
is f*GeneralInformationWITHQUOTE? And isn't JobNumber a textbox? So
Requery doesn't really apply? What are you trying to achieve here?

--
Steve Schapel, Microsoft Access MVP

I have what I believe is an update problem that I cannot figure out. I
have a main form (fGeneralInfo) with an autonumber field JobNumber.
There is a subform (fStatus) with a date field (Incoming). Once user
leaves main form, the first field is the Incoming field where a date is
entered. After entering date, a report should be generated based on the
JobNumber of the main form. It worked for a short while, but then began
printing blank pages on the report. Here is the code for the Incoming
field:
Private Sub Incoming_AfterUpdate()
On Error GoTo Err_Incoming_AfterUpdate


If [Forms]![fGeneralInformation]![CustomerName] = "Lyondell" Then
DoCmd.OpenReport "rRMABatchRegularLyondell", , , "JobNumber=" &
[Forms]![fGeneralInformation]![JobNumber]
Else
DoCmd.OpenReport "rRMABatchRegular", , , "JobNumber=" &
[Forms]![fGeneralInformation]![JobNumber]
End If

Exit_Incoming_AfterUpdate:
Exit Sub
Err_Incoming_AfterUpdate:
MsgBox Err.Description
Resume Exit_Incoming_AfterUpdate
End Sub

Here is requery code from subform - I've tried both and it still won't
work

Private Sub Form_Activate()
[Forms]![f*GeneralInformationWITHQUOTE]![JobNumber].Requery
End Sub

Private Sub Form_AfterUpdate()
[Forms]![f*GeneralInformationWITHQUOTE]![JobNumber].Requery
End Sub

I've changed this around so much trying to get it to work and am very
frustrated. Any help is greatly appreciated!!
Thanks, Phisaw

 

[Steve Schapel]

Pam,

Does the report include data as per what is on the subform? If so, you
may need to save the subform record berfore it can be correctly included
in the report's record source. Try putting this in your code, before
the report printing...
DoCmd.RunCommand acCmdSaveRecord

I had assumed the JobNumber was the basis of the relationship (Link
Master Fields / Link Child Fields) between the main form and the
subform. Is that not so? If it is, then the JobNumber on the subform
should be the same as the JobNumber on the main form. If the
relationship is based on other fields, you may need to check that this
is operating correctly.

 

[Pam]

Steve,

Per your question
Does the report include data as per what is on the subform?

No, the subform is just fields with dates (set up like stages of job
progress). When the Incoming date is entered, it prints an report with a
return authorization number. I've included the SQL from the Record Source
query of the report. JobNumber on main form should equal the JobNumber on
the report. When I enter the date in Incoming, it prints a blank report. I
can click back in the main form, then go to Incoming and it will print the
report. I've tried clicking on a command button that requeries and asks for
the job number again and then entering date into Incoming, but it still only
prints blank pages.

SELECT qGeneralInfo.JobNumber, qGeneralInfo.Phone,
qGeneralInfo.CustomerName, qGeneralInfo.PumpType, qGeneralInfo.Model,
qGeneralInfo.SerialNumber, qGeneralInfo.ItemNumber,
qGeneralInfo.ContactName, qGeneralInfo.Fax, qGeneralInfo.Coordinator,
qGeneralInfo.CoordinatorPhone, qGeneralInfo.CoordinatorEmail,
qGeneralInfo.PurchaseOrderNumber
FROM qGeneralInfo;

The main form and the subform are linked by JobNumber. I just can't figure
out what I'm missing with this. As I've said, I really appreciate your help
with this.
Pam

 

[Steve Schapel]

Pam,

What is qGeneralInfo? If this is a Query, could you also post back with
the SQL view of this? Thanks... we'll track this down soon .

 

[Pam]

Steve,

qGeneralInfo is a query and here is the SQL:

SELECT tStatus.Comments, tGeneralInfo.NikkisoType,
tGeneralInfo.MachineAssignment, tGeneralInfo.JobAssignment,
tGeneralInfo.HoldForTechIssues, tGeneralInfo.HotJob, tGeneralInfo.JobNumber,
tStatus.Incoming, tCustomerList.CustomerCode, tCustomerList.ShipTo,
tCustomerList.TaxRate, tGeneralInfo.CustomerName, tCustomerList.Address,
tCustomerList.City, tCustomerList.State, tCustomerList.Zip,
tGeneralInfo.ContactName, tContactList.Phone, tContactList.Fax,
tContactList.Email, tGeneralInfo.PurchaseOrderNumber, tGeneralInfo.PumpType,
tGeneralInfo.Model, tGeneralInfo.SerialNumber, tGeneralInfo.ItemNumber,
tGeneralInfo.Salesman, tGeneralInfo.Coordinator,
tCoordinatorList.CoordinatorPhone, tCoordinatorList.CoordinatorEmail,
tGeneralInfo.CommissionPayable
FROM (((tGeneralInfo LEFT JOIN tContactList ON tGeneralInfo.ContactName =
tContactList.ContactName) LEFT JOIN tCoordinatorList ON
tGeneralInfo.Coordinator = tCoordinatorList.Coordinator) LEFT JOIN
tCustomerList ON tGeneralInfo.CustomerName = tCustomerList.CustomerName)
INNER JOIN tStatus ON tGeneralInfo.JobNumber = tStatus.JobNumber;

Thanks again for taking the time to look at this.
Pam

 

[Steve Schapel]

Pam,

It appears that the qGeneralInfo query would require a JobNumber saved
in the tStatus table to correspond with the tGeneralInfo.JobNumber. So
this is the JobNumber that is going into your subform. So the subform
record needs to be saved before it can be included in the query. Hope
that makes sense.

So, if we can go back a couple of steps... I asked you:
Does the report include data as per what is on the subform? If so, you
may need to save the subform record before it can be correctly included
in the report's record source. Try putting this in your code, before
the report printing...
DoCmd.RunCommand acCmdSaveRecord

So, the correct answer is Yes, the report's Record Source draws on a
query that includes the JobNumber from the subform's current record.
So, did you try the code I suggested, to save the record? If so, and
you still get the blank report, let me know, as we than need to look
elsewhere for the problem. But if not, perhaps you would like to try it
now...

 

[Pam]

Steve,

I'm sorry - I guess I misunderstood the question you were asking about the
subform info on the quote. I tried Me.Refresh at the beginning of the code
for the Incoming textbox and it worked on the test job I ran. Could it be
that simple? You said I needed the code you gave, so I entered it as well
and it worked on a test job. I'm hoping this will fix the problem.
I have the same problem with a combo box on the same main form. It is
"PumpTypeCombo" and it is supposed to print a report based on the pump type
from the combo box and the job number on the form. And it prints blank
pages also. I tried Me.Refresh at the beginning of the code and still
blanks. I tried your code and still blanks. I don't understand if you have
basically the same code, it will work in some places and not others. If you
are interested in taking a look at this also, I have included the code below
and your help is again very much appreciated. The first one "SCMP" for the
rTeardownScmpBatchForFolder is the one I'm having problems with.

Private Sub PumpTypeCombo_AfterUpdate()
Me.Refresh
DoCmd.RunCommand acCmdSaveRecord
If Me.PumpTypeCombo = "SCMP" Then
DoCmd.OpenReport "rTeardownScmpBatchForFolder", , , "JobNumber=" &
[Forms]![f*GeneralInformationWITHQUOTE]![JobNumber]
DoCmd.OpenReport "rScmpDecontaminationGuidelines"
ElseIf Me.PumpTypeCombo = "NIKKISO" Then
DoCmd.OpenReport "rTeardownScmpBatchForFolder", , , "JobNumber=" &
Me.JobNumber
DoCmd.OpenReport "rScmpDecontaminationGuidelines"
ElseIf Me.PumpTypeCombo = "HMD/KONTRO" Then
DoCmd.OpenReport "rKontroDecontaminationGuidelines"
DoCmd.OpenReport "rKontroChecklistDrawing"
ElseIf Me.PumpTypeCombo = "COPPUS" Then
DoCmd.OpenReport "rTeardownCoppusTurbine", , , "JobNumber=" &
Me.JobNumber
ElseIf Me.PumpTypeCombo = "COPPUS TURBINE" Then
DoCmd.OpenReport "rTeardownCoppusTurbine", , , "JobNumber=" &
Me.JobNumber
ElseIf Me.PumpTypeCombo = "SUNDYNE" Then
DoCmd.OpenReport "rSundyneBearingWasherReplacement"
ElseIf Me.PumpTypeCombo = "TUTHILL BLOWER" Then
MsgBox "Tuthill Blower Booking Ref MDPneumatics"
ElseIf Me.PumpTypeCombo = "KONTRO" Then
MsgBox "Use Code HMD/KONTRO"
End If
End Sub

Here's the query for the report also:

SELECT qGeneralInfo.JobNumber, qGeneralInfo.CustomerName,
qGeneralInfo.PumpType, qGeneralInfo.Model, qGeneralInfo.SerialNumber,
qGeneralInfo.ItemNumber, tTeardownRotorAnalysis.RotorRecannable,
tTeardownRotorAnalysis.[00ReadMsds],
tTeardownRotorAnalysis.[01TakePictures],
tTeardownRotorAnalysis.[02InspectLiner],
tTeardownRotorAnalysis.[2ShaftDimRunout],
tTeardownRotorAnalysis.[2ShaftDimRunoutDim],
tTeardownRotorAnalysis.[2ShaftDimRunoutRunout],
tTeardownRotorAnalysis.[2AKeywayDim],
tTeardownRotorAnalysis.[2AKeywayDimDim],
tTeardownRotorAnalysis.[2AKeywayDimRunout],
tTeardownRotorAnalysis.[3ShaftDimRunout],
tTeardownRotorAnalysis.[3ShaftDimRunoutDim],
tTeardownRotorAnalysis.[3ShaftDimRunoutRunout],
tTeardownRotorAnalysis.[4KeywayDim], tTeardownRotorAnalysis.[4KeywayDimDim],
tTeardownRotorAnalysis.[4KeywayDimRunout],
tTeardownRotorAnalysis.[5ShaftDimRunout],
tTeardownRotorAnalysis.[5ShaftDimRunoutDim],
tTeardownRotorAnalysis.[5ShaftDimRunoutRunout],
tTeardownRotorAnalysis.[6ShaftDimRunout],
tTeardownRotorAnalysis.[6ShaftDimRunoutDim],
tTeardownRotorAnalysis.[6ShaftDimRunoutRunout],
tTeardownRotorAnalysis.[7KeywayDim], tTeardownRotorAnalysis.[7KeywayDimDim],
tTeardownRotorAnalysis.[7KeywayDimRunout],
tTeardownRotorAnalysis.[8LinerDimRunout],
tTeardownRotorAnalysis.[8LinerDimRunoutDim],
tTeardownRotorAnalysis.[8LinerDimRunoutRunout],
tTeardownRotorAnalysis.[9LinerDimRunout],
tTeardownRotorAnalysis.[9LinerDimRunoutDim],
tTeardownRotorAnalysis.[9LinerDimRunoutRunout],
tTeardownRotorAnalysis.[10LinerDimRunout],
tTeardownRotorAnalysis.[10LinerDimRunoutDim],
tTeardownRotorAnalysis.[10LinerDimRunoutRunout],
tTeardownRotorAnalysis.[11LinerDimRunout],
tTeardownRotorAnalysis.[11LinerDimRunoutDim],
tTeardownRotorAnalysis.[11LinerDimRunoutRunout],
tTeardownRotorAnalysis.[12ShaftDimRunout],
tTeardownRotorAnalysis.[12ShaftDimRunoutDim],
tTeardownRotorAnalysis.[12ShaftDimRunoutRunout],
tTeardownRotorAnalysis.[13KeywayDim],
tTeardownRotorAnalysis.[13KeywayDimDim],
tTeardownRotorAnalysis.[13KeywayDimRunout],
tTeardownRotorAnalysis.[14ShaftDimRunout],
tTeardownRotorAnalysis.[14ShaftDimRunoutDim],
tTeardownRotorAnalysis.[14ShaftDimRunoutRunout],
tTeardownRotorAnalysis.[15CheckRotorPlug],
tTeardownRotorAnalysis.[16CheckMsdsFlashpoints],
tTeardownRotorAnalysis.[17Bake Rotor],
tTeardownRotorAnalysis.[18ReportDatasheetQuote],
tTeardownRotorAnalysis.[19PrepRotor], tTeardownRotorAnalysis.ReportDate,
tTeardownRotorAnalysis.Supervisor, tTeardownRotorAnalysis.Technician,
tTeardownRotorAnalysis.QCSignature, tTeardownRotorAnalysis.SupervisorDate,
tTeardownRotorAnalysis.TechnicianDate, tTeardownRotorAnalysis.QCDate,
tTeardownRotorAnalysis.Comments
FROM qGeneralInfo LEFT JOIN tTeardownRotorAnalysis ON qGeneralInfo.JobNumber
= tTeardownRotorAnalysis.JobNumber;

 

[Steve Schapel]

Pam,

What happens if you change...
"JobNumber=" & [Forms]![f*GeneralInformationWITHQUOTE]![JobNumber]
to...
"JobNumber=" & Me.JobNumber

 

[Guest]

Steve,

I get a blank report. I started a job yesterday with NIKKISO (second on
list with Me.JobNumber) and got a blank report for that also. I really like
Access and have been working with db's for quite some time and am learning
more all the time. I've always used macros, but decided to code as much as I
could to make it run smoother for users. I didn't know it would be so
frustrating for me!! Thanks for looking at this.
Pam

Pam,

What happens if you change...
"JobNumber=" & [Forms]![f*GeneralInformationWITHQUOTE]![JobNumber]
to...
"JobNumber=" & Me.JobNumber

--
Steve Schapel, Microsoft Access MVP

Steve,

I'm sorry - I guess I misunderstood the question you were asking about the
subform info on the quote. I tried Me.Refresh at the beginning of the code
for the Incoming textbox and it worked on the test job I ran. Could it be
that simple? You said I needed the code you gave, so I entered it as well
and it worked on a test job. I'm hoping this will fix the problem.
I have the same problem with a combo box on the same main form. It is
"PumpTypeCombo" and it is supposed to print a report based on the pump type
from the combo box and the job number on the form. And it prints blank
pages also. I tried Me.Refresh at the beginning of the code and still
blanks. I tried your code and still blanks. I don't understand if you have
basically the same code, it will work in some places and not others. If you
are interested in taking a look at this also, I have included the code below
and your help is again very much appreciated. The first one "SCMP" for the
rTeardownScmpBatchForFolder is the one I'm having problems with.

Private Sub PumpTypeCombo_AfterUpdate()
Me.Refresh
DoCmd.RunCommand acCmdSaveRecord
If Me.PumpTypeCombo = "SCMP" Then
DoCmd.OpenReport "rTeardownScmpBatchForFolder", , , "JobNumber=" &
[Forms]![f*GeneralInformationWITHQUOTE]![JobNumber]
DoCmd.OpenReport "rScmpDecontaminationGuidelines"
ElseIf Me.PumpTypeCombo = "NIKKISO" Then
DoCmd.OpenReport "rTeardownScmpBatchForFolder", , , "JobNumber=" &
Me.JobNumber
DoCmd.OpenReport "rScmpDecontaminationGuidelines"
ElseIf Me.PumpTypeCombo = "HMD/KONTRO" Then
DoCmd.OpenReport "rKontroDecontaminationGuidelines"
DoCmd.OpenReport "rKontroChecklistDrawing"
ElseIf Me.PumpTypeCombo = "COPPUS" Then
DoCmd.OpenReport "rTeardownCoppusTurbine", , , "JobNumber=" &
Me.JobNumber
ElseIf Me.PumpTypeCombo = "COPPUS TURBINE" Then
DoCmd.OpenReport "rTeardownCoppusTurbine", , , "JobNumber=" &
Me.JobNumber
ElseIf Me.PumpTypeCombo = "SUNDYNE" Then
DoCmd.OpenReport "rSundyneBearingWasherReplacement"
ElseIf Me.PumpTypeCombo = "TUTHILL BLOWER" Then
MsgBox "Tuthill Blower Booking Ref MDPneumatics"
ElseIf Me.PumpTypeCombo = "KONTRO" Then
MsgBox "Use Code HMD/KONTRO"
End If
End Sub

 

[Steve Schapel]

Pam,

Well, VBA won't make anything run smoother compared to macros. Besides
which, I don't think this problem is related to using code.

But anyway, back to the main point! It seems to me that the report is
printing blank because the query it is based on is not returning any
data. I am not sure where the problem lies exactly, but troubleshooting
it will involve tracking back on the query. An easy way to confirm that
we are at least looking in the right place would be to put a line of
code like this immediately before the OpenReport line...
MsgBox DCount("*","NameOfQueryYourReportIsBasedOn")
I reckon your message box will say 0.
So, one thing that will have to be true before your query will produce a
record... Whatever the JobNumber on the form that the code is being
called from, there will have to exist a record with the same JobNumber
in the tTeardownRotorAnalysis table, *and* the tGeneralInfo table, *and*
the tStatus table. Might be a good thing to check on first.

 

[Pam]

Hi Steve,

I put the msg box in the code as you suggested and it returned 1073. I
checked the records and the job number just entered was not on all the
tables. I give up - I decided to place the code on the Incoming text box
now that it's working. I don't know why I didn't think of it earlier.
Thanks so much for your time and help with this! It is greatly
appreciated!!

By the way, the macros I had set up in this db, kept asking for job number
to refresh, requery and to print. Maybe I had them set up wrong. I just
thought they were limiting.
Thanks again,
Pam

 

[Steve Schapel]

Pam,

I put the msg box in the code as you suggested and it returned 1073.

Apologies, my mistake. I forgot about the Where Condition applied to
your OpenReport according to JobNumber. The test should have been...
MsgBox DCount("*","NameOfQueryYourReportIsBasedOn","[JobNumber]=" &
Me.JobNumber)

I checked the records and the job number just entered was not on all the
tables.

Ok, well it has to be, so you need to figure out how and why...

I give up - I decided to place the code on the Incoming text box
now that it's working. I don't know why I didn't think of it earlier.

Fair enough.

Thanks so much for your time and help with this! It is greatly
appreciated!!

You're welcome. Most people feel that there is a fairly steep learning
curve with stuff like this, and what you are trying to do is not
entirely trivial, so you would expect it to take some grunt to get it right.

By the way, the macros I had set up in this db, kept asking for job number
to refresh, requery and to print. Maybe I had them set up wrong. I just
thought they were limiting.

You must have had them set up wrong.