Not Responding when VBA code is Run

[bymarce]

I'm making a form for generating records to represent a matrix of
experimental results. So if I have three samples for which I need to test
melting point and boiling point we need 6 records. My users don't want to
type in every record.
The form has two multiselect list boxes and a command button. The first
list box contains the sample ID numbers and the second contains
Property/Method combinations. The sample ID is a compound primary key from
the sample catalog table. The property/method combinations come from
Property and Method tables which are joined in a many to many relationship
through a join table. The command button runs VBA code I wrote to generate
all combinations of the sample ID and Property/Method. If I select only one
sample ID and one Property/Method, the code runs fine. If I select more than
one sample ID Access quits responding. How do I fix this? I hope this is
clear. Thanks for the help.
Marcie

Private Sub cmdPreview_Click()
If Me.lstMLO.ListCount = 0 Then
MsgBox "You must select at least 1 MLO Number."
Exit Sub
End If
'Build Where string to generate data matrix of records.
Dim strWhere As String, strMLO As String
Dim strPrefix As String, strYear As String, strID As String, varItem As
Variant, strQ As String
strQ = """"
With Me.lstMLO
For Each varItem In .ItemsSelected
strPrefix = Left(.ItemData(varItem), 3)
strYear = Left(.ItemData(varItem), 8)
strYear = Right(strYear, 4)
strID = Right(.ItemData(varItem), 4)
strMLO = strMLO & "((MLOBOOK.CatalogPrefix Like " & strQ
& strPrefix & strQ & _
") AND (MLOBOOK.CatalogYear = " &
strYear & _
") AND (MLOBOOK.CatalogID = " &
strID & ")) OR "
Next varItem
Dim lenstrMLO As Long
lenstrMLO = Len(strMLO) - 4
If lenstrMLO > 0 Then
strMLO = Left(strMLO, lenstrMLO)
strMLO = strMLO & " AND "
End If
End With

Dim lenSplit As Long, strTestMethod As String, strProperty As String,
strPropertyMethod As String
With Me.lstPropertyMethod
For Each varItem In .ItemsSelected
strPropertyMethod = .ItemData(varItem)
lenSplit = InStr(1, strPropertyMethod, "/")
strProperty = Left(strPropertyMethod, lenSplit - 1)
strMethod = Right(strPropertyMethod, Len(strPropertyMethod)
- lenSplit)
strWhere = strMLO & "(((Properties.Property) = " & strQ &
strProperty & strQ & _
") AND ((TestMethods.Method)=" & strQ &
strMethod & strQ & "))"
Debug.Print strWhere
If Len(strWhere) <= 0 Then
MsgBox "You have not selected anything. Please try
again."
Else
Dim sqlPreview As String
sqlPreview = "INSERT INTO tblDataTemp ( CatalogPrefix,
CatalogYear, CatalogID, Property, TestMethod, TestAssignedTime ) " & _
"SELECT MLOBOOK.CatalogPrefix,
MLOBOOK.CatalogYear, MLOBOOK.CatalogID, Properties.Property,
TestMethods.Method, Format(Now()," & strQ & "hhnn" & strQ & ") AS
TestAssignedTime " & _
"FROM Properties, TestMethods, MLOBOOK " & _
"WHERE " & strWhere & ";"
Dim dbPreview As DAO.Database
Set dbPreview = DBEngine(0)(0)
dbPreview.Execute sqlPreview
End If
Next varItem
End With
End Sub

 

[Jack Leach]

Your app can go into "not responding" mode during very long loops (in which
case if you leave it alone it will come out), or if you accidentally initiate
an infintate loop. Putting a DoEvents statement in a long loop may keep this
from happening (it will yield your code to other system processes in the
meantime).

Usually more often during development one accidentally enters an infinate
loop, especially with complex loop operations. While your code is "not
responding" try pressing ctrl+break to halt the code and enter debug mode.
You should be able to run through the loop a few times using normal debugging
practices and determine if you are missing some peice of data or code that a
finite loop is dependant on.

good luck!


--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[bymarce]

Thanks. I tried pressing ctrl+break when it's not responding and nothing
happens. I had some debug.print lines in here to try and figure where it was
getting hung up but when it would quit responding, had to be closed and
reopened, I would loose what ever was in the immediate window. So then I put
in some msgbox lines and figured out that it's getting hung up at the
"dbPreview.Execute sqlPreview". Next I commented out that line to get the
sqlPreview in the immediate window and copied it into a query. When I run
the query with this sql it stops responding. Why does this sql not run?

INSERT INTO tblDataTemp ( CatalogPrefix, CatalogYear, CatalogID, Property,
TestMethod, TestAssignedTime ) SELECT MLOBOOK.CatalogPrefix,
MLOBOOK.CatalogYear, MLOBOOK.CatalogID, Properties.Property,
TestMethods.Method, Format(Now(),"hhnn") AS TestAssignedTime FROM Properties,
TestMethods, MLOBOOK WHERE ((MLOBOOK.CatalogPrefix Like "MLO") AND
(MLOBOOK.CatalogYear = 2009) AND (MLOBOOK.CatalogID = 0230)) OR
((MLOBOOK.CatalogPrefix Like "MLO") AND (MLOBOOK.CatalogYear = 2009) AND
(MLOBOOK.CatalogID = 0227)) AND (((Properties.Property) = "Acid Number") AND
((TestMethods.Method)="Titration"));

P.S. To Doug Steel: Thanks for your comment also. I ended up posting this
3 times accidently b/c I was unable to find it after I posted it. I could
only find the one you answered on google groups and I can't log into google
groups from work. I don't know how it got to google groups.

Marcie