Error #9 : Subscript Out Of Range

Jul 24, 2006

Ok I have the following code which keeps producing an Error # 9 : subscript out of range.....


Public Sub SaveLineItems()
On Error GoTo SaveLineItem_Error
MsgBox "I am doing the line items"
Dim sSQL As String
Dim iLine As Integer
Dim iMaxLines As Integer
Dim iMonthCount As Integer
Dim iFieldCount As Integer
Dim sThisField As String
Dim sFieldPrefix As String
Dim aFields, aMonths, sInDirectCostId, sFY, sUser
sFY = [Forms]![SWITCHBOARD]![cboFY]
sUser = [Forms]![SWITCHBOARD]![txtUser]
sInDirectCostId = sFY & sUser
aFields = Array("cboDesc", "txt", "txtMemo")
aMonths = Array("OCT", "NOV", "DEC", "JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP")
iMonthLoop = 0
iMonthCount = 11
iMaxLines = 16
iLine = 1
Do While iLine <= iMaxLines
iMonthLoop = 0
If Me.Controls(aFields(0) & iLine) <> "" And Me.Controls(aFields(0) & iLine).Locked = True Then
If Me.Controls(aFields(0) & iLine).Column(2) = -1 And Me.Controls(aFields(3) & iLine) = "" Then
MsgBox "You must have a memo for the program : " & Me.Controls(aFields(0) & iLine), vbOKOnly, "Missing Information"
Me.Controls(aFields(3) & iLine).SetFocus
Exit Sub
Else
sSQL = "UPDATE BUDGET_INDIRECTCOSTS_R_LINEDETAILS SET " & _
"ProgramId = " & Me.Controls(aFields(0) & iLine) & ", " & _
"Memo = " & Me.Controls(aFields(2) & iLine) & ", "
Do While iMonthLoop <= iMonthCount
sSQL = sSQL & aMonths(iMonthLoop) & " = " & Me.Controls(aFields(1) & aMonths(iMonthLoop) & iLine)
If iMonthLoop < iMonthCount Then
sSQL = sSQL & ", "
End If
iMonthLoop = iMonthLoop + 1
Loop
sSQL = sSQL & " WHERE INDIRECTCOSTID = " & sInDirectCostId & " AND ID " = iLine
End If
ElseIf Me.Controls(aFields(0) & iLine) <> "" Then
If Me.Controls(aFields(0) & iLine).Column(2) = -1 And Me.Controls(aFields(3) & iLine) = "" Then
MsgBox "You must have a memo for the program : " & Me.Controls(aFields(0) & iLine), vbOKOnly, "Missing Information"
Me.Controls(aFields(3) & iLine).SetFocus
Exit Sub
Else
sSQL = "INSERT INTO BUDGET_INDIRECTCOSTS_R_LINEDETAILS " & _
"(Id, ProgramId, Memo"
Do While iMonthLoop <= iMonthCount
sSQL = sSQL & ", " & aMonths(iMonthLoop)
If iMonthLoop = iMonthCount Then
sSQL = sSQL & ") VALUES ("
End If
iMonthLoop = iMonthLoop + 1
Loop
iMonthLoop = 0
sSQL = sSQL & "" & iLine & ",'" & Me.Controls(aFields(0) & iLine) & "','" & Me.Controls(aFields(2) & iLine) & "'"
Do While iMonthLoop <= iMonthCount
sSQL = sSQL & ", " & Me.Controls(aFields(1) & aMonths(iMonthLoop) & iLine)
If iMonthLoop = iMonthCount Then
sSQL = sSQL & ")"
End If
iMonthLoop = iMonthLoop + 1
Loop
End If
End If
MsgBox sSQL
If Len(Trim(sSQL)) > 0 Then
Set db = CurrentDb
db.Execute sSQL
End If
iLine = iLine + 1
Loop
SaveLineItem_Error:
If Err.Number <> 0 Then
MsgBox "Line Item Save Error : " & Err.Number & vbCrLf & Err.Description
End If
End Sub


I have gone through and commented everything out and brought back only parts and here is what I have when I get the error the first time....


