How can I move the focus to a control on a subform?

[Brandon]

Hi.
I have a form with project information. I have three
subforms all nested within the higher level form for a
total of 4 levels within this form. On the third level
(the second level of subforms), I enter total savings per
project, number of months the savings will take place
over, and a start month. The fourh level (third nested
subform and the final level) is linked by Project Number,
Phase Number, Division, and Location, so those fields are
automatically filled for each new record. What i want to
do in code is take the savings amt divided by the number
of months and enter in that amt for each of the months.

I have a time period table with an integer ID for each
month and they are in numerical order.
So I just need to set the focus to the fourth level (third
level subform) and create the records by filling in the
savings month and savings amt. So I am using a Next loop
along with the goto record command (using new record as
the argument).

Give me some tips on how to get the code to work. I can't
even set the focus to the controls with this code. TIA

Private Sub cmdCalcMonthlySavings_Click()

Dim curSavingsByMonth As Currency
Dim intMonth As Integer
Dim x As Integer


x = 1
intMonth = [ContractStart].Value
curSavingsByMonth = [AwardBidAmt].Value /
[ContractLength].Value

For x = 1 To [ContractLength].Value
'calculate the monthly savings and add records
'to the subform Brandon Cheal 7/14/04

frmprojectentry.sfrmlotentry.sfrmlotbreakout.sfrmLotAwardBr
eakout.savingsmonth.SetFocus
'why won't the previous line set the focus?
DoCmd.GoToRecord
acDataForm, "sfrmlotawardbreakout", acNewRec
[SavingsMonth].Value = intMonth
frmprojectentry.sfrmlotentry.sfrmlotbreakout.sfrmLotAwardBr
eakout.savings.SetFocus
[Savings].Value = curSavingsByMonth
intMonth = intMonth + 1
Next x


End Sub

 

[Byron]

To move the focus to a control on the subform you will
need to use somethin like:
Forms!MainFormName!FirstSubFormName!SecondSubFormName!
ThirdSubFormName.ControlName.SetFocus

You can set the value you want in the control using the
same type of statement, like:
Forms!MainFormName!FirstSubFormName!SecondSubFormName!
ThirdSubFormName.ControlName = Some
value

You can get both at the same time using a With statement"
With Forms!MainFormName!FirstSubFormName! _
SecondSubFormName!ThirdSubFormName.ControlName
.value = somevalue
.setfocus
End With

HTH

Byron

 

[Dirk Goldgar]

Hi.
I have a form with project information. I have three
subforms all nested within the higher level form for a
total of 4 levels within this form. On the third level
(the second level of subforms), I enter total savings per
project, number of months the savings will take place
over, and a start month. The fourh level (third nested
subform and the final level) is linked by Project Number,
Phase Number, Division, and Location, so those fields are
automatically filled for each new record. What i want to
do in code is take the savings amt divided by the number
of months and enter in that amt for each of the months.

I have a time period table with an integer ID for each
month and they are in numerical order.
So I just need to set the focus to the fourth level (third
level subform) and create the records by filling in the
savings month and savings amt. So I am using a Next loop
along with the goto record command (using new record as
the argument).

Give me some tips on how to get the code to work. I can't
even set the focus to the controls with this code. TIA

Private Sub cmdCalcMonthlySavings_Click()

Dim curSavingsByMonth As Currency
Dim intMonth As Integer
Dim x As Integer


x = 1
intMonth = [ContractStart].Value
curSavingsByMonth = [AwardBidAmt].Value /
[ContractLength].Value

For x = 1 To [ContractLength].Value
'calculate the monthly savings and add records
'to the subform Brandon Cheal 7/14/04

