Making one Form to update multiple tables

[joshua2008]

Okay I am trying to make one form for data entry to update the records, this is what i got:

tblComic
Comic_ID (PK)
Title_ID (FK)
Publisher_ID (FK)
Artist_ID (FK)
Writer_ID (FK)
Edition
Purchase
Market
Description
Picture

tblTitle
Title_ID (PK)
titleName

tblArtist
Artist_ID (PK)
artistName

tblPublisher
Publisher_ID (PK)
publisherName

tblWriter
Writer_ID (PK)
writerName

What I have tried to make is a form based off my tblComic, but I want comboboxes for titleName and for artistName and for publisherName and for writerName where the user can select the data from the dropdown list already populated in the comboboxes then add the new information for all the other feilds like edition, purchaseprice, market value and when they push add it updates the tblComic by adding the new information in the records. To clarify tblArtist, tblWriter, tblPublisher, already are populated with data, so the user should be able to select these and add the new data such as edition and such then have it update the tblCOmic.
What it is doing thus far is just updating the feilds in tblComic that are NOT FK, just the regualr feilds so it reads like this:
comic_ID|Title_ID|Artist_ID|Writer_ID|Publisher_ID|Eition|
127 |(BLANK) |(BLANK) |(BLANK) |(BLANK) | 128 |

PurchasePrice|Market|Description|Picture|
12.50 |16.00 | BlhaBlah | attch.|

so basically it will update everyhting exscept fro the Forighn Key Feilds which I am trying to call from the combo boxes. I know this is confusing but I cant find anything any where about this. Thanks for the Help

 

[PHV]

You should use bound comboboxes created with the combo wizard.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[joshua2008]

Okay I assuming (correct me if I am wrong) that when you say bound you are refering to the control source in the properties of the combo box? When I do this it only allows me to choose valus from the tblcomics, meaning title_ID etc. When I choose this it only displays the numerical value or if you like the Primary Key and not the actuall Name of the title. ( the user wont that 1 means "titleName". Does that make sense. BUt If I leave it unbound and use:
rowSource: SELECT tblTitle.titleName FROM tblTitle;
i actually see the title name, but dilema time it will not save the value or Primary Key of tbltitle to tblcomic.
I hope this makes sense, I am almost confused writing it lol

 

[joshua2008]

I guess no one knows?

 

[ProgramError]

Somebody does but we are not all in the same place on earth as yourself.
PHV suggested using the wizard, but it sounds as though you have ignored him and tried to set up the combobox manually. Not a wise move for a beginner. Setting up a combobox requires more than just providing the control source. What about the column count, the bound column, the row source and the column widths? Did you set these too?



Ian Mayor (UK)
Program Error
Always make your words short and sweet. Because you never know when you may have to eat them.

 

[joshua2008]

I have done this multiple ways. I have done the wizard and I have done it manually and yes I have set the column count, and the row source and the column widths. And I stated above what happens if i set the bound column

 

[ProgramError]

Then I suggest you check you relationships.

It should be a one to many on the primary keys, one on the tblcomic side and many on the tbltitle etc.

The bound column of the combox should be 1, column count 2,
column widths 0cm;2.54cm (the second column will need to fit the title text) and the row source should be something like SELECT [Table2].[ID], [Table2].[Field2] FROM [Table2];
The field for the title in the tblcomic table should be a numeric field (long integer) and the list width should be the same width as column 2 ie 2.54cm in this case.

When you set up the tblcomic table you could have used a wizard for the combo fields by selecting lookup wizard in the drop down box of the datatype.




Ian Mayor (UK)
Program Error
Always make your words short and sweet. Because you never know when you may have to eat them.