Thank you for your reply and interesting observations.
Originally Posted by
Ajax
if locationID and mapnumber are numbers this
Map_Ref Calculated (LocationID+"-"+Map_Number)
looks like you are trying to subtract one from the other (not sure how a calculated field would interpret it)
try
Map_Ref Calculated (LocationID & "-" & Map_Number)
Not sure what the benefit of this field is - calculated fields cannot be indexed, so does not directly relate to your error, but sometimes access throws out a misleading error message. But clearly ID=12 and Map Ref=2 will generate the same value as ID=1 and map ref=22
I used plus (+) in my post to express how the field is composed but in the actual coding I am using the ampersand (&), nevertheless, my apologies for the ambiguity. To be sure however, I did check and the actual formula to calculate the field is:
[Location_ID] & "-" & Right("00" & [Map_Num],2)
The location code is two or three alphanumeric characters, e.g. LE. The map number would be two numeric characters, for example 01. The map reference would then be LE-01. Map two for that location would be LE-02 and so on. This code must be unique, but since I discovered as you point out, that I cannot index a calculated field and use it as a primary key, I added an autonumber field for this purpose. This number will never be seen by the user, but it does give each map a unique record ID that can serve as the primary key. The map reference field is a file reference that is currently being used by the current manual system and needs to be retained. Otherwise there is indeed little benefit in having that calculated field in terms of coding the application.
Originally Posted by
Ajax
if you have set your relationships this should happen automatically when you create the subform - alternatively ensure the linkchild and linkmaster are populated with LocationID
I had a look at those properties and they were unset. I set them as you suggested and the Location_ID field is now indeed being populated automatically, however the filtering no longer works and I now only get one empty record for each selection. Also, the issue of the original error message still persists.
Unfortunately it seems that a number of things that 'should happen automatically' as per the numerous videos and examples available online, simply do not seem to happen that way. I initially thought that developing this application was going to be a doddle....
I don't know whether this is an issue with my installation, some corruption of the database or just Access being a frustratingly annoying, but I would like to figure it out. For example, I would like to understand exactly why that error comes up when there is no duplication in the records and why it pops up when switching back to design view, or when I am trying close the form, having taken the action only of selecting an item in the combo box to test the filter, and having made no attempt whatsoever to add any records. Trying to close the form results in:
You can't save a record at this time.
Microsoft Access may have encountered an error while trying to save a record.
If you close this object now, the data changes you made will be lost.
Do you want to close the database object anyway?
To the best of my knowledge, I have not changed anything nor am I trying to save anything. I am just trying to close the form. That being the case I usually select Yes to escape from the form and re-open it. It is the only way that I can get back to Design View.
This is the subform recordsource query:
Code:
SELECT Tbl_Maps.Location_ID, Tbl_Maps.Map_Num, Tbl_Maps.Used_By, Tbl_Maps.Last_Used, Tbl_Maps.Reserved_For
FROM Tbl_Maps
ORDER BY Tbl_Maps.Location_ID, Tbl_Maps.Map_Num;
This is the combo row source:
Code:
SELECT [Tbl_Locations].[Location_ID], [Tbl_Locations].[Location_ID] & " - " & [Tbl_Locations].[Location_Name]
FROM Tbl_Locations
ORDER BY [Location_ID];
This is the filter code in the combo After Update event:
Code:
query = "[Location_ID]='" & Me.cmbLocations.Column(0) & "'"
Me.sFrm_MapData.Form.Filter = query
Me.sFrm_MapData.Form.FilterOn = True
Me.sFrm_MapData.Requery
This is also called from the form_load event to ensure that the table is appropriately filtered by the default combo box item when the form is loaded, otherwise it shows all records, which is not what is required. I added a separate checkbox for the 'All' option which turns the filter on and off.