dialog message tied to dlookup

[Guest]

Hi all.
I got some very valuable assistance from a couple guys in this group on
DLOOKUP. I'm trying to setup a 'yes/no' message box that is based off the
DLOOKUP and will come up once DLOOKUP tests and verifies a duplicate record.
I've got code for that, and everything works, except the end result on a
"yes" response puts you back to record #1 instead of to the then matching
record; I want a "yes" response to take me to the matching record found.
Here's my code, any thoughts?
To assist, the following fields have the following formats:
[Reseller Account Number] Text
[End User Name] Text
[Amount] Currency

And [dup PO check] is a table name.

Thanks a million guys! This will save alot of headaches!

________________________________________

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim varDUPPO As Variant

If Me.NewRecord Then

varDUPPO = DLookup("[Reseller Account Number]", "[dup PO check]",
"[Reseller Account Number] = '" & Me![Reseller Account Number] & "' AND [End
User Name] = '" & Me![End User Name] & "' AND [Amount] = " & Me![Amount])

If Not IsNull(varDUPPO) Then

If MsgBox("This record already exists." & _
"Cancel this new entry and review the previous
entry? If NO, please put brief reason for duplicate in notes box.", _
vbQuestion + vbYesNo, _
"Duplicate Entry Found") _
= vbYes _
Then
Cancel = True
Me.Undo
Me.Recordset.FindFirst "[Reseller PO#]='" & varDUPPO & "'"

End If

End If

End If

End Sub
_________________________________________


bluezcruizer

 

[John Nurick]

More like this:

Dim rs As Object
...

Cancel = True
Me.Undo
Set rs = Me.Recordset.Clone
rs.FindFirst "[Reseller PO#]='" & varDUPPO & "'"
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
End If
rs.Close
End If
...

Hi all.
I got some very valuable assistance from a couple guys in this group on
DLOOKUP. I'm trying to setup a 'yes/no' message box that is based off the
DLOOKUP and will come up once DLOOKUP tests and verifies a duplicate record.
I've got code for that, and everything works, except the end result on a
"yes" response puts you back to record #1 instead of to the then matching
record; I want a "yes" response to take me to the matching record found.
Here's my code, any thoughts?
To assist, the following fields have the following formats:
[Reseller Account Number] Text
[End User Name] Text
[Amount] Currency

And [dup PO check] is a table name.

Thanks a million guys! This will save alot of headaches!

________________________________________

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim varDUPPO As Variant

If Me.NewRecord Then

varDUPPO = DLookup("[Reseller Account Number]", "[dup PO check]",
"[Reseller Account Number] = '" & Me![Reseller Account Number] & "' AND [End
User Name] = '" & Me![End User Name] & "' AND [Amount] = " & Me![Amount])

If Not IsNull(varDUPPO) Then

If MsgBox("This record already exists." & _
"Cancel this new entry and review the previous
entry? If NO, please put brief reason for duplicate in notes box.", _
vbQuestion + vbYesNo, _
"Duplicate Entry Found") _
= vbYes _
Then
Cancel = True
Me.Undo
Me.Recordset.FindFirst "[Reseller PO#]='" & varDUPPO & "'"

End If

End If

End If

End Sub
_________________________________________


bluezcruizer

 

[John Nurick]

Put
Dim rs As Object
at the beginning of your procedure with the other Dim statement. Then
replace the lines in your procedure from
Cancel = True
to the next
End If
with the snippet I posted.

What I posted is "air code" and may need some modification before it
will work.

Hi John,
Thank you. I'm confused though; where exactly would I put the code you're
giving me in place of or in addition to with my existing code?
Thank you!
bluezcruizer

More like this:

Dim rs As Object
...

Cancel = True
Me.Undo
Set rs = Me.Recordset.Clone
rs.FindFirst "[Reseller PO#]='" & varDUPPO & "'"
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
End If
rs.Close
End If
...

Hi all.
I got some very valuable assistance from a couple guys in this group on
DLOOKUP. I'm trying to setup a 'yes/no' message box that is based off the
DLOOKUP and will come up once DLOOKUP tests and verifies a duplicate record.
I've got code for that, and everything works, except the end result on a
"yes" response puts you back to record #1 instead of to the then matching
record; I want a "yes" response to take me to the matching record found.
Here's my code, any thoughts?
To assist, the following fields have the following formats:
[Reseller Account Number] Text
[End User Name] Text
[Amount] Currency

And [dup PO check] is a table name.

Thanks a million guys! This will save alot of headaches!

________________________________________

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim varDUPPO As Variant

If Me.NewRecord Then

varDUPPO = DLookup("[Reseller Account Number]", "[dup PO check]",
"[Reseller Account Number] = '" & Me![Reseller Account Number] & "' AND [End
User Name] = '" & Me![End User Name] & "' AND [Amount] = " & Me![Amount])

If Not IsNull(varDUPPO) Then

