Access: Runtime version and Trusted Locations

So I was working on this project and bumped into the following situation. Client needed a Runtime version of an Access app. No problem we downloaded the Developer Extensions (which can be found here: http://www.microsoft.com/downloads/details.aspx?FamilyId=D96A8358-ECE4-4BEE-A844-F81856DCEB67&displaylang=en ) and installed that. After that we downloaded the Access Runtime engine (which can be found here: http://www.microsoft.com/downloads/details.aspx?familyid=D9AE78D9-9DC6-4B38-9FA6-2C745A175AED&displaylang=en ) and went ahead and packaged the accdb. This all went fine.

Installation on the client pc
After installation on the client pc we bumped into some various issues which i think can be important to know for when you are confronted with this situation. The installation on the client pc using the setup from the packaged file went fine. We started the app and the very first message we saw was a security message. This message isn't al together clear but after investigation it a bit further i knew this had something to do with the trusted location settings. Ofcourse... during development we had set the trusted location to point x and that gave no issues. But when distributing you will run into this issue because you can't set anything anymore once you've created a runtime. So how to go about this situation. We thought of a couple of options we could do:

• Add the trusted location to the original accdb settings when packaging... no that would not make a difference because it's a user based situation. Besides we wouldn't know where the installation would take place;
• Try to figure out how access treats Trusted locations and try to modify those settings.

Registry settings (be careful!!)
We chose the latter one and took the plunge into the registry. Now remember that messing with the registry can mess up your computer, so unless you know what you are doing leave it alone and let someone who knows what he's doing help you with making the necessary adjustments.

Every trusted location you add to the Trusted Locations in the Trustcenter Settings from within Access is written to the registry. When adding the runtime engine to a certain client pc a set of keys will be created. By adjusting those settings you can set a specific path as an additional trusted location.

Keys
These are the keys you have to add to the trusted locations. As stated earlier, if you are not familiar with the registry don't touch it and ask for help from someone who knows this stuff!

The Key you have to add is the following:
[HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Access\Security\Trusted Locations\Location1
AllowSubFolders (REG_DWORD) = 1
Path (REG_SZ) "c:\thepathtoyourdatabasefolder"

Explanation
The first line you see is the treestructure you have to follow to get to the designated registry key
The second line is the key you have to add to the settings the 1 indicates that the subfolders should be also allowed as trusted.
The third line is the line where you add the path (don't forget the double quotes here..) to the trusted location to be added. This example set's it to c:\blablabla but this can be antoher driveletter altogether..

Now every loaction added from within Access will create an addition location number. It might be that you alreade have Location1, Location2 etc. In that case you have to add another Location(x) to make sure you don't alter anything existing

Next step
After adding the registry key we started the application again and the security warning disappeared. Yep this worked. Next we were confronted with an additional warning - error 2950. As usual this can mean a lot. In our specific case we are trying to run code when the app starts (initialize routine which checks the linked tables). At that point we were presented with the dialog that all macro's are halted and there you are knowing not wat to do. What causes this.

Diving into the registry again. Setting the sandbox option to something different? That's one of the suggestion we could do but in my opinion that wouldn't make a change here because i didn't want to work in sandbox mode.

Finding the solution to this problem
I didn't find a solution yest for this problem but we are working on it. I'm going to eliminate the variables to see what might cause this issue. Meanwhile i've posted a question on the Technet Access forum (which you can find here: http://social.technet.microsoft.com/Forums/en-US/access/threads ) so other developers can think with me on this one. I'll keep you posted on this one.

Conclusion...
In the early days the enable macro options was one of the features you had to worry about. Now you have a lot of other issues you have to consider when distributing the app. Keeps you sharp ;-)

Access: Returning Filtered Data on Form

I like spending time in the Access newsgroups. Trying to help people out finding a solution for a problem they are struggling with. Sam asked a question about a search option in a form header and showing the results of the query to the detailsection of the form. After a couple of posts we are almost there. But sometimes it's better to show the image. So this post is for Sam explaining what we are talking about in the thread of the newsgroup. So here we go Sam.

Images (click to enlarge...)

I'll add several images for clarity here and try to explain what the goal should be. Remember Sam that you have to tune this to your own situation because I don't have your data...

I've based the example on a single simple table called Customers:

Now it's time to take the next step: create the query: "qGenerics". In your sample you can run the sql which you have create in your VBA. This will add the SQL statement to the query.

When the query is added it's time to create the form and it's functionality you are looking for see the next page. My form is called "form2"

You create the form by using a wizard or create one yourself. I used Access 2003 but the funtionality in 2007 is nearly the same. Use a wizard or create a blank form with the "qGenerics" as recordsource.

The next couple of steps are important they are based on the view you would like:

The images should be followed top down so after the new form image we take the next step: you have to set the recordset. If done by the wizard this is taken care of for you. When you don't use the wizard you'll have to chose it yourself.

The fieldlist will show up next to the design of the form. Add a formheader and a formfooter to the form. Drag the fields to the detailsection of the form. Rearrange them the way you like. I often use the tabular layout which is pretty familiar with most people.

As you can see i added a form header and a for footer. I've taken the labels and added them to the form header. When you set your form to "continuous" this will prevent the labels being showed with every record. After that i've narrowed the detail section to the smalles

possible size. The records will be shown as a list so we can save the space and the records will be show tightly under one another.

Now set the form to "continuous" in the form properties.

Add a search text box to the header of the form (regular textbox).

When the textbox is added set an event to the afterupdate event. In VBA add the following code: see next pincture.

The VBA code is fairly straight forward. I set the DB to the currentdb. set the strSQL to be a regular select statement. For conveniance i added an if statement to check something has been typed in the searchbox. If not then all the records will be displayed. If something is typed in the strSQL is adapted accordingly.

I remove the recordsource by setting it to "" and the resetting it by the newly filled "qGeneric". Because this action will paint the form i set the application echo to false, perform the action and set the application echo to true again. This prevents the painting of the form and will show the filtered set directly. Which reminded me that you can achieve the same result by just using a filter. I've added a double click event to the search text box. This will trigger the filter which i set based on the parameter entered in the searchbox. In this case it's the 'city' parameter.

Now let's see the results:

When the textbox is empty and the user doubleclicks on the searchbox all the records will be shown. If something is typed in the searchbox and enter is pressed the recordset will be adapted and shown accordingly. Remember you have to add some nice errorhandling to top things of after all we are dealing with users and you'll never know what a user is up to ;-)

Final result

before and after the search has been set..

Sample database can be found here:

Access: Data Macro - First steps to a new approach

In this post i'd like to show you a simple approach to the DataMacro at the Table level. I've been playing around with the [CreateDataMacro]. You can find this option in table design and apply this to the tabledata you are currently working on. (You can click on the images to enlarge the view)

Let me show you...
For this example i used a table with a couple of fields and i wanted to achieve an after update trigger for the record. The final outcome should be:
When the value of the field status="Completed" the value of the field [Percentage Completed]=100%. For other options from the combo i will chose different percentages.

So first things first let's find the action button to create the DataMacro. When in table design collapse the [Create Data Macro's] button. This is where you will find the various event triggers. Once you have added a data macro you can always check to see if any data macro's are present because in that case you'll see a small square around the trigger icon (in this example the after update option).

Next step:
You will be presented with a blank data tab where you are expected to write your own events. This might be scary at first because you have to discover the approach of creating the triggers.
In this case we are trying to create an after update event. We'll do so by using a simple IF statement.

IF - statement
On the right side of the screen you can see the Action Catalog. This is the place where you can choose the various actions. And as usual you can Drag the IF statement from the action catalog to the [Add New Action] frame. Drop it in the frame and you are presented with the view you can see in the picture shown on the right. Click in the block after the IF word and start typing the name of the field that has to be checked for a value. In our case it will be the [Status] field. Nice feature is the auto complete / intellisense option which appears when typing your field name. It's often that you don't even have to type the complete fieldname... brilliant!

ELSE IF
A very important step is to add a  [Add Else] or [Add Else If]. In our sample we added an Else If statement. That's not the hard option, just click on that option on the right side of the If block and it will be added to your macro. But it's important to choose the right action which should follow after the trigger fires. In our case it should [Edit a record]. That's the option we choose from the Action Catalog and drag it to the if block already present. Now add one final step namely the [set field] option from the catalog. Like previously drag and drop it to the logical place in the IF block. You sample should look something like this:

Summary:
1. Add an IF block from the Action Catalog
2. Add an [Data Block] - [Edit Record] and drag and drop this in the IF block
3. Add a [Data Actions] - [Set Field] and drag and drop this in the [Edit Record] block

repeat the steps for the else if block (copy does the trick ;-)

Now you are ready to fill in the appropriate fields in the various blocks. I've added an already filled in macro picture to clarify.

Explanation:
In the first step we added the field [Status] and set the validation to check the value of the field to "Completed". In the second step we added the setfield option to the field that has to be updated; in this case the field [Percentage Completed] and filled in the value field to the value 1 (being 100%).
The Elseif statement can be filled in the same way with a different validation. In the third step I added an Or validation to tackle two options which would set the value of the [Percentage Complete] field to 0 (0%). All the other options should be a piece of cake now ;-)