frmprojectentry.sfrmlotentry.sfrmlotbreakout.sfrmLotAwardBr
eakout.savingsmonth.SetFocus
'why won't the previous line set the focus?
DoCmd.GoToRecord
acDataForm, "sfrmlotawardbreakout", acNewRec
[SavingsMonth].Value = intMonth
frmprojectentry.sfrmlotentry.sfrmlotbreakout.sfrmLotAwardBr
eakout.savings.SetFocus
[Savings].Value = curSavingsByMonth
intMonth = intMonth + 1
Next x


End Sub

First, please be aware that you don't have to set the focus to a control
in order to set or get its value. However, if you want to use
DoCmd.GoToRecord to do record navigation of the nested subform, you must
at least set the focus to the subform, so I'd better explain how to do
it.

The trouble you're having is due to the fact that every form object has
its own separate "active control". If the focus is currently in some
non-subform control on a parent form, and you use SetFocus on a control
on a subform of that parent, that subform's focus is indeed set to the
control you referenced ... but the *parent* form's focus is still on the
original control. Not until you set the focus to the subform (on that
parent form) will the control that really has the application's focus be
the one on the subform.

Now, in your case, you need to set the focus to the lowest level
subform. The form object that each subform represents is displayed in a
subform control on its parent form. You have 4 levels of forms:

frmProjectEntry
sfrmLotEntry
sfrmLotBreakout
sfrmLotAwardBreakout

Therefore, to get the application's focus onto the lowest level subform,
you must execute multiple SetFocus calls if necessary to make sure that
each of these subform controls is the control that has the focus on its
parent form. If your code were operating in a vacuum, with no knowledge
of where the focus currently is, you'd have to do something like this
(watch out for lines broken by the newsreader):

' Make sure main form has the focus.
DoCmd.SelectObject acForm, "frmProjectEntry"

' Make sure sfrmLotEntry has the focus on frmProjectEntry.
Forms!frmProjectEntry!sfrmLotEntry.SetFocus

' Make sure sfrmLotBreakout has the focus on sfrmLotEntry.
Forms!frmProjectEntry!sfrmLotEntry!sfrmLotBreakout.SetFocus

' Make sure sfrmLotAwardBreakout has the focus on sfrmLotBreakout.

Forms!frmProjectEntry!sfrmLotEntry!sfrmLotBreakout!sfrmLotAwardBreakout.
SetFocus

At this point, you know that sfrmLotAwardBreakout is the active data
object, and whatever control on it has the form's focus is also the
control that has the application's focus. If you really needed to set
the focus to some specific control on this bottommost subform, you could
now say:

' Make sure SavingsMonth has the focus on sfrmLotAwardBreakout.

Forms!frmProjectEntry!sfrmLotEntry!sfrmLotBreakout!sfrmLotAwardBreakout!
SavingsMonth.SetFocus

However, as I said earlier, you don't have to set the focus to the
control in order to set its value, so I don't see that you need that
last statement at all for your purposes.

In reality, you almost certainly don't need all of the SetFocus calls I
wrote down above, nor quite such long reference chains. That's because
you know something about where the focus is when you execute your
procedure. This is the Click event procedure of a command button named
cmdCalcMonthlySavings, and unless I've misinterpreted your description,
that command button is on the third-level form already; that is, the
command button is on sfrmLotBreakout. Correct? Then, if this button
has just been clicked, you know that sfrmLotBreakout currently has the
application focus. Also, you can refer to this form object (on which
the code is running) by the keyword "Me". That will shorten up the
references a lot! To make sfrmLotAwardBreakout the current "active data
object", so that GoToRecord (correctly specified, which it isnt yet)
will operate on it, all you have to say is this:

Me!sfrmLotAwardBreakout.SetFocus

Now, if you still want to set the focus to a particular control on
sfrmLotAwardBreakout, you would have to add the *additional* line

Me!sfrmLotAwardBreakout!SavingsMonth.SetFocus

However, I'll say once again that I don't see any reason for doing this.

I mentioned above that your call to GoToRecord wasn't correctly
specified. You wrote

