Open either a New Form or Last Form in SUBFORM from MAINFORM

[williekay]

I have a main form called MoldList and a sub form named MoldHistory. On the mainform is a field named Rework. If this field has a date, the mold is out of service. The subform is a form that lists the work to be done on the mold. If the Rework field of the subform is Null I need the subform to open a new form, if the main form lists a date I need it to open the last record listed for the same Mold# (Mold# is the link between the mainform and subform and the MoldList table and MoldHistory table). I played with the following and a few different ways to refernce the Rework field in the mainform. Me.RW in the following code is a text box on the subform that has a contol source of DLookup("[Rework]", "[MoldList]", "[Mold#] = " & [Mold#] & ""). One other thought is possibly the code I have to make Rework in the Main is not equalling Null. I wrote it 2 ways Rework = Null and Rework = "", both seemed to give me Null in the table, but I have the form CONDITIONALLY FOTRMATTED Not (IsNull([Rework])), When I place a date in the Rework field it highlights as it is supposed to, when I execute the code to produce the Null it is still highlighted???

Private Sub Form_Open(Cancel As Integer)
Dim JOKER As String
'JOKER = Forms![MoldList]![MoldHistory1].Form![RW]
JOKER = Me.RW
'JOKER = DLookup("[Rework]", "[MoldList]", "[Mold#] = " & [Mold#] & "")
'JOKER = IIf(IsNull(Forms![MoldList]![MoldHistory1].Form![RW]), "A", "B")
If IsNull(JOKER) Then
DoCmd.GoToRecord , , acNewRec
Else
DoCmd.GoToRecord , , acLast
End If

'Text539 = JOKER

End Sub

Willie

 

[Remou]

Where have you put this code? If it is on the main form, the refernce to RW will work, but you must set focus to the subform before you can use GoToRecord. Is that what you mean?

 

[PHV]

Anyway, if you play with controls I suggest to use the Load event procedure instead of the Open.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886