Results 1 to 3 of 3

Open form on currently selected record in datasheet without referencing primary key

  1. #1
    thevaik is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Jul 2013
    Posts
    8

    Open form on currently selected record in datasheet without referencing primary key

    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.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,225
    See if this works.

    Name textboxes bound to primary key like tbxPK.

    Get the field tbxPK is bound to by referencing the ControlSource property, something like:

    DoCmd.OpenForm myForm, , , Me!NavigationSubform.SourceObject.tbxPK.ControlSou rce & "=" & Me!NavigationSubform.SourceObject.tbxPK
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    thevaik is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Jul 2013
    Posts
    8
    Quote Originally Posted by June7 View Post
    See if this works.

    Name textboxes bound to primary key like tbxPK.

    Get the field tbxPK is bound to by referencing the ControlSource property, something like:

    DoCmd.OpenForm myForm, , , Me!NavigationSubform.SourceObject.tbxPK.ControlSou rce & "=" & Me!NavigationSubform.SourceObject.tbxPK
    A little tweaking and I got it to work perfectly - thank you very much. I didn't know I could grab a ControlSource using the Name, this will come in handy .

    Here's the final code if anyone cares

    Dim myForm As String
    Dim myPK As String
    Dim myID As String
    'Gets navigation subform name and replaces "dat_" with "frm_"
    myForm = "frm_" + Right(Me!NavigationSubform.SourceObject, Len(NavigationSubform.SourceObject) - 4)
    'Gets navigation subform PK field's control source
    myPK = Me!NavigationSubform.Form!tbxPK.ControlSource
    'Gets navigation subform PK ID
    myID = Me!NavigationSubform.Form!tbxPK
    DoCmd.OpenForm myForm, , , myPK & "=" & myID

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 06-19-2013, 08:01 AM
  2. Replies: 3
    Last Post: 09-20-2012, 11:09 AM
  3. Replies: 7
    Last Post: 05-01-2012, 11:43 AM
  4. Replies: 2
    Last Post: 10-21-2011, 07:11 AM
  5. Replies: 1
    Last Post: 03-31-2011, 12:18 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums