Overflow Error

**mike02** · 07-10-2013, 12:33 PM

Hey, I keep getting an overflow error, now i feel like i have it cycling through, but if someone could take a look for me that would be great.

Thanks:

Code:

iTCount = 0
    Do While iTCount <= iMaxTcount
    For Each ctl In Me.Controls
         If ctl.Name = "T_Head" & iTCount Then
                TValue = ctl.Value
         End If
    Next ctl
 For Each ctl In Me.Controls
            If ctl.Name = "T" & iTCount Then
                sSQL = "INSERT INTO tblMatrix ("
                sSQL = sSQL & "ClientID, "
                sSQL = sSQL & "T, "
            
            
                sSQL = sSQL & ") VALUES ("
                sSQL = sSQL & MySelector & ", "
                sSQL = sSQL & TValue & ", "
                              
                sSQL = sSQL & Nz(ctl.Value, "")
                sSQL = sSQL & ")"
                db.Execute sSQL
            
            End If
         Next ctl
                     iTCount = iTCount + 1
                     Loop

**ssanfu** · 07-10-2013, 12:50 PM

It is kinda hard to troubleshoot with only a snippet of the code, but it looks like the SQL string is wrong. There is a comma after the "T".
It looks like there are 2 field names and 3 values.

Add the line in RED, set a breakpoint on the "db.execute" line and post back the result:

Code:

            sSQL = sSQL & Nz(ctl.Value, "")
            sSQL = sSQL & ")"
            Debug.Print sSQL
            db.Execute sSQL

**mike02** · 07-10-2013, 01:23 PM

Here is the full code, I broke it down into the little section hoping to make it easier,

I get the same error by putting that line in, what does it do?

Code:

Dim db As Database
Dim iMaxTcount As Double
Dim iMaxYCount As Double
Dim iMaxZcount As Double
Dim iMaxWCount As Double
Dim iMaxVcount As Double


Dim iYCount As Integer
Dim iTCount As Integer
Dim iZCount As Integer
Dim iVCount As Integer
Dim iWCount As Integer


Dim ctl As Control
Dim TValue As Double
Dim YValue As Double
Dim ZValue As Double
Dim VValue As Double
Dim WValue As Double


Dim ctl_T As Control
Dim ctl_Y As Control
Dim ctl_Z As Control
Dim ctl_V As Control
Dim ctl_W As Control


Set db = CurrentDb
Debug.Print sSQL
db.Execute sSQL '(with this in I get an Error, with this out i get Overflow Error)


iMaxTcount = 0
iMaxYCount = 0
iMaxZcount = 0
iMaxVcount = 0
iMaxWCount = 0


iTCount = 1
iYCount = 1
iZCount = 1
iVCount = 1
iWCount = 1




For Each ctl In Me.Controls
    If InStr(ctl.Tag, "TV") > 0 Then
        If Len(ctl.Value) > 0 Then
            iMaxTcount = iMaxTcount + 1
        End If
    End If
Next ctl


For Each ctl In Me.Controls
    If InStr(ctl.Tag, "YV") > 0 Then
        If Len(ctl.Value) > 0 Then
            iMaxYCount = iMaxYCount + 1
        End If
    End If
Next ctl


For Each ctl In Me.Controls
    If InStr(ctl.Tag, "ZV") > 0 Then
        If Len(ctl.Value) > 0 Then
            iMaxZcount = iMaxZcount + 1
        End If
    End If
Next ctl


For Each ctl In Me.Controls
    If InStr(ctl.Tag, "VV") > 0 Then
        If Len(ctl.Value) > 0 Then
            iMaxVcount = iMaxVcount + 1
        End If
    End If
Next ctl


For Each ctl In Me.Controls
    If InStr(ctl.Tag, "WV") > 0 Then
        If Len(ctl.Value) > 0 Then
            iMaxWCount = iMaxWCount + 1
        End If
    End If
