Search results

  1. SurreyNick

    New Member Introduction

    Hello. It's a pleasure to welcome a fellow member and to congratulate you on your choice. I joined AWF a complete novice and yet, with the help of the members, I was able to achieve everything I set out to do :) Good luck with your projects!
  2. SurreyNick

    Excel vs Access

    It’s not uncommon for employees who have been in post for many, many years to possess excessive and unwarranted influence. This can become a problem if that person is older and just wants to ride out their time to retirement because they may have drifted into ‘coasting’ mode. These employees...
  3. SurreyNick

    Artistic Design

    As you gave the title of your thread "Artistic Deign" I thought I'd throw in a few suggestions. Have you heard of wireframing? This is a technique used in the development of websites and applications. A wireframe often serves as the first stage of design and a good one gives you a vision for...
  4. SurreyNick

    IF statement syntax question

    Ah. Yes, that makes sense. Thank you. It's one thing to know what to do but another thing entirely to understanding why and I'm keen to learn the why.
  5. SurreyNick

    IF statement syntax question

    I'm interested to understand why the syntax of this very simple IF statement works in example A but not in example B. Even though both examples require only one resulting action Example B requires an End If block to work, whist Example A doesn't. The only difference is that in Example B the...
  6. SurreyNick

    Solved Tips on good books on programming VBA

    I use Access 2010 and would recommend: Access 2010 VBA Programming Inside Out by Andrew Couch I haven't checked but there might be a revised edition for more recent versions of Access
  7. SurreyNick

    Correct method for transposing data from an Excel spreadsheet into an Access table?

    Thank you, the process is very clear. It is this procedure I don't know how to accomplish but I'm assuming I need to learn how to work with record sets. I'll let you know if I succeed. Thank again :)
  8. SurreyNick

    Correct method for transposing data from an Excel spreadsheet into an Access table?

    Using Access VBA is the sort of solution I was originally hoping for but I don't know how to do it or where to begin. If you could perhaps give me enough of a clue or point me in the right direction so I can run with it?
  9. SurreyNick

    Correct method for transposing data from an Excel spreadsheet into an Access table?

    Thank you. I am almost too embarrassed to say I don't think this is going to be the solution I need, because you have given me the solution to the problem I posed. I am however guilty of laziness in not fully explaining what I am trying to do. When I posted the original question I assumed there...
  10. SurreyNick

    Correct method for transposing data from an Excel spreadsheet into an Access table?

    Well, the union query was very straightforward and I can see it's going to be a useful tool, but alas I haven't been able to get it to produce exactly what I need. I have been able to use it to produce the result shown in pic1 and this gets me part way, but it lacks the all important question...
  11. SurreyNick

    Correct method for transposing data from an Excel spreadsheet into an Access table?

    Will do. The Excel power query proved itself to be a breeze to make and run, but creating and running it from within Access isn't quite as simple a task. I found conflicting opinion online whether it's even possible, but did eventually find a thread that claims to show a method. I haven't...
  12. SurreyNick

    Correct method for transposing data from an Excel spreadsheet into an Access table?

    It was perfectly reasonable to assume this was a one off. I should have made clear it wasn't. But in fact it is a procedure that will be executed regularly. It's for the exams assessment db I recently completed for my son's Biology dept. But until it has been thoroughly road tested the...
  13. SurreyNick

    Correct method for transposing data from an Excel spreadsheet into an Access table?

    Yes, thanks very much. A really clever solution and a very useful piece of knowledge to own. Now all I need to do is check I can execute the procedure within Access using VBA.
  14. SurreyNick

    Correct method for transposing data from an Excel spreadsheet into an Access table?

    Gosh. That's extraordinary! Please tell. How is it done? Nick
  15. SurreyNick

    Correct method for transposing data from an Excel spreadsheet into an Access table?

    Wow! Never heard of a Power query. Definitely want to know more about that :) Spreadsheet attached.
  16. SurreyNick

    Solved Intermittent "Object variable or With block variable not set" error with VBA routine

    Thanks @Isaac I have successfully completed this part of the exercise. I have got rid of all extraneous data in the original spreadsheet and am left with a nice clean table of records to transpose into my db. Very grateful to you. Now I just need to get it into Access and have posted a new...
  17. SurreyNick

    Correct method for transposing data from an Excel spreadsheet into an Access table?

    Using VBA can someone explain the process I need to follow to transpose the data in the spreadsheet shown in pic1 into a db table laid out as shown in pic2? Cycling through each of the records in the spreadsheet I need to take the StudentID and ClassRef and append to it an ExamPaperID which...
  18. SurreyNick

    Solved Intermittent "Object variable or With block variable not set" error with VBA routine

    Thanks, I'll do that, and thanks for your help on this issue. I'm confident I can take it from here :) Nick
  19. SurreyNick

    Solved Intermittent "Object variable or With block variable not set" error with VBA routine

    Yes, that's exactly what I was hoping I can do. Having recorded the macro I want to paste the code into the routine after XlBook.SaveAs fileName:="C:\Users\Nick\Desktop\Target.xlsx", and then finish off with the clean up, save and close statements. This is a routine that is going to be used on...
Top Bottom