By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,826 Members | 2,443 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,826 IT Pros & Developers. It's quick & easy.

Access VBA used not printing to templates - failing on second or third document

P: 1
I have an application where reviwers test two types of water samples (ground water, table 16_5 and surface water, 17_5) for contaminants. They enter in the location of the water source and a multitude of samples

The data displays correctly in the form and in the tables. If the reviewers need a hard copy of the report they click a print button and the data from the tables is transferred to two Microsoft Word documents, ws1 (location) and ws2.dot (samples) using code and bookmarks.

The bookmarks are working correcly because I do get at least one location and its corresponding samples to print print correctly. The first two locations and corresponding samples will print correctly. The third location prints correctly and the sample document displays with the permit number and correct location ID but none of the sample data displays and the entire print job is terminated. The following error displays:

"The Requested member of the collection does not exist."

When I step through the code it fails at the line
Expand|Select|Wrap|Line Numbers
  1. "wrdDoc.FormFields("Sample" + SampleLoc).Result = IIf(IsNull(rst2(1).Value), "", rst2(1).Value)"
VBA Code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub PrintSampleSheets(ItemSheet As String, _
  2.                               wrdApp As Object, _
  3.                               wrdDoc As Object, _
  4.                               ComparePrt As Boolean, _
  5.                               CompareFolder As String)
  6. Dim rst, rst2 As Recordset
  7. Dim docPathname As String
  8. Dim SampleCnt As Integer
  9. Dim SampleLoc As String
  10. Dim SamplePrinted As Boolean
  11. Dim SiteCnt As Integer
  12. Dim SampleSheetCnt As Integer
  13. Dim SaveName As String
  14. Dim CurrentSample As Long
  15.  
  16.  
  17.   SiteCnt = 1
  18.   Set rst = CurrentDb.OpenRecordset("Section" + ItemSheet + "_5_Qry")
  19.   Do While Not rst.EOF()
  20.     docPathname = InstallDir + "sections\wq1.dot"
  21.     If Dir(docPathname) = "" Then
  22.       MsgBox ("WQ Form Not Found.")
  23.       Exit Sub
  24.     End If
  25.     Set wrdDoc = wrdApp.Documents.Add(docPathname)
  26.     wrdDoc.FormFields("PermitNumber").Result = IIf(IsNull(Forms!main!PermitNumber.Value), "", Forms!main!PermitNumber.Value)
  27.     wrdDoc.FormFields("StationNumber").Result = IIf(IsNull(rst(0).Value), "", rst(0).Value)
  28.     wrdDoc.FormFields("SOAP").Result = IIf(IsNull(Forms!main!Section6!SOAP_Number.Value), "", Forms!main!Section6!SOAP_Number.Value)
  29.     wrdDoc.FormFields("County").Result = IIf(IsNull(rst(7).Value), "", rst(7).Value)
  30.     wrdDoc.FormFields("Basin").Result = IIf(IsNull(rst(8).Value), "", rst(8).Value)
  31.     wrdDoc.FormFields("QUAD").Result = IIf(IsNull(rst(9).Value), "", rst(9).Value)
  32.     Select Case rst(10).Value
  33.       Case 1
  34.         wrdDoc.FormFields("Lake").CheckBox.Value = True
  35.       Case 2
  36.         wrdDoc.FormFields("Discharge").CheckBox.Value = True
  37.       Case 3
  38.         wrdDoc.FormFields("Influent").CheckBox.Value = True
  39.       Case 4
  40.         wrdDoc.FormFields("Spring").CheckBox.Value = True
  41.       Case 5
  42.         wrdDoc.FormFields("Spring").CheckBox.Value = True
  43.       Case 6
  44.         wrdDoc.FormFields("Well").CheckBox.Value = True
  45.     End Select
  46.     wrdDoc.FormFields("Depth").Result = IIf(IsNull(rst(11).Value), "", rst(11).Value)
  47.     wrdDoc.FormFields("Diameter").Result = IIf(IsNull(rst(12).Value), "", rst(12).Value)
  48.     wrdDoc.FormFields("Aquifer").Result = IIf(IsNull(rst(13).Value), "", rst(13).Value)
  49.     wrdDoc.FormFields("TopOfAuifer").Result = IIf(IsNull(rst(14).Value), "", rst(14).Value)
  50.     wrdDoc.FormFields("Thickness").Result = IIf(IsNull(rst(15).Value), "", rst(15).Value)
  51.     wrdDoc.FormFields("Elevation").Result = IIf(IsNull(rst(16).Value), "", rst(16).Value)
  52.     wrdDoc.FormFields("Watershed").Result = IIf(IsNull(rst(17).Value), "", rst(17).Value)
  53.     wrdDoc.FormFields("DrainageArea").Result = IIf(IsNull(rst(18).Value), "", rst(18).Value)
  54.     wrdDoc.FormFields("Lat_Degree").Result = IIf(IsNull(rst(1).Value), "", Str(rst(1).Value))
  55.     wrdDoc.FormFields("Lat_Min").Result = IIf(IsNull(rst(2).Value), "", Str(rst(2).Value))
  56.     wrdDoc.FormFields("Lat_Sec").Result = IIf(IsNull(rst(3).Value), "", Str(rst(3).Value))
  57.     wrdDoc.FormFields("Long_Degree").Result = IIf(IsNull(rst(4).Value), "", Str(rst(4).Value))
  58.     wrdDoc.FormFields("Long_Min").Result = IIf(IsNull(rst(5).Value), "", Str(rst(5).Value))
  59.     wrdDoc.FormFields("Long_Sec").Result = IIf(IsNull(rst(6).Value), "", Str(rst(6).Value))
  60.     wrdDoc.FormFields("Stream").Result = IIf(IsNull(rst(19).Value), "", rst(19).Value)
  61.     wrdDoc.FormFields("Permittee").Result = IIf(IsNull(Forms!main!Section3!ApplName.Value), "", Forms!main!Section3!ApplName.Value)
  62.     wrdDoc.FormFields("Collecting").Result = IIf(IsNull(rst(20).Value), "", rst(20).Value)
  63.     wrdDoc.FormFields("Analyzing").Result = IIf(IsNull(rst(21).Value), "", rst(21).Value)
  64.  
  65.     Call PrintComment("Comments", IIf(IsNull(rst(22).Value), "", rst(22).Value), wrdDoc, True)
  66.  
  67.     Set rst2 = CurrentDb.OpenRecordset("select * from Section" + ItemSheet + "_5_Data_Qry where Station_Number = '" + rst(0).Value + "'")
  68.     If Not rst2.EOF() Then
  69.         If CompareFolder <> "None" Then
  70.             If ComparePrt Then
  71.               SaveName = CompareFolder + "Section" + ItemSheet + "Site" + Trim(Str(SiteCnt)) + "Sheet"
  72.               wrdDoc.SaveAs FileName:=SaveName
  73.             Else
  74.               wrdDoc.PrintOut
  75.             End If
  76.             wrdDoc.Close SaveChanges:=wdDoNotSaveChanges
  77.             Set wrdDoc = Nothing
  78.         End If
  79.     End If
  80.     SampleSheetCnt = 1
  81.     SampleCnt = 0
  82.     SamplePrinted = False
  83.     If Not rst2.EOF Then
  84.       docPathname = InstallDir + "sections\wq2.dot"
  85.       If Dir(docPathname) = "" Then
  86.         MsgBox ("WQ Form Not Found.")
  87.         Exit Sub
  88.       End If
  89.       Set wrdDoc = wrdApp.Documents.Add(docPathname)
  90.       wrdDoc.FormFields("PermitNumber").Result = IIf(IsNull(Forms!main!PermitNumber.Value), "", Forms!main!PermitNumber.Value)
  91.       wrdDoc.FormFields("StationNumber").Result = IIf(IsNull(rst(0).Value), "", rst(0).Value)
  92.     End If
  93.     Do While Not rst2.EOF()
  94.       SamplePrinted = True
  95.       If CurrentSample <> rst2(1).Value Then
  96.         SampleCnt = SampleCnt + 1
  97.         CurrentSample = rst2(1).Value
  98.       End If
  99.       If SampleCnt > 3 Then
  100.         If CompareFolder <> "None" Then
  101.             If ComparePrt Then
  102.               SaveName = CompareFolder + "Section" + ItemSheet + "Site" + Trim(Str(SiteCnt)) + "SampleSheet" + Trim(Str(SampleSheetCnt))
  103.               SampleSheetCnt = SampleSheetCnt + 1
  104.               wrdDoc.SaveAs FileName:=SaveName
  105.               wrdDoc.Close SaveChanges:=wdDoNotSaveChanges
  106.               Set wrdDoc = wrdApp.Documents.Add(docPathname)
  107.               wrdDoc.FormFields("PermitNumber").Result = IIf(IsNull(Forms!main!PermitNumber.Value), "", Forms!main!PermitNumber.Value)
  108.               wrdDoc.FormFields("StationNumber").Result = IIf(IsNull(rst(0).Value), "", rst(0).Value)
  109.             Else
  110.               wrdDoc.PrintOut
  111.             End If
  112.         End If
  113.         SampleCnt = 1
  114.       End If
  115.       SampleLoc = Trim(Str(SampleCnt))
  116.       wrdDoc.FormFields("Sample" + SampleLoc).Result = IIf(IsNull(rst2(1).Value), "", rst2(1).Value)
  117.       wrdDoc.FormFields("SampleDate" + SampleLoc).Result = IIf(IsNull(rst2(2).Value), "", Str(rst2(2).Value))
  118.       Select Case rst2(3).Value
  119.         Case "ACID"
  120.           wrdDoc.FormFields("ACIDITY" + SampleLoc).Result = IIf(IsNull(rst2(4).Value), "", Str(rst2(4).Value))
  121.           wrdDoc.FormFields("ACIDITYInd" + SampleLoc).Result = IIf(IsNull(rst2(5).Value), "", rst2(5).Value)
  122.           Call BuildSampleComment(rst2, "ACIDITY", SampleLoc, wrdDoc)
  123.         Case "ALK"
  124.           wrdDoc.FormFields("ALKALINITY" + SampleLoc).Result = IIf(IsNull(rst2(4).Value), "", Str(rst2(4).Value))
  125.           wrdDoc.FormFields("ALKALINITYInd" + SampleLoc).Result = IIf(IsNull(rst2(5).Value), "", rst2(5).Value)
  126.           Call BuildSampleComment(rst2, "ALKALINITY", SampleLoc, wrdDoc)
  127.         Case "DPTH"
  128.           wrdDoc.FormFields("Depth" + SampleLoc).Result = IIf(IsNull(rst2(4).Value), "", Str(rst2(4).Value))
  129.           wrdDoc.FormFields("DepthInd" + SampleLoc).Result = IIf(IsNull(rst2(5).Value), "", rst2(5).Value)
  130.           Call BuildSampleComment(rst2, "DEPTH", SampleLoc, wrdDoc)
  131.         Case "DSCHG"
  132.           wrdDoc.FormFields("Discharge" + SampleLoc).Result = IIf(IsNull(rst2(4).Value), "", Str(rst2(4).Value))
  133.           wrdDoc.FormFields("DischargeInd" + SampleLoc).Result = IIf(IsNull(rst2(5).Value), "", rst2(5).Value)
  134.           Call BuildSampleComment(rst2, "DISCHARGE", SampleLoc, wrdDoc)
  135.         Case "FED"
  136.           wrdDoc.FormFields("FEDISS" + SampleLoc).Result = IIf(IsNull(rst2(4).Value), "", Str(rst2(4).Value))
  137.           wrdDoc.FormFields("FEDISSInd" + SampleLoc).Result = IIf(IsNull(rst2(5).Value), "", rst2(5).Value)
  138.           Call BuildSampleComment(rst2, "DISSOLVED IRON", SampleLoc, wrdDoc)
  139.         Case "FET"
  140.           wrdDoc.FormFields("FETOTAL" + SampleLoc).Result = IIf(IsNull(rst2(4).Value), "", Str(rst2(4).Value))
  141.           wrdDoc.FormFields("FETOTALInd" + SampleLoc).Result = IIf(IsNull(rst2(5).Value), "", rst2(5).Value)
  142.           Call BuildSampleComment(rst2, "TOTAL IRON", SampleLoc, wrdDoc)
  143.         Case "MND"
  144.           wrdDoc.FormFields("MDISS" + SampleLoc).Result = IIf(IsNull(rst2(4).Value), "", Str(rst2(4).Value))
  145.           wrdDoc.FormFields("MDISSInd" + SampleLoc).Result = IIf(IsNull(rst2(5).Value), "", rst2(5).Value)
  146.           Call BuildSampleComment(rst2, "DISSOLVED MANGANESE", SampleLoc, wrdDoc)
  147.         Case "MNT"
  148.           wrdDoc.FormFields("MTOTAL" + SampleLoc).Result = IIf(IsNull(rst2(4).Value), "", Str(rst2(4).Value))
  149.           wrdDoc.FormFields("MTOTALInd" + SampleLoc).Result = IIf(IsNull(rst2(5).Value), "", rst2(5).Value)
  150.           Call BuildSampleComment(rst2, "TOTAL MANGANESE", SampleLoc, wrdDoc)
  151.         Case "pH"
  152.           wrdDoc.FormFields("pH" + SampleLoc).Result = IIf(IsNull(rst2(4).Value), "", Str(rst2(4).Value))
  153.           wrdDoc.FormFields("pHInd" + SampleLoc).Result = IIf(IsNull(rst2(5).Value), "", rst2(5).Value)
  154.           Call BuildSampleComment(rst2, "PH", SampleLoc, wrdDoc)
  155.         Case "SO4"
  156.           wrdDoc.FormFields("SODISS" + SampleLoc).Result = IIf(IsNull(rst2(4).Value), "", Str(rst2(4).Value))
  157.           wrdDoc.FormFields("SODISSInd" + SampleLoc).Result = IIf(IsNull(rst2(5).Value), "", rst2(5).Value)
  158.           Call BuildSampleComment(rst2, "SULFATE", SampleLoc, wrdDoc)
  159.         Case "SPCON"
  160.           wrdDoc.FormFields("CONDUCTIVITY" + SampleLoc).Result = IIf(IsNull(rst2(4).Value), "", Str(rst2(4).Value))
  161.           wrdDoc.FormFields("CONDUCTIVITYInd" + SampleLoc).Result = IIf(IsNull(rst2(5).Value), "", rst2(5).Value)
  162.           Call BuildSampleComment(rst2, "CONDUCTIVITY", SampleLoc, wrdDoc)
  163.         Case "SS"
  164.           wrdDoc.FormFields("SETTSOLIDS" + SampleLoc).Result = IIf(IsNull(rst2(4).Value), "", Str(rst2(4).Value))
  165.           wrdDoc.FormFields("TempInd" + SampleLoc).Result = IIf(IsNull(rst2(5).Value), "", rst2(5).Value)
  166.           Call BuildSampleComment(rst2, "SETTLEABLE SOLIDS", SampleLoc, wrdDoc)
  167.         Case "TDS"
  168.           wrdDoc.FormFields("TDS" + SampleLoc).Result = IIf(IsNull(rst2(4).Value), "", Str(rst2(4).Value))
  169.           wrdDoc.FormFields("TDSInd" + SampleLoc).Result = IIf(IsNull(rst2(5).Value), "", rst2(5).Value)
  170.           Call BuildSampleComment(rst2, "TOTAL DISSOLVED SOLIDS", SampleLoc, wrdDoc)
  171.         Case "TSS"
  172.           wrdDoc.FormFields("TSS" + SampleLoc).Result = IIf(IsNull(rst2(4).Value), "", Str(rst2(4).Value))
  173.           wrdDoc.FormFields("TSSInd" + SampleLoc).Result = IIf(IsNull(rst2(5).Value), "", rst2(5).Value)
  174.           Call BuildSampleComment(rst2, "TOTAL SUSPENDED SOLIDS", SampleLoc, wrdDoc)
  175.         Case "DO"
  176.           wrdDoc.FormFields("ODISS" + SampleLoc).Result = IIf(IsNull(rst2(4).Value), "", Str(rst2(4).Value))
  177.           wrdDoc.FormFields("ODISSInd" + SampleLoc).Result = IIf(IsNull(rst2(5).Value), "", rst2(5).Value)
  178.           Call BuildSampleComment(rst2, "DISSOLVED OXYGEN", SampleLoc, wrdDoc)
  179.         Case "TEMP"
  180.           wrdDoc.FormFields("Temp" + SampleLoc).Result = IIf(IsNull(rst2(4).Value), "", Str(rst2(4).Value))
  181.           wrdDoc.FormFields("TempInd" + SampleLoc).Result = IIf(IsNull(rst2(5).Value), "", rst2(5).Value)
  182.           Call BuildSampleComment(rst2, "TEMPERATURE", SampleLoc, wrdDoc)
  183.       End Select
  184.       rst2.MoveNext
  185.     Loop
  186.     rst2.Close
  187.     rst.MoveNext
  188.     If Not rst.EOF Then
  189.         If CompareFolder <> "None" Then
  190.             If SamplePrinted Then
  191.                 If ComparePrt Then
  192.                   SaveName = CompareFolder + "Section" + ItemSheet + "Site" + Trim(Str(SiteCnt)) + "SampleSheet" + Trim(Str(SampleSheetCnt))
  193.                   wrdDoc.SaveAs FileName:=SaveName
  194.                 Else
  195.                   wrdDoc.PrintOut
  196.                 End If
  197.                 wrdDoc.Close SaveChanges:=wdDoNotSaveChanges
  198.                 Set wrdDoc = Nothing
  199.             End If
  200.         End If
  201.         SiteCnt = SiteCnt + 1
  202.     End If
  203.   Loop
  204.   If CompareFolder <> "None" Then
  205.     If SamplePrinted Then
  206.       If ComparePrt Then
  207.         SaveName = CompareFolder + "Section" + ItemSheet + "Site" + Trim(Str(SiteCnt)) + "SampleSheet" + Trim(Str(SampleSheetCnt))
  208.         wrdDoc.SaveAs FileName:=SaveName
  209.       Else
  210.         wrdDoc.PrintOut
  211.       End If
  212.       wrdDoc.Close SaveChanges:=wdDoNotSaveChanges
  213.       Set wrdDoc = Nothing
  214.     End If
  215.   End If
  216.  
  217. End Sub
  218.  
  219. Private Sub BuildSampleComment(rst As Recordset, _
  220.                                SampleType As String, _
  221.                                SampleLoc As String, _
  222.                                wrdDoc As Object)
  223. Dim CommentStr As String
  224.  
  225.   If Not IsNull(rst(6).Value) Then
  226.     CommentStr = SampleType + ":" + rst(6).Value
  227.     wrdDoc.Application.Selection.GoTo what:=wdGoToBookmark, Name:="Comments" + SampleLoc
  228.     wrdDoc.Bookmarks("Comments" + SampleLoc).Delete
  229.     wrdDoc.Application.Selection.TypeText Text:=" " + CommentStr
  230.     wrdDoc.Bookmarks.Add Name:="Comments" + SampleLoc
  231.   End If
  232. End Sub
  233.  
  234. Sub PrintComment(CommentGoto As String, _
  235.                  CommentText As String, _
  236.                  wrdDoc As Object, _
  237.                  DelField As Boolean)
  238.   wrdDoc.Application.Selection.GoTo what:=wdGoToBookmark, Name:=CommentGoto
  239.   If CommentText = "" Then
  240.     If DelField Then
  241.       wrdDoc.Application.Selection.Tables(1).Select
  242.       wrdDoc.Application.Selection.Tables(1).Delete
  243.     End If
  244.   Else
  245.     wrdDoc.Application.Selection.TypeText Text:=CommentText
  246.   End If
  247.  
  248. End Sub
Sep 21 '12 #1
Share this Question
Share on Google+
1 Reply


Rabbit
Expert Mod 10K+
P: 12,366
Please use code tags when posting code.

That error, basically, means that you don't have a form field with the name that you're trying to access.

So, assuming SampleLoc is equal to FOO, then the error is saying that you have no form field called SampleFOO.
Sep 21 '12 #2

Post your reply

Sign in to post your reply or Sign up for a free account.