Public Sub SaveLineItems()
On Error GoTo SaveLineItem_Error
MsgBox "I am doing the line items"
Dim sSQL As String
Dim iLine As Integer
Dim iMaxLines As Integer
Dim iMonthCount As Integer
Dim iFieldCount As Integer
Dim sThisField As String
Dim sFieldPrefix As String
Dim aFields, aMonths, sInDirectCostId, sFY, sUser
sFY = [Forms]![SWITCHBOARD]![cboFY]
sUser = [Forms]![SWITCHBOARD]![txtUser]
sInDirectCostId = sFY & sUser
aFields = Array("cboDesc", "txt", "txtMemo")
aMonths = Array("OCT", "NOV", "DEC", "JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP")
iMonthLoop = 0
iMonthCount = 11
iMaxLines = 16
iLine = 1
Do While iLine <= iMaxLines
iMonthLoop = 0
If Me.Controls(aFields(0) & iLine) <> "" And Me.Controls(aFields(0) & iLine).Locked = True Then
If Me.Controls(aFields(0) & iLine).Column(2) = -1 And Me.Controls(aFields(3) & iLine) = "" Then
MsgBox "You must have a memo for the program : " & Me.Controls(aFields(0) & iLine), vbOKOnly, "Missing Information"
End If
ElseIf Me.Controls(aFields(0) & iLine) <> "" Then
If Me.Controls(aFields(0) & iLine).Column(2) = -1 And Me.Controls(aFields(3) & iLine) = "" Then
MsgBox "You must have a memo for the program : " & Me.Controls(aFields(0) & iLine), vbOKOnly, "Missing Information"
End If
End If
iLine = iLine + 1
Loop
SaveLineItem_Error:
If Err.Number <> 0 Then
MsgBox "Line Item Save Error : " & Err.Number & vbCrLf & Err.Description
End If
End Sub


I have gone and researched the problem and have not been able to find anything that relates to this......

Thanks,
Richard

View Replies


ADVERTISEMENT

Subscript Out Of Range Error....

Dec 5, 2007

I'm attempting to upload an excel file into access table using the wizard however I get this error message "subscript out of range" Please Help! I've uploaded other files via excel and don't have this problem.

Thanks
Kon

View 1 Replies View Related

Error - Subscript Out Of Range

Aug 14, 2013

When I try to import a sheet from Excel into an existing table I get a subscript out of range error.

When I then import the sheet into a new table and then import it into the existing table I was trying above immediately afterwards it works fine.

View 3 Replies View Related

Subscript Out Of Range Error

Sep 16, 2010

I am trying to import data from Excel 2007 to Access 2007. However, after hitting the final button to submit the import I get this message: "Subscript out of range."

I have tracked down the fields that seem to be causing the problem. Below is a picture of the Design View for my table. The fields named Ht, College, Dob, State, and Country are the fields that give me the subscript message (I know this because I tried uploading different times with just one of these fields mixed in with the fields that would upload). My first guess for the Height was just a matter of the data type being wrong, but I changed the Ht column to text on my Excel spreadsheet and the same message occurs.

College: Is listed in the picture. This is being pulled from another table. I have another field labeled Transfer College that does this as well, but the expression is a bit different, and any data in a Transfer College column on excel will import over. I have tried altering the College data type to match the Transfer college, but I have to delete too many relationships and it screws up the record source for all of my forms and reports.

DoB: It has an input mask of: 99/99/0000;0;" " Not sure why this doesn't work. Access seems to be picky with dates. When going through the import steps it changes my dates in my spreadsheet to a 5 digit number.

State: Again, another combo box with a value list. State abbreviations are the record source. I would think this would upload. Should I just remove the combo box and value list for now, and then reset this after I transfer my data over?

Country: Another combo box, but this one is pulling its data from a table with countries listed.

View 14 Replies View Related

Importing From Excel - Error Message Subscript Out Of Range

Jan 11, 2015

Trying to import data from Excel into an existing table in Access 2007. The import fails and keeps getting the error message: "subscript out of range". All of the column headings in the 1st row are exactly the same as the table in Access, and the data types are all text except for the key which is a number. I've imported to this table many times and not had a problem and can't figure this out.

View 3 Replies View Related

Modules & VBA :: Subscript Out Of Range If Run Twice

Sep 4, 2014

The below code exports a table (via a function) to a spreadsheet and saves it in a defined location. the code then opens the file does some work with it then moves it(left the move bit out as it works fine)

so when i run the code it works absolutle fine, table is exported, work is done and file is moved. however if i run the code again it fails, i get the error message out of range. i was originally getting this error when using the .usedrange.copy so i commented this out and now i get it on the next line that tries to work with the file so obviously something is wrong in my logic.

Code:
Private Sub Export2JDE_Click()
Application.Run ("JDE_Export") 'Exports to an xlsx file in the location described in the function.
Dim xlApp As excel.Application
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

[Code] .....

View 8 Replies View Related

General :: Subscript Out Of Range In VBA

Jun 6, 2014

What the Variant value on Ms Access latest version is same with Ms Access previously(97,2003).?

I am using office 2010

I have face error again in line

Code:
-----------------------------------------
bd = ""
bd = Split(bx(1), " ")
For i = 0 To (UBound(bd) - 1)
Me("p" & i).Caption = bd(i)
Me("p" & i).Visible = True

