Copy data from a form when its there

[RobPotts]

Hi,

What I'm trying to do is when I enter a item number in my form (frmNewSpecs) I want a Form to Open (FrmOldSpecs) which has the last lot of specs for that item number which runs from a query (I can do this bit OK), then I want to copy these enteries to FrmNewSpecs. I can do this when there has been a previous entry but not when there hasn't. The code I'm using is:

Private Sub TPSEd_AfterUpdate()

Dim stDocName As String
stDocName = "FrmOldSpecs"
DoCmd.OpenForm stDocName, acNormal

If IsNull(Forms![FrmOldSpecs]![LastOfTest1]) = True Then
DoCmd.Close acForm, StDocName
Else
Me.Test1 = Forms![FrmOldSpecs]![LastOfTest1]
Me.Test2 = Forms![FrmOldspecs]![LastOfTest2]
Me.Test3 = Forms![FrmOldSpecs]![LastOfTest3]
DoCmd.Close acForm, StDocName
End If
End Sub

When there is no data it keeps saying that the value isn't valid for this field and highlights the Me.Test1=... line. If there is no data I want the FrmOldSpecs to close and the code to stop running so I can make the enteries manually on the FrmNewSpecs

Where am I going wrong?




Rob! [Bigcheeks]
Process and Procedure are the last hiding place of people without the wit
and wisdom to do their job properly.

 

[vbajock]

Try changing
If IsNull(Forms![FrmOldSpecs]![LastOfTest1]) = True
to
If IsNull(Forms![FrmOldSpecs]![LastOfTest1]) = vbnullstring

if that doesn't work try

If IsNull(Forms![FrmOldSpecs]![LastOfTest1]) or Forms![FrmOldSpecs]![LastOfTest1]=""

 

[RobPotts]

Hi vbajock,

Thanks for the advise. I'm afraid that neither worked, however I have sorted the issue out now. When I open FrmOldSpecs I do a check in the on open even to see if theres any data, if not it closes the form, if there is it does the paste for me, then closes.

If Me.RecordsetClone.RecordCount = 0 Then
Cancel = True
DoCmd.Close acForm, "FrmOldSpecs"
Else
Forms![FrmNewSpecs]![Test1] = Me.LastOfTest1
Forms![FrmNewspecs]![Test2] = Me.LastOfTest2
Forms![FrmNewSpecs]![Test3] = Me.LastOfTest3
DoCmd.Close acForm, "FrmOldSpecs"
End if

doing it this way does the job exactly as hoped.

Thanks anyway


Rob! [Bigcheeks]
Process and Procedure are the last hiding place of people without the wit
and wisdom to do their job properly.