Refresh Form Data

[handlebarry]

Hi,

trying to refresh formA, on closing formB

Placing a command button on formA (with wizard) I get the following code.

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

What I want to do is put this code under OnClose in formB.
However I'm not familiar with DoMenuItem and there is nothing in help to advise me. Can someone tell me how to refer to FormA using this code please

 

[Remou]

Hi
It should be possible to do this:
Forms!frmFormName.Refresh

DoCmd.DoMenuItem is not recommended in the more recent versions of Access. DoCmd.RunCommand is used instead.

 

[handlebarry]

I tried that but it doesn't work

Forms!frmContractMonitoring!sfmNoticesOfIntention.Form.Refresh

the problem is with a field that is based on the following query field

Days Late: IIf([Day1]<=14,"On Time",Format([Day1]-14,"0")) & IIf([ContractStatus]="NOE PREPARATION","NOE","")

this field returns #Error after closing formB

 

[Remou]

Where are the fields Day1 and ContractStatus? How does FormB interact with FormA? It does not seem like a refresh problem, because
Forms!frmContractMonitoring!sfmNoticesOfIntention.Form.Refresh
should fix any refresh problem.

 

[handlebarry]

The fields Day1 and ContractStatus are both on the same query as Days late. This query is the record source for FormA - (sfmNoticesOfIntention)

There is also a field called contractname, double clicking on this field opens formB (frmContractNotes2)

On closing formB the error appears

Maybe the query is too complicated and I should change it

 

[Remou]

I have tried a mock-up of the above, even tortured it a little, and I did not get an error. Is it possible that formB changes the Record Source of sfmNoticesOfIntention in some way?
(I used a table with three fields Day1, ContractStatus and ContractName to create a subform, to which I added a calculated field Days Late, with a Control Source set to the line shown.)

 

[handlebarry]

here is the actual query if you want to have a go but truthfully I think I'll have to stick with the command button - I've been doing this on and off for over a week and the command button is the only progress i've made!

Thanks very much for the help though

SELECT tblContracts.ContractCode, tblContracts.ContractName, tblContracts.ContractStatus, tblContracts.NOIOfficer, tblContracts.NOPOfficer, IIf([ContractStatus]="NOI PREPARATION",[NOIOfficer],[NOPOfficer]) AS [CWG Officer], tblContracts.NOIDateCompleteInfoReceived, tblContracts.NOIDateNoticeServed, tblContracts.NOPDateCompleteInfoReceived, tblContracts.NOPDateNoticeServed, DateDiff("d",[NOIDateCompleteInfoReceived],Date()) AS LateNOI, DateDiff("d",[NOPDateCompleteInfoReceived],Date()) AS LateNOP, IIf([ContractStatus]="NOI PREPARATION",[LateNOI],[LateNOP]) AS Day1, IIf([Day1]<=14,"On Time",Format([Day1]-14,"0")) & IIf([ContractStatus]="NOE PREPARATION","NOE","") AS [Days Late], ([NOIDateCompleteInfoReceived]+14-Date()) AS [NOI Prep Days Left1], IIf(IsNull([NOPDateCompleteInfoReceived]),[NOI Prep Days Left1],"N/A") AS [NOI Prep Days Left], ([NOPDateCompleteInfoReceived]+14-Date()) AS [NOP Prep Days Left]
FROM tblContracts
WHERE (((tblContracts.ContractStatus)="NOP PREPARATION" Or (tblContracts.ContractStatus)="NOI PREPARATION" Or (tblContracts.ContractStatus)="NOE PREPARATION"));