OpenForm code has got this amateur puzzled!

[tscotti]

Hello all,

Here's what I'm trying to do:

If a value exists after input, display the message that it already exists in the "archived" version and then open the form of the archived table and display the record of the existing value.

Here's the code I'm using:

Private Sub LotNo_BeforeUpdate(Cancel As Integer)
Dim varX As Variant
varX = DLookup("[LotNo]", "tbLArchiveDACS", "[LotNo] = '" & Forms!frmDACS.[LotNo] & "'")
If Not IsNull(varX) Then
MsgBox "LotNo " & [LotNo].Value & " already exists as a product number in ArchiveDacs!"
Me.Undo
Cancel = True

DoCmd.OpenForm "frmArchiveDACS", acNormal, , "[LotNo]= '" & Forms!frmDACS.[LotNo] & "'"


Else
'do nothing!
End If

End Sub​

The message portion of the code works well but the "archived" form opens without any record and is also "filtered".

Any ideas?

Thanks, in advance, for any help offered.

Tony

 

[WayneRyan]

Tony,

I actually don't know what "should" happen.
If your DLookUp succeeds, aren't you telling it to display the entry
that you just "undid"?

Private Sub LotNo_BeforeUpdate(Cancel As Integer)
Dim varX As Variant
varX = DLookup("[LotNo]", "tbLArchiveDACS", "[LotNo] = '" & Forms!frmDACS.[LotNo] & "'")
If Not IsNull(varX) Then
   MsgBox "LotNo " & [LotNo].Value & " already exists as a product number in ArchiveDacs!"
   Me.Undo
   Cancel = True
   'After you've "undone" the entry that succeeded display the form
   'The form SHOULD have nothing
   DoCmd.OpenForm "frmArchiveDACS", acNormal, , "[LotNo]= '" & Forms!frmDACS.[LotNo] & "'"
Else
   'do nothing!
End If

End Sub

Wayne

 

[tscotti]

Hi Wayne,

Prior to adding the DoCmd.OpenForm line, it worked as it should.

However, now I've added an archive table/form, and I want it to display the record of the archived information - not the current form. This is why I've added the DoCmd.OpenForm line.

Are you saying I should remove the "Me.undo"?

Thanks,

Tony