hanging

[patti]

I have a report that has the following code in its On Open event:
================================

Private Sub Report_Open(Cancel As Integer)
Dim db As Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strLastDate As String
Dim strMessage As String

Set db = CurrentDb
strSQL = "SELECT lastRun " & _
"FROM tblLastRun " & _
"WHERE file_Type = 'OO' "
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

rs.MoveFirst
strLastDate = rs!lastrun
rs.Close
Set db = Nothing

strMessage = "This data was last updated on " & Trim(Format(strLastDate,
"Short Date")) & " at " & _
Format(Trim(strLastDate), "hh:mm AM/PM") & ". " & Chr(13) & _
"Would you like to refresh it now? "

If MsgBox(strMessage, vbQuestion + vbYesNo, "Refresh Data") = vbYes Then
fnPopulateTable_2
Set db = CurrentDb
strSQL = "UPDATE tblLastRun " & _
"SET lastRun = #" & Now() & "# " & _
"WHERE file_Type = 'OO' "
db.Execute strSQL
Set db = Nothing
End If
End Sub

=================================

It worked fine last week (it is run weekly). Now, i say yes to prompt to
update data and then i just hang. I have compacted & repaired db. The
tblLastRun is a linked table and appears to be fine in that db.

(If i say no, don't update, i roll merrily along & see last week's reports
fine.

How can i find out what is going wrong?

thanks.

patti

 

[Roger Carlson]

Have you tried putting a breakpoint in your code and stepping through it
with the F8? It might give you some idea where to look.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[patti]

Thanks for the help.

I haven't much experience w/ vba. Not quite sure what you mean by
breakpoint. Could you provide or point me to some help with that.

I have pulled out queries form the vba and they run fine. I had only posted
the first part of the code.

 

[Roger Carlson]

In the Visual Basic Editor, if you click in the vertical bar on the left
hand side of the code window, a red dot will appear. Leave the editor open
and go back and open your report. Code execution will stop at that point
(called a breakpoint). Hit the F8 key and it will advance one line at a
time. Then you can see where the code is hanging.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[patti]

Thanks for explainig the breakpoint. I F8 may way through and i seem to be
stuck in a loop in code for a module.
-----------------------------------------------------

Public Function fnGetPO(po1, po2, po3, po4, po5, po6, po7, po8, po9, po10,
po11) As String
Dim strPO As String

If Not IsNull(po1) Then
strPO = po1
Else
If Not IsNull(po2) Then
strPO = po2
Else
If Not IsNull(po3) Then
strPO = po3
Else
If Not IsNull(po4) Then
strPO = po4
Else
If Not IsNull(po5) Then
strPO = po5
Else
If Not IsNull(po6) Then
strPO = po6
Else
If Not IsNull(po7) Then
strPO = po7
Else
If Not
IsNull(po8) Then
strPO =
po8
Else
If Not
IsNull(po9) Then

strPO = po9
Else

If Not IsNull(po10) Then

strPO = po10

Else

If Not IsNull(po11) Then

strPO = po11

Else

strPO = "None"

End If

End If
End If
End If
End If
End If
End If
End If
End If

End If
End If

fnGetPO
End Function
------------------------------------------------------------------
It pops from stringPO = PO3 to third to last end if then rolls back to top
of if statement.

I have not changed any code and don't know why this has stopped working.

I suppose i need to track down the PO3 stuff. But what happened to the code?
or is it that there is a problem with the data itself?

Thanks for your time.

 

[Roger Carlson]

Are you certain it wasn't changed accidentally? It looks like the next to
the last line is simply calling the function again. Such recursive
functions without an exit point will form an infinite loop until all your
memory is consumed.

Are you sure that line shouldn't be:
fnGetPO = strPO
?

That would assign the return value to the function.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[patti]

Sorry. I did a sloppy job on copy & paste. That Line does read as you stated
below. I am stuck in a loop. This is code that calls that module: (sorry for
the ugly format)

strSQL = "SELECT [Category Table].div, [Category Table].[sub div], [Category
Table].class, [Item Proof].Vendor, [Description Table].Style, [Description
Table].[Short Description], [Description Table].Color, [Offer
Comparison].[Current Retail], NZ([Current Retail],[Item Proof].[Orig Retail])
AS CrrntRetail, [Item Proof].[Orig Retail], [On Order - Average
Cost].[AvgOfItem Cost], Sum((([CrrntRetail]-[avgofitem cost])/[CrrntRetail]))
AS [MU%], Sum([Product Sales Query Format].[Ship Units]) AS [SumOfShip
Units], Sum([Inventory Age].[Total Units]) AS [SumOfTotal Units], " & _
"[On Order PO Info].[M1 PO], Sum([On Order PO Info].m1qty) AS
[m1 qty], Sum([m1qty]*[Crrntretail]) AS [M1 OO $], Sum([m1qty]*[AvgOfItem
Cost]) AS [M1 OO cost$], [On Order PO Info].[M2 PO], Sum([On Order PO
Info].M2Qty) AS [M2 Qty], Sum([M2Qty]*[Crrntretail]) AS [M2 OO $],
Sum([M2Qty]*[AvgOfItem Cost]) AS [M2 OO cost$], [On Order PO Info].[M3 PO],
Sum([On Order PO Info].M3Qty) AS [M3 Qty], Sum([M3Qty]*[Crrntretail]) AS [M3
OO $], Sum([M3Qty]*[AvgOfItem Cost]) AS [M3 OO cost$], [On Order PO Info].[M4
PO], Sum([On Order PO Info].m4qty) AS [M4 Qty], Sum([m4qty]*[Crrntretail]) AS
[M4 OO $], Sum([m4qty]*[AvgOfItem Cost]) AS [M4 OO cost$], [On Order PO
Info].[M5 PO], Sum([On Order PO Info].m5qty) AS [M5 Qty],
Sum([m5qty]*[Crrntretail]) AS [M5 OO $], Sum([m5qty]*[AvgOfItem Cost]) AS [M5
OO cost$], [On Order PO Info].[M6 PO], Sum([On Order PO Info].m6qty) AS [M6
Qty], Sum([m6qty]*[Crrntretail]) AS [M6 OO $], Sum([m6qty]*[AvgOfItem Cost])
AS [M6 OO cost$], " & _
"[On Order PO Info].[M7 PO], Sum([On Order PO Info].m7qty) AS
[M7 Qty], Sum([m7qty]*[Crrntretail]) AS [M7 OO $], Sum([m7qty]*[AvgOfItem
Cost]) AS [M7 OO cost$], [On Order PO Info].[M8 PO], Sum([On Order PO
Info].m8qty) AS [M8 Qty], Sum([m8qty]*[Crrntretail]) AS [M8 OO $],
Sum([m8qty]*[AvgOfItem Cost]) AS [M8 OO cost$], [On Order PO Info].[M9 PO],
Sum([On Order PO Info].m9qty) AS [M9 Qty], Sum([m9qty]*[Crrntretail]) AS [M9
OO $], Sum([m9qty]*[AvgOfItem Cost]) AS [M9 OO cost$], [On Order PO
Info].[M10 PO], Sum([On Order PO Info].m10qty) AS [M10 Qty],
Sum([m10qty]*[Crrntretail]) AS [M10 OO $], Sum([m10qty]*[AvgOfItem Cost]) AS
[M10 OO cost$], [On Order PO Info].[M11 PO], Sum([On Order PO Info].m11qty)
AS [M11 Qty], Sum([m11qty]*[Crrntretail]) AS [M11 OO $],
Sum([m11qty]*[AvgOfItem Cost]) AS [M11 OO cost$], " & _
"fnGetPO([ON Order PO Info].[m1 po],[ON Order PO Info].[m2
PO],[ON Order PO Info].[m3 po],[ON Order PO Info].[m4 po],[ON Order PO
Info].[m5 po],[ON Order PO Info].[m6 po],[ON Order PO Info].[m7 po],[ON Order
PO Info].[m8 po],[ON Order PO Info].[m9 po],[ON Order PO Info].[m10 po],[ON
Order PO Info].[m11 po]) AS poNumber, Date() AS NextRecDate INTO tmpOnOrder "
& _
"FROM ((((([Category Table] INNER JOIN ([Description Table]
INNER JOIN [Item Proof] ON [Description Table].[Long Style] = [Item
Proof].Style) ON [Category Table].Cat = [Item Proof].Cat) LEFT JOIN [Offer
Comparison] ON [Item Proof].Style = [Offer Comparison].[Long Style]) INNER
JOIN [On Order - Average Cost] ON [Item Proof].Style = [On Order - Average
Cost].[Long Style]) LEFT JOIN [Product Sales Query Format] ON [Item
Proof].Style = [Product Sales Query Format].[Long Style]) LEFT JOIN
[Inventory Age] ON [Item Proof].Style = [Inventory Age].[Long Style]) INNER
JOIN [On Order PO Info] ON [Description Table].[Long Style] = [On Order PO
Info].[Long Style] " & _
"GROUP BY [Category Table].div, [Category Table].[sub div],
[Category Table].class, [Item Proof].Vendor, [Description Table].Style,
[Description Table].[Short Description], [Description Table].Color, [Offer
Comparison].[Current Retail], NZ([Current Retail],[Item Proof].[Orig
Retail]), [Item Proof].[Orig Retail], [On Order - Average Cost].[AvgOfItem
Cost], [On Order PO Info].[M1 PO], [On Order PO Info].[M2 PO], [On Order PO
Info].[M3 PO], [On Order PO Info].[M4 PO], [On Order PO Info].[M5 PO], [On
Order PO Info].[M6 PO], [On Order PO Info].[M7 PO], [On Order PO Info].[M8
PO], [On Order PO Info].[M9 PO], [On Order PO Info].[M10 PO], [On Order PO
Info].[M11 PO], fnGetPO([ON Order PO Info].[m1 po],[ON Order PO Info].[m2
PO],[ON Order PO Info].[m3 po],[ON Order PO Info].[m4 po],[ON Order PO
Info].[m5 po],[ON Order PO Info].[m6 po],[ON Order PO Info].[m7 po],[ON Order
PO Info].[m8 po],[ON Order PO Info].[m9 po],[ON Order PO Info].[m10 po],[ON
Order PO Info].[m11 po]), Date() " & _
"HAVING [Item Proof].[Orig Retail] >0 "

 

