Report Data Not Matching Query Results

[Rawb]

Hey all! I'm having a weird issue with one of my Reports. This is a Report that we save as a (Snapshot) File using DoCmd.OutputTo.



The problem is that, even though I can successfully update the Saved Query that the Report is based on (I have to update the Query using VBA because DoCmd.OutputTo doesn't let you specify a Filter or WhereCondition), the Report that gets saved shows old data!

I've checked the Query and it is being updated just fine. It's just the Report itself that shows the wrong data when saved to a file (important note: If I open the Report instead of just saving it to a file, it shows the correct data).

The Report used to save correctly and, since it's INSANELY complex I'd really prefer NOT to have to rebuild it from scratch again.

Has anyone run across this before?

[June7]

Do you open the report in PrintPreview before OutputTo? I do:

DoCmd.OpenReport strReport, acViewPreview, , "Submit.LabNum='" & rs!Labnum & "'"
DoCmd.OutputTo acOutputReport, "", acFormatPDF, strPath & rs!Labnum & ".pdf", False
DoCmd.Close acReport, strReport, acSaveNo

[Rawb]

I just tried that and it still doesn't properly update the Report

I've done a little more looking around and I think it might have to do with one of the fields the underlying Query returns. One of the fields is generated by a Function rather than just data from a Table. The problem is that I need this field to be included at the Query level because, well, it's basically the page number (the primary method of sorting on the Report). And, to make things worse, the project manager refuses to let me write this information to the database AND each location uses it's own method for generating this number.

Because of the level of complexity on the Report itself (not to mention the underlying Query), I would hate to have to manage a different Query and Report for each location

I've already tried putting together a SubSELECT to use instead of this Function, but I've been unable to get the correct result because not all of our Sales Order numbers are the same length (they're numbers that are saved as text strings for some reason): in this Query/field, the Sales Order number is ShopFloorMstr.SfmSoNo.

Note: I've stripped out the huge IIf statement that determines the plant's location. This example is just assuming the location is plant 7.

Rack: GetRackNo_07([ShopFloorMstr].[SfmGroupNo],[ShopFloorMstr].[SfmSoNo],[ShopFloorMstr].[SfmSoLineNo])

GetRackNo Code:

Public Function GetRackNo_07(GroupNo As String, SONo As String, LineNo As Integer) As Integer
  ' For Plant 07, the Rack number is determined by sorting the group by SO
  ' number and then the line number within each SO
  'On Error GoTo Error_GetRackNo_07 ' Commented out for debugging porpoises (YAY DOLPHINS!)

  GetRackNo_07 = -1 ' Initial value

  Dim dbsdb1 As DAO.Database

  Dim rst As DAO.Recordset

  Dim nbrRack As Integer

  Set dbsdb1 = CurrentDb()

  ' Get the list of every SO that is in the group
  Set rst = dbsdb1.OpenRecordset("SELECT [SfmSoNo], [SfmSoLineNo] FROM ShopFloorMstr WHERE [SfmGroupNo]='" & GroupNo & "' ORDER BY [SfmSoNo], [SfmSoLineNo]", dbOpenForwardOnly)

  nbrRack = 1

  ' Search for the SO and line on our list
  Do While Not (rst("SfmSoNo") = SONo And rst("SfmSoLineNo") = LineNo)
    ' If we don't find it, move to the next item and increment our counter
    nbrRack = nbrRack + 1

    rst.MoveNext
  Loop

  ' Once we've found it, return the counter value (essentially the .AbsolutePosition of our Record in the Recordset)
  GetRackNo_07 = nbrRack

FunctionClosing:
  'On Error Resume Next

  rst.Close

  Set rst = Nothing

  Set dbsdb1 = Nothing

  Exit Function

Error_GetRackNo_07:
  GetRackNo_07 = -1

  MsgBox Err.Number & ": " & Err.Description

  Resume FunctionClosing
End Function

[Rawb]

Maybe you can help me get the SubSELECT to work? If we can get it right for one of the locations, the others should be easy (well, easier) for me to figure out...

Plant 7 uses the following data to sort their Report:

1. Sales Order number string length, ascending
2. Sales Order number, ascending
3. Sales Order's line item number, ascending

The orders, once they are given a group number and released to the floor are recorded in the ShopFloorMstr Table in the following Fields:

1. Len([ShopFloorMstr].[SfmSoNo])
2. [ShopFloorMstr].[SfmSoNo]
3. [ShopFloorMstr].[SfmSoLineNo]

Based on the above, you would think that the following SubSELECT would work (assuming that the "outer" Query is referencing only the ShopFloorMstr Table):

SELECT
  COUNT([SFM].[SfmSoNo])
FROM 
  ShopFloorMstr AS [SFM]
WHERE
  (
    (
      Len([SFM].[SfmSoNo])<Len([ShopFloorMstr].[SfmSoNo])
    ) OR (
      Len([SFM].[SfmSoNo])=Len([ShopFloorMstr].[SfmSoNo]) AND 
      [SFM].[SfmSoNo]<[ShopFloorMstr].[SfmSoNo]
    ) OR (
      [SFM].[SfmSoNo]=[ShopFloorMstr].[SfmSoNo] AND 
      [SFM].[SfmSoLineNo]<=[ShopFloorMstr].[SfmSoLineNo]
    )
  ) AND
  [SFM].[SfmGroupNo]=[ShopFloorMstr].[SfmGroupNo]

But for some reason (which I can't figure out), it doesn't.

[June7]

Sales Order No as a text is appropriate as these values are identifiers, not numbers for calculation such as quantity. Identifier 'numbers' can include alphas. Whether or not they sort properly depends on structure. "10" will sort before "2". Need leading zeros. "002" and "010" will sort in that order.

I can't see anything blatantly wrong with code or query.

I presume you have step debugged.

I would have to examine the db if you want to provide.

[Rawb]

Well, I tried a few other things and, completely on accident I assure you, seem to have stumbled upon the solution.

The Query is being updated from within a Transaction and, as such, isn't actually getting changed until the Transaction is Committed (after the Function is finished running)! Frankly, I'm surprised I wasn't get an error by trying to load Recordsets after a transaction was started (although they are all just read-only).

So, what I need to do now is recode this portion to happen outside of the transaction instead of within it.

I'll leave this Thread open for now, but I'm hopeful that moving this Function outside of the Transaction will eliminate the issue.

[June7]

Interesting. I have a Transaction process that includes function that deletes records and opening a recordset is in that function.

[Rawb]

Whelp, I guess I know you can do that now