To all those who get an error message with Subscript out of range - instead of importing it into the table you created beforehand, choose the option "Import the source data into a new table in the current database". Then, tick the option "First Row Contains Column Headings", do not import the CompanyID column and instead let access add its own primary key (later you can rename it to CompanyID in the table design view). All you need to do next is to rearrange the data types the way she did it before - so format phone number, hyperlink and so on. It's a shame that the authors despite creating such a great tutorial limit themselves to sending us all to their website, even though it's so easy to fix.
I have watched quite a few of these Access training videos and this, I can tell you is the very best so far. Clear, accurate, no flim flam and no "umm-ing" or "ar-ing". Very very good. Many thanks
I have to say.....despite my having to find small fixes because my MS Access didn't do a few things the same, I'm having a much easier time understanding this video than any of the others so far on MS Access. I've tried and given up several times over a couple decades, lol. It's starting to make more sense now.
ERROR 2 Ran into the "Subscript out of range" error at 1:13:37 in the Access 2021 Beginner Tutorial? Here's a workaround that helped me move forward: Instead of importing the entire sheet at once, try copying one column at a time from the Excel file named "Import into Access" and pasting directly into the corresponding column in Datasheet View in Access. If you encounter a problem when copping the phone Column over here is the fix I found works for me: 1. Pop into Design View 2. Click anywhere on the phone Field Name 3. Down the bottom in Field Properties select input musk then move right across till you see three dots like … and click on it 4. Click on Phone Number under Input Mask: then click the edit List box. You should make the following corrections Description: Phone Number Input Mask: (000) 000-0000 Placeolder: _ Sample Data: (123) 123-1234 Mask Type: Text/Unbound Click close and finish. Return to Design View say yes to save on the way. Now it should work.
This show is right up my niche! A few months ago, I upgraded from Access 2019 to Access 2021. Microsoft Access 2002 was the first database program I used as a part of Microsoft Office XP Professional, since I had my first microcomputer in 2002. This is a reason I chose to stay with a Windows computer, instead of using the Apple Macintosh computer. In 2017, I bought a new and a Dell computer. I "leaped" to Microsoft Office 2016 Professional; then Microsoft Office 365 Professional, then Microsoft 365 Professional, then Microsoft Office 2019, and finally Office 2021 Professional. Your tutorial is a godsend!
ERROR 1. Ran into the "Subscript out of range" error at 1:13:37 in the Access 2021 Beginner Tutorial? Here's a workaround with the help of Ai in ChatGpt, we was able to move forward: Instead of importing the entire sheet at once, try copying one column at a time from the Excel file named "Import into Access" and pasting directly into the corresponding column in Datasheet View in Access. If you encounter a green error triangle in cells within the Excel 'Zip' column, it may indicate a potential issue. For example, I had to change the value in cell G7 from '02720' to '12720' to correct the format. After making the necessary corrections in Excel, double-click each cell with a green triangle to address any errors. This step ensures that the data is clean and ready for import. Once all errors are resolved, copy the corrected column from Excel and paste it into the 'Zip' column of the 'tblCustomers' table in Access's Datasheet View. Hope this helps anyone who might be stuck on the same issue. Happy learning!
Outstanding course. I wish I had this when I started learning Access back in the 80's. I will complete all sections for sure. The only thing you might include in the beginning is how to adjust the different visual change settings like fonts, size, color, how to tile vertically and horizontally. You are an excellent trainer. 🙂
Very good tutorial, thank you Trish. I would like to learn more about Listbox with multiple selection and multilevel with multi-column listboxes to pass data between each other. Example I have 300 Customers with a daily order, I need to put in a listbox those Customers and choose which ones I need to reprint their orders, of course with options like double click the customer to pass between listboxes. Thank you very much.
When i try to import the customer data i get subscript out of range - update i resolved the issue.. save the file re opened it and i was able to import with the "ërror"
when i try to use the "lookup wizard" it doesn´t work, it doesn´t bring the data from the other table into my table, it just stays the same (shows the numbers). I have done the exact steps that are shown on the video. Any help? im kinda losing my mind at this point
I'm having the exact same problem myself. If you've figured out how to solve this problem, please share with me how you did it. I would really love to know how.
,Have not watched the whole tutorial but what caught my eye was the dark theme. Can I do the same on access 2019? A query what are the updates on this from the one we went through with Dan McAllister.
1:21:07 what application are you using, instructor, to have this more options to arrange your window? This is super wicked! Thank you for the complete, step-by-step, and with amazing didactics course!
When I tried to import the Excel data to tblCustomers, the final step for importing says”Subscript out of range” at 1:13:37. I was stopped there. Would you pls advise further? Thanks a lot. I appreciate it.
I have also got the error "Subscript out of range" error at 1:13:37 in the Access 2021 Beginner Tutorial, could someone explain why we are getting a different errors? Thank you in advance
Tabular Form (10 rows) - How to update record of a second field by searching all the 10 rows ( or lòoping through) of another field. From some experts received a ❤ or "Good Question" but not a positive reply.
Hi Trish , This Error appear when i import it to excel sheet on the last part when i Click Finished. Error saying "Field extensions doesnt exist in destination table tblEmployees". Please help
Hello @Learnit Traning, i really want to continue this tutorial, but the subscript out of range is killing me here... this is what i have tried this far: Copy-paste with values in Excel to ensure that there is nothing wrong in the data file Deleted all the values in Customer ID in Excel (Access will create new ones anyway??) Tried changing the "active" collumn to numbers in Excel to match the "active" row in access Changed the data type of each row in tblCustomer to short text (except Customer ID) Please, what else is there to do?? And also, sorry if i miss-spelled the rows and collumns in Access and Excel
Why can't I find a tutorial using Access for a NON business model? Everything seems to be number excel related. Several years ago I used Access to set up databases for over 1,000 magazine articles, dates, issues, authors names publishers subject and so on. It's been a long time and with lack of use and need, I've forgotten how. Now its all business related tutorials?
Update...I found a workaround. Imported the full sheet to a new table & just copied the records over to the exsiting tblCustomers table, then deleted the one I had just imported. This allowed me to keep working.
Issue # 2: Tried to connect tblEmployees field "EmployeeID" to tblCustomers field "Employee" which is a number field. Warning pops up saying Access can't create this relationship and enforce referrential integrity, that data in the tblCustomers table violates integrity rules. The solution: In the tblCustomers table I created a new "Employee" field & transferred the note, then deleted the old field. For the property type, I selected "Setup Wizard" > "Want to look up the field to get values from another table or query > Next > selected "tblEmployees" and selected "Tables" > Next > Selected & moved "EmployeeID" to the right panel > Next > on the 1st open field on the next pane, selected "EmployeeID" > Next > kept the label the same but selected "Enable Data Integrity" & ensured "Restrict Delete" was chosen > Finish > and saved table so the relationship could be created. Now you'll see a line connecting the two in the Relationships view, but it isn't yet complete. What is correct is that the relationship line not only now appears, but also says "1" on the tblEmployees "EmployeeID" field and has the infintiy sign on the other one. Now the last step: double-click the relationship line & you'll see in the dialogue box that both fields are empty. You'll select the correct fields & then select "Enforce Referential Integrity" and "Cascade Update Integrity." Now it'll save without issue.
Issue #3: I'm noticing that when you change the data types to a Number and then use the Lookup Wizard, the data to become a dropdown still displays as a number in the table view, and it won't let you create a relation between the two tables. What I found works is to go ahead and close the two tables you just changed & added the Lookup Wizard to (save them as you do), and then create the relation in the relations view (or update it). Then when you open those tables again, it no longer displays as a number but intead as words.
New and bigger issue: Why is it creating new tables in the Relationships view??? It created extra copies of three of the tables and added a "_1" at the end of it, but the tables in the navigation pane remain the same.........Huh??.......Well, the "_1" ones weren't linked to everything else, so I just hid those. Now it matches, lol.
Hi Sir, When I import Customer table from Excel to Access tblCustomer table I have Error message " Subscript out of range". Can I know what is happening?
Great lesson. There's only one problem, on the error you show us I'm not getting the option to continue with the erros, instead I'm getting Subscript out of range. How can I fix it?
I had this too, they've attached incorrect files. You need to ensure data-types are the same type; especially relating to primary keys. They really have messed up attaching the files - & don't get me started on the naming & order of the playlist lol.
@@lucys4312 They need to sort out the exercise files; need to be accurate as to what is at the beginning of the tutorial. I think they've dispatched Lesson-01 tutorials to the entire series lol.
@@dalskiBo Yes, you're right, they need to attach the correct files. I ran into a problem with the Lookup Wizard at the end of the video. It just didn't work the way shown in the video. Anyway, I thought it wasn't a big deal because I was expecting that they'd provide the Access file that they use at the start of the second tutorial - Access 2021 Advanced Tutorial (Part 2 of 3). But no, they attached the files from the 1st tutorial. This is so frustrating. The instructor is so good and I'd like to be able to follow along without bumping into these kind of problems.
How to fix the subscript error in the 'import into access' file. DO THIS -- In the excel file delete the numbers in the customerID column they will then regenerate without a problem when the file is imported.
@@gradimukwasa1386i deleted the whole column, and saved at another location. But “out of rang” problem is still there. Would you pls advise further? Thanks a lot.
To all those who get an error message with Subscript out of range - instead of importing it into the table you created beforehand, choose the option "Import the source data into a new table in the current database". Then, tick the option "First Row Contains Column Headings", do not import the CompanyID column and instead let access add its own primary key (later you can rename it to CompanyID in the table design view). All you need to do next is to rearrange the data types the way she did it before - so format phone number, hyperlink and so on. It's a shame that the authors despite creating such a great tutorial limit themselves to sending us all to their website, even though it's so easy to fix.
Thank you for the great tip!
Thanks, that saved me.
Thank you so much for your help there! It's much appreciated! :-)
I have watched quite a few of these Access training videos and this, I can tell you is the very best so far. Clear, accurate, no flim flam and no "umm-ing" or "ar-ing". Very very good. Many thanks
Wow, thank you!
I have to say.....despite my having to find small fixes because my MS Access didn't do a few things the same, I'm having a much easier time understanding this video than any of the others so far on MS Access. I've tried and given up several times over a couple decades, lol. It's starting to make more sense now.
Great to hear!
ERROR 2
Ran into the "Subscript out of range" error at 1:13:37 in the Access 2021 Beginner Tutorial? Here's a workaround that helped me move forward:
Instead of importing the entire sheet at once, try copying one column at a time from the Excel file named "Import into Access" and pasting directly into the corresponding column in Datasheet View in Access.
If you encounter a problem when copping the phone Column over here is the fix I found works for me:
1. Pop into Design View
2. Click anywhere on the phone Field Name
3. Down the bottom in Field Properties select input musk then move right across till you see three dots like … and click on it
4. Click on Phone Number under Input Mask: then click the edit List box.
You should make the following corrections
Description: Phone Number
Input Mask: (000) 000-0000
Placeolder: _
Sample Data: (123) 123-1234
Mask Type: Text/Unbound
Click close and finish. Return to Design View say yes to save on the way.
Now it should work.
This show is right up my niche! A few months ago, I upgraded from Access 2019 to Access 2021. Microsoft Access 2002 was the first database program I used as a part of Microsoft Office XP Professional, since I had my first microcomputer in 2002. This is a reason I chose to stay with a Windows computer, instead of using the Apple Macintosh computer. In 2017, I bought a new and a Dell computer. I "leaped" to Microsoft Office 2016 Professional; then Microsoft Office 365 Professional, then Microsoft 365 Professional, then Microsoft Office 2019, and finally Office 2021 Professional. Your tutorial is a godsend!
Thank you so much for watching!
ERROR 1.
Ran into the "Subscript out of range" error at 1:13:37 in the Access 2021 Beginner Tutorial? Here's a workaround with the help of Ai in ChatGpt, we was able to move forward:
Instead of importing the entire sheet at once, try copying one column at a time from the Excel file named "Import into Access" and pasting directly into the corresponding column in Datasheet View in Access.
If you encounter a green error triangle in cells within the Excel 'Zip' column, it may indicate a potential issue. For example, I had to change the value in cell G7 from '02720' to '12720' to correct the format.
After making the necessary corrections in Excel, double-click each cell with a green triangle to address any errors. This step ensures that the data is clean and ready for import.
Once all errors are resolved, copy the corrected column from Excel and paste it into the 'Zip' column of the 'tblCustomers' table in Access's Datasheet View.
Hope this helps anyone who might be stuck on the same issue. Happy learning!
click on the magnet, and from there you can adjust how the tracks snap onto the grid. if you want it to be each 1/4 of a bar, click "line"
Thank you for the tip Ermias!
Very educative, easy to understand..
I will recommend this page to anyone interested in learning ms access
Thanks for that!
it was great thanks for sharing , i hope i can get more details about Many to Many Relationship in future videos of this course
We will look into it!
Outstanding course. I wish I had this when I started learning Access back in the 80's. I will complete all sections for sure. The only thing you might include in the beginning is how to adjust the different visual change settings like fonts, size, color, how to tile vertically and horizontally. You are an excellent trainer. 🙂
Thank you so much for the kind words!
I really appreciate your video on the access database for beginners, I have learnt alot... thankyou!
You're very welcome!
Love your course and the way you explain things precise and calmly. Awesome!!
Thank you so much!
Very good tutorial, thank you Trish. I would like to learn more about Listbox with multiple selection and multilevel with multi-column listboxes to pass data between each other. Example I have 300 Customers with a daily order, I need to put in a listbox those Customers and choose which ones I need to reprint their orders, of course with options like double click the customer to pass between listboxes. Thank you very much.
You are welcome!
This is amazing! Please, DO NOT STOP! Thanks!
Thanks!
Best ever IT learning Chanel
Thanks for the support, Mohammad!
This is the best course ever
Thanks!
em muon goi loi cam on chan thanh nhat den voi ban , chuc ban suc khoe
Không có chi! Cảm ơn vì đã xem!
When i try to import the customer data i get subscript out of range -
update i resolved the issue.. save the file re opened it and i was able to import with the "ërror"
Great work!
when i try to use the "lookup wizard" it doesn´t work, it doesn´t bring the data from the other table into my table, it just stays the same (shows the numbers). I have done the exact steps that are shown on the video. Any help? im kinda losing my mind at this point
Hello Fernando! You can get help directly from our instructors at Offsite, our online community - www.offsitebylearnit.com/
I'm having the exact same problem myself. If you've figured out how to solve this problem, please share with me how you did it. I would really love to know how.
,Have not watched the whole tutorial but what caught my eye was the dark theme. Can I do the same on access 2019?
A query what are the updates on this from the one we went through with Dan McAllister.
1:21:07 what application are you using, instructor, to have this more options to arrange your window? This is super wicked!
Thank you for the complete, step-by-step, and with amazing didactics course!
Thanks Trish great stuff
Thank you!
Thank you Trish!
Thank you for watching!
Great teaching skills!
Glad it was helpful!
Thank you very much
You are welcome
When I tried to import the Excel data to tblCustomers, the final step for importing says”Subscript out of range” at 1:13:37. I was stopped there. Would you pls advise further? Thanks a lot. I appreciate it.
Hello Popo Popo! You can get help directly from our instructors at Offsite, our online community - www.offsitebylearnit.com/
I have also got the error "Subscript out of range" error at 1:13:37 in the Access 2021 Beginner Tutorial, could someone explain why we are getting a different errors? Thank you in advance
Tabular Form (10 rows) - How to update record of a second field by searching all the 10 rows ( or lòoping through) of another field. From some experts received a ❤ or "Good Question" but not a positive reply.
صلى الله عليه وسلم
شكرًا لك
Excellent ACCESS tutorial👍🏻👍🏻
Thank you so much Kebin!
Thank you
You are welcome!
Hi Trish , This Error appear when i import it to excel sheet on the last part when i Click Finished. Error saying "Field extensions doesnt exist in destination table tblEmployees". Please help
Hello Paradise! You can get help directly from our instructors at Offsite, our online community - www.offsitebylearnit.com
can skip fields?
It looks that the intermediate level removed, doesn't it?
Here is the full Ad-Free version for our members: th-cam.com/video/RhmshZtV65I/w-d-xo.html
Hello @Learnit Traning, i really want to continue this tutorial, but the subscript out of range is killing me here... this is what i have tried this far:
Copy-paste with values in Excel to ensure that there is nothing wrong in the data file
Deleted all the values in Customer ID in Excel (Access will create new ones anyway??)
Tried changing the "active" collumn to numbers in Excel to match the "active" row in access
Changed the data type of each row in tblCustomer to short text (except Customer ID)
Please, what else is there to do??
And also, sorry if i miss-spelled the rows and collumns in Access and Excel
Hello Simen! You can get help directly from our instructors at Offsite, our online community - www.offsitebylearnit.com
Why can't I find a tutorial using Access for a NON business model? Everything seems to be number excel related. Several years ago I used Access to set up databases for over 1,000 magazine articles, dates, issues, authors names publishers subject and so on. It's been a long time and with lack of use and need, I've forgotten how. Now its all business related tutorials?
We focus on skills that will be used in the office hence our videos are very business centric.
@@LearnitTraining My job was in Publishing!
I am trying all the tips to fix the "Subscript out of range" but nothing is working!!! Any help!
Hello BASKETBALLEKO! You can get your question answered by our instructors at Offsite, our online community - www.offsitebylearnit.com
@@LearnitTraining Thanks!
I tried the follow-along and upon trying to import Customers, the wizard said "Subscript Out of Range." I did everything exactly as you showed. Ugh.
Update...I found a workaround. Imported the full sheet to a new table & just copied the records over to the exsiting tblCustomers table, then deleted the one I had just imported. This allowed me to keep working.
Issue # 2: Tried to connect tblEmployees field "EmployeeID" to tblCustomers field "Employee" which is a number field. Warning pops up saying Access can't create this relationship and enforce referrential integrity, that data in the tblCustomers table violates integrity rules.
The solution: In the tblCustomers table I created a new "Employee" field & transferred the note, then deleted the old field. For the property type, I selected "Setup Wizard" > "Want to look up the field to get values from another table or query > Next > selected "tblEmployees" and selected "Tables" > Next > Selected & moved "EmployeeID" to the right panel > Next > on the 1st open field on the next pane, selected "EmployeeID" > Next > kept the label the same but selected "Enable Data Integrity" & ensured "Restrict Delete" was chosen > Finish > and saved table so the relationship could be created. Now you'll see a line connecting the two in the Relationships view, but it isn't yet complete.
What is correct is that the relationship line not only now appears, but also says "1" on the tblEmployees "EmployeeID" field and has the infintiy sign on the other one.
Now the last step: double-click the relationship line & you'll see in the dialogue box that both fields are empty. You'll select the correct fields & then select "Enforce Referential Integrity" and "Cascade Update Integrity." Now it'll save without issue.
Issue #3: I'm noticing that when you change the data types to a Number and then use the Lookup Wizard, the data to become a dropdown still displays as a number in the table view, and it won't let you create a relation between the two tables. What I found works is to go ahead and close the two tables you just changed & added the Lookup Wizard to (save them as you do), and then create the relation in the relations view (or update it). Then when you open those tables again, it no longer displays as a number but intead as words.
New and bigger issue: Why is it creating new tables in the Relationships view??? It created extra copies of three of the tables and added a "_1" at the end of it, but the tables in the navigation pane remain the same.........Huh??.......Well, the "_1" ones weren't linked to everything else, so I just hid those. Now it matches, lol.
Thank you for the incredible feedback! It helps us to create better videos going forward
can i have the link for the presentations?
Hello Mohamed! We do not normally supply the presentation slide decks unfortunately.
Hi Sir, When I import Customer table from Excel to Access tblCustomer table I have Error message " Subscript out of range". Can I know what is happening?
Hello Soe! You can get help for this directly from our instructors at Offsite, our online community - www.offsitebylearnit.com/
Great lesson. There's only one problem, on the error you show us I'm not getting the option to continue with the erros, instead I'm getting Subscript out of range. How can I fix it?
Hi Pagliarone! You can get help directly from our instructors at Offsite, our online community - www.offsitebylearnit.com
I had this too, they've attached incorrect files. You need to ensure data-types are the same type; especially relating to primary keys. They really have messed up attaching the files - & don't get me started on the naming & order of the playlist lol.
I changed the formatting of the values in the "Active" column (0 and -1) in the Excel file to number format and then it worked.
@@lucys4312 They need to sort out the exercise files; need to be accurate as to what is at the beginning of the tutorial. I think they've dispatched Lesson-01 tutorials to the entire series lol.
@@dalskiBo Yes, you're right, they need to attach the correct files. I ran into a problem with the Lookup Wizard at the end of the video. It just didn't work the way shown in the video. Anyway, I thought it wasn't a big deal because I was expecting that they'd provide the Access file that they use at the start of the second tutorial - Access 2021 Advanced Tutorial (Part 2 of 3). But no, they attached the files from the 1st tutorial. This is so frustrating. The instructor is so good and I'd like to be able to follow along without bumping into these kind of problems.
How to fix the subscript error in the 'import into access' file.
DO THIS -- In the excel file delete the numbers in the customerID column they will then regenerate without a problem when the file is imported.
Thank you for the tip!
@@LearnitTraining Unfortunately the Excel file is locked as "Read Only" and I can't delete or change anything.
@@RobertBrunnerBREG try to save the file in different location on your PC
Did you mean delete the whole column of CustomerID?thanks a lot in advance.
@@gradimukwasa1386i deleted the whole column, and saved at another location. But “out of rang” problem is still there. Would you pls advise further? Thanks a lot.
dat escalated quickly
I cannot import the customers table because i am getting an index error….can you help
Hello Lolo1412! You can get help directly from our instructors at Offsite, our online community - www.offsitebylearnit.com
everything is okay until you start experiencing the 'Subscript out of range " error message and your frustrations begin 😔😔😔
Same here
How did you so;be the problem finally?