Issue with Calculating Dates on Subform

[kjohns]

Sorry in advance - this is long

I have a database in which I maintain information about patients. I am having problems getting the results of several calculations and "pulled" fields to display without closing the form and re-opening it.

• tblMaternalInfo - includes demographic info, as well as the fields EvaluationDate and EDC (due date). EvaluationDate is used as the basis of two of my calculations and EDC is "pulled" to my subform
  
• tblPsychosocial - table which is populated for only SOME patients. This table is used to document follow-up contact with the patient. Calculations are NOT performed in this table.
  
• frmPatientRecordbyLName - my main form, from which all patient information is available.
  
• sfmPsychosocial - queries MaternalInfo for some of the main info (including EvaluationDate and EDC) and any tblPsychosocial-specific info. This subform is on a tab of frmPatientRecordbyLName.

On sfmPsychosocial, I have pulled EvaluationDate and EDC, then I use [EvaluationDate]+7 and [EvaluationDate]+30 to calculate dates on which contact with the patient should occur in unbound textboxes. (I just want to re-display EDC.) The problem is that when this form is displayed for a new patient, the "pulled" values and calculated values won't display because there is no record in tblPsychosocial for this patient. These values will only display after some Psychosocial info is entered and the entire form frmPatientRecordbyLName is closed and re-opened.

What I'd like to have happen is that after a piece of information for tblPsychosocial is entered (which should create a record in tblPsychosocial) or a button is clicked, those "pulled" values and calculations are shown without the user having to close and re-open the form.

Suggestions please??? I've tried several versions of Repaint, Requery, Refresh and also considered that maybe there's a way to "rig" saving a record and requerying before closing out of the form??

 

[RuralGuy]

What is the RecordSource for the sfmPsychosocial form? I would assume it is a query that joins the tblMaternalInfo and tblPsychosocial tables. The query could also contain the date calculations so they are available for the SubForm to use.

 

[kjohns]

Thanks for the response, RuralGuy.

You are correct that the RecordSource for sfmPsychosocial is a query of tblMaternalInfo and tblPsychosocial. I had originally been calculating the values of the expressions on the form instead of in the query. I went ahead and altered the query to perform the calculations in the query instead of on the form and I changed the ControlSource on those calculated fields from the expression to the field name (Expr1, Expr2, etc.). This didn't solve my problem. I'm still not able to get these values to calculate without closing and re-opening the entire form.

I think it may have something to do with the fact that not every patient has a tblPsychosocial record - only those that have some info entered on sfmPsychosocial, which is the way I need to keep it. I'm thinking I need a way to force a save to the table before I close the form so that a requery/refresh will work.

A few more questions:
1. Should replacing the ControlSource on those (unbound) fields be enough or do I need to replace them with bound fields?
2. Maybe RuralGuy's fix could be part of the solution. Could someone provide some code that would force the form to requery/recalculate/refresh (can you tell I don't really know which one to use?) and pick up those dates once some portion of info is entered, therefore creating a record? Also, should this code go on a certain field, sfmPsychosocial, or frmPatientRecordbyLName? I'm not opposed to creating a "Get Dates" command button if that's an easier option.

 

[RuralGuy]

I believe if you use bound controls on your subform then the calculation control will update with every change you make to a field involved in the calculation.

 

[kjohns]

Your explanation makes sense, except that the fields my users will be updating on sfmPsychosocial are NOT the fields that are involved in the calculation. Basically, my Psychosocial form captures an address and phone #, as well as checkboxes (to show completed contact with patients), and comments fields.

The fields involved in calculations (EvaluationDate) and being pulled (EDC) are both entered on the main tab of frmPatientRecordbyLName. Neither are entered/updated on sfmPsychosocial.

(If it helps, I've had no problems with calculations like you describe, where a date/value is entered on a form and the calculation is performed on the same form/query.)

 

[RuralGuy]

Your MainForm controls are bound and they are not updating when you change the [EvaluationDate]?

 

[kjohns]

My main form controls are bound and they are updating when I update the EvaluationDate.

The problem is that when I have an existing patient (who did not have a Psychosocial entry) or a new patient and I click on the Psychosocial tab - to get to sfmPsychosocial - it does not pull the EDC or EvaluationDate from the main form to the Psychosocial tab. So, I cannot see the results of those calculations until I close and re-open the main form.

It's almost like the new entry in tblPsychosocial doesn't save until I've closed out of the form and re-opened it.

 

[RuralGuy]

The SubForm record will save when you move the focus away from the SubForm (like back to the MainForm). Are you displaying the calculated dates on both the MainForm and the SubForm?

 

[kjohns]

Hmmm. I must be doing something else wrong then, because I have to completely close out of the main form, not just move to a different tab within the main form (so away from sfmPsychosocial) to get those calculated dates to show up.

No, I'm only doing the calculations on the subform. Are you thinking that I should go ahead and do the calculations on the main form? I'm still not sure that would work because when I tried to display (Visible=Yes) the fields EDC and EvaluationDate on sfmPsychosocial, they, like the calculations, wouldn't display on sfmPsychosocial until I had closed out of frmPatientRecordbyLName and re-opened it. However, they would continue to display on the main form of frmPatientRecordbyLName.

 

[RuralGuy]

As a test, create a CommandButton on the MainForm that requeries the SubForm and see what it does.
SubFormControlName.FORM.Requery.

 

[kjohns]

Command button placed on main page of frmPatientRecordbyLName.

Private Sub Command173_Click()
sfmPsychosocial.Form.Requery
End Sub

I went into the Psychosocial tab for a patient (who did not have a record) and entered an address, then I flipped back to my main tab and clicked the button. It did pull the EDC and calculate those two dates based off of EvaluationDate! Horray!!!

I did figure out, though, that I could put it on the actual tab used for sfmPsychosocial, but outside of the subform so that my users don't have to move away from the Psychosocial tab.

Thank you so much for your help and patience!!!

 

[RuralGuy]

You can make it happen without the button in the AfterUpdate event of the SubForm. A requery moves the RecordPointer back to the beginning of the RecordSet so you only want to do the requery when there were no records in the RecordSet and you added the first record. The condition you are looking for is Me.NewRecord AND Me.RecordSet.RecordCount = 0. You can set a flag if this condition exists in the BeforeUpdate event of the SubForm and if the flag is set, reset it and Requery in the AfterUpdate event of the SubForm. There will only be one record in this case and the user will never see any movement.

 

[kjohns]

RuralGuy, I like the sound of making this happen without a button, but I'm not completely clear on what code goes where?

Could you be more specific about what code needs to be used for the BeforeUpdate and AfterUpdate actions? And will all this go in the sfmPsychosocial events or will some go in the main form's events? I appreciate your help. Thanks.

 

[RuralGuy]

All of the code goes in the sfmPsychosocial form. You need to Dim a Public variable in the code module of the form. Public variables are defined before your first Function or SubRoutine. They are then available to all routines in the code. Warning!! <<<AIR CODE>>>

Option Compare Database
Option Explicit

Dim NeedRequery As Boolean

Private Sub Form_BeforeUpdate(Cancel As Integer)

   If Me.NewRecord And (Me.Recordset.RecordCount = 0) Then
      NeedRequery = True
   End If

End Sub

Private Sub Form_AfterUpdate()

   If NeedRequery Then
      NeedRequery = False
      Me.Requery
   End If

End Sub