support@ascassociates.biz
,
Sales Enquiries
sales@ascassociates.biz
This article shows you how you can easily get tripped up when attempting to write a Before Change data macro to set a default for a lookup.
One of things I often want to do is set a default value for a lookup field, but in a web database this is not supported. So the simple solution is to write a BeforeChange data macro to set the value.In this article I want to explain how to avoid a number of easily made mistakes when writing Before Change data macros to resolve this problem.
The example database we will look at has two tables. One for recording crops grown in the garden tblCrop and the other to classify the crop as a vegetable or herb tblCropType. Note there is an intentional miss-spelling of the word vegetable as vegtable in what follows, the reason for this will become clear.
When you define a lookup in a web database, Access does not allow you to define a default value, so it would seem sensible in this application to use a data macro to set a default if it has not been provided.
The macro code you may be tempted to write could look like the following:-
The problem is that if you do this you will get the following error ('type mismatch on field': -
So let us now see how to do this correctly, and then we will understand explain why the first attempt failed. Below is the correct way to assign the default value.
To understand why our first attempt failed we need to look back at the structure of the lookup table.
We can see that it is the actual ID value that needs to be recorded (5) and not the associated text (Vegtable) which is displayed because this is a lookup.
But if we look at our main table we see the following:-
The field CropType is in fact not CropType text but actually the ID field from the other table. This is an example of software being very clever, but also being a bit subtle.
To make matters worse I am have a habit of making systematic typos such as using Vegtable and not Vegetable.
So now I have to correct all the miss-spellings of Vegtable to Vegetable. How do we do this?
The good news is that because the lookup from tblCrop only holds the ID and not the text we can go back into our table and edit the text as shown below.
And now in the main table it automatically displays the corrected text, because it looks up the text value.
Access Web Databases do not support a cascade update on a primary key, and at first this looks like a limitation. But because a simple lookup to a text field has been replaced with a table having an ID you don’t need the cascade update in the first place.
You just need to be very careful when working with the lookups, and make sure you are saving the correct data values in the correct fields.
After publishing this database we run into a small problem as shown below, when allowing our data macro to set the default.
The problem here can be understood once we look at the contents of both the old and new lookup tables.
The unpublished table looked like this:-
The published table looks like the following (right click on the shortcut above any field and select Unhide fields to see this):-
We see that during publication the ID fields have been remapped (don’t worry they have also been remapped correctly for the tables using this lookup). The _OldID will NOT be updated for new records, so we don’t want to use this.
But our macro code would now need changed to use (2) rather than (5).
Below is a better solution, because it avoids any dependency on hard-coding internal numbers and makes use of the Look Up A Record In macro command to find the ID that corresponds to the piece of data.
The conclusion of this article is that you can assign sensible default values in lookups, but when doing so you should lookup any identifiers to avoid creating a dependency on numbers which after publishing could have changed.
by Andrew Couch Office Access MVP, Director ASC Associates
andy@ascassociates.biz
support@ascassociates.biz
,
Sales Enquiries
sales@ascassociates.biz