NEW RECORDS IN A TABLE

[jadams0173]

If you use an append query to move records to a table, when are those records actuall there?

I have a problem where I append records to a table and then run a report. The new records don't show up on the report unless I close the report and then re-open it. I've tried for a week to figure out why.

I have captured the number of records before the append runs and after the append runs to make sure the recordcount does go up. So far nothing strange there.

I've tried the docmd.sendobject and now have it so the report is output to a file and the sent as an email attachment.

At the bottom of the report are some totals calculated using DSum. These totals are right even when the report doesn't show the new lines that were just added. I can post code if you want to see it. I've even added Sleep times and open the table and close it just to see if the records are there. Please help!

 

[Remou]

Yes, please post the code. Is it possible that the report is open during the append? If so, the totals would be correct as they reference the table but the report would have already been built, so it would not have the new lines.

 

[jadams0173]

Here is the code. It is being activated by a cmd button. There is a little that goes on above this but nothing to do with the report or the table in question.

sql = "INSERT INTO tblReleased ( MO_NUMBER, ITEM, ORDER_QTY, SCHED_DATE, MOTYPE, FEEDERTOTAL, WH_PICKS1, RELEASED, TotalPicks, TotalFeedersNeeded, RELDate, RELtime, lines, PRIORITY, COMMENT )" _
    & "SELECT " & TempTable & ".MO_NUMBER, " & TempTable & ".ITEM, " & TempTable & ".ORDER_QTY, " & TempTable & ".SCHED_DATE, " & TempTable & ".MOTYPE, Sum(" & TempTable & ".TotalFeeders) AS FEEDERTOTAL, " & TempTable & ".WH_PICKS1, " & TempTable & ".RELEASED,  " & Val([Forms]![frmmain]![txtPicksReleased]) & " AS TotalPicks, " & Val([Forms]![frmmain]![txtReleasedFeeders]) & " AS TotalFeedersNeeded, " & TempTable & ".RELDATE, " & TempTable & ".RELTIME, [" & TempTable & "]![PrimaryB] & ' / ' & [" & TempTable & "]![PrimaryT] AS lines, " & TempTable & ".PRIORITY, " & TempTable & ".COMMENT " _
    & "FROM " & TempTable _
    & " GROUP BY " & TempTable & ".MO_NUMBER, " & TempTable & ".ITEM, " & TempTable & ".ORDER_QTY, " & TempTable & ".SCHED_DATE, " & TempTable & ".MOTYPE, " & TempTable & ".WH_PICKS1, " & TempTable & ".RELEASED, " & TempTable & ".RELDATE, " & TempTable & ".RELTIME, [" & TempTable & "]![PrimaryB] & ' / ' & [" & TempTable & "]![PrimaryT], " & TempTable & ".PRIORITY, " & TempTable & ".COMMENT " _
    & "HAVING (((" & TempTable & ".RELEASED) = -1))"

CurrentProject.Connection.Execute sql, , adExecuteNoRecords


'MAKE SURE THERE ARE NO DUPLICATES IN TBLRELEASED
sql = "DELETE * " _
    & "FROM TBLRELEASED " _
    & "WHERE MYID Not In (SELECT Max(MYID) FROM TBLRELEASED GROUP BY MO_NUMBER)"
DoCmd.RunSQL sql

Call RC_After
NiteNite (2000)
With rs
    .Open "tblReleased", CurrentProject.Connection, adOpenStatic, adLockReadOnly, adCmdTable
    NiteNite (3000)
    .Close
End With

DoCmd.SetWarnings True
Call OpenConnection(conn)
    With rs
        .Open "tblPriority", conn, adOpenKeyset, adLockOptimistic, adCmdTable
        !PRIORITY = g_Priority
        .Update
        .Close
    End With
conn.Close
Set rs = Nothing
Set conn = Nothing

[red]If Me!chMORELEASED = -1 Then
    Screen.MousePointer = 11
        Call OUTPUTREPORT
        Call SENDREPORTASATTACHMENT
        
        DoCmd.OpenReport "RPT_WH_FEEDERLOAD_REQUIREMENTS", acViewNormal
        DoCmd.OpenReport "RPT_WH_FEEDERLOAD_REQUIREMENTS", acViewPreview
    Call Form_Refresh_Data
        Screen.MousePointer = 0
