Command Button-Subform and focus

B

bob norris

I have a form that contains record data, navigation buttons and a subform.
For data entry purposes, I would like btnNext to advance to the next record
(which it does) and set focus to a text box on the subform. I have added the
following code to the On Click event of btnNext,

Forms!frmRatioAppraisal.LandUseCode.SetFocus

but when I run the form and click btnNext, I get an error message,

Microsoft Access can't find the form "frmRatioAppraisal" referred to in a
macro expression or Visual Basic Code

can anyone help?

thanks!
 
V

Van T. Dinh

It sounds to me that "frmRatioAppraisal" is the name of the Form being used
as the "Subform". In this case, the "Subform" is not a member of the Forms
Collection so you cannot refer to it the way you posted.

The full reference should be something like:

Forms!YourMainForm!SubformControl.Form.LandUseCode.SetFocus

If your code is executed in the context of the Main Form them you can use Me
like:

Me.SubformControl.Form.LandUseCode.SetFocus

If your code is executed in the context of the Subform, the you can use Me
(of the Subform) like:

Me.LandUseCode.SetFocus

Note: you need to check the name of the SubfromControl in the DesignView of
the Main Form. The name of the SubformControl may be different from the
name of the Form being used as the "Subform" (more technically accurate,
being used as the SourceObject of the SubformControl).
 
J

John Vinson

I have a form that contains record data, navigation buttons and a subform.
For data entry purposes, I would like btnNext to advance to the next record
(which it does) and set focus to a text box on the subform. I have added the
following code to the On Click event of btnNext,

Forms!frmRatioAppraisal.LandUseCode.SetFocus

but when I run the form and click btnNext, I get an error message,

Microsoft Access can't find the form "frmRatioAppraisal" referred to in a
macro expression or Visual Basic Code

can anyone help?

thanks!
Click to expand...

That's because a Subform is not open in its own right, and is not a
member of the Forms collection.

Instead you should use two Setfocus commands in sequence: one to set
focus to the Subform Control on the main form (which might or might
not have the same name as the form within it), then to the control:

Me!frmRatioAppraisal.SetFocus
Me!frmRatioAppraisal.Form!LandUseCode.SetFocus

John W. Vinson[MVP]
 
B

bob norris

thanks John! I came across a document (a talbe that Doug Steele??? put
together)that helped me understand the syntax better after my posting. But
I'm still working on it. With your input, hopefully I'll have it going
shortly.

bob
 
B

bob norris

thanks Van!

I came across a table that describes the syntax variations as you have
indicated. I'm still working with it, but with your input, I hope to get it
all going soon.

bob
 
B

bob norris

John
setting focus in two steps works simply --- as you said it would

my next problem was, after editing the data on the subform, and setting
focus back to the parent. after a couple of tries, the two-step set focus
worked, but the record pointer would move off the subform record... and to
the casual user, they might think the data disappeared.

again, a couple of tries and I found that if I set the focus on a different
subform control and then set focus to the parent form and then set focus to
btnNext on the parent I got the results I was after.

thanks again - your recommendation got me over this hurdle!

now on to the next one....

by the way, how are you at statistics and the confidence interval????

ren
 
J

John Vinson

by the way, how are you at statistics and the confidence interval????
Click to expand...

A bit rusty... but my biologist wife is pretty expert. What's the
question?

John W. Vinson[MVP]
 
B

bob norris

In the statistical report that I am reproducing, the statistics include the
confidence interval for the median ratio at 95% and 99%. I have one query
that arrayst the observations and a query that calculates the positions I
need, that is, on a sample of 35 observations, I need the calculated ratios
for the 10th, 12th, 24th and 26th positions.

my question(s) is: how do I move the record pointer to the desired
positions, capture the ratio and then print them in the report?

I worked a bit this week-end with a do loop, and moveNext but couldn't
quite get everything to fall into place. After playing with it a while, I
suspect my problem isn't so much moving the record pointer as it is declaring
the variables and getting the scope correct so that the variable is available
when the report prints.

it may be too much to ask, but do you have any suggestions?

thanks
 
J

John Vinson

In the statistical report that I am reproducing, the statistics include the
confidence interval for the median ratio at 95% and 99%. I have one query
that arrayst the observations and a query that calculates the positions I
need, that is, on a sample of 35 observations, I need the calculated ratios
for the 10th, 12th, 24th and 26th positions.

my question(s) is: how do I move the record pointer to the desired
positions, capture the ratio and then print them in the report?

I worked a bit this week-end with a do loop, and moveNext but couldn't
quite get everything to fall into place. After playing with it a while, I
suspect my problem isn't so much moving the record pointer as it is declaring
the variables and getting the scope correct so that the variable is available
when the report prints.

it may be too much to ask, but do you have any suggestions?
Click to expand...

I'm not at all sure I follow, but please post your code.

John W. Vinson[MVP]
 
B

bob norris

Good morning John!
here is the code I've written. It is intended to move through a set of
records (35) and capture the ratio at certain positions, i.e. 10th,12th,24th
and 26th when the data is in ascending order by ratio.
-------------------------start of code
Public Function RatioList()
Dim TempDB As DAO.Database
Dim rsRatio As DAO.Recordset
Dim RCount As Integer, i As Integer, x As Double, y As Double
Dim r_value, CI_95Low, CI_95High, CI_99Low, CI_99High As Double

Set TempDB = CurrentDb()
Set rsRatio = TempDB.OpenRecordset("qry_RatioStudy_ArrayTest",
dbOpenDynaset)
i = 1
Do
r_value = (rsRatio!ASSD_VALUE / rsRatio!AppraisedValue)
Select Case i

Case 10
'Debug.Print "The 10th position is the 99 CI Low Ratio: " & r_value
CI_99Low = r_value
Case 12
'Debug.Print "The 12th position is the 95 CI Low Ratio: " & r_value
CI_95Low = r_value
Case 24
'Debug.Print "The 24th position is the 95 CI High Ratio: " & r_value
CI_95High = r_value
Case 26
'Debug.Print "The 26th position is the 99 CI High Ratio: " & r_value
CI_99High = r_value
Case Else

End Select


rsRatio.MoveNext
i = i + 1
Loop Until i = 36
End Function
--------------------------end of code
when I run it and check the results in the immediate window (and the
debug.print statements are active) then I can view the results I'm expecting.


I placed the code in the report module. In my report I have four text boxes
that list the variables (CI_95Low, CI_95High, CI_99Low and CI_99High ) as the
control source. But when I run the report, it prompts me for the values of
the variables and does not find them as a result of the function.

I'm guessing at this point, but my reference to the variable must be
incorrect. That is in the control source for the textbox it is:

=[CI_95Low]

etc.

once again, I really appreciate any time and guidance that you can provide!

bob
 
Top