Next ctl
        
    iTCount = 0
    Do While iTCount <= iMaxTcount
    For Each ctl In Me.Controls
         If ctl.Name = "T_Head" & iTCount Then
                TValue = ctl.Value
         End If
    Next ctl
        
    iYCount = 0
    Do While iYCount <= iMaxYCount
    For Each ctl In Me.Controls
         If ctl.Name = "Y_Head" & iYCount Then
                YValue = ctl.Value
         End If
    Next ctl
        
    iZCount = 0
    Do While iZCount <= iMaxZcount
    For Each ctl In Me.Controls
         If ctl.Name = "Z_Head" & iZCount Then
                ZValue = ctl.Value
         End If
    Next ctl
        'Brokerage Fee
    iWCount = 0
    Do While iWCount <= iMaxWCount
    For Each ctl In Me.Controls
         If ctl.Name = "W_Head" & iWCount Then
                WValue = ctl.Value
         End If
    Next ctl
        
    iVCount = 0
    Do While iVCount <= iMaxVcount
    For Each ctl In Me.Controls
         If ctl.Name = "V_Head" & iVCount Then
                VValue = ctl.Value
         End If
    Next ctl
        
        
        
        For Each ctl In Me.Controls
            If ctl.Name = "T" & iTCount & "Y" & iYCount & "Z" & iZCount & "V" & iVCount & "W" & iWCount Then
                sSQL = "INSERT INTO tblAccounts ("
                sSQL = sSQL & "ClientID, "
                sSQL = sSQL & "T, " 
                sSQL = sSQL & "MonthlyDate, "
                sSQL = sSQL & "ProductID, "
                sSQL = sSQL & "Y, " 
                sSQL = sSQL & "Z, " 
                sSQL = sSQL & "V, " 
                sSQL = sSQL & "W, "
            
                sSQL = sSQL & ") VALUES ("
                sSQL = sSQL & MySelector & ", "
                sSQL = sSQL & TValue & ", "
                sSQL = sSQL & cboMonthlyDate & ", "
                sSQL = sSQL & cboProduct & ", "
                sSQL = sSQL & YValue & ", "
                sSQL = sSQL & ZValue & ", "
                sSQL = sSQL & VValue & ", "
                sSQL = sSQL & WValue & ", "
                sSQL = sSQL & ")"
                Debug.Print sSQL 'Your Suggestion
                db.Execute sSQL
            
            End If
         Next ctl
                     iTCount = iTCount + 1
                     Loop
                  iYCount = iYCount + 1
                  Loop
            iZCount = iZCount + 1
            Loop
        iWCount = iWCount + 1
        Loop
    iVCount = iVCount + 1
    Loop


    Cmd_Save.Enabled = False

**June7** · 07-10-2013, 01:41 PM

Did you set the breakpoint? Refer to link at bottom of my post for guidance on debugging techniques.

Debug.Print will display info in the VBA immediate window as one method to review results of code execution.

**mike02** · 07-10-2013, 01:53 PM

It stops and errors at the same point each time i adjust what you have mentioned, or changed the Max to 12, still runs error

Code:

                     iTCount = iTCount + 1
                     Loop
                  iYCount = iYCount + 1
                  Loop
            iZCount = iZCount + 1
            Loop
        iWCount = iWCount + 1
        Loop
    iVCount = iVCount + 1
    Loop

**June7** · 07-10-2013, 02:23 PM

The first db.Execute will error because sql has not been built.