End If[/red]
Screen.MousePointer = 0

Here are the functions OUTPUTREPORT and SENDREPORTASATTACHMENT

The Call RC_BEFORE and AFTER is where i'm simply putting the recordcount into a table before and after the append to make sure that records were actuall being append. This check has show that the correct number of records were appended.

Function SENDREPORTASATTACHMENT()
Dim otk As Outlook.Application
Dim eml As Outlook.MailItem
Dim strList As String
Dim rs      As ADODB.Recordset

    ' Open your table and build your distribution list
    Set rs = New ADODB.Recordset
    With rs
        .Open "tblEmail", CurrentProject.Connection, adOpenStatic, adLockReadOnly, adCmdTable
    End With
    
    Do While Not rs.EOF
        strList = strList & Trim(rs.Fields("EmailAddress").Value) & ";"
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing

    Set otk = CreateObject("Outlook.Application")
    Set eml = otk.CreateItem(olMailItem)
    
    With eml
        .To = strList
        .Subject = "Feeder Load and Warehouse Report"
        .Body = "" '"This is a test."
        .Attachments.Add "F:\WH_FEEDERLOAD_RPT.SNP"
        .Send
    End With
    
    Set eml = Nothing
    Set otk = Nothing
'    otk.Quit

End Function
[BLUE]------------------------------------------------[/BLUE]
Function OUTPUTREPORT()
Dim fso     As FileSystemObject

Set fso = CreateObject("scripting.filesystemobject")

If fso.FileExists("F:\WH_FEEDERLOAD_RPT.SNP") Then
    fso.CopyFile "F:\WH_FEEDERLOAD_RPT.SNP", "F:\FEEDERLOAD_RPT_" & Format(Now(), "MMDDYY_HHMMSS") & ".SNP"
    Kill "F:\WH_FEEDERLOAD_RPT.SNP"
End If
'TEMP TO TEST THE OUTPUT OF THE REPORT...ITS NOT ACCURATE
    DoCmd.OutputTo acOutputReport, "RPT_WH_FEEDERLOAD_REQUIREMENTS", "MicrosoftExcel(*.xls)", "F:\SCHEDULER\REPORT.XLS", False, ""

    DoCmd.OutputTo acOutputReport, "RPT_WH_FEEDERLOAD_REQUIREMENTS", "SnapshotFormat(*.snp)", "F:\WH_FEEDERLOAD_RPT.SNP", False, ""
'    DoCmd.OutputTo acReport, "RPT_WH_FEEDERLOAD_REQUIREMENTS", "MicrosoftExcel(*.xls)", "F:\SCHEDULER\REPORT.XLS", False, ""

Set fso = Nothing
End Function

 

[Remou]

I'm afraid I can't see anything. I suppose NiteNite is some sort of wait? What is the point of this bit?
[tt]NiteNite (2000)
With rs
.Open "tblReleased", CurrentProject.Connection, adOpenStatic, adLockReadOnly, adCmdTable
NiteNite (3000)
.Close
End With[/tt]
Not that I can see it affecting anything.

I suppose you have tried opening the report rather that outputting it?

 

[jadams0173]

NiteNite is me looking for some sort of humor in this entire issue. It is nothing more than the Sleep. At one point I was thinking it may be a timing issue so I added the Sleep(nitenite) just to add a delay. I open the table and close just as a way to try to force the updates of the table however I don't feel that this is the issue anymore.

Public Declare Sub Sleep Lib "kernel32" (ByVal dwmilliseconds As Long)

Function NiteNite(ByVal dwmilliseconds As Long)

Sleep (dwmilliseconds)
End Function

The only thing I didn't post was the code in the open event of the report. It's just a maketable query that I use as the reocordsource of the report. I'll post it below.

Private Sub Report_Open(Cancel As Integer)
Dim sql     As String
Dim TempReport      As String
Dim RelDate         As Date

DoCmd.SetWarnings False

RelDate = Format(Now(), "mm/dd/yyyy")


TempReport = "Report_" & Environ("username")
    
