I have a situation where I have a main form with a subform. The thing is, each record in the main form only has one record in the subform. The reason why is because this is a superclass-subclass type of situation. Here is what I mean.
There are two categories of rocks. Some rocks are hand samples and there can be many of them located in many different locations. On the other hand, some rocks are part of a collection, and they are each given their own sample number and can therefore be located in one and only one location. All of these rocks have certain attributes in common. But, the collection type rocks also have additional attributes that need to be collected, such as the sample number and the collection it belongs to.
Here are the example tables:
RockCategory (hand sample or collection)
RockID (Foreign key back to tblRock)
And, of course, I have a lookup table with all the locations.
I've set up the data entry on the forms for this set up by putting the main form recordsource set to "tblRock" with a subform with recordsource "tblRockInCollection", linked via the RockID. If the user selects a toggle button on the form that changes its RockCategory to "collection rock", then the collection rock subform shows and they can enter in the information. This also hides another subform that uses "tjxLocateHandSample" as its recordsource.
The dilemma I'm having is that, I have reason to sometimes open up this form, then move to a specific sample number in the rock collection subform. But, I'm having trouble figuring out how to tell access to move to a specific record in the subform.
Can anyone help me out with that?