Page 2 of 2 FirstFirst 12
Results 16 to 27 of 27
  1. #16
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643

    I googled "Audit trail with subforms" and it turned up numerous hits with your exact code and issue.
    heres one link...http://www.utteraccess.com/forum/Aud...-t2022252.html

  2. #17
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    I guess post #8 was unclear.
    The OPs post #1 would be
    Code:
    Debug.print FORM_QJuntion.PK_Customers
    There's no need for a variable, just name the form and field directly as above.

    The form that the value is being extracted from must have property CODE MODULE YES
    Here's how to get a field value from ANY open form textbox from outside the form with VBA using the technique above:
    Click image for larger version. 

Name:	Capture.JPG 
Views:	30 
Size:	68.5 KB 
ID:	34163
    For the example, this code is in the main form (frmName):
    Code:
    Option Compare Database
    Option Explicit
    
    
    Private Sub Form_Current()
        'current event of the main form
        'using the function...
        txtFromSubform = fcnGetField([Form_tbladdress subform].city)
        ' but you don't really need the function...
        'txtFromSubform = [Form_tbladdress subform].city
    End Sub
    
    
    'below function must be in a module, not behind the main form
    Public Function fcnGetField(frmName) As String
        fcnGetField = frmName
    End Function
    I can post the DB if anyone wants to see it.
    Last edited by davegri; 05-23-2018 at 12:08 AM. Reason: forgot code tags

  3. #18
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Thank you Guys,

    If you want to save the form name and the ID, both can be passed as procedure arguments.
    Exactly, how to do it ?

    Your Method, June7 from alienbrownie is ok but this is only for tables. When i have table Junction it can be confused having all FK numbers, this would be not useful for my users.
    But when you have form you can easily see what you inputed and reverse changes.

    davegri
    thank you but this is not what i want.

    I created sample database to show you.
    On Form1 i have 2 subforms.

    Table1 Subform - i changed the name to Table1Name.
    I want to refer to this name:

    Click image for larger version. 

Name:	Screenshot_3.jpg 
Views:	28 
Size:	132.1 KB 
ID:	34164

    because of i can use it (i hope) as variable in code:
    Code:
    Private Sub Form_Current()
    
    Dim FormName As String
     
    Debug.Print Me.Name
    
    
    FormName = Screen.ActiveForm.Name
    
    
    Dim vartemp As String
    
    
        Debug.Print Forms(FormName)!Table1Name.Form.ID
        txtFromSubform = fcnGetField([Form_Table1 subform])
    
    
    End Sub
    this code is working.

    Question is how to refer to subform name as variable in VBA?
    Expected result:

    Code:
    TempFormVar = "Table1Name"
    
    Debug.Print Forms(FormName)!TempFormVar.Form.ID
    Or maybe i can refer to form object name and put the subform as variable?

    thank you for help!
    Best,
    Jacek
    Attached Files Attached Files

  4. #19
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    see my previous post #15

    Your subform container is named Table1Name. Your subforms name is Table1 subform.

    you can get the subforms name by referncing the subform containers sourceobject property

    Code:
    =[Table1Name].[SourceObject]
    Click image for larger version. 

Name:	1.PNG 
Views:	29 
Size:	14.3 KB 
ID:	34167
    Click image for larger version. 

Name:	2.PNG 
Views:	28 
Size:	19.8 KB 
ID:	34168

    your using a datasheet in the subform so i'm not sure how you would capture a specific ID

  5. #20
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I always name subform container control different from the object it holds, like ctrDetails.


    VBA can reference the subform container with controls collection:

    [Forms]![formname].Controls("ctrDetails")!ID

    or

    [Forms]![formname].Controls("ctrDetails").Form.tbxID

    So the container name can be provided via a variable.

    And if you want the parent form to be dynamic use Forms collection:

    Forms("formname").Controls("ctrDetails").[Form]!ID

    So another variable can provide the parent form name.
    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.

  6. #21
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Putting aside all the above, did you check the link I posted in Post #16? I didnt study the results I got from googling, but I believe the solution involved running the code from the subforms

  7. #22
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Forms("formname").Controls("ctrDetails").[Form]!ID
    Bingo ! Working!

    moke123 - sorry i didnt know that this object is called "form container".
    and thank you for link!

    Best,
    Jacek

  8. #23
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Database1-davegri-v01.zip
    Here's your DB back to you.
    Added a new form with textboxes that retrieve the data from the original form's subforms with button click.

  9. #24
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    davegri thank you,

    your solution is very good also,

    Best,
    Jacek

  10. #25
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Sorry Guys,

    i have to reopen topic.

    How can i write:

    Code:
    Forms("formname").Controls("ctrDetails").[Form]!ID
    to

    Code:
    Forms(FstFormName).Controls(SndFormName).Controls(IDField)
    I want to instead of specific field name refer to variable there...
    My attempts to find solution and my own in VBA failed...

    Best,
    Jacek

  11. #26
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    To reference a control like textbox or combobox:

    Forms("formname").Controls("ctrDetails").Form.Cont rols("tbxID")

    - actually, if the field and control have the same name, that will find either. In other words if there is no control by the name, the field in the form RecordSource will be read.

    Can also reference field with:

    Forms("formname").Controls("ctrDetails").Form!("ID ")


    Rats! Forum threw in two extra spaces I can't edit out.



    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.

  12. #27
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Maybe this will help or get you started:


    Quote Originally Posted by jaryszek View Post
    <snip>
    I want to instead of specific field name refer to variable there...
    Basic example: (no pun intended)
    There is an unbound form named "Form1"
    There is a button named "Button7"
    There is an unbound text box named "txtView"

    The code for the click event of the button:
    Code:
    Private Sub button7_Click()
        Dim frmName As String
        Dim ctlName As String
        Dim tmp As String
    
        frmName = "Form1"
        ctlName = "txtView"
        
        tmp = Forms("" & frmName & "").Controls("" & ctlName & "")
    
        MsgBox tmp
    End Sub
    Enter something into the text box and click the button.....



    As long as a form is open, the control can be read using the above code.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 10-03-2017, 09:36 AM
  2. DLookup reference column according to variable
    By hockeyman9474 in forum Programming
    Replies: 7
    Last Post: 05-26-2017, 11:55 PM
  3. Variable to reference a spreadsheet from Access
    By lawdy in forum Programming
    Replies: 9
    Last Post: 03-03-2015, 11:31 AM
  4. Variable within form control reference
    By Tyork in forum Programming
    Replies: 2
    Last Post: 10-13-2010, 09:55 AM
  5. Replies: 2
    Last Post: 05-09-2010, 04:10 AM

Posting Permissions

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