How to replace duplicate entries in subform with correct reference ID

[Wim VAN PAEPEGEM]

Dear all,

I am developing a database for a scientific conference. I have 3
related tables:
* table "paper" (fields: title, abstract, filename, session, etc.)
* table "author" (fields: last name, first name, e-mail, affiliation,
etc.)
* table "authors_per_paper" (links the ID paper with several ID's
author)

You can enter the details of the paper in a form, and in a subform of
this form, the authors list is entered for that particular paper. The
author fields are not defined as lookup lists, but as text boxes. If I
enter an author name that is already authoring another paper, a record
already exists in the table "author" and I get an error that duplicate
entries exist in the table "author". What is an elegant way to select
that existing record (with the correct ID) then in the table "author",
while keeping the functionality to enter new records for the other
author names ?
It is a sort of "Autocomplete" function, like in Excel. If the record
already exists, it is correctly referred to, and otherwise, a new
value is entered.

With best regards,
Wim Van Paepegem

 

[Bob Quintal]

(e-mail address removed)
m:

Dear all,

I am developing a database for a scientific conference. I have 3
related tables:
* table "paper" (fields: title, abstract, filename, session, etc.)
* table "author" (fields: last name, first name, e-mail,
affiliation, etc.)
* table "authors_per_paper" (links the ID paper with several ID's
author)

You can enter the details of the paper in a form, and in a subform
of this form, the authors list is entered for that particular
paper. The author fields are not defined as lookup lists, but as
text boxes. If I enter an author name that is already authoring
another paper, a record already exists in the table "author" and I
get an error that duplicate entries exist in the table "author".
What is an elegant way to select that existing record (with the
correct ID) then in the table "author", while keeping the
functionality to enter new records for the other author names ?
It is a sort of "Autocomplete" function, like in Excel. If the
record already exists, it is correctly referred to, and otherwise,
a new value is entered.

With best regards,
Wim Van Paepegem

You have the authors list in the subform.
You should have the "authors_per_paper" table in the subform.
To select the author, you use a combobox set to show the authors
table as the rowsource.

 

[Larry Daugherty]

Use a combobox with Autocomplete enabled and with the NotInList event
enabled. See:

http://www.mvps.org/access/forms/frm0015.htm

HTH

 

[Wim VAN PAEPEGEM]

Hello,

Indeed, I used the "authors_per_paper" table in the subform (I was
probably not clear about that).
According to the suggestion of Larry, I changed the "ID author" in the
"authors_per_paper" table into a lookup combo-box to the "ID author"
in the table "Author" (showing also the last name and first name of
the author in the query, so that I can easily select the right one).
In the subform "authors_per_paper", I selected the field "ID author"
and gave it the settings:
- "Limit to List": No
- "On Not in List": <Event> Macro that opens the form "author" to add
an author (I checked the macro separately and it runs fine)

However, if I open the subform "authors_per_paper", I can now select
any of the existing "ID author" numbers from the combo-box and I am
allowed to just type in a new value for the "ID author", but in
neither case, the form "author" pops up. So I don't see how to trigger
the event "Not in List".

Thanks for the prompt replies !
Wim

 

[Larry Daugherty]

First: I use an offline reader and expect to find the entire thread
in each post. I won't participate in threads that require jumping
back and forth between discrete posts.

I'm not at all sure that we're all talking the same entities when we
talk about subforms and their content. I never use a table in a
subform control. Instead, I use a subform which is a form designed
such that the individual record is the heighth of just over a single
control. I display that form "continuous" within the subform control
in order to display many records.

It you are displaying a table in a subform control and have designed
Lookup Fields (combobox controls) into your tables, then I don't want
to play. Look on www.mvps.org/access for the evils of Lookup Fields
in Tables.

It is the NotInList event of the combobox on the sub form you designed
that's in your subform control that should fire when the Author name
you've entered cannot be found in the list of Authors. BTW it would
seem to me that if an Author ID were not found you'd want to alert the
user of a typo or other error but *not* offer to add a non-existent
ID.

Just found an old instance of NotInList. Your mileage may vary so use
at your own risk....

Private Sub GetShopName_NotInList(NewData As String, Response As
Integer)

On Error GoTo GetShopName_NotInList_error

Dim strMsg As String
Dim mydb As Database
Dim rst As Recordset
Dim CapStr As String

strMsg = "'" & NewData & "' is not a listed shop. "
strMsg = strMsg & "Would you like to add it?"
If MsgBox(strMsg, MB_YESNO, "New Job Shop") = IDNO Then
SendKeys "{ESC}"
Response = DATA_ERRCONTINUE
Else
Response = DATA_ERRADDED

