Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    yes sir is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    159

    "Subscript out of range" Error

    Hello,

    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.

    Here is some more insight on these fields:
    Ht: Display control: combo box, with a value list. The record source contains values such as: "6'1";"6'2";"6'3", etc.

    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.
    Last edited by yes sir; 09-16-2010 at 09:36 PM. Reason: removed image

  2. #2
    yes sir is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    159
    where is the trumpet when you need him most?

  3. #3
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    what code is behind the final button?

  4. #4
    yes sir is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    159
    Quote Originally Posted by ajetrumpet View Post
    what code is behind the final button?
    By "final button" I was just referring to the basic steps of importing an excel spreadsheet into the database. I don't necessarily have a button that once clicked submits an import. Sorry about that.

  5. #5
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    so you're doing this through the wizard? I don't have an answer for you exactly, because subscript errors have more than one meaning.

    What I would do if I were you, is take one of the problem columns in Excel and copy it. then paste it directly into the field (in DS view) that you want to copy it to in Access. For instance, if the field has a mask in it as a requirement and it's throwing a subscript at you, take the mask out and try it again. No error = the mask is the problem.

    I don't have experience with this specifically. Just throwing you a bone...

  6. #6
    yes sir is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    159
    Adam,

    Ok, I copied a field from each problem column. First off, the only way I could copy and paste a field into the database was when I clicked on the box with the thick white cross showing. Thus the box was highlighted, but no cursor blinking in the box.

    Here are the results:
    Ht: Would not copy in at all.
    College: Successful copy and paste
    DOB: Successful copy and paste
    State: successful c & p
    Country: successful c & p

    Technically, the DoB is the only field with an input mask, the others just have a combo box with a value list or table/query as the record source.

  7. #7
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    First, I have to say that this is funny:
    Quote Originally Posted by yes sir View Post
    where is the trumpet when you need him most?
    :laugh: Thanks though!

    At any rate, I'm simply guessing that the HT field is the problem since Access does not accept. I forget...was it a number? what type of number is it? subscript will show it's ugly head if for example, you try to import the number "34,000,000" in a field that is of the INTEGER data type (or something similar, don't quote me). Numbers have subTypes like LONG, DOUBLE, BYTE, etc...

    If you try to associate a value to a number field that's outside the specified limits for the data type, you get a ss error. Could that be the problem I wonder? what's the limit of the HT field data type?

  8. #8
    yes sir is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    159
    Quote Originally Posted by ajetrumpet View Post
    At any rate, I'm simply guessing that the HT field is the problem since Access does not accept. I forget...was it a number? what type of number is it? subscript will show it's ugly head if for example, you try to import the number "34,000,000" in a field that is of the INTEGER data type (or something similar, don't quote me). Numbers have subTypes like LONG, DOUBLE, BYTE, etc...

    If you try to associate a value to a number field that's outside the specified limits for the data type, you get a ss error. Could that be the problem I wonder? what's the limit of the HT field data type?
    For my database, the HT has:
    data type: Text
    field size: 15

    Then, as mentioned earlier:
    Display Control: Combo Box
    Row Source Type: Value List
    Row Source: "5'4";"5'5";"5'6";"5'7";"5'8";"5'9";"5'10";"5'11"; "6'0";"6'1";"6'2";"6'3";"6'4";"6'5";"6'6";"6'7";"6 '8";"6'9";"6'10";"6'11";"7'0";"7'1";"7'2";"7'3";"7 '4";"7'5";"7'6";"7'7";"7'8"

  9. #9
    yes sir is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    159
    The Row Source, copied and pasted weird on the last post. Those unnecessary spaces aren't there in my database (example, the extra space by 6'0 and 7'4)

    The Data Type for Height (aka Ht) on my excel spreadsheet is Text.

  10. #10
    yes sir is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    159
    Adam,

    I have also tried this: creating a new column in the database table, with the use of a text box and not the combo box/value list. Upon trying to import the data with this new column, I still get the same SS message. I tried importing with the data type on the Excel spreadsheet as Type and as General, both ways ended with the same result.

    However, I can now simply copy and paste the height from the spreadsheet to the database.

    And I am also getting frustrated

  11. #11
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    well...since noone else has said anything, I'm guessing they might not have anything to add either. The only other thing i can think of is your use of controls in the table. as a general rule among programmers, NEVER do that. But I know you're probably stuck with it. One big mistake by MS was making that stupid lookup wizard available at the table level. Every person and their dog has fallen into the trap of using it at some point or another.

    That might actually be causing the problem for you. Is it a possibility that one of the heights in the Excel column does not match anything in the value list? That would error out I'm sure.

    If you want to upload the files you are questioning I might be able to look at it. I have access to office ver's 07 and 03 both on both vista and xp so I'm sure I could duplicate it, if nothing else.

    what version of excel is it? same as access?

  12. #12
    yes sir is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    159
    Both are 2007 versions.

    I'll upload the files. The lookup feature may be used on a couple of my fields, so that might explain the problem for some.

  13. #13
    yes sir is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    159
    My spreadsheet doesn't contain much data, because I am just doing a practice run before the person I'm doing this database for uploads 2,000+ records.

    Spreadsheet: First tab, columns in green represent data that uploads
    properly.

    Database: Go to the Prospect table.

  14. #14
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    Quote Originally Posted by yes sir View Post
    My spreadsheet doesn't contain much data, because I am just doing a practice run before the person I'm doing this database for uploads 2,000+ records.

    Spreadsheet: First tab, columns in green represent data that uploads
    properly.

    Database: Go to the Prospect table.
    Alright bro, here's what I did:

    I imported the ss into a table called NEW. worked fine. now to try and figure the differences I ran an APPEND operation between the one record in table NEW and the prospects table. The discovery was that the COLLEGE NAME field in the prospects table is a NUMBER. Shouldn't that be text? It is, but that's not what's defining the field type. Access nullified the value because of a datatype mismatch. That's a problem. And again, the reason it's a problem is because you are using the lookup wizard in that field. What's happening (just like what happens on forms) is that Access is reading the bound field, which in this case is the ID that you have in the first invisible column of that combo box. It's accepting that value as the field definition data, so the string conflicts with it. It's trying to import the string into a LONG data type field.

    This is going to be problem for all of the other "lookup" fields that you've created as well, even if it's not this particular time. You really should get rid of them now. I'm sure I speak for others too when I say that I don't have much experience with lookup fields because I've heard that they are pain in the rear. I avoided them almost from the start. So if nothing is stopping you, drop them immediately and perform the lookups in forms when the user wants to see related data.

    I'm also going to assume that you created the lookups at the table level because it was easy to create tables and see related data during development? If so, it is true that data entry for testing and whatever else is easier this way, but the time difference for the alternative is marginal. The other trap people fall into is the fact that creating lookups like this automatically do the lookup work for you when you create an autoform from a table, or when you drag and drop fields from the field list when developing forms. yes, that saves a lot of time when developing forms, but sometimes you run into the "evils" of using them later and you end up spending more time dealing with it than planned in "fix" operations.

    But enough of my babbling...check that out and see if it works.

  15. #15
    yes sir is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    159
    Yes the lookups were created at the table level. When originally creating the db, (I was not thinking ahead too much), so I wanted any user to be able to select the prospect's college at the Table level. Stupid me, this can be done at the form level. This concept applies to some other fields too.

    So here is what I think needs to happen, feel free to correct me:

    1) Delete relationships of problem columns with conflicted tables.
    2) Create a new column with just Text as the data type
    3) Recreate relationships, maybe use the College Name and another field as the Primary Key for the College Table.

    But looking at Step 3 makes me think, will I need to use a combo box that lists the college and the other field (the reason why College can't be use only is because there are some colleges with the same name)? Or do I need to change the data type of my field to number and use the ID as the primary key for college?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 13
    Last Post: 12-05-2011, 05:10 AM
  2. Database Split - Error Subscript out of range
    By Huddle in forum Database Design
    Replies: 7
    Last Post: 07-16-2010, 01:52 PM
  3. Replies: 12
    Last Post: 06-14-2010, 08:39 PM
  4. Replies: 0
    Last Post: 03-04-2010, 06:32 PM
  5. Error message and How do I find the "value Field" ?
    By createdwithfire in forum Forms
    Replies: 1
    Last Post: 11-05-2009, 12:26 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums