I need a way to open the currently selected record on a datasheet in a navigation form without referencing the primary key.
The reason for this is I am creating a generic "View" button that will be able to view any record on any datasheet available on the nav board.
I was able to select the right form because my datasheets and forms have similar names just with either "dat_" or "frm_" appended on it.
Unfortunately I did set up a similar naming scheme for my field names. I want to try to avoid having a large "if" clause for each datasheet to pick out the right primary key. Is there another way I can accomplish this? Perhaps using Me.CurrentRecord?
This is what I've got so far
Code:
Dim myForm As String
'Gets navigation subform name and replaces "dat_" with "frm_"
myForm = "frm_" + Right(Me!NavigationSubform.SourceObject, Len(NavigationSubform.SourceObject) - 4)
'The following I am referencing the primary key - this only works for the Customer form, I need a generic statement that will work for ANY form
DoCmd.OpenForm myForm, , , "[Cust_ID]=" & Me.Cust_ID
If this is not possible what should be my next step? An if statement to get the correct name of the primary key for each datasheet? A loop that finds the primary key? Just rename all my fields to have the same naming scheme so I can just append the correct text?
I'd just to keep with this "generic" code because eventually I will be password protecting the "edit" and "delete" options and I'd like to not have to code that for each individual datasheet.