DoCmd.GoToRecord acDataForm, "sfrmlotawardbreakout", acNewRec

That won't work, because the acDataForm argument value is telling Access
that "sfrmlotawardbreakout" is a top-level form -- a main form. But it
isn't. It's a subform, and you can't get at it except by chasing all
the way down the subform control references from top to bottom. But
the syntax for GoToRecord doesn't allow that kind of reference.
Luckily, GoToRecord allows you to specify (or default to)
acActiveDataObject for its object type argument. That option means that
the GoToRecord action will be applied to whatever is the active data
object -- such as the subform that currently has the focus. So you
should change the statement I quoted above to

DoCmd.GoToRecord acActiveDataObject, , acNewRec

That will make it work on sfrmLotAwardBreakout, once you've set the
focus to it.

 

[Brandon]

Thanks for the info, I will try tomorrow and let you know
how it works!
Brandon

-----Original Message-----
To move the focus to a control on the subform you will
need to use somethin like:
Forms!MainFormName!FirstSubFormName!SecondSubFormName!
ThirdSubFormName.ControlName.SetFocus

You can set the value you want in the control using the
same type of statement, like:
Forms!MainFormName!FirstSubFormName!SecondSubFormName!
ThirdSubFormName.ControlName = Some
value

You can get both at the same time using a With statement"
With Forms!MainFormName!FirstSubFormName! _
SecondSubFormName!ThirdSubFormName.ControlName
.value = somevalue
.setfocus
End With

HTH

Byron

-----Original Message-----
Hi.
I have a form with project information. I have three
subforms all nested within the higher level form for a
total of 4 levels within this form. On the third level
(the second level of subforms), I enter total savings per
project, number of months the savings will take place
over, and a start month. The fourh level (third nested
subform and the final level) is linked by Project Number,
Phase Number, Division, and Location, so those fields are
automatically filled for each new record. What i want to
do in code is take the savings amt divided by the number
of months and enter in that amt for each of the months.

I have a time period table with an integer ID for each
month and they are in numerical order.
So I just need to set the focus to the fourth level (third
level subform) and create the records by filling in the
savings month and savings amt. So I am using a Next loop
along with the goto record command (using new record as
the argument).

Give me some tips on how to get the code to work. I can't
even set the focus to the controls with this code. TIA

Private Sub cmdCalcMonthlySavings_Click()

Dim curSavingsByMonth As Currency
Dim intMonth As Integer
Dim x As Integer


x = 1
intMonth = [ContractStart].Value
curSavingsByMonth = [AwardBidAmt].Value /
[ContractLength].Value

For x = 1 To [ContractLength].Value
'calculate the monthly savings and add records
'to the subform Brandon Cheal 7/14/04

frmprojectentry.sfrmlotentry.sfrmlotbreakout.sfrmLotAwar

d

Br
eakout.savingsmonth.SetFocus
'why won't the previous line set the focus?
DoCmd.GoToRecord
acDataForm, "sfrmlotawardbreakout", acNewRec
[SavingsMonth].Value = intMonth
frmprojectentry.sfrmlotentry.sfrmlotbreakout.sfrmLotAwar

d

Br
eakout.savings.SetFocus
[Savings].Value = curSavingsByMonth
intMonth = intMonth + 1
Next x


End Sub
.

.

 

[Brandon]

-----Original Message-----

Hi.
I have a form with project information. I have three
subforms all nested within the higher level form for a
total of 4 levels within this form. On the third level
(the second level of subforms), I enter total savings per
project, number of months the savings will take place
over, and a start month. The fourh level (third nested
subform and the final level) is linked by Project Number,
Phase Number, Division, and Location, so those fields are
automatically filled for each new record. What i want to
do in code is take the savings amt divided by the number
of months and enter in that amt for each of the months.

I have a time period table with an integer ID for each
month and they are in numerical order.
So I just need to set the focus to the fourth level (third
level subform) and create the records by filling in the
savings month and savings amt. So I am using a Next loop
along with the goto record command (using new record as
the argument).

