Directing Focus to another form field 1

[CharlieT302]

Hello Everyone,

I have a main form with two subforms.

Subform A Contains:
Call_Day field: A field containing a numeric entry between 1 - 30 (days of month)
Command Button: To direct the focus to Subform B

Subform B has 30 fields; each named 1 - 30 in sequence (corresponding to days of a sample month)

When the user clicks the command button on Form A, I would like the system to read the value in the Call_Day field and move to the field in Subform B with the corresponding Name.

Among other examples, I have tried the following:

Dim cal_field As Integer
cal_field = Me.Call_Day.Value


Forms!Frm_Setup_Options!Frm_Value_Test_6mth.SetFocus
Forms!Frm_Setup_Options!Frm_Value_Test_6mth.Form![cal_field].SetFocus

Ideas? Thank you in advance

 

[MajP]

You need the name of the subform control (not the form inside the control). By default they have the same name, but not always. Lets assume the subform control is called SubFormB.

'If you are calling the code from subform A

dim MainForm as access.form
dim sFrm as access.form
dim ctl as access.control

set MainForm = me.Parent
set sFrm = MainForm.SubFormB.Form 'returns the form within the subform control
set ctl = sFrm.controls(Call_Day.Value) 'gets the control within the subform
ctl.setfocus

In some version of access you had to set focus first to the subform before setting it to a control in the subform. Do not know why, think it is a bug. So if that does not do it then

set MainForm = me.Parent
set sFrm = MainForm.SubFormB.Form 'returns the form within the subform control
sFrm.setfocus
set ctl = sFrm.controls(Call_Day.Value) 'gets the control within the subform
ctl.setfocus

 

[MajP]

Sorry
set ctl = sFrm.controls(cstr(Call_Day.Value))

Because you can actually call a control by its name or index. That would have returned the first control on the page.

 

[CharlieT302]

Thank you for the input Mjap,

I see what you are doing. I tried both variations and, unfortunately, neither is not working.

Below is the code I have entered:

Dim MainForm As Access.Form
Dim sFrm As Access.Form
Dim ctl As Access.Control

Set MainForm = Me.Parent
Set sFrm = MainForm.SubformB.Form
sFrm.SetFocus
Set ctl = sFrm.Controls(CStr(Call_Day.Value))
ctl.SetFocus

When I ran it, I receieved the error:
Runtime error 2449
There is an Invalid Method in an Expression

This was caused by the line: sFrm.SetFocus
When I took it out, I received no error, but the Call_Day field in SubformB did not receive the focus.

Two More Things:
Call_Day is a Number field (the field in the
In addition to moving to the field, I would like to alter its BackColor Property

Also, should there not be an exclamation mark (!) after the line:
Set sFrm = MainForm.SubformB.Form

I tried putting one in with no result.

 

[CharlieT302]

MajP

The code appears to be working. I had a control source expression in the target field and that made it difficult to tell of the focus had moved or not. Everything is fine and I have the Backcolor changing as well. Thanks so much.

One Question: How did the Cstr() instruct the code to view the Name and not Index?

 

[MajP]

to refer to a control by name like "txtBoxOne"

using the literal
me.controls("txtBoxOne").value = 123
or as a variable
dim controlName as string
controlName = "txtBoxOne"
me.controls(controlName).value = 123

With most collections you can use the name or the index. The first control you drop on the form has an index of 0, then 1 ......
So in your case

me.Controls(1) actually refers to the second control that was placed on the form during design time
me.Controls("1") refers to a control named 1

so cstr(1) converts the integer of 1 into a string 1.
Suggestion would be to use a better naming. Something like txtBx1, txtBx2....