Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568

    Reference to form by variable

    Hi,

    i can not access my form field when i am trying to write code:

    Code:
    debug.print Forms(FormName).Controls(IDField)
    FormName is "QJuntion"
    Field is "PK_Customers"

    Why this is not working ?

    Best,
    Jacek

  2. #2
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Ok problem is connected with active form.

    First i have to refer to main form (active form) and next to subform, i am trying:

    Code:
     strg = Screen.ActiveForm.Name
      Debug.Print Debug.Print Forms(strg)!Controls(FormName).Controls(IDField)
    but this is not working...

    Best,
    Jacek

  3. #3
    Join Date
    Apr 2017
    Posts
    1,673
    Remove abundant "Debug.Print"

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

    But still it is not working...

    Jacek

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    Try (replace FormName, SubformName and ControlName)
    Code:
    Debug.Print Forms("FormName").SubformName.Form.ControlName
    NB! Maybe you need to convert the expression result to string using CStr() too.

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

    still i get the error:

    Click image for larger version. 

Name:	Screenshot_8.jpg 
Views:	30 
Size:	92.2 KB 
ID:	34148

    "Microsoft Access can't find the field '|1' reffered in your expression...

    Best,
    Jacek

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Use
    Code:
    Forms!FormName.ControlName,
    OR if control in subform then

    Code:
    Forms!FormName.SubFormControlName.Form.ControlName
    See this link http://allenbrowne.com/casu-04.html
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    debug.print FORM_frmName.field

    Just add the red to the form name and away you go...

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

    i caught the issue.

    My subform is not Copy Of Copy Of QJunction but "MainForm".

    Code:

    Code:
    Debug.Print Forms(strg)!MainForm.Form.TopoToJunction_ID
    1. How can i use variable for MainForm and for TopoToJunction_ID field?
    StringSubformName ="MainForm"

    Code:
    Debug.Print Forms(strg)!StringSubformName.Form.Controls("TopoToJunction_ID")
    --> this is not working.

    2.Click image for larger version. 

Name:	Screenshot_9.png 
Views:	31 
Size:	54.1 KB 
ID:	34153 How to take the name of form (MainForm) with VBA?
    When i am try to do is: Me.Name --> result: "Copy Of Copy Of QJunction" and this throws an error.

    Best Wishes,
    Jacek

  10. #10
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Are you trying to retrieve the newly added PK within the .AddNew proceedure?

    you can do this with something like

    rs.AddNew
    ...
    ...
    ...
    NewID = rs.Fields("PkfieldName")
    rs.update

  11. #11
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Hi moke123

    thank you. I have PK but i have to pass it to audit sub.

    So i need the name of subform and reference to one field of it (with PK).

    Please look at my above 2 questions.

    Best,
    Jacek

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Dynamically referencing form/subform paths is difficult, as you have discovered.

    Pass the PK by procedure argument or by setting a global variable or TempVars.

    You have subform sitting on an UNBOUND Form1? Your 'Main' form is actually a subform? Why the UNBOUND form complication?

    Why code to create new record?
    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.

  13. #13
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Code:
    Pass the PK by procedure argument or by setting a global variable or TempVars.
    I could do this but I want to audit all forms within access.
    So i have to pass proper name of form to my sub.

    I can create global variable but i have to know how to refer to Name of form (like in picture above).

    Code:
    You have subform sitting on an UNBOUND Form1? Your 'Main' form is actually a subform? Why the UNBOUND form complication?
    this is specially for synchronize 2 subforms like here:

    http://www.fmsinc.com/microsoftacces...edsubforms.asp

    so it has to be like that.

    Code for create new records is connected with auditing forms.

    Something like here:

    https://www.fontstuff.com/access/acctut21.htm

    Best,
    Jacek

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Image does not show two subforms side-by-side.

    If you want to save the form name and the ID, both can be passed as procedure arguments.

    Adapt code. I have never used Screen.ActiveForm.

    I don't see a need to reference the form object directly.

    Is it really important to document the form the edit took place in? Here is another audit trail example http://allenbrowne.com/AppAudit.html
    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.

  15. #15
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    How to take the name of form (MainForm) with VBA?
    When i am try to do is: Me.Name --> result: "Copy Of Copy Of QJunction" and this throws an error.
    By any chance, is the name of your subform control and your subform control sourceobject different?
    could the subform control be named Copy Of Copy Of QJunction and its source object be MainForm?

Page 1 of 2 12 LastLast
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