After more than 20 years as a VBA developer I still use it occasionally to remind me how to perform a particular action, to show me how Excel performs a task I’ve never had to automate before, or simply to save time writing the code myself. How could it be fixed?Įxcel’s Macro Recorder is a great tool for creating simple macros and for helping you learn VBA programming. When run on the original data the macro worked fine but when run on different data (in this case the number of rows varied) the macro didn’t work correctly. The writer had recorded a macro that included sorting a range of data. This question turned up in my Inbox a few days ago. Modifying Recorded Macros: A Universal Sort Macro for Excel Instead of waiting for the user to finish typing then maybe using the AfterUpdate event of the text box as they leave it, or the Click event of a command button, my solution makes use of the Change event to run a procedure each time the content of the text box changes. The solution is to make use of the Change event of the text box into which the user types. Then they add another letter and the list shrinks to display only those items starting with that pair of letters, and so on… First they type a letter and they get a list of items starting with that letter. In this case my writer wanted a list that they could gradually refine. Usually you enter the text that you are looking for, then maybe click a button, and Access presents a list of items for you to peruse.
I have published various tutorials showing how to build different kinds of search tools, but this request was different. Recently a question arrived in my mailbox that interested me because, although it seemed quite a complicated request, the solution was relatively simple.
If you are looking for more on using the date functions in Access, here is a good tutorial video from YouTube: The first thing you notice is the fact that Access pretends it doesn’t know the Date function. The references you set aren’t recognized by the older versions and your code fails. You forgot to check which version of Access the client was using or, worse, you checked but they neglected to tell you that some users had different versions. You developed your database in a particular version of Access and cleverly had it interact with another application such as Excel or Outlook. That’s just Access teasing you and trying to throw you off the scent. It turns out that the problem isn’t the Date function after all. Even more confusing is that it doesn’t necessarily happen on every machine. Everything works fine so you roll it out with your client or colleagues and suddenly find that the Date function throws up an error. You have built your database with liberal use of the Date function in calculated fields on forms, in SQL in queries and in VBA code. In those days the Internet was new with little help available so if you had a problem you pretty much had to fix it yourself, not always an easy task especially when Access presents you with some very misleading clues. “Why Doesn’t the Date Function Work in My Access Database?” When I first came across this problem, many years ago as a novice Access programmer, I was really stumped. Here are some recent answers to a variety of questions: Fixing the Access Date Function
Welcome to FontStuff – your source for Office tips and tricks.
0 kommentar(er)
