Add a Duplicate Record


E

esee

How do I add duplicate Bank Names with the events I am currently
using?

Private Sub combo68_AfterUpdate()
Dim rs As DAO.Recordset
If intRQ Then
Me.Requery
DoEvents
intRQ = False
End If
Set rs = Me.Recordset.Clone
' If BankID is a number here, do it this way:
rs.FindFirst "[BankID] = " & Me![Combo68]
' If BankID is text, then do it this way:
'rs.FindFirst "[BankID] = '" & Me!Combo68 & "'"
If rs.NoMatch Then
MsgBox "Record not found"
Else
Me.Bookmark = rs.Bookmark
End If
rs.Close
Set rs = Nothing

End Sub

Private Sub Combo68_NotInList(NewData As String, Response As Integer)
If vbYes = MsgBox("Do you want to add this NEW Bank?", vbYesNo)
Then
CurrentDb.Execute "INSERT INTO tblBanks(BankName) VALUES( """ &
NewData & """);", dbFailOnError
Response = acDataErrAdded ' Does an automatic requery of the
combo
' Tell after update that a Requery is needed for the new row
intRQ = True
Else
Response = acDataErrContinue
End If
End Sub
 
Ad

Advertisements

J

Jeff Boyce

Why would you want more than one? Which one is the "right" one? How many
do you have to look for to find all of your data?

If you'll describe why you think you need "duplicate bank names", folks here
may be able to offer suggestions.

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.
 
E

esee

Why would you want more than one?  Which one is the "right" one?  Howmany
do you have to look for to find all of your data?

If you'll describe why you think you need "duplicate bank names", folks here
may be able to offer suggestions.

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.


How do I add duplicate Bank Names with the events I am currently
using?
Private Sub combo68_AfterUpdate()
   Dim rs As DAO.Recordset
   If intRQ Then
       Me.Requery
       DoEvents
       intRQ = False
   End If
   Set rs = Me.Recordset.Clone
   ' If BankID is a number here, do it this way:
   rs.FindFirst "[BankID] = " & Me![Combo68]
   ' If BankID is text, then do it this way:
  'rs.FindFirst "[BankID] = '" & Me!Combo68 & "'"
   If rs.NoMatch Then
       MsgBox "Record not found"
   Else
       Me.Bookmark = rs.Bookmark
   End If
   rs.Close
   Set rs = Nothing
Private Sub Combo68_NotInList(NewData As String, Response As Integer)
    If vbYes = MsgBox("Do you want to add this NEW Bank?", vbYesNo)
Then
     CurrentDb.Execute "INSERT INTO tblBanks(BankName) VALUES( """ &
