Auto insert into lookup table

[Peter Morris]

Hi all

I never thought I'd be posting a question in the Access group

I have a relative with a very simple 3 table database.

Book 1---* BookIndex *----1 Song

Book
ID AutoNumber
Name Text

Song
ID AutoNumber
Name Text

BookIndex
BookID Number
SongID Number
PageNumber Number

He uses it (surprise surprise) to catalogue which songs are in which books
(music scores) so he can look them up quickly. The problem is that he has
to first ensure that all the songs in the book are in the Songs table. So
his method of working is

1: Add the book
2: Open the song table
3: Try to add the song
4: If it complains of a duplicate hit ESC
5: If no complaint then continue from step 3 until all songs are entered
6: Open BookIndex
7: For each song in the book type in the name of the book + the name of the
song

In step 7 typing in the name of the song will auto-lookup the song ID for
him to give his referential integrity, but it's very time consuming having
to type every song twice. He's asked me if I can change it so that he can
skip steps 3-5 and just type the Song name in the BookIndex table so that
Access looks up the song as usual but adds it to the Song table if it
doesn't already exist.

I normally write DB apps but I know nothing about Access at all. I was
wondering if this is possible, and if so how?


Thanks

 

[tina]

first of all, recommend you *don't* use the word Name as the name of
anything in your database, because it's a Reserved word in Access. the
easiest way to avoid using reserved words is to put a prefix or suffix on
every name you assign in a database. in my example code below, i replaced
Name with songName. also, i personally never use the same name twice in a
single database, to avoid confusion in queries, expressions, and especially
in VBA code. again, easily accomplished when you use a prefix/suffix naming
convention - Book becomes tblBooks, and associated objects might be
qryBooks, frmBooks, rptBooks.

second, you can't do what you're asking at the table level. so if you
haven't created forms for the data entry, that's your next step. and btw, if
you have Lookup fields in the BookIndex table, recommend you remove them -
use ordinary Number data type (fieldsize Long Integer) instead.

next, make sure the tables are linked, and referential integrity enforced,
in the Relationships window.

create a form, Single Form view, bound to table Book, called frmBooks.
create another form, Continuous Forms view (or Datasheet view), bound to
table BookIndex, called sfrmBookIndex. you don't need to bind the BookID
field to a control. bind the SongID field to a combobox control, using table
Song as the RowSource. set the combobox control's properties as

ColumnCount: 2
ColumnWidths: 0";2"
(adjust the width of the second column to suit your needs)
BoundColumn: 1
ListWidth: 2.25"
(adjust ditto above)
LimitToList: Yes

add code to the NoInList event procedure to allow the user to decide whether
or not to add a new song name to the list. note: best not to add new items
automatically, without asking the user, because the event will be triggered
by typos as well as valid new titles.

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

If MsgBox("Do you want to add " & """" _
& NewData & """" & " to the songs list?", _
vbYesNo + vbDefaultButton2 + vbExclamation) = vbYes Then
CurrentDb.Execute "INSERT INTO Song ( songName ) SELECT '" _
& NewData & "'", dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
Me!ControlName.Undo
Me!ControlName.Dropdown
End If

End Sub

recommend you read up on combobox controls, to familiarize yourself with how
they function, and read up on the NotInList Event topic in VBA Help.

finally, in frmBooks, add a subform control and set its' properties as

Name: ChildSongs
SourceObject: sfrmBookIndex
LinkChildFields: BookID
(this is a reference to the foreign key field in table BookIndex)
LinkMasterFields: ID
(this is a reference to the primary key field in table Book)

the user can open frmBooks in Form view, and add/edit/delete book records in
the mainform. in the subform, the user can add/delete songs in the current
book's songlist. no coding is required to properly link subform records to
the record in the mainform - it's handled automatically. the user can also
edit song records in the subform, but keep in mind that a *change* to a song
name in one book will show up in all books that include that song.

hth