"Subscript out of range" Error

[ajetrumpet]

will I need to use a combo box

NEVER use combos in tables. The reason should never matter.

I think your data is fine IMO. The college field can still be there, but it needs to be the FK for the PK in the COLLEGE table. The ideal situation from a development standpoint would be to develop a form for prospect input so that if they start typing in a college that is not in the database, Access will force them to input the college name into that table before entering the prospect record.

But it's obviously not ideal to have these relationships set up if this person (I assume this is a coach?) has data that they want to import into Access as their first step in the setup routine. If they were to import prospect data first and you had no referential integrity established with any of your relationships, that would be easy but it also creates the opportunity to upload orphaned records into the prospects table which will most likely never be recognized by Access in the future because (as far as I know) RI is enforced strictly with data entry routines.

I don't know this 100%...it's just a guess.

[yes sir]

2/2 so far on fixing the problem columns. Knocked the two easy ones out of the way to build up my confidence

Another issue I just ran in to. I created a new column for my database to make up for one of the problem columns. I adjusted my spreadsheet accordingly, however, I was receiving the subscript message still. I saved and closed both my db and ss and then reopened my db, then the upload worked just fine.

[yes sir]

4/4, last one left is the college field. Saved the hardest for last.

[yes sir]

Wrapped this baby up 30 minutes ago.

5/5.

Database is good for importing.

[ajetrumpet]

don't thank us or anything...

Good luck with it brother. I'm certainly glad you got it.

[yes sir]

don't thank us or anything...

Good luck with it brother. I'm certainly glad you got it.

Hey man, you know I appreciate everything that you have helped me with. You've basically taught me how to open up me eyes, lol.

[dlp]

I had this same problem and what I found is that for some reason, an @ symbol had been automatically entered into the General - Format field on several of my Fields when I was creating my table. As soon as I removed the @ symbols I was able to import with no issues.

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.