Form / Subform Help

[Debris]

Hello,

I have a simple database to track my CD collection -- two tables, tbl_Albums
and tbl_Artists.

Tbl_Artists contains one field, the artist name, and that field is the
primary key. Tbl_Albums contains an AutoNum field for the primary key, the
album name, the associated artist, and some other data. In my Relationships
I have Referential Integrity enforced between the two tables.

I'm trying to make a form and subform. The parent form displays one field,
the artist name, in a combo box. The child form displays the albums
associated with that artist. I built both forms "by hand" -- no wizards
(well, the form/subform wizard).

So the form opens, but when I close/save I receive the following:
The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key, or relationship.
(Error 3022)
I think I understand the error message -- the database is trying to save the
artist name in tbl_Artist, and that name already exists.

But I *think* the bigger problem is that I'm trying to save the artist name
back in tbl_Artist, and I should be saving it in the associated field in
tbl_Albums. And therein lies my problem, and frankly I don't know what to
try next. Is there a way to use a combo box to look up a value in one
table, and store it in another...?

Any help is appreciated. Thanks,

D

 

[NetworkTrade]

you know - from your description everything should work....but obviously it
doesn't...

Consider whipping up a new form real quick and dirty based upon your 2nd
table (Albums...)

Then add a form header - and drag a combo box into it...(actually you really
don't need the header if you don't want)......when the combobox wizard
launches - base it on Table1(Artists) and be sure to select the option when
presented to "select records based on the combobox"...(wording is something
like this in the wizard)...

I think this will work fine this way for you

 

[CW]

I am far from an exper so don't take this as authoritative advice but...I
have had this problem too and have now got my head around what causes it. I
think!
As you have only one field in your Artists table, and have set that as the
primary Key, it will almost certainly have an index upon it and it will be
set to have No Duplicates.
You are trying to enter multiple occurrences of what Access considers to be
a No Duplicates field.
Try adding another field to that table - call it Ref or something, set it to
an Autonumber, and make that new field the Primary Key. You don't have to use
the field in any other way or have it on your form. It will simply take the
unique referencing away from ArtistName, and you should then be able to enter
multiple records.
The other issue might be the Relationship - the type of join. You don't
mention it, so I don't know how you have got this set, but as you want to
have multiple albums by an artist, you need to have it set to Artists-
Albums, with Join type 2 i.e. one to many.
Hope this helps!
CW

 

[Debris]

Hello Again,

Thanks for the tips. Here's how I fixed the problem, please comment --

I changed the RecordSource of the parent form (the one with the "Artist"
combo box) from tbl_Artists to tbl_Albums.
In that combo box in the parent form, I changed the Control Source from the
artist field in tbl_Artists to the one in tbl_Albums.
I then set the Row Source of the combo box to tbl_Artists.

I think what I did was set the combo box to "look up" values from Artists,
but save the record in Albums.

It seems to work so far... comments welcome, I did all of this by pure
trial and error...

D

 

[CW]

It's not the way I would have done it myself but hey, if it works, I'd leave
it at that!