Give me some tips on how to get the code to work. I can't
even set the focus to the controls with this code. TIA

Private Sub cmdCalcMonthlySavings_Click()

Dim curSavingsByMonth As Currency
Dim intMonth As Integer
Dim x As Integer


x = 1
intMonth = [ContractStart].Value
curSavingsByMonth = [AwardBidAmt].Value /
[ContractLength].Value

For x = 1 To [ContractLength].Value
'calculate the monthly savings and add records
'to the subform Brandon Cheal 7/14/04

frmprojectentry.sfrmlotentry.sfrmlotbreakout.sfrmLotAwardB
r

eakout.savingsmonth.SetFocus
'why won't the previous line set the focus?
DoCmd.GoToRecord
acDataForm, "sfrmlotawardbreakout", acNewRec
[SavingsMonth].Value = intMonth

frmprojectentry.sfrmlotentry.sfrmlotbreakout.sfrmLotAwardB
r

eakout.savings.SetFocus
[Savings].Value = curSavingsByMonth
intMonth = intMonth + 1
Next x


End Sub

First, please be aware that you don't have to set the focus to a control
in order to set or get its value. However, if you want to use
DoCmd.GoToRecord to do record navigation of the nested subform, you must
at least set the focus to the subform, so I'd better explain how to do
it.

The trouble you're having is due to the fact that every form object has
its own separate "active control". If the focus is currently in some
non-subform control on a parent form, and you use SetFocus on a control
on a subform of that parent, that subform's focus is indeed set to the
control you referenced ... but the *parent* form's focus is still on the
original control. Not until you set the focus to the subform (on that
parent form) will the control that really has the application's focus be
the one on the subform.

Now, in your case, you need to set the focus to the lowest level
subform. The form object that each subform represents is displayed in a
subform control on its parent form. You have 4 levels of forms:

frmProjectEntry
sfrmLotEntry
sfrmLotBreakout
sfrmLotAwardBreakout

Therefore, to get the application's focus onto the lowest level subform,
you must execute multiple SetFocus calls if necessary to make sure that
each of these subform controls is the control that has the focus on its
parent form. If your code were operating in a vacuum, with no knowledge
of where the focus currently is, you'd have to do something like this
(watch out for lines broken by the newsreader):

' Make sure main form has the focus.
DoCmd.SelectObject acForm, "frmProjectEntry"

' Make sure sfrmLotEntry has the focus on frmProjectEntry.
Forms!frmProjectEntry!sfrmLotEntry.SetFocus

' Make sure sfrmLotBreakout has the focus on sfrmLotEntry.

Forms!frmProjectEntry!sfrmLotEntry!

sfrmLotBreakout.SetFocus

' Make sure sfrmLotAwardBreakout has the focus on sfrmLotBreakout.

Forms!frmProjectEntry!sfrmLotEntry!sfrmLotBreakout!

sfrmLotAwardBreakout.
SetFocus

At this point, you know that sfrmLotAwardBreakout is the active data
object, and whatever control on it has the form's focus is also the
control that has the application's focus. If you really needed to set
the focus to some specific control on this bottommost subform, you could
now say:

' Make sure SavingsMonth has the focus on sfrmLotAwardBreakout.

Forms!frmProjectEntry!sfrmLotEntry!sfrmLotBreakout!

sfrmLotAwardBreakout!
SavingsMonth.SetFocus

However, as I said earlier, you don't have to set the focus to the
control in order to set its value, so I don't see that you need that
last statement at all for your purposes.

