Misbehaving Value List
I wonder if someone can shed any light on my problem - I'll describe it as best I can:
I have created an Engineers booking system for sending and monitoring the work of our company's gas engineers who service house boilers.
Everything is fine on our central booking form (using various linked tables) apart from a problem occuring with looking up house addresses. We have a table for Property Addresses which I use as a Value List. The idea is that when a call comes in the caller is asked their Postcode (Zipcode). In the UK a single postcode such as this - LS103AQ - describes a section of a street on which there could be maybe a dozen different numbered properties - for example 1,2,3,4,5,6,7,8,9,10 Bedford Street can all end in the same postcode.
So I have set the Value List up to filter only the addresses corresponding to the entered postcode and the resultant small filtered section of addreses appear in a drop down list which the operator can then pick a single correct address from.
My problem is this - whatever the last picked address is for any postcode - let's use LS103AQ above as the example - then the last picked property value alters all the previous property values for that postcode used in the Job booking table. So for instance if I do a job booking and use that postcode above and I pick 1 Bedford Street as the address that's fine, it is entered on the form. Then lets say a day later another call comes in from 8 Bedford Street and we enter the same postcode and pick Number 8 from the Bedford Street list, then the record entered the day before for 1, Bedford Street immediately changes to 8 Bedford Street as would any other record with the same postcode.
This is driving me nuts!! Can anyone shed any light on what I may be doing wrong?
I have defined relationships between the Job Booking Form and then Property List with a field I call Postcode to link the tables together but I could expand on this information if any wants to look at my database file.