sql = "SELECT tblReleased.MO_NUMBER, tblReleased.MOTYPE, tblReleased.ORDER_QTY, tblReleased.ITEM, tblReleased.FEEDERTOTAL, tblReleased.WH_PICKS1 AS PicksRequired, tblReleased.TotalPicks, tblReleased.TotalFeedersNeeded, tblReleased.relDate, tblReleased.lines, tblReleased.reltime, tblReleased.PRIORITY, tblReleased.COMMENT, IIf(IsNull([PicklistComplete]),'N','Y') AS DISB, IIf(IsNull([CountOfCOMPONENT]),0,[COUNTOFCOMPONENT]) AS SMKTPARTS " _
    & "INTO " & TempReport _
    & " FROM (TBLDISBURSED_MOS RIGHT JOIN tblReleased ON TBLDISBURSED_MOS.OrderID = tblReleased.MO_NUMBER) LEFT JOIN TBLSUPERMARKET ON tblReleased.ITEM = TBLSUPERMARKET.PARENT " _
    & "GROUP BY tblReleased.MO_NUMBER, tblReleased.MOTYPE, tblReleased.ORDER_QTY, tblReleased.ITEM, tblReleased.FEEDERTOTAL, tblReleased.WH_PICKS1, tblReleased.TotalPicks, tblReleased.TotalFeedersNeeded, tblReleased.relDate, tblReleased.lines, tblReleased.reltime, tblReleased.PRIORITY, tblReleased.COMMENT, IIf(IsNull([PicklistComplete]),'N','Y'), IIf(IsNull([CountOfCOMPONENT]),0,[COUNTOFCOMPONENT]) " _
    & "HAVING (((tblReleased.relDATE) = #" & RelDate & "#))" & " ORDER BY tblReleased.PRIORITY"

If TableExists(CurrentProject.Path & "\" & CurrentProject.Name, TempReport) Then
    DoCmd.RunSQL "drop table " & TempReport
End If
CurrentProject.Connection.Execute sql, , adExecuteNoRecords

Reports!rpt_wh_feederload_requirements.RecordSource = TempReport
DoCmd.SetWarnings True
End Sub

I somehow think that the query is the culprit. I just don't know how or why. I did this after having the problem for a few days so I could see for sure what the query was returning. The new reocrds at times do not show up in the query which got me to thinking they weren't being appended or it was a timing issue with led the to the NITENITE and just opening the table for a few seconds and closing it.

I can't figure this one out and if anybody has any ideas I'm open for suggestions. If you have any questions or want to see more code I'll post it. Thanks for the input Remou

 

[MajP]

This is probably a dumb question, but you said it works if you open the report then close it then open it. Does it work if you do this by code or only manually. Could you open, close, open by code as a work around until you find out why?

 

[CautionMP]

jadams0173,
It's always been a little bit of a mystery to me how/when reports actually open up a recordsource. I suspect that your report is opening the recordsource before your append query fires. If that's the case you may be able to trick the report to re-load the recordset by resetting the [tt]RecordSource[/tt] property of the Report after you run the append query.

Just a thought,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[jadams0173]

CMP,

I have discovered chasing this problem that I am with you on when/how a report makes its recordset. I put some more checks in on the Report_open. However, when the

DoCmd.OpenReport "RPT_WH_FEEDERLOAD_REQUIREMENTS", acViewPreview

is used the Report_open even does not fire. You have use acViewNormal. So that was a little puzzling to me.

I have since added a function that compares the recordcount in the tempreport table(the recordsource for the report), and the transactions for the current day in another table to see if they are the same, before I set the recordsource of the report. If they aren't the same I rebuild the report recordsource table and check again, up to a 5 iteration limit.

MajP,
When I say the report opens correctly the second time I am opening the report manually from access. Not code.

Thanks for the input. I'll keep ya'll posted if I figure it out or it cures its self which I don't see happening!

 

[jadams0173]

I think I have solved this issue. What I ended up doing was before the code in RED in the first post runs, I call a function that makes the report table. After this in that function I compare the record count from the report table to the number of records that should be in the table based off of another recordset. I query tblReleased for transactions with the current date. The number of records returned by this rs and the recordcount from the report table should be the same, if not I loop until they are up to a 5 iteration limit. So far the problem seems to have been resolved. I don't know why this fixed it.

I'd still like to know why this works and the real cause.