In reality, you almost certainly don't need all of the SetFocus calls I
wrote down above, nor quite such long reference chains. That's because
you know something about where the focus is when you execute your
procedure. This is the Click event procedure of a command button named
cmdCalcMonthlySavings, and unless I've misinterpreted your description,
that command button is on the third-level form already; that is, the
command button is on sfrmLotBreakout. Correct? Then, if this button
has just been clicked, you know that sfrmLotBreakout currently has the
application focus. Also, you can refer to this form object (on which
the code is running) by the keyword "Me". That will shorten up the
references a lot! To make sfrmLotAwardBreakout the current "active data
object", so that GoToRecord (correctly specified, which it isnt yet)
will operate on it, all you have to say is this:

Me!sfrmLotAwardBreakout.SetFocus

Now, if you still want to set the focus to a particular control on
sfrmLotAwardBreakout, you would have to add the *additional* line

Me!sfrmLotAwardBreakout!SavingsMonth.SetFocus

However, I'll say once again that I don't see any reason for doing this.

I mentioned above that your call to GoToRecord wasn't correctly
specified. You wrote

DoCmd.GoToRecord

acDataForm, "sfrmlotawardbreakout", acNewRec

That won't work, because the acDataForm argument value is telling Access
that "sfrmlotawardbreakout" is a top-level form -- a main form. But it
isn't. It's a subform, and you can't get at it except by chasing all
the way down the subform control references from top to bottom. But
the syntax for GoToRecord doesn't allow that kind of reference.
Luckily, GoToRecord allows you to specify (or default to)
acActiveDataObject for its object type argument. That option means that
the GoToRecord action will be applied to whatever is the active data
object -- such as the subform that currently has the focus. So you
should change the statement I quoted above to

DoCmd.GoToRecord acActiveDataObject, , acNewRec

That will make it work on sfrmLotAwardBreakout, once you've set the
focus to it.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

I can't tell you how much I appreciate the explanation
you gave. i will make the changes tomorrow and let you
know how it goes. THanks again so much!

Brandon

 

[Brandon]

-----Original Message-----

-----Original Message-----

Hi.
I have a form with project information. I have three
subforms all nested within the higher level form for a
total of 4 levels within this form. On the third level
(the second level of subforms), I enter total savings per
project, number of months the savings will take place
over, and a start month. The fourh level (third nested
subform and the final level) is linked by Project Number,
Phase Number, Division, and Location, so those fields are
automatically filled for each new record. What i want to
do in code is take the savings amt divided by the number
of months and enter in that amt for each of the months.

I have a time period table with an integer ID for each
month and they are in numerical order.
So I just need to set the focus to the fourth level (third
level subform) and create the records by filling in the
savings month and savings amt. So I am using a Next loop
along with the goto record command (using new record as
the argument).

Give me some tips on how to get the code to work. I can't
even set the focus to the controls with this code. TIA

Private Sub cmdCalcMonthlySavings_Click()

Dim curSavingsByMonth As Currency
Dim intMonth As Integer
Dim x As Integer


x = 1
intMonth = [ContractStart].Value
curSavingsByMonth = [AwardBidAmt].Value /
[ContractLength].Value

For x = 1 To [ContractLength].Value
'calculate the monthly savings and add records
'to the subform Brandon Cheal 7/14/04

frmprojectentry.sfrmlotentry.sfrmlotbreakout.sfrmLotAwardB
r

eakout.savingsmonth.SetFocus
'why won't the previous line set the focus?
DoCmd.GoToRecord
acDataForm, "sfrmlotawardbreakout", acNewRec
[SavingsMonth].Value = intMonth

frmprojectentry.sfrmlotentry.sfrmlotbreakout.sfrmLotAwardB
r

eakout.savings.SetFocus
[Savings].Value = curSavingsByMonth
intMonth = intMonth + 1
Next x


End Sub

First, please be aware that you don't have to set the focus to a control
in order to set or get its value. However, if you want to use
DoCmd.GoToRecord to do record navigation of the nested subform, you must
at least set the focus to the subform, so I'd better explain how to do
it.