Save and check-try
Ofcourse you are curious to check if the data macro works. Save your macro (or click close and then choose yes). Now look at the table in datasheet view and fill in one record. When you go to the next record you will see that the appropriate field [Percentage Completed] is updated with the values as set in the datamacro. And you know what the beauty of it is? When you create a form which has this table as a datasource the datamacro will also work on your newly created form.

Conclusion...
A great feature has been added to this Access version. It might take some getting used to but it's worthwhile getting to know it. Did i mention that you can save the macro's ouside your project and that you can reuse them in other projects..no.. well you can. So don't dive back into VBA when you are getting stuck the first time. Practice a little and you'll see that this is time well spent.

Access: We're getting there...

So curious as i was i started beta testing Office 2010. I heard many good things about this version. I spent most of my time in Microsoft Access so that would be the first one to take a closer look at. I knew that the Office button wouldn't be around anymore so that was no suprise. Finding 'backstage' instead felt more intuitive for me looking like the old 'File' menu. So starting Access to see what i heard about it was actually as cool as it sounded. In the next couple of posts i'll be looking into some of the added features in Access 2010. I'll describe them as I go along.

Parameter in query
Everyone uses this frequently; the parameter in a query. Often you have to refer to a control on a form or subform to use what the user typed in as the parameter for your query. If you have been around a while you know that this action always ends up as a copy paste action hoping that you didn't make any typo's the first time.

While i was creating my parameter query something popped up during this progress. Wow did I see that correctly or are my eyes deceiving me? Did a dropdown menu just appeared when i added the exclamation mark? Yes it did. I couldn't help cheering for a short moment. Yes this is what we are looking for, this is what make life easy.
Now my only hope would be that all those options i have been dreaming about will eventually make it through to the next versions.

creating the query parameter...

Making your wishes known
Many people i talk to have the tendancy to think that the developers of Microsoft don't care or listen to their comments. I dare to disagree.. there are many options that have been implemented just because the testers and users do post there wishes to newsgroups, blogs, beta groups etc.
You need one more example? Ok how about the recently used file option where you can 'pin' your documents and sheets in Word and Excel. So quiz question... Was this option available in 2007 for Access. Answer no. I made the suggestion via Microsoft Connect to also add this feature to Access and why not... every other office app had it. And now see what happend!

My next wish?

I've been praying for a magnifying option in
form designview for a couple of versions now and it hasn't been implemented yet. Well just keep posting my wish.. maybe next version! Some day we'll get there ;-)