Copying ID from one table to another

  • Thread starter Thread starter Pic
  • Start date Start date
P

Pic

My database used to be built on a single table. I recently split it
because I needed to use one of the fields as a lookup for another
field, and I got error messages telling me I couldn't use a field from
the same table as a lookup.

The tables are related by a field called "WorkID". In the table
"Works" this is an autonumber field. In the table "WorkDetails", this
is a number field.

I build a query from both tables to underlie the form I use for data
entry and retrieval. It contains both "WordID" fields. Everything
works fine except for one thing. When I try to add a new record, the
WorkID number from the Works table is not picked up in the WorkDetails
table.

Can anyone tell my what my mistake is and how I might fix it?

Thanks in advance,
Pic
 
If you add a new lookup value to your lookup table, why would Access need to
add it to your "main" table? Perhaps you are using the term "lookup" in a
way that varies from how Access describes this?

If you are using forms for your data entry (and please do, tables are great
for storing data, but forms are designed to display/add/edit), create a
combo box with your lookup table as a row source. Bind this control to your
main table's WorkID field. This gives you a mechanism for adding a new
(main) record and selecting the appropriate WorkID (from the lookup table).

You can also use the NotInList event of that combo box to add a new lookup
value.
 
Hi Jeff:
Thanks for your reply. Since you're the gent who replied to my
previous post (quoted below), you might recall what I'm trying to do.

This database tracks my writings, which fall into five genres: Plays,
Musicals, Monologues, Lyrics, and Other. Each genre has its own set
of details. For example, Plays and Musicals have "Number of acts",
Monologues, Lyrics and Other do not.

For data entry and retrieval, I have one form for all works, others
for details pertaining to each of the five genres.