The trouble you're having is due to the fact that every form object has
its own separate "active control". If the focus is currently in some
non-subform control on a parent form, and you use SetFocus on a control
on a subform of that parent, that subform's focus is indeed set to the
control you referenced ... but the *parent* form's focus is still on the
original control. Not until you set the focus to the subform (on that
parent form) will the control that really has the application's focus be
the one on the subform.

Now, in your case, you need to set the focus to the lowest level
subform. The form object that each subform represents is displayed in a
subform control on its parent form. You have 4 levels of forms:

frmProjectEntry
sfrmLotEntry
sfrmLotBreakout
sfrmLotAwardBreakout

Therefore, to get the application's focus onto the lowest level subform,
you must execute multiple SetFocus calls if necessary to make sure that
each of these subform controls is the control that has the focus on its
parent form. If your code were operating in a vacuum, with no knowledge
of where the focus currently is, you'd have to do something like this
(watch out for lines broken by the newsreader):

' Make sure main form has the focus.
DoCmd.SelectObject acForm, "frmProjectEntry"

' Make sure sfrmLotEntry has the focus on frmProjectEntry.
Forms!frmProjectEntry!sfrmLotEntry.SetFocus

' Make sure sfrmLotBreakout has the focus on sfrmLotEntry.

Forms!frmProjectEntry!sfrmLotEntry!

sfrmLotBreakout.SetFocus

' Make sure sfrmLotAwardBreakout has the focus on sfrmLotBreakout.

Forms!frmProjectEntry!sfrmLotEntry!sfrmLotBreakout!

sfrmLotAwardBreakout.
SetFocus

At this point, you know that sfrmLotAwardBreakout is the active data
object, and whatever control on it has the form's focus is also the
control that has the application's focus. If you really needed to set
the focus to some specific control on this bottommost subform, you could
now say:

' Make sure SavingsMonth has the focus on sfrmLotAwardBreakout.

Forms!frmProjectEntry!sfrmLotEntry!sfrmLotBreakout!

sfrmLotAwardBreakout!
SavingsMonth.SetFocus

However, as I said earlier, you don't have to set the focus to the
control in order to set its value, so I don't see that you need that
last statement at all for your purposes.

In reality, you almost certainly don't need all of the SetFocus calls I
wrote down above, nor quite such long reference chains. That's because
you know something about where the focus is when you execute your
procedure. This is the Click event procedure of a command button named
cmdCalcMonthlySavings, and unless I've misinterpreted your description,
that command button is on the third-level form already; that is, the
command button is on sfrmLotBreakout. Correct? Then, if this button
has just been clicked, you know that sfrmLotBreakout currently has the
application focus. Also, you can refer to this form object (on which
the code is running) by the keyword "Me". That will shorten up the
references a lot! To make sfrmLotAwardBreakout the current "active data
object", so that GoToRecord (correctly specified, which it isnt yet)
will operate on it, all you have to say is this:

Me!sfrmLotAwardBreakout.SetFocus

Now, if you still want to set the focus to a particular control on
sfrmLotAwardBreakout, you would have to add the *additional* line

Me!sfrmLotAwardBreakout!SavingsMonth.SetFocus

However, I'll say once again that I don't see any reason for doing this.

I mentioned above that your call to GoToRecord wasn't correctly
specified. You wrote

DoCmd.GoToRecord

acDataForm, "sfrmlotawardbreakout", acNewRec

That won't work, because the acDataForm argument value is telling Access
that "sfrmlotawardbreakout" is a top-level form -- a main form. But it
isn't. It's a subform, and you can't get at it except by chasing all
the way down the subform control references from top to bottom. But
the syntax for GoToRecord doesn't allow that kind of reference.
Luckily, GoToRecord allows you to specify (or default to)
acActiveDataObject for its object type argument. That option means that
the GoToRecord action will be applied to whatever is the active data
object -- such as the subform that currently has the focus. So you
should change the statement I quoted above to

DoCmd.GoToRecord acActiveDataObject, , acNewRec

That will make it work on sfrmLotAwardBreakout, once you've set the
focus to it.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

I can't tell you how much I appreciate the explanation
you gave. i will make the changes tomorrow and let you
know how it goes. THanks again so much!

Brandon
.

Ok, the only thing that doesn't work properly is the goto
record command. I made the changes you suggested, but the
goto record command actually takes me to a new record on
the third level form (sfrmLotBreakout) even though I have
set the focus to the fourth level form and it does indeed
have the focus. I only want to go to a new record on the
fourth level form because the forms are linked and records
are prepopulated with the linked info. If I go to a new
record on the third form, I lose my division and location
information I have entered.
Here is the code

Dim curSavingsByMonth As Currency
Dim intMonth As Integer
'Dim rstTable As Recordset
'Dim dbsScorecard As Database
Dim x As Integer
Dim intContractLength As Integer
Dim intContractStart As Integer
Dim curSavingsAmt As Currency
Dim curHistoric As Currency
Dim curAward As Currency

'Set dbsScorecard = OpenDatabase("E-Scorecard.mdb")

'Set rstTable = _
' dbsScorecard.OpenRecordset("tblLotAwardBreakout",
dbOpenDynaset)
intContractLength = Me!ContractLength.Value
intContractStart = Me!ContractStart.Value
x = 1
intMonth = intContractStart
curHistoric = Me!HistoricVolume.Value
curAward = Me!AwardBidAmt.Value
curSavingsAmt = curHistoric - curAward
curSavingsByMonth = curSavingsAmt / intContractLength

Me!sfrmLotAwardBreakout.SetFocus

For x = 1 To [ContractLength].Value
'calculate the monthly savings and add records
'to the subform Brandon Cheal 7/14/04
DoCmd.GoToRecord , , acNewRec
Me!sfrmLotAwardBreakout!SavingsMonth.Value =
_intMonth
Me!sfrmLotAwardBreakout!Savings.Value =
_curSavingsByMonth
intMonth = intMonth + 1
Next x


End Sub

 

[Dirk Goldgar]

Ok, the only thing that doesn't work properly is the goto
record command. I made the changes you suggested, but the
goto record command actually takes me to a new record on
the third level form (sfrmLotBreakout) even though I have
set the focus to the fourth level form and it does indeed
have the focus. I only want to go to a new record on the
fourth level form because the forms are linked and records
are prepopulated with the linked info. If I go to a new
record on the third form, I lose my division and location
information I have entered.
Here is the code

Dim curSavingsByMonth As Currency
Dim intMonth As Integer
'Dim rstTable As Recordset
'Dim dbsScorecard As Database
Dim x As Integer
Dim intContractLength As Integer
Dim intContractStart As Integer
Dim curSavingsAmt As Currency
Dim curHistoric As Currency
Dim curAward As Currency

'Set dbsScorecard = OpenDatabase("E-Scorecard.mdb")

'Set rstTable = _
' dbsScorecard.OpenRecordset("tblLotAwardBreakout",
dbOpenDynaset)
intContractLength = Me!ContractLength.Value
intContractStart = Me!ContractStart.Value
x = 1
intMonth = intContractStart
curHistoric = Me!HistoricVolume.Value
curAward = Me!AwardBidAmt.Value
curSavingsAmt = curHistoric - curAward
curSavingsByMonth = curSavingsAmt / intContractLength

Me!sfrmLotAwardBreakout.SetFocus

For x = 1 To [ContractLength].Value
'calculate the monthly savings and add records
'to the subform Brandon Cheal 7/14/04
DoCmd.GoToRecord , , acNewRec
Me!sfrmLotAwardBreakout!SavingsMonth.Value =
_intMonth
Me!sfrmLotAwardBreakout!Savings.Value =
_curSavingsByMonth
intMonth = intMonth + 1
Next x


End Sub

Hmm, that surprises me, but now that I test it, I get the same results.
I *think* it's a bug, but maybe someone will pop up and tell me I'm just
wrong about how GoToRecord is supposed to work.

Anyway, the immediate question is how to work around it. My inclination
in cases like this -- and I've been suppressing it all along -- is to
forget about form operations and just add the records directly to the
table, and then requery the form if necessary. So your code might be
modified like this:

With Me!sfrmLotAwardBreakout.Form.RecordsetClone
For x = 1 To intContractLength
'calculate the monthly savings and add records
'to the subform Brandon Cheal 7/14/04
.AddNew
!SavingsMonth = intMonth
!Savings = curSavingsByMonth
.Update
intMonth = intMonth + 1
Next x
End With

You may or may not have to requery the subform when you're done:

Me!sfrmLotAwardBreakout.Requery

Since you're working on the subform's recordsetclone, I'd try it first
without requerying, and only add the requery if it's necessary.

 

[Brandon]

-----Original Message-----

Ok, the only thing that doesn't work properly is the goto
record command. I made the changes you suggested, but the
goto record command actually takes me to a new record on
the third level form (sfrmLotBreakout) even though I have
set the focus to the fourth level form and it does indeed
have the focus. I only want to go to a new record on the
fourth level form because the forms are linked and records
are prepopulated with the linked info. If I go to a new
record on the third form, I lose my division and location
information I have entered.
Here is the code

Dim curSavingsByMonth As Currency
Dim intMonth As Integer
'Dim rstTable As Recordset
'Dim dbsScorecard As Database
Dim x As Integer
Dim intContractLength As Integer
Dim intContractStart As Integer
Dim curSavingsAmt As Currency
Dim curHistoric As Currency
Dim curAward As Currency

'Set dbsScorecard = OpenDatabase("E-Scorecard.mdb")

'Set rstTable = _
' dbsScorecard.OpenRecordset("tblLotAwardBreakout",
dbOpenDynaset)
intContractLength = Me!ContractLength.Value
intContractStart = Me!ContractStart.Value
x = 1
intMonth = intContractStart
curHistoric = Me!HistoricVolume.Value
curAward = Me!AwardBidAmt.Value
curSavingsAmt = curHistoric - curAward
curSavingsByMonth = curSavingsAmt / intContractLength

Me!sfrmLotAwardBreakout.SetFocus

For x = 1 To [ContractLength].Value
'calculate the monthly savings and add records
'to the subform Brandon Cheal 7/14/04
DoCmd.GoToRecord , , acNewRec
Me!sfrmLotAwardBreakout!SavingsMonth.Value =
_intMonth
Me!sfrmLotAwardBreakout!Savings.Value =
_curSavingsByMonth
intMonth = intMonth + 1
Next x


End Sub

Hmm, that surprises me, but now that I test it, I get the same results.
I *think* it's a bug, but maybe someone will pop up and tell me I'm just
wrong about how GoToRecord is supposed to work.

Anyway, the immediate question is how to work around it. My inclination
in cases like this -- and I've been suppressing it all along -- is to
forget about form operations and just add the records directly to the
table, and then requery the form if necessary. So your code might be
modified like this:

With Me!sfrmLotAwardBreakout.Form.RecordsetClone
For x = 1 To intContractLength
'calculate the monthly savings and add records
'to the subform Brandon Cheal 7/14/04
.AddNew
!SavingsMonth = intMonth
!Savings = curSavingsByMonth
.Update
intMonth = intMonth + 1
Next x
End With

You may or may not have to requery the subform when you're done:

Me!sfrmLotAwardBreakout.Requery

Since you're working on the subform's recordsetclone, I'd try it first
without requerying, and only add the requery if it's necessary.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

Dirk, no need to requery and it works like a charm!!!! I
can't tell you how excited I am! Have a great weekend and
thanks again for all your help!

Brandon