Results 1 to 9 of 9
  1. #1
    ahuffman24 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    99

    "Subscript out of range" error when creating subform


    Hi,

    I have Microsoft Access 2016. I have two related tables in my program (Inspection Table and Customer/Order Info Table), as shown in the attached picture. I am trying to create a form displaying all fields from the Customer/Order Info Table and a subform with just the Serial Number and Heat Number fields from the Inspection Table. The intent is for the user to enter in information for each field. However, when I try to create the subform, I get an error that says "Subscript out of range" (shown in attached picture). I tried researching this online, and it seems that most people get this error when trying to import an Excel sheet into Access. Why would I be getting this error, and how can I fix it?


    Click image for larger version. 

Name:	MS Related tables.png 
Views:	13 
Size:	57.8 KB 
ID:	38442Click image for larger version. 

Name:	MS Inspection subform.png 
Views:	13 
Size:	21.2 KB 
ID:	38441

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I would start with assuming corruption as I see that you are using this db from One Drive. Drives like this are for storage only, and if using an Access db across one, you are asking for corruption. Copy the db to a local folder that is Trusted. Create new db and from it, import every object from the old db and see if that fixes it.

    While it's possible that the issue can be caused by a field from a query or table that isn't found during this operation, I kind of doubt a wizard would give the option of including one if it isn't there, nor would it present a misspelled version of it. My bet is on corruption - especially because of how you're accessing this db. Nor would I use one over a usb drive.

    It is also possible that the main form is corrupt and that importing it will only bring over the corruption. Best approach would be to ditch the main form and recreate if that happens or recreate the form then import everything. However, I would do none of this while the db is on OneDrive.
    Last edited by Micron; 05-20-2019 at 12:45 PM. Reason: added info
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    ahuffman24 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    99
    Micron, I did as you said and created a blank database stored in a folder not on OneDrive. I imported all of my tables/forms and tried creating the subform again, and I got the same error. Just in case the imported tables/forms carried over the corruption, I made a completely new database to test if the subform creation would work if I started from scratch. Icreated two tables, established a relationship between them, created a form, and tried to create a subform, and I still received the same error. Pictures of this new database are attached

    Click image for larger version. 

Name:	MS test subform.png 
Views:	13 
Size:	20.7 KB 
ID:	38445Click image for larger version. 

Name:	MS Test Related Tables.png 
Views:	14 
Size:	39.8 KB 
ID:	38444

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Sounds like you covered every potential. All I can suggest at this point is to upload your latest db to see if it happens to anyone else. I'm also using W10 and A2016. However, one last thing I can think of is whether or not Trusted Locations are at play. While the wizards basically use code, it's built in and not user created. I just don't know if built in code is an issue with a db not being in a TL. Probably not, but I'm grasping at straws so to speak, without being able to test in my version.

    For sure the data and data types of the fields you're joining on are compatible?
    Last edited by Micron; 05-20-2019 at 02:10 PM. Reason: added question

  5. #5
    ahuffman24 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    99
    Figured out what it was. When I was prompted to add the desired fields to my subform, for some reason I needed to include both the desired fields AND the fields linking the tables together. Originally I was not including the fields linking the tables. Including the fields linking the tables solved the issue. Thanks for your help!

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    While it's possible that the issue can be caused by a field from a query or table that isn't found during this operation
    So that was the reason, but not because of the field (by any spelling) not being in the db. It was because the wizard had no field specified for it to automatically link the Child and Master fields. Interesting. I learned something - but I hardly ever use wizards, so might not come in useful.

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I'm going to be a picker of nits.......

    To me, you need to work on your table designs and relationships. It looks like a spreadsheet was imported into Access.
    You are duplicating data - fields: order number & item number).

    Other things:
    you started a field name with a number
    you have spaces in object names (fields, tables and forms)
    you have special characters in table names (1 table)



    My $0.02..............

  8. #8
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    We all need a nitpicker at times. Keeps us on track. Thanks for pointing that out - I completely bypassed the visual and went to the problem as the tables didn't seem pertinent to the problem. That's called over-looking?
    Now that you mention it, OP will probably find out down the road that ID by itself makes for a rather ambiguous field name.

    Order Number shouldn't even be in the inspection table. That's what order ID is for. Nor should customer info be in an orders table. The name of that table suggests there is or will be a mix of relational data.
    Now who's nitpicking??

    Some info around naming and such:
    - http://access.mvps.org/access/general/gen0012.htm
    What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
    Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
    Last edited by Micron; 05-20-2019 at 04:41 PM. Reason: added comment

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Ack!! I saw the PK field names of "ID" also and meant to have it in the list, but it seems I have a memory leak in my limited RAM...

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 02-18-2017, 05:45 AM
  2. Replies: 3
    Last Post: 01-13-2015, 05:21 PM
  3. Replies: 4
    Last Post: 02-13-2013, 02:35 PM
  4. Replies: 3
    Last Post: 02-01-2013, 12:26 PM
  5. "Subscript out of range" Error
    By yes sir in forum Access
    Replies: 21
    Last Post: 08-16-2012, 08:02 PM

Tags for this Thread

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