Duplicate in Last 100 Records

[aMack]

I need to generate a warning that a [Container#] has been entered within the
last 100 orders [REF#]

I need the user to then choose whether to accept the entry or re-enter the
data.

My assumption is this code can be entered on the from at the "After Update"
event.

Suggestions please.

 

[Douglas J. Steele]

Create a query that returns the last 100 orders:

SELECT TOP 100 [Container#]
FROM MyTable
ORDER BY OrderDate DESC

Use DLookup or DCount to determine whether the container number appears in
that list.

If IsNull(DLookup("[Container#]", "QueryName", "[Container#] = " &
lngContainer)) Then
' It hasn't been used
Else
' Is has been used
End If

Note that I'm assume that that Container# is a numeric field, not a text
one.

 

[Jeff Boyce]

Are you requiring your users to remember and enter a [Container#]?

Why?

If you (and Access) already know the [Container#]s, why not give your users
a combobox so they can look it up?

What am I missing?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[aMack]

The Container number is specific to each order - there are very few times
that the same container will be used on 2 orders and if in the rare case it
does come back to us it should be 1 or 2 years later unless there extenuating
circumstances.


--
A MACKENZIE, CMA, MBA

Are you requiring your users to remember and enter a [Container#]?

Why?

If you (and Access) already know the [Container#]s, why not give your users
a combobox so they can look it up?

What am I missing?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

I need to generate a warning that a [Container#] has been entered within
the
last 100 orders [REF#]

I need the user to then choose whether to accept the entry or re-enter the
data.

My assumption is this code can be entered on the from at the "After
Update"
event.

Suggestions please.

.

 

[aMack]

Thanks

What does "INGContainer" refer to?


--
A MACKENZIE, CMA, MBA

Create a query that returns the last 100 orders:

SELECT TOP 100 [Container#]
FROM MyTable
ORDER BY OrderDate DESC

Use DLookup or DCount to determine whether the container number appears in
that list.

If IsNull(DLookup("[Container#]", "QueryName", "[Container#] = " &
lngContainer)) Then
' It hasn't been used
Else
' Is has been used
End If

Note that I'm assume that that Container# is a numeric field, not a text
one.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I need to generate a warning that a [Container#] has been entered within
the
last 100 orders [REF#]

I need the user to then choose whether to accept the entry or re-enter the
data.

My assumption is this code can be entered on the from at the "After
Update"
event.

Suggestions please.

.

 

[Douglas J. Steele]

lngContainer would be the name of a Long Integer variable that stores the
value of the container's number.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks

What does "INGContainer" refer to?


--
A MACKENZIE, CMA, MBA

Create a query that returns the last 100 orders:

SELECT TOP 100 [Container#]
FROM MyTable
ORDER BY OrderDate DESC

Use DLookup or DCount to determine whether the container number appears
in
that list.

If IsNull(DLookup("[Container#]", "QueryName", "[Container#] = " &
lngContainer)) Then
' It hasn't been used
Else
' Is has been used
End If

Note that I'm assume that that Container# is a numeric field, not a text
one.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I need to generate a warning that a [Container#] has been entered within
the
last 100 orders [REF#]

I need the user to then choose whether to accept the entry or re-enter
the
data.

My assumption is this code can be entered on the from at the "After
Update"
event.

Suggestions please.

.