Procedure Too Large

[InDeSkize]

I have a submit button on a form where the code is extremely large. It works and I can't think of a more efficient way to run it.

The code runs 400 individual SQL statements.

I'd like to know how to split up my code into a couple different functions and run them sequentially. Or run the code from a text file.

Thanks for any help.

 

[pbaldy]

Is there a pattern to the 400 SQL statements that could be handled with a loop of some sort?

 

[InDeSkize]

Well, take a look and let me know what you think.

----------------------------------------------

Dim SQL As String
Dim SQL1 As String
Dim SQL2 As String
Dim SQL3 As String
Dim SQL4 As String
Dim SQL5 As String
Dim X As String
Dim Y As String
SQL1 = "INSERT INTO TBL_SpecScore ( SpecID, Product, Systems, Points )"
SQL2 = "SELECT [Forms]![FRM_AddSpecScore]![SpecID] AS SpecID, [Forms]![FRM_AddSpecScore]![p"
SQL3 = "] AS Product, [Forms]![FRM_AddSpecScore]![s"
SQL4 = "] AS Systems, [Forms]![FRM_AddSpecScore]!["
SQL5 = "] AS Points;"
DoCmd.SetWarnings False

If [1x1] >= 0.01 Then X = "1": Y = "1": SQL = SQL1 & SQL2 & X & SQL3 & Y & SQL4 & X & "x" & Y & SQL5: DoCmd.RunSQL SQL
If [1x2] >= 0.01 Then X = "1": Y = "2": SQL = SQL1 & SQL2 & X & SQL3 & Y & SQL4 & X & "x" & Y & SQL5: DoCmd.RunSQL SQL
If [1x3] >= 0.01 Then X = "1": Y = "3": SQL = SQL1 & SQL2 & X & SQL3 & Y & SQL4 & X & "x" & Y & SQL5: DoCmd.RunSQL SQL
If [1x4] >= 0.01 Then X = "1": Y = "4": SQL = SQL1 & SQL2 & X & SQL3 & Y & SQL4 & X & "x" & Y & SQL5: DoCmd.RunSQL SQL
If [1x5] >= 0.01 Then X = "1": Y = "5": SQL = SQL1 & SQL2 & X & SQL3 & Y & SQL4 & X & "x" & Y & SQL5: DoCmd.RunSQL SQL
DoCmd.SetWarnings True

-----------------------------------------------------

it works like a champ if I cut it down to 200 or so lines.. 400 gives me the too large error. The text boxes count all the way to [20x20].

Thanks.

 

[pbaldy]

I've got to run out for a bit, but off the top of my head something like this might work:

For X = 1 to 20
  For Y = 1 to 20
    If Me(X & "x" & Y) > .01 Then
      SQL = SQL1 & SQL2 & X & SQL3 & Y & SQL4 & X & "x" & Y & SQL5
      DoCmd.RunSQL SQL
    End If
  Next Y
Next X

Warning, rushing to post before meeting so haven't checked the SQL string.

 

[datAdrenaline]

I would probably go a route like this ... {Note: this is AIR CODE!!! ... also, I assumed all values were numeric, if they are not, then you will have to add single hashes around the text values}

Dim frm As Form
Dim db As DAO.Database
Dim strSQL As String
 
Dim x As Integer
Dim y As Integer
 
'Point to the reference form and the current db
Set db = CurrentDb
Set frm = Forms("FRM_AddSpecScore")
 
'Loop all the controls of the refere
For x = 1 To 20
    For y = 1 To 20
 
        If Me.Controls(x & "x" & y) >= 0.01 Then
 
            'Build the literal SQL statement
            strSQL = "INSERT INTO TBL_SpecScore ( SpecID, Product, Systems, Points )" & _
                     " VALUES (" & frm.Controls("SpecID") & _
                             "," & frm.Controls("p" & x) & _
                             "," & frm.Controls("s" & y) & _
                             "," & frm.Controls(x & "x" & y) & ")"
 
            'Execute the SQL statement
            db.Execute strSQL, dbFailOnError
 
        End If
    Next y
Next x