If MsgBox("This record already exists." & _
"Cancel this new entry and review the previous
entry? If NO, please put brief reason for duplicate in notes box.", _
vbQuestion + vbYesNo, _
"Duplicate Entry Found") _
= vbYes _
Then
Cancel = True
Me.Undo
Me.Recordset.FindFirst "[Reseller PO#]='" & varDUPPO & "'"

End If

End If

End If

End Sub
_________________________________________


bluezcruizer

 

[John Nurick]

You said in your first message that

everything works, except the end result on a
"yes" response puts you back to record #1 instead of to the
then matching record.

The snippet I posted doesn't affect the checking for duplicates: it just
changes the bit where you were trying to move to the existing record. So
I think the problem must be in your original procedure.

One thing that catches the eye is that at the beginning of the procedure
you do this:

varDUPPO = DLookup("[Reseller Account Number]", "[dup PO check]",
"[Reseller Account Number] = '" & Me![Reseller Account Number] & "' AND [End
User Name] = '" & Me![End User Name] & "' AND [Amount] = " & Me![Amount])

But then when you want to find the existing record you do

Me.Recordset.FindFirst "[Reseller PO#]='" & varDUPPO & "'"

In other words you first set varDUPPO to the [Reseller Account Number]
of the first (existing) record that matches the form's [Reseller Account
Number], [End User Name] and [Amount]. But you then try to find the
record where [Reseller PO#] equals varDUPPO. This seems wrong to me.

BTW: you do have a unique index on whatever fields you don't want to be
duplicated, don't you?

Hi John,

Thank you. That code didn't work; in testing it, the code allowed me to
enter one record twice, then on the third time recognized that the record
already exists. When I clicked "YES" for deleting additional record and send
me to the original record that was duplicated, it sent me back to the very
first record on the db, which happens not to be the matching record.

I'm still learning VB and very basic. Please forgive my rudimentary skills;
I'll need some help on this.

What I need the code to do is not allow me to duplicate a record without the
use indexing as the entire record needs to be checked; not an individual
field. I also need it to take me to the original record listed in the db for
review. If no previous record is found, then allow the record to be added.
Please help. Thank you.

bc

Put
Dim rs As Object
at the beginning of your procedure with the other Dim statement. Then
replace the lines in your procedure from
Cancel = True
to the next
End If
with the snippet I posted.

What I posted is "air code" and may need some modification before it
will work.

Hi John,
Thank you. I'm confused though; where exactly would I put the code you're
giving me in place of or in addition to with my existing code?
Thank you!
bluezcruizer

:

More like this:

Dim rs As Object
...

Cancel = True
Me.Undo
Set rs = Me.Recordset.Clone
rs.FindFirst "[Reseller PO#]='" & varDUPPO & "'"
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
End If
rs.Close
End If
...


On Fri, 12 Jan 2007 14:09:00 -0800, bluezcruizer

Hi all.
I got some very valuable assistance from a couple guys in this group on
DLOOKUP. I'm trying to setup a 'yes/no' message box that is based off the
DLOOKUP and will come up once DLOOKUP tests and verifies a duplicate record.
I've got code for that, and everything works, except the end result on a
"yes" response puts you back to record #1 instead of to the then matching
record; I want a "yes" response to take me to the matching record found.
Here's my code, any thoughts?
To assist, the following fields have the following formats:
[Reseller Account Number] Text
[End User Name] Text
[Amount] Currency

And [dup PO check] is a table name.

Thanks a million guys! This will save alot of headaches!

________________________________________

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim varDUPPO As Variant

If Me.NewRecord Then

varDUPPO = DLookup("[Reseller Account Number]", "[dup PO check]",
"[Reseller Account Number] = '" & Me![Reseller Account Number] & "' AND [End
User Name] = '" & Me![End User Name] & "' AND [Amount] = " & Me![Amount])

If Not IsNull(varDUPPO) Then

If MsgBox("This record already exists." & _
"Cancel this new entry and review the previous
entry? If NO, please put brief reason for duplicate in notes box.", _
vbQuestion + vbYesNo, _
"Duplicate Entry Found") _
= vbYes _
Then
Cancel = True
Me.Undo
Me.Recordset.FindFirst "[Reseller PO#]='" & varDUPPO & "'"

End If

End If

End If

End Sub
_________________________________________


bluezcruizer

 

[John Nurick]

Very good point; didn't even think of that. Concerning your BTW: yes; I've
got an autocount field as a primary key hidden on the form.

That doesn't answer the question. This

DLookup("[Reseller Account Number]", "[dup PO check]",
"[Reseller Account Number] = '" & Me![Reseller Account Number] &
"' AND [End User Name] = '" & Me![End User Name] & "'
AND [Amount] = " & Me![Amount])

suggests to me that your definition of a duplicate record is one with
the same [Reseller Account Number], [End User Name] and [Amount] as an
existing record.

If that's right and you don't want duplicate records, you should make it
impossible to create them by creating a unique index on those three
fields in the underlying table.

I've made the change on the field referral [Reseller Account Number]. That
helped, but it didn't pull the exact match that it refers to in the dialog
box. Do I have to have the entire Dlookup statement restated in the code for
the "yes" statement?
I also don't want to necessarily find first, just find the exact record
identified via the DLOOKUP statement.
At the top, varDUPPO is listed as "Dim varDUPPO As Variant" to identify what
I want to match in the dialog box. So what would I need to change in this
statement to get Access to show the originating record on the form?

Set rs = Me.Recordset.Clone
rs.FindFirst "[Reseller Account Number]='" & varDUPPO & "'"

Remember, I don't know your tables, queries, or business rules. But if
you are using an autonumber field to generate a unique ID number for
each record in this table, then you could use the DLookup() to return
this value. E.g. replace the "[Reseller Account Number]" in the
DLookup() call with "[PO Number]" or whatever the autonumber field is
called.

Then use the autonumber field in the FindFirst.

Alternatively, if you give FindFirst the same criteria (all three
fields) as you gave DLookup(), you'll obviously find the same record.

You said in your first message that

everything works, except the end result on a
"yes" response puts you back to record #1 instead of to the
then matching record.

The snippet I posted doesn't affect the checking for duplicates: it just
changes the bit where you were trying to move to the existing record. So
I think the problem must be in your original procedure.

One thing that catches the eye is that at the beginning of the procedure
you do this:

varDUPPO = DLookup("[Reseller Account Number]", "[dup PO check]",
"[Reseller Account Number] = '" & Me![Reseller Account Number] & "' AND [End
User Name] = '" & Me![End User Name] & "' AND [Amount] = " & Me![Amount])

But then when you want to find the existing record you do

Me.Recordset.FindFirst "[Reseller PO#]='" & varDUPPO & "'"

In other words you first set varDUPPO to the [Reseller Account Number]
of the first (existing) record that matches the form's [Reseller Account
Number], [End User Name] and [Amount]. But you then try to find the
record where [Reseller PO#] equals varDUPPO. This seems wrong to me.

BTW: you do have a unique index on whatever fields you don't want to be
duplicated, don't you?

Hi John,

Thank you. That code didn't work; in testing it, the code allowed me to
enter one record twice, then on the third time recognized that the record
already exists. When I clicked "YES" for deleting additional record and send
me to the original record that was duplicated, it sent me back to the very
first record on the db, which happens not to be the matching record.

I'm still learning VB and very basic. Please forgive my rudimentary skills;
I'll need some help on this.

What I need the code to do is not allow me to duplicate a record without the
use indexing as the entire record needs to be checked; not an individual
field. I also need it to take me to the original record listed in the db for
review. If no previous record is found, then allow the record to be added.
Please help. Thank you.

bc

:

Put
Dim rs As Object
at the beginning of your procedure with the other Dim statement. Then
replace the lines in your procedure from
Cancel = True
to the next
End If
with the snippet I posted.

What I posted is "air code" and may need some modification before it
will work.

On Mon, 15 Jan 2007 06:48:00 -0800, bluezcruizer

Hi John,
Thank you. I'm confused though; where exactly would I put the code you're
giving me in place of or in addition to with my existing code?
Thank you!
bluezcruizer

:

More like this:

Dim rs As Object
...

Cancel = True
Me.Undo
Set rs = Me.Recordset.Clone
rs.FindFirst "[Reseller PO#]='" & varDUPPO & "'"
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
End If
rs.Close
End If
...


On Fri, 12 Jan 2007 14:09:00 -0800, bluezcruizer

Hi all.
I got some very valuable assistance from a couple guys in this group on
DLOOKUP. I'm trying to setup a 'yes/no' message box that is based off the
DLOOKUP and will come up once DLOOKUP tests and verifies a duplicate record.
I've got code for that, and everything works, except the end result on a
"yes" response puts you back to record #1 instead of to the then matching
record; I want a "yes" response to take me to the matching record found.
Here's my code, any thoughts?
To assist, the following fields have the following formats:
[Reseller Account Number] Text
[End User Name] Text
[Amount] Currency

And [dup PO check] is a table name.

Thanks a million guys! This will save alot of headaches!

________________________________________

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim varDUPPO As Variant

If Me.NewRecord Then

varDUPPO = DLookup("[Reseller Account Number]", "[dup PO check]",
"[Reseller Account Number] = '" & Me![Reseller Account Number] & "' AND [End
User Name] = '" & Me![End User Name] & "' AND [Amount] = " & Me![Amount])

If Not IsNull(varDUPPO) Then

If MsgBox("This record already exists." & _
"Cancel this new entry and review the previous
entry? If NO, please put brief reason for duplicate in notes box.", _
vbQuestion + vbYesNo, _
"Duplicate Entry Found") _
= vbYes _
Then
Cancel = True
Me.Undo
Me.Recordset.FindFirst "[Reseller PO#]='" & varDUPPO & "'"

End If

End If

End If

End Sub
_________________________________________


bluezcruizer