One of the detail fields for Lyrics is "Show Source" ("Which Musical
does it come from?"). Having this also allows me to make a query
listing all the songs in a particular Musical. To populate this field
for any Lyric record, I need a combo box built on a query listing all
of my Musicals.

Access wouldn't allow me to make that combo box while all my fields
were in one table. That's why I split it into two tables, retaining
the "WorkID" field in each of them so I could relate them. All
fields common to all genres are in one table, all details regardless
of genre are in the other. I'm sure my table structure is not ideal,
but that's what I came up with.

The existing forms are now built on queries
recombining the two tables. Everything seems to be working except
that when I add a new record, its row in the second table doesn't pick
up the ID field from its associated field in the first table.

I know I'm probably trying to do more than I have the ability to do,
but I seem so close. Any ideas?

Thanks again,
Pic

If you add a new lookup value to your lookup table, why would Access need to
add it to your "main" table? Perhaps you are using the term "lookup" in a
way that varies from how Access describes this?

If you are using forms for your data entry (and please do, tables are great
for storing data, but forms are designed to display/add/edit), create a
combo box with your lookup table as a row source. Bind this control to your
main table's WorkID field. This gives you a mechanism for adding a new
(main) record and selecting the appropriate WorkID (from the lookup table).

You can also use the NotInList event of that combo box to add a new lookup
value.
Click to expand...


Not really sure I understand your design, but the error message you
reported
makes it sound like you are trying to put more than one fact in a
field.
And it also implies that you are trying to do a lookup of a lookup.

Just a hunch, but do a search on "cascading combo boxes" and see if
this
isn't what you are trying to do.

Also, before you push further on this, step back and diagram the
relationships -- your explanation seems to indicate that the Genre
"Lyrics"
has related "Musicals", and the Genre "Musicals" has related "Lyrics".
Is
this a many-to-many relationship?

Your post also implies that you are using the "lookup" data type in
your
table definition -- if so, review this newsgroup's (tablesdbdesign)
postings
on the topic -- there are a LOT of reasons not to use this "feature"
in your
table definitions.
 
I'm having trouble visualizing your data. Could you post a listing of
tables and fields? Perhaps something like:

tblOrder
OrderID (PK - autonumber)
OrderDate
OrderSalesRepID (FK, from tlkpSalesRep)
...

trelOrderDetail
OrderDetailID
OrderID (FK, from tblOrder)
ProductID (FK, from tblProduct)
Quantity
...
 
Hi Jeff:
I took your advice and read the postings on the evils of lookup
fields. I then got rid of them, rejoined the tables I'd previously
split, and created combo boxes on the forms per your advice. Things
seem to be working better now, but there's still some problems, the
most annoying being that sometimes when I add a record to the
DevelopHist table (info about new drafts of an existing Work), a new,
blank record is mysteriously added to the Works table.

Here are my present tables. "Works" is related one-to-many with each
of the other two tables. I welcome any additional advice you have to
offer. Otherwise, thanks again for the advice already given.

Best,
Pic


tbl Works
WorkID Autonumber
Genre Text
Title Text
DateOfOrigin Date/Time
Length Text
Style Text
Link Anchor
AddNotes Anchor
Musical Yes/No
Notes Memo
Chooser Yes/No
Top Yes/No
OneLine Memo
Synopsis Memo
THIS IS WHERE I PREVIOUSLY SPLIT THE TABLE
NumberSets Double
MinCastSize Long Integer
Performable Yes/No
AlmostPerformable Yes/No
MonoMalcontent Yes/No
MonoEastNewYork Yes/No
MonoMyVoice Yes/No
MonoMurray Yes/No
MonoFirstLine Memo
MonoRunTime Long Integer
LyrSetToMusic Yes/No
LyrBucky? Yes/No
LyrNumberVoice Double
LyrForm Text
LyrHook Text
LyrComposer Text
LyrShowSource Text (from combo box on form)
LyrOtherSource Text


tblCharacters
CharacterID Autonumber
WorkID Long Integer (from tblWorks)
Name Text
Gender Text
AgeIdeal Long Integer
AgeMaximum Long Integer
AgeMinimum Long Integer
Description Memo


tblDevelopHist
DevelopHistID Autonumber
WorkID Long Integer (from tblWorks)
Date Date/Time
Draft Text
DraftStatus Text
CurrentBinder Text
TabFileName Text
FileName Text
NextStep Memo
History Memo
 
See comments in-line...

Pic said:
Hi Jeff:
I took your advice and read the postings on the evils of lookup
fields. I then got rid of them, rejoined the tables I'd previously
split, and created combo boxes on the forms per your advice. Things
seem to be working better now, but there's still some problems, the
most annoying being that sometimes when I add a record to the
DevelopHist table (info about new drafts of an existing Work), a new,
blank record is mysteriously added to the Works table.
Click to expand...

How do you "add a record to the DevelopHist table"? If you have a main form
that holds your Works info, and a subform that holds your DevelopHist info,
and the subform has the parent/child link properties defined, I don't
understand how you can add a record to DevelopHist WITHOUT a main/Works
record.
Here are my present tables. "Works" is related one-to-many with each
of the other two tables. I welcome any additional advice you have to
offer. Otherwise, thanks again for the advice already given.

Best,
Pic


tbl Works
WorkID Autonumber
Genre Text
Click to expand...

If Genre (and others) are found in related look up tables, does this mean
the primary key of Genre (and others) is of data type = Text?
Title Text
DateOfOrigin Date/Time
Length Text
Style Text
Link Anchor
Click to expand...

I am unfamiliar with the "Anchor" data type.
AddNotes Anchor
....ditto

Musical Yes/No
Notes Memo
Chooser Yes/No
Top Yes/No
OneLine Memo
Synopsis Memo
THIS IS WHERE I PREVIOUSLY SPLIT THE TABLE
NumberSets Double
MinCastSize Long Integer
Performable Yes/No
AlmostPerformable Yes/No
MonoMalcontent Yes/No
MonoEastNewYork Yes/No
MonoMyVoice Yes/No
MonoMurray Yes/No
MonoFirstLine Memo
MonoRunTime Long Integer
LyrSetToMusic Yes/No
LyrBucky? Yes/No
LyrNumberVoice Double
LyrForm Text
LyrHook Text
LyrComposer Text
LyrShowSource Text (from combo box on form)
LyrOtherSource Text
Click to expand...

Whenever I see multiple repeating Y/N fields, I wonder about normalization.
If any one entry could have zero, one or many of these set to "Yes",
consider keeping this kind of "property" information in a related table.
 
Back
Top