[Code] .....

View 12 Replies View Related

General :: Subscript Is Out Of Range

Aug 13, 2013

When I try to import a sheet from Excel into an existing table I get a subscript out of range error.When I then import the sheet into a new table and then import it into the existing table I was trying above immediately afterwards it works fin.

View 1 Replies View Related

Modules & VBA :: Subscript Out Of Range In Excel Worksheet

Sep 10, 2013

I have written the following program code:

Dim xlApp As New Excel.Application
Dim xlwrkBk As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim strXLS As String
strXLS = "c:AccessMyfile.xls"
Set xlwrkBk = xlApp.Workbooks.Open(strXLS)

[code]...

The program is stopping at " Set xlSheet = xlwrkBk.Worksheets("singles")"

and I am receiving the following error box:

Runtime error 9

Subscript out of range.

I don't understand this error because I am using the same program code with another Excel file in another module and it works perfectly.

View 6 Replies View Related

Reports :: Subscript Out Of Range In Report Wizard

Sep 10, 2013

I am trying to generate a report from a select query using the report wizard. The wizard is showing the error of subscript of out range. What is the reason and how can be rectified?

View 4 Replies View Related

Modules & VBA :: Checking Array - Subscript Is Out Of Range If Value Is Equal To D

Aug 6, 2013

I am having trouble checking if the last array value is equal to 'D'.

This is what i have so far but it keeps saying that 'subscript is out of range'... 'g' by the way is equal to 1. It just really means that there's only one value in ArrAC.

Code : If Not ArrAC(g) = "D" Then

View 2 Replies View Related

Subscript And Superscript

Nov 16, 2005

please could someone advise on how to get subscript and superscript to be recongnised in tables.

View 4 Replies View Related

Subscript In Access

Nov 18, 2004

Is there any way of entering a mixture of subscript, superscript and normal characters in Access. I am trying to enter subscript numbers into a database of mineralogical formulae.

Many thanks,
Rob

View 1 Replies View Related

An Access Font That Supports Subscript?

Apr 5, 2006

Is there a font that comes installed by default in access(windows) that supports subscript? My subscript in access is showing up as a box. If I change it to a font that I have installed/purchased seperate from windows it gets displayed properly. The problem with this, is when it pulls this info displayed in that font to the website, it reverts it to a font that the user doesn't have installed and goes back to a box.

So need a default windows xp font installed in access/windows that supports subscript....

Thanks in advance!

Dave

View 2 Replies View Related

Tables :: Subscript / Superscript Characters In A Table

Nov 1, 2014

if it is possible to store text in a table that includes subscript / superscript characters. As an example; need to indicate the units of measurement for some data and therefore need to be able to pull data from the table such as the following: kg/m2, m/s2 etc. In both these case i need the 2 character to be superscript.

I'm trying to make this an automated process so pulling it directly from the database.o a method of storing the data as a text string would be ideal.Otherwise i imagine a rather difficult VBA function will be involved

View 1 Replies View Related

Can You Query A Range Of Dates With A Range Of Dates

Mar 17, 2008

I have a client that wants to enter a range of dates in a query of when they will call that person back. Then they want to be able to type in a range of dates and have a make table query show them all the people that fall in between these two dates....is this even possible???
Ex.
Joe March 3 to March 8
Mary March 4 to March 9
John March 5 to March 10

So if they type into the query March 3 to March 6 all three people should show up because one of the dates specified lies within the parameters they are asking for.....man I am out of ideas

Anyone.....

View 5 Replies View Related

All Dates In Range

Mar 24, 2006

As part of a larger issue, I am trying to figure out how to have an Access 2000 select query produce all dates in a date range into one field on the query. Assume at this point there are no tables in the query.

If 01/01/06 (mm/dd/yy) and 02/01/06 is used, then it'd list all the dates between those two, inclusive.

Is this even possible?

Thanks for looking.

View 3 Replies View Related

Date Range Within A Date Range

May 12, 2006

I'm trying to figure out how I can set a parameter on my query to search for any activity that occurs within a month, that falls within a date range. Specifically:

Criteria TextBox: 4/2006

Activity Start: 3/6/2006
Activity End: 5/5/2006

I want this activity to show in my query because the time between start and end occurs during the month of April. It's no problem setting this up for a specific date, ie 4/1/2006, but I don't know how to do it for an activity that occurs all days between 4/1/2006 and 4/30/2006..or for one that starts 4/12/2006 and ends 4/18/2006, or starts 4/12/2006 and ends 5/9/2006. etc.

View 2 Replies View Related

Date Range