[patti]

looking at the if-then, it seems like there's no way it will move to po2 when
po1 is not null. this is so odd. i even tried to run an old copy of this
database. and am still stuck.

 

[aaron.kempf]

I don't think that you should be using DAO.

DAO constantly hangs. Especially if you're a newbie programmer.

For example.. with DAO you've got to declare a recordset; and then
close it; set it = nothing.

In ADO - you don't.

If you're trying to learn a decent database; then learn SQL Server.

Access and DAO is just a complete waste of time.

-Aaron

 

[patti]

Thanks for the help.

I copied the db and files to local and the report ran fine. So this has to
be a network issue - though IT said no probs!

 

[aaron.kempf]

again-- if your database is too flaky.. and the dorks in this group
are blaming it on your network-- _THAT_ is when you should just move
to SQL Server.

There's nothing wrong with the network.
The Access database isn't reliable enough for real world usage.

-Aaron

 

[Roger Carlson]

Well, aaron, other than you, I'm the only "dork" who has responded to this
thread, and I've said nothing about the network.

I prefer to answer the question actually asked. I believe patti can decide
on the long-term feasibility of Access on her own.

--
--Roger Carlson
MS Access MVP
www.rogersaccesslibrary.com

again-- if your database is too flaky.. and the dorks in this group
are blaming it on your network-- _THAT_ is when you should just move
to SQL Server.

There's nothing wrong with the network.
The Access database isn't reliable enough for real world usage.

-Aaron

 

[aaron.kempf]

again-- if your database is too flaky.. and the dorks in this group
are blaming it on your network-- _THAT_ is when you should just move
to SQL Server.

There's nothing wrong with the network.
The Access database isn't reliable enough for real world usage.


-Aaron