NewData & """);", dbFailOnError
       Response = acDataErrAdded  ' Does an automatic requery of the
combo
       ' Tell after update that a Requery is needed for the newrow
       intRQ = True
    Else
       Response = acDataErrContinue
    End If
End Sub

"Why would you want more than one? Which one is the "right" one? How
many
do you have to look for to find all of your data?"
------------------------------------------------------------------
The code I am using only allows me to find and add one bank. I have
since changed the table to allow duplicates, because there are banks
that have duplicate names. I am currently adding the duplicates to
the table by adding a "2" to the end of the duplicate bank name, and
then going to the table and removing the "2". Works well, but of
course tedious.

I distinguish the duplicate banks with a yes/no field that I have
added. I will probably change this to a different type of field in
the future, if I come up with more than one duplicate bank (which I
don't expect).

So for now, I just want to add a single duplicate bank name that will
have the yes/no field checked. When I enter (find) the bank name, the
after update procedure will take me to bank name entered, and the
"right" one will have the yes/no field on or off (as I choose). There
should be no more than one duplicate.

Please, let me know if you need additional information.
 
J

Jeff Boyce

I'm still having a bit of trouble figuring out how that will work out in the
long run.

Say you have two banks with the same name (after your cleanup, you have two
"Giant Humongous Bank"s).

But you know that they are actually two different banks.

If you try to look up data pertaining to one of them, by the name of the
bank, you won't be able to tell which is which.

Even if you have a checkbox so that one GHB is checked and the other isn't,
what do you do to distinguish them when a THIRD GHB comes along? (I don't
know, in your situation, this may NEVER happen ... but what if?)

Does your table of BankNames have a primary key? If so, that would be how
you could keep three or four GHBs separate. But why are you limited to
no-duplicates? Can't you modify the table structure to allow duplicates in
that BankName field?

(I guess I'm still not clear on what you have and what you are trying to
accomplish by doing this...)

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.


Why would you want more than one? Which one is the "right" one? How many
do you have to look for to find all of your data?

If you'll describe why you think you need "duplicate bank names", folks
here
may be able to offer suggestions.

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.


How do I add duplicate Bank Names with the events I am currently
using?
Private Sub combo68_AfterUpdate()
Dim rs As DAO.Recordset
If intRQ Then
Me.Requery
DoEvents
intRQ = False
End If
Set rs = Me.Recordset.Clone
' If BankID is a number here, do it this way:
rs.FindFirst "[BankID] = " & Me![Combo68]
' If BankID is text, then do it this way:
'rs.FindFirst "[BankID] = '" & Me!Combo68 & "'"
If rs.NoMatch Then
MsgBox "Record not found"
Else
Me.Bookmark = rs.Bookmark
End If
rs.Close
Set rs = Nothing
Private Sub Combo68_NotInList(NewData As String, Response As Integer)
If vbYes = MsgBox("Do you want to add this NEW Bank?", vbYesNo)
Then
CurrentDb.Execute "INSERT INTO tblBanks(BankName) VALUES( """ &
NewData & """);", dbFailOnError
Response = acDataErrAdded ' Does an automatic requery of the
combo
' Tell after update that a Requery is needed for the new row
intRQ = True
Else
Response = acDataErrContinue
End If
End Sub

"Why would you want more than one? Which one is the "right" one? How
many
do you have to look for to find all of your data?"
------------------------------------------------------------------
The code I am using only allows me to find and add one bank. I have
since changed the table to allow duplicates, because there are banks
that have duplicate names. I am currently adding the duplicates to
the table by adding a "2" to the end of the duplicate bank name, and
then going to the table and removing the "2". Works well, but of
course tedious.

I distinguish the duplicate banks with a yes/no field that I have
added. I will probably change this to a different type of field in
the future, if I come up with more than one duplicate bank (which I
don't expect).

So for now, I just want to add a single duplicate bank name that will
have the yes/no field checked. When I enter (find) the bank name, the
after update procedure will take me to bank name entered, and the
"right" one will have the yes/no field on or off (as I choose). There
should be no more than one duplicate.

Please, let me know if you need additional information.
 
E

esee

I'm still having a bit of trouble figuring out how that will work out in the
long run.

Say you have two banks with the same name (after your cleanup, you have two
"Giant Humongous Bank"s).

But you know that they are actually two different banks.

If you try to look up data pertaining to one of them, by the name of the
bank, you won't be able to tell which is which.

Even if you have a checkbox so that one GHB is checked and the other isn't,
what do you do to distinguish them when a THIRD GHB comes along?  (I don't
know, in your situation, this may NEVER happen ... but what if?)

Does your table of BankNames have a primary key?  If so, that would be how
you could keep three or four GHBs separate.  But why are you limited to
no-duplicates?  Can't you modify the table structure to allow duplicates in
that BankName field?

(I guess I'm still not clear on what you have and what you are trying to
accomplish by doing this...)

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.


Why would you want more than one? Which one is the "right" one? How many
do you have to look for to find all of your data?
If you'll describe why you think you need "duplicate bank names", folks
here
may be able to offer suggestions.

Jeff Boyce
Microsoft Access MVP
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.
How do I add duplicate Bank Names with the events I am currently
using?
Private Sub combo68_AfterUpdate()
Dim rs As DAO.Recordset
If intRQ Then
Me.Requery
DoEvents
intRQ = False
End If
Set rs = Me.Recordset.Clone
' If BankID is a number here, do it this way:
rs.FindFirst "[BankID] = " & Me![Combo68]
' If BankID is text, then do it this way:
'rs.FindFirst "[BankID] = '" & Me!Combo68 & "'"
If rs.NoMatch Then
MsgBox "Record not found"
Else
Me.Bookmark = rs.Bookmark
End If
rs.Close
Set rs = Nothing
End Sub
Private Sub Combo68_NotInList(NewData As String, Response As Integer)
If vbYes = MsgBox("Do you want to add this NEW Bank?", vbYesNo)
Then
CurrentDb.Execute "INSERT INTO tblBanks(BankName) VALUES( """ &
NewData & """);", dbFailOnError
Response = acDataErrAdded ' Does an automatic requery of the
combo
' Tell after update that a Requery is needed for the new row
intRQ = True
Else
Response = acDataErrContinue
End If
End Sub

"Why would you want more than one?  Which one is the "right" one?  How
many
do you have to look for to find all of your data?"
------------------------------------------------------------------
The code I am using only allows me to find and add one bank.  I have
since changed the table to allow duplicates, because there are banks
that have duplicate names.  I am currently adding the duplicates to
the table by adding a "2" to the end of the duplicate bank name, and
then going to the table and removing the "2".  Works well, but of
course tedious.

I distinguish the duplicate banks with a yes/no field that I have
added.  I will probably change this to a different type of field in
the future, if I come up with more than one duplicate bank (which I
don't expect).

So for now, I just want to add a single duplicate bank name that will
have the yes/no field checked.  When I enter (find) the bank name, the
after update procedure will take me to bank name entered, and the
"right" one will have the yes/no field on or off (as I choose). There
should be no more than one duplicate.

Please, let me know if you need additional information.


I'm sorry I'm not explaining this well.

The BankProfile table allows for duplicate BankNames and is sorted by
BankNames and the "duplicate" yes/no field. The BankProfile table has
a Primary Key BankID, which does not allow duplicates.
With the current After_Update event that I have shown, I will find
first of duplicate banks, and a simple Page down key press will
display the second (next) duplicate bank.

"Even if you have a checkbox so that one GHB is checked and the other
isn't,
what do you do to distinguish them when a THIRD GHB comes along? (I
don't
know, in your situation, this may NEVER happen ... but what if?)"

Good question, but this will probably never happen, and I don't know
the answer to your question.

"Does your table of BankNames have a primary key? If so, that would
be how
you could keep three or four GHBs separate. But why are you limited
to
no-duplicates? Can't you modify the table structure to allow
duplicates in
that BankName field?"

You are suggesting exactly my situation as it is now. BankNames have
a primary Key BankID. BankNames are NOT limited to no-duplicates.

So again, the problem is, how does my "Not In List" event add a
BankName which already exists?

Thanks for your questions...
 
Ad

Advertisements

J

Jeff Boyce

Now I'm totally turned around. I thought your original post was asking how
to store a duplicate Bank Name?

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'm still having a bit of trouble figuring out how that will work out in
the
long run.

Say you have two banks with the same name (after your cleanup, you have
two
"Giant Humongous Bank"s).

But you know that they are actually two different banks.

If you try to look up data pertaining to one of them, by the name of the
bank, you won't be able to tell which is which.

Even if you have a checkbox so that one GHB is checked and the other
isn't,
what do you do to distinguish them when a THIRD GHB comes along? (I don't
know, in your situation, this may NEVER happen ... but what if?)

Does your table of BankNames have a primary key? If so, that would be how
you could keep three or four GHBs separate. But why are you limited to
no-duplicates? Can't you modify the table structure to allow duplicates in
that BankName field?

(I guess I'm still not clear on what you have and what you are trying to
accomplish by doing this...)

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.


Why would you want more than one? Which one is the "right" one? How many
do you have to look for to find all of your data?
If you'll describe why you think you need "duplicate bank names", folks
here
may be able to offer suggestions.

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.
How do I add duplicate Bank Names with the events I am currently
using?
Private Sub combo68_AfterUpdate()
Dim rs As DAO.Recordset
If intRQ Then
Me.Requery
DoEvents
intRQ = False
End If
Set rs = Me.Recordset.Clone
' If BankID is a number here, do it this way:
rs.FindFirst "[BankID] = " & Me![Combo68]
' If BankID is text, then do it this way:
'rs.FindFirst "[BankID] = '" & Me!Combo68 & "'"
If rs.NoMatch Then
MsgBox "Record not found"
Else
Me.Bookmark = rs.Bookmark
End If
rs.Close
Set rs = Nothing
End Sub
Private Sub Combo68_NotInList(NewData As String, Response As Integer)
If vbYes = MsgBox("Do you want to add this NEW Bank?", vbYesNo)
Then
CurrentDb.Execute "INSERT INTO tblBanks(BankName) VALUES( """ &
NewData & """);", dbFailOnError
Response = acDataErrAdded ' Does an automatic requery of the
combo
' Tell after update that a Requery is needed for the new row
intRQ = True
Else
Response = acDataErrContinue
End If
End Sub

"Why would you want more than one? Which one is the "right" one? How
many
do you have to look for to find all of your data?"
------------------------------------------------------------------
The code I am using only allows me to find and add one bank. I have
since changed the table to allow duplicates, because there are banks
that have duplicate names. I am currently adding the duplicates to
the table by adding a "2" to the end of the duplicate bank name, and
then going to the table and removing the "2". Works well, but of
course tedious.

I distinguish the duplicate banks with a yes/no field that I have
added. I will probably change this to a different type of field in
the future, if I come up with more than one duplicate bank (which I
don't expect).

So for now, I just want to add a single duplicate bank name that will
have the yes/no field checked. When I enter (find) the bank name, the
after update procedure will take me to bank name entered, and the
"right" one will have the yes/no field on or off (as I choose). There
should be no more than one duplicate.

Please, let me know if you need additional information.


I'm sorry I'm not explaining this well.

The BankProfile table allows for duplicate BankNames and is sorted by
BankNames and the "duplicate" yes/no field. The BankProfile table has
a Primary Key BankID, which does not allow duplicates.
With the current After_Update event that I have shown, I will find
first of duplicate banks, and a simple Page down key press will
display the second (next) duplicate bank.

"Even if you have a checkbox so that one GHB is checked and the other
isn't,
what do you do to distinguish them when a THIRD GHB comes along? (I
don't
know, in your situation, this may NEVER happen ... but what if?)"

Good question, but this will probably never happen, and I don't know
the answer to your question.

"Does your table of BankNames have a primary key? If so, that would
be how
you could keep three or four GHBs separate. But why are you limited
to
no-duplicates? Can't you modify the table structure to allow
duplicates in
that BankName field?"

You are suggesting exactly my situation as it is now. BankNames have
a primary Key BankID. BankNames are NOT limited to no-duplicates.

So again, the problem is, how does my "Not In List" event add a
BankName which already exists?

Thanks for your questions...
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top