Jun 18, 2006

Hi All,

I have a table of data, one of the fields is a date.

What i want to do is be able to have a query that can check if the date falls within a certain range - ie fiscal year and output in another column the fiscal year "code".

Ie: dates between 01/06/05 and 31/05/06 is fiscal year 0506
dates between 01/06/06 and 31/05/07 is fiscal year 0607

Could this query be dynamic so if a new fiscal year begins it would know to make the output the next fiscal year code???

Any help is much appreciated.

Cheers

Rudi

View 1 Replies View Related

Date Range SQL

Oct 10, 2006

I have two tables: tblClasses & tblSchedule. There are joined by ClassID. For each class in the tblClasses there are several records with date field in the tblSchedule. (So each class stored in tblClasses happens on multiple dates stored in tblSchedule).

I want to create a query (SQL view) that would take two date inputs from a form(date range): datefrom & dateto, and return any class of which FIRST day of classes falls in between those dates.

(Or: how can I add a field to my query called [First day of class] that would basically have the value of the first date from the tblSchedule for the joined classID?)

View 2 Replies View Related

Look Up A Range From Within A Query

Mar 30, 2007

Below is a table that I want to look up from a query. Basically I want to look up a value (e.g 3.56) and find which PRA it fits in so in this case 3.56 is between 3 and 6 so the value returned is 0.7. Where it gets complacted is that the 3.56 is generated on the fly in the query. Can I use a dlookup with a range. Ive tried but the syntax is wrong i think. DateDiff_RepToMat is the generated field (3.56)Any help or suggestion are appreciatedx = DLookup("PRA", "tab_ref_MaturityRiskRatings", [DateDiff_RepToMat] BETWEEN [RangeLow] and [RangeHigh])RangeLowRangeHighPRA0 1 0.003 6 0.46 12 0.7

View 2 Replies View Related

Need Help With Sql Statement For A Range

Jul 11, 2007

Hi, I have atable with ID ranges like this:
ID1 ID2 Logo
1 50 HO
51 75 HY
76 100 GM

I want to know how I would write a SQL statement that would find which logao given a certain input. So for example, If I had th euser enter 53 from a textbox on a form it should bring back HO. Can someone please help me with this.
This is the kind of thing that I tryed so far, but does not work:

SELECT Logo
FROM tblLogo
Where forms!frmTest!Text0 Between(ID1) (ID2);

View 2 Replies View Related

Age Range Query

Aug 29, 2007

ok here is whats in my table

Document ID
UserName
Age

I need to have grouped UserName, Total count of DocumentID.
Then i need to have age range count between 0 and 5, 10 and 15, 20+ ....
basically, Username , Total DocumentID count for User, Range Count (0-5,10-15....)

Thanks

View 5 Replies View Related

How To Use A Date Range

Nov 19, 2007

I need to get records between two dates. Here is my query:

SELECT WGMAHIST_TIMEDTY.TDYPT, WGMAHIST_TIMEDTY.TDYCO, WGMAHIST_TIMEDTY.TDYSSN, WGMAHIST_TIMEDTY.TDYFND
FROM WGMAHIST_TIMEDTY
WHERE WGMAHIST_TIMEDTY.TDYSSN = 464299266
AND WGMAHIST_TIMEDTY.TDYEDT >= #06/29/2007#
AND WGMAHIST_TIMEDTY.TDYEDT <= #09/21/2007#
AND WGMAHIST_TIMEDTY.TDYPT = 1
AND WGMAHIST_TIMEDTY.TDYCO = 8

I am obviously doing it wrong because I keep getting an error that complains about the key work BETWEEN.

How can I make this work? I am using MS Access 2007.

Thanks,
wgma

View 5 Replies View Related

Select Range

Feb 28, 2008

I need to attribute a value to records between a certain range, example
All records that the field "Date" is between 01/04/2007 and 31/03/2008 I need to return a value in a field named "Group" 2008 in my table.
Example:
12/02/2003 = 2003
15/03/2003 = 2003
15/04/2003 = 2004
20/08/2003 = 2004
17/12/2003 = 2004
20/02/2004 = 2004
01/04/2004 = 2005

View 5 Replies View Related

Moving Range

Mar 16, 2008

Hi all,

I'm new in this forum and can be considered as beginner in Ms Access. I tried to make a SPC database and currently stuck at calculating the data range for moving range graph.
The question is, how to calculate range between 2 consecutive data and then sum it.
For example:
A, 195
B, 198
C, 196
D, 194

Thus the range between A and B is 3 (198-195), B and C is 2 (198-196), C and D is 2 (396-194). The total range is 7 (3+2+2)

Anyone has the idea?

Thanks.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved