how can i do this

  • Thread starter Thread starter mrk73
  • Start date Start date
M

mrk73

i am using office 2007

i have a table called titles that has title of the movie and the another
field called year of movie also another table called actor that has actor
name of the actos and another infos

and third table called title-actor which is junction table of the two
previous tables and has the fields of title,year,actor name year

the title and the year is conectted to titles table and the actor name to
the actor name in actor table

now i have form based on each of the two tables and each one has sub form
whichs based one the junction table


like this

the title form has sub form which has actor name

now when i entered a new actor name in this subform is doesnt accpet it
unless it exists in the original actor table


but when the actor name does exists in the actor table it brings it from
there and put it in the third table with the name of title and year of the
movie that i enetr in the form


what i want is to write a program which will check if the actor name does
exists in actor table and then if it doesnt it will add it there and in the
title-actor (junction table) with the corsponding title name and teh year and
if the actor does exists it will not add it in the actor table but only in
juncion table with the corsponding title and year

of course the actor name is index key so is the title and year combination
in title table

thanks
 
mrk73 said:
i am using office 2007

i have a table called titles that has title of the movie and the another
field called year of movie also another table called actor that has actor
name of the actos and another infos

and third table called title-actor which is junction table of the two
previous tables and has the fields of title,year,actor name year

the title and the year is conectted to titles table and the actor name to
the actor name in actor table

now i have form based on each of the two tables and each one has sub form
whichs based one the junction table


like this

the title form has sub form which has actor name

now when i entered a new actor name in this subform is doesnt accpet it
unless it exists in the original actor table


but when the actor name does exists in the actor table it brings it from
there and put it in the third table with the name of title and year of the
movie that i enetr in the form


what i want is to write a program which will check if the actor name does
exists in actor table and then if it doesnt it will add it there and in
the
title-actor (junction table) with the corsponding title name and teh year
and
if the actor does exists it will not add it in the actor table but only in
juncion table with the corsponding title and year

of course the actor name is index key so is the title and year combination
in title table

thanks
Click to expand...

As I understand, you have a Title table (tblTitle) and an Actor table
(tblActor). Since a movie has many actors, and an actor can appear in many
movies, there is a junction table (tblTitleActor) to resolve the
many-to-many relationship. The main form (frmTitle) is based on tblTitle,
with a subform (fsubTitleActor) based on tblTitleActor. I assume the
subform has a combo box (cboActor) based on tblActor.
If this is correct, the subform can be used only to add records to the table
to which it is bound. Since it is bound to tblTitleActor, you can add
records to tblTitleActor. The simplest thing would probably be to set the
Limit to List property of cboActor to False so that if a name is not in the
Actor table you can type it in so it is stored in the junction table only.
Of course, the next time the actor's name is needed you will need to type it
in again, and hope there are no spelling errors when you repeat the process
over and over.
Better may be to add an actor to tblActor using a form bound to tblActor.
One way is to use the Not In List event for the Actor combo box on the
subform to open a form based on tblActor. When you add the actor's name and
close the form, you can use code in the Actor form's Close event to requery
the combo box, something like:
Forms!frmTitle!fsubTitleActor.Form!cboActor.Requery
I'm not sure what you mean by:
"when the actor name does exists in the actor table it brings it from
there and put it in the third table with the name of title and year of the
movie that I enter in the form"
Although using a name as the primary key is not ordinarily a good idea
because of the possibility of duplication, maybe it will work with an actor
name, which *may* be unique, at least for major actors. Minor actors,
especially in older movies, may have the same name. I'm not sure why you
would choose to limit yourself in that way, but in any case there is no "of
course" to using the actor name as the primary key. If you are storing the
full name in one field it will make it difficult to sort by anything other
than first name (assuming you are entering the names in First Last format).
Since the primary key is the actor name, and you are storing the primary key
in the junction table, you are storing the actor name.
 
all what you said ture the sentence that you didnt understand i mean if the
name does exists in the actor table in that case when i type it in the
subform in the title main form the form accept it but if the name doesnt
exists there the form display an error message saying the name should be add
there to actor tabel first what i want is to add the name is the subform and
a program go and check if the name does esxixt in the actor tbale if does
then it will add it only to the junction tbale if it doesnt then it will add
it to the junction table as well as the actor table

yes i use one cell in the table to save the first name and last name what
else i can do i tried to use ids by autonumber but then moer other problems
will arise

so the junction table has actor name and title and movie's year
 
The Actor table stores actor names and maybe other information about the
actors. The Title table stores the movie's title and other specific
information such as year, director, and so forth. Each movie has many
actors, and each actor may be in many movies (a many-to-many relationship),
so the junction table is for TitleActor.
The movie's title would be a poor choice for primary key in the Title table
since many films have been remade.
You could make a compound primary key out of FirstName and LastName and hope
it is enough, but some actors are known by three names or with an initial
(Michael J. Fox, for instance), so you would have to hope there is no actor
named Michael Fox.
What problems will arise by the use of autonumber primary key in the Actor
table?
I decribed a form/subform structure for your database. I would use a combo
box for the actor names, as I described, but you can type them in if you
insist. You could run into problems if you make a typing mistake, and it is
more work, but that is up to you. In any case, if the main form is based on
Title and the subform is based on TitleActor, your best choice is probably
to open an Actor form (or use an input box, maybe) to add the name to the
Actor table. You can use code to copy the new name to the subform, if you
like.
You can use code to check the name against the Actor table, and to generate
a message if the name does not appear there. It may be something like this
in the text box After Update event:
If DCount("[FirstName] + [LastName]", "[tblActor]", "[FirstName] = """ &
Me.FirstName & _
""" AND [LastName] = """ & Me.LastName & """") > 0 Then
MsgBox "Name already exists"
Me.Undo
Cancel = True
End If

You would probably need to use FirstName, MiddleName, LastName, Suffix to
guarantee a unique name, so the code would be more complex than I have
described. Also, you should create a unique index for the combination of
fields. Then maybe you could use an append query to add a new record to the
actor table and to the ActorTitle table. I'm not sure how to go about that,
but a Google groups search for something like:
Append record VBA
should give you some ideas.
If you make a mistake when typing the name you will add a new record, so
that Michael J. Fox and Michael J Fox are two different people as far as the
Actor table is concerned.
Or you could use a combo box Not In List event to open the Actor form. Add
a new record, refresh the combo box row source when you close the Actor
table, and select the new name from the combo box.
 
Back
Top