Textbox to display field selected in subform - VBA (1 Viewer)

p4nny

New member
Local time
Today, 10:20
Joined
Mar 16, 2020
Messages
15
Hi

I have a form with a textbox (txtCSD) that I'd like to display a field within a subform - "[start date]" and this should change when a new record is selected in the subform

I can do this via a Control Source, however this locks the textbox and users cannot overwrite the value.

Code:
Private Sub Form_Click()
Forms!increase!txtCSD = [dbo_resources9].[Form]![Start Date]
End Sub


This code produces an error "MS Access can't find the field '1' referred to in your expression"
Much appreciated
 

vba_php

Forum Troll
Local time
Today, 05:20
Joined
Oct 6, 2019
Messages
2,884
Code:
Private Sub Form_Click()
Forms!increase!txtCSD = [dbo_resources9].[Form]![Start Date]
End Sub


This code produces an error "MS Access can't find the field '1' referred to in your expression"
the error occurs because of an enumeration issue when access tries to find the control you've referenced. you're referring to the subform and it's control incorrectly. it's been a long time for me, and I know I have a FAQ thread in the FAQ section of this forum for stuff like this. but try this:
Code:
Forms!increase!txtCSD = forms![dbo_resources9].[Form]![Start Date]
or this:
Code:
Forms!increase!txtCSD = forms![dbo_resources9].[Form].controls("Start Date")
there are literally many ways to do these types of things, and microsoft should have fixed this LONG ago. but alas, they let it go on.
 

p4nny

New member
Local time
Today, 10:20
Joined
Mar 16, 2020
Messages
15
Hi thanks for your reply.

Unfortunately either solution did not work. Access cannot find the subform even though everything is correct with spelling etc. Really strange.
 

vba_php

Forum Troll
Local time
Today, 05:20
Joined
Oct 6, 2019
Messages
2,884
like I said, there are a million ways to do this. i know some of the other guys here know the answer. if no one else offers anything, upload your file with the form and subform in it, and I will fix it for you. =)
 

vba_php

Forum Troll
Local time
Today, 05:20
Joined
Oct 6, 2019
Messages
2,884
p4nny, I have many records on this issue from years ago. here is one piece of information I have:

REFERENCE SUBFORM FROM PARENT FORM
Me.subFormName.Form.Action

REFERENCE SUBFORM CONTROL FROM PARENT FORM
Me.subFormName.Form!ControlName.Action

REFERENCE SUBFORM CONTROL FROM A POPUP FORM
Forms!MainFormName!SubformControlName.Controls("ControlName").Action

REFERENCE SUBFORM'S SUBFORM CONTROL
Me.subForm2Name.Form.ControlName.Action

REFERENCE SUBFORM of a SUBFORM FROM PARENT FORM (2 NESTED SUBFORMS DEEP)
Me.subForm1Name.Form!subForm2Name.Action

REFERENCE SUBFORM of a SUBFORM CONTROL FROM PARENT FORM (A CONTROL 2 NESTED SUBFORMS DEEP)
Me.subForm1Name.Form!subForm2Name.Form!ControlName.Action

REFERENCE PARENT FORM FROM SUBFORM
Me.Parent.Action

REFERENCE PARENT CONTROL FORM FROM SUBFORM
Me.Parent.ControlName.Action

REFERENCE MAIN FORM FROM SUBFORM 2 LEVELS DEEP
Forms(Me.Parent.Parent.Name).Action

REFERENCE MAIN FORM CONTROL FROM SUBFORM 2 LEVELS DEEP
Forms(Me.Parent.Parent.Name).ControlName.Action

Note: The code in italics indicates object and control names that you have to include.
 

Attachments

  • Form and Subform Reference Syntax.txt
    1.3 KB · Views: 116

isladogs

MVP / VIP
Local time
Today, 10:20
Joined
Jan 14, 2017
Messages
18,186
Several issues here:
1. Why are you using the form_click event?
2. You need to reference the name of the subform 'container' i.e. the name of the subform control on the main form
3. If the textbox is on the main form then the code can be simplified to:
SQL:
Me.txtCSD = Me.SubformControlName.Form.[Start_Date]
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:20
Joined
May 21, 2018
Messages
8,463
Also the name of the subForm control may not be the same as the name of the subform inside the control. By default it usually is, but there are many times it is not. Make sure to click on the outside to get the name of the subform control.
 

p4nny

New member
Local time
Today, 10:20
Joined
Mar 16, 2020
Messages
15
Hi

thanks everyone for your responses.

I'm trying to update the textbox (txtCSD) in mainform (Increase), using [start date] from the subform (dbo_resources9) and each time a record is selected in the subform, this to change.

Hope that makes sense.
Thanks again
 

Attachments

  • Screenshot (2).gif
    Screenshot (2).gif
    134.1 KB · Views: 95

vba_php

Forum Troll
Local time
Today, 05:20
Joined
Oct 6, 2019
Messages
2,884
Hi

thanks everyone for your responses.

I'm trying to update the textbox (txtCSD) in mainform (Increase), using [start date] from the subform (dbo_resources9) and each time a record is selected in the subform, this to change.

Hope that makes sense.
Thanks again
i'm pretty sure the solution is in my last post regarding the syntaxes to use. what that too complex to follow? if it is,, i apologize. but I still agree with isladogs in that you could improve this whole structure you've got to make it easier on yourself.
 

p4nny

New member
Local time
Today, 10:20
Joined
Mar 16, 2020
Messages
15
Thanks again for coming back to me. I need to be able to edit the textbox too. Your solution works fine if I enter in the Expression Builder (control source) but this doesn't allow the textbox to be overwritten.
 

vba_php

Forum Troll
Local time
Today, 05:20
Joined
Oct 6, 2019
Messages
2,884
Thanks again for coming back to me. I need to be able to edit the textbox too. Your solution works fine if I enter in the Expression Builder (control source) but this doesn't allow the textbox to be overwritten.
well I'm sure a lot of the other guys here can answer this question for you. There's really no way for me to tell what the problem is or how to fix it without seeing your actual file so you might want to depend on the other professionals here who are actually trained in this area to help you
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:20
Joined
May 21, 2018
Messages
8,463
In the subform oncurrent event
Me.parent.txtCSD = me.[Start Date]
 

Users who are viewing this thread

Top Bottom