Duplicate in last 100 records

[aMack]

I need a Msgbox to appear as a warning only if a data field matches within
the last 100 records:

Each record is an order with Key field [REF#]

If the data field [Container] matches within the last 100 [REF#]'s than I
need a MsgBox to appear "Are You Sure - this Container exists on REF XXXXXX"

I think I would add the code builder to the ON Exit event but do not know
how to compare the current record to a the last 100 records and I do not know
how to pull the [REF#] and put it in the msgbox.

Is there a different way to do this?

Thanks

 

[Marshall Barton]

I need a Msgbox to appear as a warning only if a data field matches within
the last 100 records:

Each record is an order with Key field [REF#]

If the data field [Container] matches within the last 100 [REF#]'s than I
need a MsgBox to appear "Are You Sure - this Container exists on REF XXXXXX"

I think I would add the code builder to the ON Exit event but do not know
how to compare the current record to a the last 100 records and I do not know
how to pull the [REF#] and put it in the msgbox.

Try creating a query to retrieve the highest 100 Ref
numbers:

SELECT TOP 100 [Ref#[ FROM table ORDER BY [Ref#] DESC

Then you can use code something like this in the Ref# text
box's BeforeUpdate event:

If DCount("*", "query", "[Ref#]=" & Me.textbox) > 0 Then
If MsgBox("are you sure ...", vbYesNo) = vbNo Then
Camcel = True
Me.textbox.Undo
End If
End If

 

[aMack]

code entered as follows: (Query name is "Selectlast100"

If DCount("*", "Selectlast100", "[Container]= " & Me.Container) > 0 Then
If MsgBox("Are you sure this Container is correct", vbYesNo) = vbNo Then
Cancel = True
Me.Container.Undo
End If
End If

I get an error: "Run Time Error "2001"

It takes me to the If DCount line and highlights it???

This is still not all I need - the MsgBox needs to container the [REF#]
where the duplicate exists.

Thanks

--
A MACKENZIE, CMA, MBA

I need a Msgbox to appear as a warning only if a data field matches within
the last 100 records:

Each record is an order with Key field [REF#]

If the data field [Container] matches within the last 100 [REF#]'s than I
need a MsgBox to appear "Are You Sure - this Container exists on REF XXXXXX"

I think I would add the code builder to the ON Exit event but do not know
how to compare the current record to a the last 100 records and I do not know
how to pull the [REF#] and put it in the msgbox.

Try creating a query to retrieve the highest 100 Ref
numbers:

SELECT TOP 100 [Ref#[ FROM table ORDER BY [Ref#] DESC

Then you can use code something like this in the Ref# text
box's BeforeUpdate event:

If DCount("*", "query", "[Ref#]=" & Me.textbox) > 0 Then
If MsgBox("are you sure ...", vbYesNo) = vbNo Then
Camcel = True
Me.textbox.Undo
End If
End If

 

[Linq Adams via AccessMonster.com]

What datatype is Container?

"[Container]= " & Me.Container

is valid syntax if Container is a Numeric value. If it is defined as Text

"[Container]= '" & Me.Container & "'"

would be correct.

 

[Marshall Barton]

code entered as follows: (Query name is "Selectlast100"

If DCount("*", "Selectlast100", "[Container]= " & Me.Container) > 0 Then
If MsgBox("Are you sure this Container is correct", vbYesNo) = vbNo Then
Cancel = True
Me.Container.Undo
End If
End If

I get an error: "Run Time Error "2001"

It takes me to the If DCount line and highlights it???

This is still not all I need - the MsgBox needs to container the [REF#]
where the duplicate exists.

If MsgBox("Are you sure Container " & Me.[Ref#] & " is
correct", vbYesNo) = vbNo Then