EDITS >>>
.... Sorry for the dup info ... <dazed> ... paul always gets the jump on me! ... Note the differences, our loops are basically the same, execept I build one literal SQL statement (ie: no form references are in the SQL, I resolved them to create a literal statement). I then execute the statement using a reference to the currentdb, which is the prefered way to execute resolved SQL statements.

 

[InDeSkize]

I will try these out first thing tomorrow. Thanks guys!

 

[InDeSkize]

This works like a champ! Loops have always been a mystery to me.. like women. But now I know, the more I play with them, the more comfortable I'll be.

Thanks again.

 

[KenHigg]

Those were some cool suggestions by Paul & Brent...

kudos

I was originally thinking along the lines of putting the lines in a table until I read all the way through the thread -

 

[pbaldy]

Glad it worked for you. Loops <> mystery...women = big mystery

 

[InDeSkize]

Hey Guys... The code you've helped me with works perfectly. I have one more question maybe you can help with.

I need to take what we've dumped into the table, and put it back it the field it came from.

I have a table 'TBL_SpecScore' that has 5 fields.
SpecID, Product, Systems, Points and Location
---------------------------------
SpecID needs to filter the results
Product is the X axis labels
Systems is the Y axis labels
Points would populate the empty text boxes (1x1 - 20x20)
Location tells us where it was.. ex.. 4x5 or 8x2

So I patched together how I thought it might work.. which doesn't.. haha.

Dim MyDB As DAO.Database, MyRec As DAO.Recordset
Set MyDB = CurrentDb
For X = 1 to 20
  For Y = 1 to 20
     Set MyRec = MyDB.OpenRecordset("Select Points From TBL_SpecScore where SpecID=[Forms]![FRM_ViewSpecScore]![SpecID] And Location = X & "x" & Y;")
 Me(X & "x" & Y) = MyRec
  Next Y
Next X
MyRec.Close
MyDB.Close

My Head Hurts... What do you think?

 

[pbaldy]

You have to concatenate the variables and form references. Try this if SpecID is numeric:

Set MyRec = MyDB.OpenRecordset("Select Points From TBL_SpecScore where SpecID=" & [Forms]![FRM_ViewSpecScore]![SpecID] & " And Location = " & X & "x" & Y)

You also need to specify the field you want data from:

Me(X & "x" & Y) = MyRec!Points

 

[RainLover]

This works like a champ! Loops have always been a mystery to me.. like women. But now I know, the more I play with them, the more comfortable I'll be.

Thanks again.

I would suggest that you buy them flowers first.

 

[InDeSkize]

We're so close. It's getting stuck at the first record. I think because the first record [1x1] doesn't exist. I think I need to find a way to handle null values in the SQL code.

Here's what I have so far.

Dim MyDB As DAO.Database, MyRec As DAO.Recordset
Set MyDB = CurrentDb
For X = 1 To 20
  For Y = 1 To 20
     Set MyRec = MyDB.OpenRecordset("Select Points From TBL_SpecScore where SpecID=" & _
     [Forms]![FRM_ViewSpecScore]![SpecID] & " And Location = " & X & "x" & Y)
     Me(X & "x" & Y) = MyRec!Points
  Next Y
Next X
MyRec.Close
MyDB.Close

 

[InDeSkize]

It might not have to do with records not existing. Here's the message I'm getting.

Syntax error(missing operator) in query expression 'SpecID=48 And Location = 1x1'.

Any Help would be great. Thanks.

 

[KenHigg]

Here's a stab in the dark:

[Forms]![FRM_ViewSpecScore]![SpecID] & " And Location = '" & X & "x" & Y & "'")

???

 

[pbaldy]

I think Ken is right; it didn't dawn on me that the second criteria was text. Sorry about that.

 

[InDeSkize]

The Syntax error went away. but Yay! a new error.

"The value you entered isn't valid for this field"

on

Me(X & "x" & Y) = MyRec!Points

I'll keep plugging away it.. playing with field types and such.

thanks.

 

[pbaldy]

If you said the recordset wouldn't always have a value, you'd want to test for it:

If Not MyRec.EOF Then
  Me(X & "x" & Y) = MyRec!Points
End If

 

[InDeSkize]

You guys are the best. Works like a champ. Thanks for all your help.

 

[pbaldy]

No problem, glad we got it sorted out.