Set mydb = CurrentDb()
Set rst = mydb.OpenRecordset("tblItem")
rst.AddNew
rst!Item = NewData
rst.Update
rst.Close
mydb.Close

DoCmd.OpenForm "frmAddShops", , , , A_ADD ', A_DIALOG
Forms!frmAddShops.Caption = "Adding " & NewData
Forms![frmAddShops]![Item].SetFocus
Forms![frmAddShops]![Item].Text = NewData
Forms![frmAddShops].Modal = True

End If

Exit Sub

HTH

 

[Wim VAN PAEPEGEM]

First:  I use an offline reader and expect to find the entire thread
in each post.  I won't participate in threads that require jumping
back and forth between discrete posts.

I'm not at all sure that we're all talking the same entities when we
talk about subforms and their content.  I never use a table in a
subform control.  Instead, I use a subform which is a form designed
such that the individual record is the heighth of just over a single
control. I display that form "continuous" within the subform control
in order to display many records.

It you are displaying a table in a subform control and have designed
Lookup Fields (combobox controls) into your tables, then I don't want
to play.  Look onwww.mvps.org/accessfor the evils of Lookup Fields
in Tables.

It is the NotInList event of the combobox on the sub form you designed
that's in your subform control that should fire when the Author name
you've entered cannot be found in the list of Authors.  BTW it would
seem to me that if an Author ID were not found you'd want to alert the
user of a typo or other error but *not* offer to add a non-existent
ID.

Just found an old instance of NotInList.  Your mileage may vary so use
at your own risk....

Private Sub GetShopName_NotInList(NewData As String, Response As
Integer)

    On Error GoTo GetShopName_NotInList_error

    Dim strMsg As String
    Dim mydb As Database
    Dim rst As Recordset
    Dim CapStr As String

    strMsg = "'" & NewData & "' is not a listed shop.  "
    strMsg = strMsg & "Would you like to add it?"
    If MsgBox(strMsg, MB_YESNO, "New Job Shop") = IDNO Then
        SendKeys "{ESC}"
        Response = DATA_ERRCONTINUE
    Else
        Response = DATA_ERRADDED

        Set mydb = CurrentDb()
        Set rst = mydb.OpenRecordset("tblItem")
        rst.AddNew
            rst!Item = NewData
        rst.Update
        rst.Close
        mydb.Close

        DoCmd.OpenForm "frmAddShops", , , , A_ADD ', A_DIALOG
        Forms!frmAddShops.Caption = "Adding " & NewData
        Forms![frmAddShops]![Item].SetFocus
        Forms![frmAddShops]![Item].Text = NewData
        Forms![frmAddShops].Modal = True

    End If

Exit Sub

HTH
--
-Larry-
--

Hello,

Indeed, I used the "authors_per_paper" table in the subform (I was
probably not clear about that).
According to the suggestion of Larry, I changed the "ID author" in the
"authors_per_paper" table into a lookup combo-box to the "ID author"
in the table "Author" (showing also the last name and first name of
the author in the query, so that I can easily select the right one).
In the subform "authors_per_paper", I selected the field "ID author"
and gave it the settings:
- "Limit to List": No
- "On Not in List": <Event> Macro that opens the form "author" to add
an author (I checked the macro separately and it runs fine)

However, if I open the subform "authors_per_paper", I can now select
any of the existing "ID author" numbers from the combo-box and I am
allowed to just type in a new value for the "ID author", but in
neither case, the form "author" pops up. So I don't see how to trigger
the event "Not in List".

Thanks for the prompt replies !
Wim- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Dear Larry,

Thank you very much for your help. I have only one minor problem left:
I included a combo box in the subform and the "Not in List" function
is working fine. However, one of the authors that is associated with a
particular paper is the "corresponding author". If I select this
author name, then I would like to add automatically a record to
another table "associated_functions_per_author", where I link the "ID
author" with the "ID function_type" (the last one being the function
type "corresponding author"). However, this record might already be
present, if the author is already "corresponding author" for another
paper.
For example, the "ID author" is 16 and the "ID function_type" is 6. I
want to insert the record (16, 6) in the table
"associated_functions_per_author", but only if that record is not
existing yet.
I looked through the help and the user forum, and most people suggest
to use an "append query". I created an append query that refers to
[Forms]![authors_per_paper_subform]![ID author], but that query seems
to work only for adding *existing* records to another table, so the
append query only wants to append the record (16, 6) if it already
exists in the table "associated_functions_per_author", and has zero
rows if it does not exist yet.
Are there better ways to do this ? And how can I integrate the SQL-
commands from the append query into the VBA code ?

