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
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
I guess post #8 was unclear.
The OPs post #1 would be
There's no need for a variable, just name the form and field directly as above.Code:Debug.print FORM_QJuntion.PK_Customers
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:
For the example, this code is in the main form (frmName):
I can post the DB if anyone wants to see it.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
Last edited by davegri; 05-23-2018 at 12:08 AM. Reason: forgot code tags
Thank you Guys,
Exactly, how to do it ?If you want to save the form name and the ID, both can be passed as procedure arguments.
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:
because of i can use it (i hope) as variable in code:
this code is working.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
Question is how to refer to subform name as variable in VBA?
Expected result:
Or maybe i can refer to form object name and put the subform as variable?Code:TempFormVar = "Table1Name" Debug.Print Forms(FormName)!TempFormVar.Form.ID
thank you for help!
Best,
Jacek
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]
your using a datasheet in the subform so i'm not sure how you would capture a specific ID
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.
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
Bingo ! Working!Forms("formname").Controls("ctrDetails").[Form]!ID
moke123 - sorry i didnt know that this object is called "form container".
and thank you for link!
Best,
Jacek
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.
davegri thank you,
your solution is very good also,
Best,
Jacek
Sorry Guys,
i have to reopen topic.
How can i write:
toCode:Forms("formname").Controls("ctrDetails").[Form]!ID
I want to instead of specific field name refer to variable there...Code:Forms(FstFormName).Controls(SndFormName).Controls(IDField)
My attempts to find solution and my own in VBA failed...
Best,
Jacek
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.
Maybe this will help or get you started:
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:
Enter something into the text box and click 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
As long as a form is open, the control can be read using the above code.