Replies

So you are trying to update a control on a form that is not open? You cannot do that.
If the form is not open, just update the value in the underlying field.

Thanks John, I think I asked that question myself in my first reply. Are you able to help with code that would let me update the field in the underlying table, as I’m not sure how to do this. The table name is tblBill and the field is AIP_LatestAIPDate

Cheers,
Jason

You have made mention of subforms earlier in this thread.

When you want to refer to a control on a subform, you need to use a different syntax than if you refer to a control on a form that is not a subform.
The Forms collection includes all open forms, but not open subforms.

So is the control you want to update on a form or a subform?

Hi John,

The control I want to update is on a form – the field is called ‘LatestAIPDate’ and it is on the ‘ProgramMovements’ form.

I mentioned a sub-form earlier because the example code I used works in a sub-form that is on the ‘ProgramMovements’ form. Wendell mentioned that the same code doesn’t work on another form because the ‘ProgramMovements’ form isn’t open when I try to run the code.

Cheers,
Jason

And what is the relationship of BidDataCenter and ProgramMovements? Is BidDataCenter a subform of ProgramMovements or the other way round or neither?

There is no relationship between BidDataEnter and ProgramMovements.

Can you tell us exactly where the controls DeptAIP and LatestAIPDate are, i.e. on which form/subform?

Hi Hans,

DeptAIP is in form “BidDataEnter” and LatestAIPDate is in form “ProgramMovements”

Cheers,
Jason

In order to update a field on a form, that form must be open. The error message suggests it is not. If the subform is on that form, then the code would work, because the form has to be open in order for the subform to be open. If that is not the case, let us know and we’ll try to help resolve the issue.

Hi Wendel,

Yes, that is the case – the subform is on that form, which is why that code works. Is there another way around this, or should I just update the underlying field in the table itself?

Cheers,
Jason

Yes, that worked! Thankyou so much for your quick replies!

Welcome to the Lounge!

The After Update event of CabAppDate only occurs when the user enters or modifies it directly. When the user selects a date in the popup form, the value of CabAppDate is set but its After Update event does not occur, and hence the date on the main form is not modified. So you will have to run the CabAppDate_AfterUpdate explicitly. How to do that depends on the way the popup form and the associated module work.

Hi Hans,

Thanks so much for the quick reply. I’m not much of a programmer, and have copied/pasted etc code from other databases and the web to get everything working! In the ‘On_Dbl_click’ part of the field, I’m calling this code:

=PopupCalendar(Screen.ActiveControl)

Which references the basCalendar module which contains this code:

Option Compare Database ‘Use database order for string comparisons
Option Explicit

Const CALENDAR_FORM = “zsfrmCalendar”

Type udDateType
wYear As Integer
wMonth As Integer
wDay As Integer
End Type

Private Function isFormLoaded(strFormName As String)
isFormLoaded = SysCmd(SYSCMD_GETOBJECTSTATE, A_FORM, strFormName)
End Function

Function PopupCalendar(ctl As Control) As Variant
‘
‘ This is the public entry point.
‘ If the passed in date is Null (as it will be if someone just
‘ opens the Calendar form raw), start on the current day.
‘ Otherwise, start with the date that is passed in.
‘
Dim frmCal As Form
Dim varStartDate As Variant

varStartDate = IIf(IsNull(ctl.Value), Date, ctl.Value)
DoCmd.OpenForm CALENDAR_FORM, , , , , A_DIALOG, varStartDate

‘ You won’t get here until the form is closed or hidden.
‘
‘ If the form is still loaded, then get the final chosen date
‘ from the form. If it isn’t, return Null.
‘
If isFormLoaded(CALENDAR_FORM) Then
Set frmCal = Forms(CALENDAR_FORM)
ctl.Value = Format(DateSerial(frmCal!Year, frmCal!Month, frmCal!Day), “dd/mmm/yyyy”)
DoCmd.Close A_FORM, CALENDAR_FORM
Set frmCal = Nothing
End If
End Function

Hope this helps!

Cheers,
Jason