2nd Request - can't transfer value between forms.

[DDBeards]

The two tck_201_ fields are text boxes on a form that are being set by to
calculated text boxes on a different form!subform. As you can see by the
debug statements and results, the values will just not transfer. I have set
the format for both of the TCK _201_ fields to Fixed! Any Ideas?

Me.TCK_201_Done = Forms!frmPTS_Edit!SFrm_Date_Used_E!TMS_Actual
Me.TCK_201_Tot = Forms!frmPTS_Edit!SFrm_Date_Used_E!TMS_NumDates

Debug.Print Me.TCK_201_Done
Debug.Print Me.TCK_201_Tot
Debug.Print Forms!frmPTS_Edit!SFrm_Date_Used_E!TMS_Actual
Debug.Print Forms!frmPTS_Edit!SFrm_Date_Used_E!TMS_NumDates

Debug results
Null
Null
2
2


Thanks DDBeards

 

[Marshall Barton]

The two tck_201_ fields are text boxes on a form that are being set by to
calculated text boxes on a different form!subform. As you can see by the
debug statements and results, the values will just not transfer. I have set
the format for both of the TCK _201_ fields to Fixed! Any Ideas?

Me.TCK_201_Done = Forms!frmPTS_Edit!SFrm_Date_Used_E!TMS_Actual
Me.TCK_201_Tot = Forms!frmPTS_Edit!SFrm_Date_Used_E!TMS_NumDates

Debug.Print Me.TCK_201_Done
Debug.Print Me.TCK_201_Tot
Debug.Print Forms!frmPTS_Edit!SFrm_Date_Used_E!TMS_Actual
Debug.Print Forms!frmPTS_Edit!SFrm_Date_Used_E!TMS_NumDates

Debug results
Null
Null
2
2

That sure appears to be a timing issue where the
calculations are done after the assignments. This happens
frequently because control source expressions are evaluated
in a lower priority task than executin VBA code. The way to
keep things synchonized is to do both the caclulations and
the assignments the same way. Either use code or control
source expressions for both.

The easiest think to try is to just set text box
TCK_201_Done to the expression
=Forms!frmPTS_Edit!SFrm_Date_Used_E!TMS_Actual

If that doesn't do it, then use code in form frmPTS_Edit to
calculate the value for SFrm_Date_Used_E!TMS_Actual
I don't know what the calculations are or where their
variables come from so I can't say where the code to do the
calculations should go, probably in some other control's
AfterUpdate event and/or in the form's Current event.

 

[DDBeards]

Thanks Marsh, both of the values are count() statements contained in the form
footer of the subform SFrm_Date_Used_E. I tried to put a count statement in
the new form pointing to the subform but could not get the syntax to work.

 

[Marshall Barton]

That can not possibly work because the aggregate functions
(Count, Sum, etc) are unknown to VBA.

Did you try my first suggestion of using the expression:
=Forms!frmPTS_Edit!SFrm_Date_Used_E!TMS_Actual
in a text box?

If that doesn't sync up, then use VBA code to calculate the
count. The code for Count(*) could look like:
With
Forms!frmPTS_Edit!SFrm_Date_Used_E.Form.RecordsetClone
.MoveLast
Me.TCK_201_Done = .RecordCount
End With

 

[DDBeards]

The count function is not in VBA, it is in the data source of the two text
boxes in the footer. With that said, I like the idea you have but the code
provided will count the records, that will take care of the total number
however the second number is the number of records with the completed date
field filled in.

thanks

That can not possibly work because the aggregate functions
(Count, Sum, etc) are unknown to VBA.

Did you try my first suggestion of using the expression:
=Forms!frmPTS_Edit!SFrm_Date_Used_E!TMS_Actual
in a text box?

If that doesn't sync up, then use VBA code to calculate the
count. The code for Count(*) could look like:
With
Forms!frmPTS_Edit!SFrm_Date_Used_E.Form.RecordsetClone
.MoveLast
Me.TCK_201_Done = .RecordCount
End With
--
Marsh
MVP [MS Access]

Thanks Marsh, both of the values are count() statements contained in the form
footer of the subform SFrm_Date_Used_E. I tried to put a count statement in
the new form pointing to the subform but could not get the syntax to work.

.

 

[Marshall Barton]

The count function is not in VBA, it is in the data source of the two text
boxes in the footer.

I guess I was thrown off where you said "I tried to put a
count statement in the new form". There are VBA statements
and SQL statements, but it is an Expression when you put
=something
in a control source property.

That also can not possibly work in another form because the
data for an aggregate function must come from the original
form's record source table/query.

I like the idea you have but the code
provided will count the records, that will take care of the total number
however the second number is the number of records with the completed date
field filled in.

You can add something like this code inside the With block:
Dim lngCompleted As Long
.MoveFirst
Do Until .EOF
If Not IsNull( ![completed date] ) _
Then lngCompleted = lngCompleted + 1
Loop
Me.TCK_201_Tot = lngCompleted