The Values in the SQL string might need delimiters. If saving to a text field use apostrophe ('), if saving to a date field use #, nothing for numbers.

sSQL = sSQL & "#" & cboMonthlyDate & "#, "

I think you need iTCount = iTCount +1 within the For Each ctl loop, just above Next ctl. Your code assumes controls are hit in alphabetical order. I have no idea if that is true.

Not sure about location of other counters.

**ssanfu** · 07-10-2013, 04:43 PM

I looked at your code. There are a couple of problems, as June said.

The Debug.Print statement will print the sSQL string you created so it can be checked.

I created a form and added controls, and entered some data.
This is the string that was generated with your code:

Code:

INSERT INTO tblAccounts (ClientID, T, MonthlyDate, ProductID, Y, Z, V, W, ) VALUES (1, 0, 4/1/2013, 8, 0, 0, 0, 0, )

Note the comma after the "W" and the comma after the last zero. They shouldn't be there.

I modified your code and now have this:

Code:

INSERT INTO tblAccounts (ClientID, T, MonthlyDate, ProductID, Y, Z, V, W ) VALUES (1, 0, #4/1/2013#, 8, 0, 0, 0, 0)

Code:

Option Compare Database  '<<- these two lines should be at the top of EVERY code module
Option Explicit         '<<- these two lines should be at the top of EVERY code module

Private Sub button_Click()
   Dim db As DAO.Database
   Dim iMaxTcount As Double
   Dim iMaxYCount As Double
   Dim iMaxZcount As Double
   Dim iMaxWCount As Double
   Dim iMaxVcount As Double

   Dim iYCount As Integer
   Dim iTCount As Integer
   Dim iZCount As Integer
   Dim iVCount As Integer
   Dim iWCount As Integer

   Dim ctl As Control
   Dim TValue As Double
   Dim YValue As Double
   Dim ZValue As Double
   Dim VValue As Double
   Dim WValue As Double

   Dim ctl_T As Control
   Dim ctl_Y As Control
   Dim ctl_Z As Control
   Dim ctl_V As Control
   Dim ctl_W As Control

   Dim sSQL As String  '<<-- you didn't have sSQL declared

   Set db = CurrentDb

   iMaxTcount = 0
   iMaxYCount = 0
   iMaxZcount = 0
   iMaxVcount = 0
   iMaxWCount = 0

   iTCount = 1
   iYCount = 1
   iZCount = 1
   iVCount = 1
   iWCount = 1

   For Each ctl In Me.Controls
      If InStr(ctl.Tag, "TV") > 0 Then
         If Len(ctl.Value) > 0 Then
            iMaxTcount = iMaxTcount + 1
         End If
      End If
   Next ctl

   For Each ctl In Me.Controls
      If InStr(ctl.Tag, "YV") > 0 Then
         If Len(ctl.Value) > 0 Then
            iMaxYCount = iMaxYCount + 1
         End If
      End If
   Next ctl

   For Each ctl In Me.Controls
      If InStr(ctl.Tag, "ZV") > 0 Then
         If Len(ctl.Value) > 0 Then
            iMaxZcount = iMaxZcount + 1
         End If
      End If
   Next ctl

   For Each ctl In Me.Controls
      If InStr(ctl.Tag, "VV") > 0 Then
         If Len(ctl.Value) > 0 Then
            iMaxVcount = iMaxVcount + 1
         End If
      End If
   Next ctl

   For Each ctl In Me.Controls
      If InStr(ctl.Tag, "WV") > 0 Then
         If Len(ctl.Value) > 0 Then
            iMaxWCount = iMaxWCount + 1
         End If
      End If
   Next ctl

   iTCount = 0
   Do While iTCount <= iMaxTcount
      For Each ctl In Me.Controls
         If ctl.Name = "T_Head" & iTCount Then
            TValue = ctl.Value
         End If
      Next ctl

      iYCount = 0
      Do While iYCount <= iMaxYCount
         For Each ctl In Me.Controls
            If ctl.Name = "Y_Head" & iYCount Then
               YValue = ctl.Value
            End If
         Next ctl

         iZCount = 0
         Do While iZCount <= iMaxZcount
            For Each ctl In Me.Controls
               If ctl.Name = "Z_Head" & iZCount Then
                  ZValue = ctl.Value
               End If
            Next ctl
            'Brokerage Fee
            iWCount = 0
            Do While iWCount <= iMaxWCount
               For Each ctl In Me.Controls
                  If ctl.Name = "W_Head" & iWCount Then
                     WValue = ctl.Value
                  End If
               Next ctl

               iVCount = 0
               Do While iVCount <= iMaxVcount
                  For Each ctl In Me.Controls
                     If ctl.Name = "V_Head" & iVCount Then
                        VValue = ctl.Value
                     End If
                  Next ctl

                  For Each ctl In Me.Controls
                     If ctl.Name = "T" & iTCount & "Y" & iYCount & "Z" & iZCount & "V" & iVCount & "W" & iWCount Then
                        sSQL = "INSERT INTO tblAccounts ("
                        sSQL = sSQL & "ClientID, "
                        sSQL = sSQL & "T, "
                        sSQL = sSQL & "MonthlyDate, "
                        sSQL = sSQL & "ProductID, "
                        sSQL = sSQL & "Y, "
                        sSQL = sSQL & "Z, "
                        sSQL = sSQL & "V, "
                        sSQL = sSQL & "W "

                        sSQL = sSQL & ") VALUES ("
                        sSQL = sSQL & MySelector & ", "
                        sSQL = sSQL & TValue & ", "
                        sSQL = sSQL & "#" & cboMonthlyDate & "#, "
                        sSQL = sSQL & cboProduct & ", "
                        sSQL = sSQL & YValue & ", "
                        sSQL = sSQL & ZValue & ", "
                        sSQL = sSQL & VValue & ", "
                        sSQL = sSQL & WValue & ") "
                        
                        Debug.Print sSQL   'Your Suggestion
                        db.Execute sSQL

                     End If
                  Next ctl
                  iTCount = iTCount + 1
               Loop
               iYCount = iYCount + 1
            Loop
            iZCount = iZCount + 1
         Loop
         iWCount = iWCount + 1
      Loop
      iVCount = iVCount + 1
   Loop

      Cmd_Save.Enabled = False
End Sub

I have no idea what you are doing with the code, so I don't know if there are other errors.

**mike02** · 07-11-2013, 09:01 AM

attached is a sample, I am still getting the Same errors, with those adjustments.

**June7** · 07-11-2013, 12:29 PM

You have MonthlyDate field set as text instead date/time.

You did not move the iTCount = iTCount + 1 line as I suggested. However, my suggestion was not correct. Your counters are in wrong order and mispositioned. Try:

Code:

                  Next ctl
                  iVCount = iVCount + 1
               Loop
               iWCount = iWCount + 1
            Loop
            iZCount = iZCount + 1
         Loop
         iYCount = iYCount + 1
      Loop
      iTCount = iTCount + 1
   Loop

I don't see any controls with names that will match this concatenation (x being a number variable): TxYxZxVxWx

The procedure did run without error, however, no record saved even when data other than 0's entered. This code is agonizingly slow and I would not build something like this.

This line fails because the Save button has focus:
Cmd_Save.Enabled = False

**mike02** · 07-11-2013, 01:23 PM

Thanks for the help, Since this is extremely slow, (I had hoped it wasnt with the error) do you have a better idea on how i could set it up like I have the form in the template?

Thanks,

**June7** · 07-11-2013, 01:38 PM

If you must use unbound form ...

You are writing the code to handle an unspecified number of controls. The code repeatedly cycles through all controls (data controls, labels, lines, rectangles - any and all). There aren't many controls on this form and not a wide variety so wouldn't think this should take so long but it is.

I have had need to cycle through controls but I limit the code to just the data controls. I name the controls similar with incrementing number suffix, just as you have. Then my code:

For i = 1 to 12
do something with Me.("XYZ" & i)
do something with Me.("ABC" & i)
Next

**mike02** · 07-11-2013, 02:13 PM

thanks ill give it a shot!

**mike02** · 07-12-2013, 08:19 AM

I made it into one form with 12 subforms, now, i have set it up that the client, product, and date are all updated by selecting the respected combo boxes and then clicking save, this runs my code:

Dim i As Integer

i = 1

For i = 1 To 12
[Forms]!["Sub" & i].[Form]![MyClient] = Me.MySelector
[Forms]!["Sub" & i].[Form]![MyProduct] = Me.CboProduct
[Forms]!["Sub" & i].[Form]![MyDate] = Me.cboMonthlyDate
i = i + 1
Next i

Now, im getting an error around "Sub" & i I have set this syntax up in excel and havent had issues, but it doesnt seem to work,

My subforms are named Sub1, Sub2, Sub3 and so on.... up to Sub12

The text box names are:
MyDate
MyProduct
MyClient

In every subform so i can run a For statement...

**ssanfu** · 07-12-2013, 10:31 AM

Just curious, your post #8 says there is a sample dB attached, but I don't see it....

**June7** · 07-12-2013, 10:53 AM

You don't need the i = i + 1 line. The For Next will automatically increment the counter, otherwise you may be doubling the increment.

You want one procedure to work for all subforms? Never done anything like that. Try:

Me.("Sub" & i)!MyClient

Overflow Error

Thread Tools

Overflow Error

Similar Threads

OVERFLOW ERROR; doesn’t say where or what line or anything.

OVERFLOW error in both Query and Report

OverFlow Error.

If and only if statement, to fix overflow error

Run time error '6': Overflow

Posting Permissions