W
wee
erewrt435dffdg546
K
Ken->
I have a many to many relation between two tables named "Artists" and
"Disciplines".
There is a subform in Artists which shows what disciplines are associated
with the current artist.
There is a button on the Artists form called "Add Discipline" which kicks
off a routine to open another form named "Discipline Select" as a dialog
box. Discipline Select has a multiselect listbox where the user can select
one or more disciplines to add to the artist. The Discipline Select dialog
has a cancel button which just closes the form. It also has an OK button
which calls a routine which is supposed to do all of the work and then close
the form.
I can't figure out what should go into the routine that does the work. I
understand stepping through the ItemsSelected collection, but I don't
understand what to do with the data.
Do I have to add new rows to the junction table and fill them in or do I
need to add the data to the subform on the Artists form and let Access write
to the table.
Also, I made a compound index on the junction table of the two fields and
said that it has to be unique. Can I quitely handle the error that will
come back if someone chooses an discipline for the artist that the artist
already has and just not enter the new record?
Thanks,
Ken
"Disciplines".
There is a subform in Artists which shows what disciplines are associated
with the current artist.
There is a button on the Artists form called "Add Discipline" which kicks
off a routine to open another form named "Discipline Select" as a dialog
box. Discipline Select has a multiselect listbox where the user can select
one or more disciplines to add to the artist. The Discipline Select dialog
has a cancel button which just closes the form. It also has an OK button
which calls a routine which is supposed to do all of the work and then close
the form.
I can't figure out what should go into the routine that does the work. I
understand stepping through the ItemsSelected collection, but I don't
understand what to do with the data.
Do I have to add new rows to the junction table and fill them in or do I
need to add the data to the subform on the Artists form and let Access write
to the table.
Also, I made a compound index on the junction table of the two fields and
said that it has to be unique. Can I quitely handle the error that will
come back if someone chooses an discipline for the artist that the artist
already has and just not enter the new record?
Thanks,
Ken
K
Ken->
I still need help. I think wee was someone who thought Access was a dating
service
service
L
Larry Daugherty
Hi Ken,
A many-to-many relationship between tables requires a third table called a
"junction table". The junction table usually has both parent table names in
its name: tblArtistsDisci;lines. The primary keys of the parent tables
appear as foreign keys in the junction table. The junction table can have
other fields for things that apply just to the current instance.
HTH
A many-to-many relationship between tables requires a third table called a
"junction table". The junction table usually has both parent table names in
its name: tblArtistsDisci;lines. The primary keys of the parent tables
appear as foreign keys in the junction table. The junction table can have
other fields for things that apply just to the current instance.
HTH
K
Ken->
I have the junction table already and it is named Artist SubDiscipline.
Does it matter that there is a space in the table name and that it doesn't
start with tbl?
What I need to do is fill in the junction table to show what disciplines the
artist has.
Does it matter that there is a space in the table name and that it doesn't
start with tbl?
What I need to do is fill in the junction table to show what disciplines the
artist has.
J
John Vinson
Access doesn't care WHAT the name is. You could call it George or
[xE32YY78] if you wished. The only drawbacks to having spaces in the
name are that you must always use [Square brackets] around the name in
Queries and in code, and it will make it harder to upgrade to
SQL/Server if you should decide to do so.
The *simplest* way to do this is to not use a Listbox, but instead to
use a continuous Subform based on [Artist SubDiscipline] with a combo
box displaying the discipline and storing the foreign key to the
Discipline table.
If you really want to use the Listbox, I've got some VBA code to do
so: you'll need to adapt the table and fieldnames to your own
application.
Private Sub cmdProcess_Click()
' Comments : Update the AnimalCondition table based on the
' selections in the unbound multiselect listbox lstHealthIssues.
' Newly selected rows will be added to the table,
' newly cleared rows will be deleted.
' Parameters: None
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
' Populate the AnimalCondition table with the selected issues
On Error GoTo PROC_ERR
Dim iItem As Integer
Dim lngCondition As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
' save the current record if it's not saved
If Me.Dirty = True Then
Me.Dirty = False
End If
Set db = CurrentDb
' Open a Recordset based on the table
Set rs = db.OpenRecordset("AnimalCondition", dbOpenDynaset)
With Me!lstHealthIssues
' Loop through all rows in the Listbox
For iItem = 0 To .ListCount - 1
lngCondition = .Column(0, iItem)
' Determine whether this AnimalID-HealthID combination is
currently
' in the table
rs.FindFirst "[AnimalID] = " & Me.AnimalID & " AND " _
& "[HealthIssueID] = " & lngCondition
If rs.NoMatch Then ' this item has not been added
If .Selected(iItem) Then
' add it
rs.AddNew
rs!AnimalID = Me.AnimalID
rs!HealthIssueID = lngCondition
rs.Update
End If ' if it wasn't selected, ignore it
Else
If Not .Selected(iItem) Then
' delete this record if it's been deselected
rs.Delete
End If ' if it was selected, leave it alone
End If
Next iItem
End With
rs.Close
Set rs = Nothing
Set db = Nothing
Me.subAnimalCondition.Requery
PROC_EXIT:
Exit Sub
PROC_ERR:
MsgBox "Error " & Err.Number & " in cmdProcess_Click:" _
& vbCrLf & Err.Description
Resume PROC_EXIT
End Sub
John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
K
Ken->
Thanks John,
I think I understand it but I get an error on this statement
Dim db As DAO.Database
The error is
Compiler Error
User-defined type not defined
I tried removing the DAO but that had no effect. What must I do so that
DAO.Database is defined?
I hate exposing my ignorance so much.
Thanks,
Ken
I think I understand it but I get an error on this statement
Dim db As DAO.Database
The error is
Compiler Error
User-defined type not defined
I tried removing the DAO but that had no effect. What must I do so that
DAO.Database is defined?
I hate exposing my ignorance so much.
Thanks,
Ken
John Vinson said:
J
John Vinson
Open the VBA editor, and select Tools... References from the menu.
Scroll down until you find
Microsoft DAO x.xx Object Library
and check the highest version.
It's Microsoft's infuriating habit of setting incorrect defaults
that's to blame, not you.
John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
K
Ken->
John,
Thank you very much. I have this part working just like I hoped it would.
I didn't respond sooner because of the usual glitches you have to work out
and I thought I was going to have to ask for more help.
Thanks again,
Ken
Thank you very much. I have this part working just like I hoped it would.
I didn't respond sooner because of the usual glitches you have to work out
and I thought I was going to have to ask for more help.
Thanks again,
Ken