relations in sub-forms

[jodym43]

Got a question that I'm hoping someone can help me with....

I created a DB that tracks projects and maps that are associated with each project. The maps are shown in a subform off of the projects form. I have a separate table (called projects_maps) that simply associates a specific map with a specific project (many to many relationship).

I have it set up in the map sub-form that when a user enters in a new map, it searches for any past maps that have the same exact information, and if so updates the projects_maps table with the older entered map's ID.

My problem arrises when I lose focus off of the recently entered map info. The database creates a new map (with the same specific info) and creates additional entries into both the maps table and the projects_maps table specifing the new map with the current project. The next time I bring up the specific project, it has both maps listed (the old and the new).

Does anyone have any ideas on how I might remedy the problem. I can try inserting some code to delete the most recent record after it has been added, but I'm thinking that there has to be some way to prevent the duplication from even occuring in the first place.

Any help is much appreciated. Thanks in advance.
Jody

 

[downwitchyobadself]

Sounds like you're doing twice as much work as you need to. (Though I'm not sure I've fully understood your setup...) Why not do a recordset search on the map's properties in the BeforeInsert event of the subform? If an identical map is found, use the existing map ID, create the new-project=old-map instance in the projects_maps table, and undo the new record using BeforeInsert's Cancel? If the characteristics exist already, seems silly to me to (1) create a new record, (2) go find the old record and change all its related data, and (3) get rid of the old record. Plus you're losing history.

 

[jodym43]

Exactly......I was trying to get to the point you described, I just didn't know which command I was looking to use.

The BeforeInsert wouldn't work for me because I had multiple fields that needed to be filled in for each map, but you pointed me down the right path. I ended up using a BeforeUpdate event and if a prev map was found, I created a new instance in the proj_maps table with the new project and old maps ID. I then did an undo and refresh to populate the maps subform with the older maps information.

It's working beautifully now....thanks much for taking the time.