Best regards,
Wim

 

[Larry Daugherty]

It seems that your original issue is resolved. Following the idea of
one issue per thread it would be reasonable to open a new thread to
express and resolve your new issue. There may be others who have no
interest in duplicate entries but who would have an interest in your
new issue.

HTH
--
-Larry-
--

First: I use an offline reader and expect to find the entire thread
in each post. I won't participate in threads that require jumping
back and forth between discrete posts.

I'm not at all sure that we're all talking the same entities when we
talk about subforms and their content. I never use a table in a
subform control. Instead, I use a subform which is a form designed
such that the individual record is the heighth of just over a single
control. I display that form "continuous" within the subform control
in order to display many records.

It you are displaying a table in a subform control and have designed
Lookup Fields (combobox controls) into your tables, then I don't want
to play. Look onwww.mvps.org/accessfor the evils of Lookup Fields
in Tables.

It is the NotInList event of the combobox on the sub form you designed
that's in your subform control that should fire when the Author name
you've entered cannot be found in the list of Authors. BTW it would
seem to me that if an Author ID were not found you'd want to alert the
user of a typo or other error but *not* offer to add a non-existent
ID.

Just found an old instance of NotInList. Your mileage may vary so use
at your own risk....

Private Sub GetShopName_NotInList(NewData As String, Response As
Integer)

On Error GoTo GetShopName_NotInList_error

Dim strMsg As String
Dim mydb As Database
Dim rst As Recordset
Dim CapStr As String

strMsg = "'" & NewData & "' is not a listed shop. "
strMsg = strMsg & "Would you like to add it?"
If MsgBox(strMsg, MB_YESNO, "New Job Shop") = IDNO Then
SendKeys "{ESC}"
Response = DATA_ERRCONTINUE
Else
Response = DATA_ERRADDED

Set mydb = CurrentDb()
Set rst = mydb.OpenRecordset("tblItem")
rst.AddNew
rst!Item = NewData
rst.Update
rst.Close
mydb.Close

DoCmd.OpenForm "frmAddShops", , , , A_ADD ', A_DIALOG
Forms!frmAddShops.Caption = "Adding " & NewData
Forms![frmAddShops]![Item].SetFocus
Forms![frmAddShops]![Item].Text = NewData
Forms![frmAddShops].Modal = True

End If

Exit Sub

HTH
--
-Larry-
--

ups.com...

Indeed, I used the "authors_per_paper" table in the subform (I was
probably not clear about that).
According to the suggestion of Larry, I changed the "ID author" in the
"authors_per_paper" table into a lookup combo-box to the "ID author"
in the table "Author" (showing also the last name and first name of
the author in the query, so that I can easily select the right one).
In the subform "authors_per_paper", I selected the field "ID author"
and gave it the settings:
- "Limit to List": No
- "On Not in List": <Event> Macro that opens the form "author" to add
an author (I checked the macro separately and it runs fine)

However, if I open the subform "authors_per_paper", I can now select
any of the existing "ID author" numbers from the combo-box and I am
allowed to just type in a new value for the "ID author", but in
neither case, the form "author" pops up. So I don't see how to trigger
the event "Not in List".

Thanks for the prompt replies !
Wim- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Dear Larry,

Thank you very much for your help. I have only one minor problem left:
I included a combo box in the subform and the "Not in List" function
is working fine. However, one of the authors that is associated with a
particular paper is the "corresponding author". If I select this
author name, then I would like to add automatically a record to
another table "associated_functions_per_author", where I link the "ID
author" with the "ID function_type" (the last one being the function
type "corresponding author"). However, this record might already be
present, if the author is already "corresponding author" for another
paper.
For example, the "ID author" is 16 and the "ID function_type" is 6. I
want to insert the record (16, 6) in the table
"associated_functions_per_author", but only if that record is not
existing yet.
I looked through the help and the user forum, and most people suggest
to use an "append query". I created an append query that refers to
[Forms]![authors_per_paper_subform]![ID author], but that query seems
to work only for adding *existing* records to another table, so the
append query only wants to append the record (16, 6) if it already
exists in the table "associated_functions_per_author", and has zero
rows if it does not exist yet.
Are there better ways to do this ? And how can I integrate the SQL-
commands from the append query into the VBA code ?

Best regards,
Wim