Form based on Table 1, adds new PK to Table 2?

[Nailati]

I’m using Access 2007, and I’m very new to it. I apologize if this has been answered before, but my google-fu has failed me!

My question is regarding forms and PKs. I’d like to create a form that enters data into multiple tables. The catch is that one of the form fields is the PK for one of these tables. When I enter a new value in this field (one that isn’t already defined in the table), I get an error stating that the PK can’t be found (naturally).



I’d like the form to go ahead and add the new PK, or even (if we’re getting fancy) to prompt me and ask whether I’d like to add the PK. Is it possible to do this without disabling referential integrity?

Now, for the specifics. I work in a training library that issues technical manuals to instructors, and I created this DB so that I can see information about the manuals, which courses require them, who has requested them, etc. in one place.

The trouble I’m running into is in creating a single form to enter all the data pertaining to a new request.

I often receive requests for publications that aren’t in my inventory, so there is no related record for them in Publications. I’d like my form to create a new record (by which I mean just the PK, I can fill in the rest later), rather than me running into the error message and having to go back and manually add the pub before I can enter the request.

The request form is based on the Requests table and has the following fields (w/related table and column):

FOR - tbl_Contacts - FullName (PK)
DATE, QTY, etc - tbl_Requests
TMIN - tbl_Publications - TMIN (PK) [TMIN = Technical Manual ID Number]
TITLE, MEDIATYPE, etc - tbl_Publications

It works just fine if I remove the PK fields. (As you can see, I also have the PK issue with the FOR field, though it’s not as much of a problem.)

Here are my table relationships (critiques of my structure are more than welcome!). The relevant ones are Publications, Requests, and Contacts:



I’ve successfully created a Publications form with subforms showing Inventory copies, Requests, and Courses for each pub, so my relationships should be sound.

Not sure if I need to do something creative with subforms or what. I’d like this to be as streamlined and user-friendly as possible, in case I ever need to share the DB with my non-tech-savvy coworkers. I’m definitely open to using VBA if needed.

Any tips would be greatly appreciated!

Edit: Download DB

[June7]

Not understanding why the error for entering a PK. Do you mean entering into the subform as foreign key? If Master/Child links are properly set there should be no reason to enter foreign keys in subform.

If you want to provide the project, I will look at it.

You have FullName as PK in Contacts. Recommend against names as PK. What if you have two Jane Smith? Names should really be broken into separate fields for the name parts (First Middle Last Suffix). This will assure consistency in the name structure so that sorting and filtering can be dependable, i.e. not some entries as Last First and others as First Last.

[Nailati]

The specific error generated is:

The Microsoft Office Access database engine cannot find a record in the table 'tbl_Publications' with key matching field(s) 'TMIN'.

Which makes perfect sense as there is no matching record, but I would like it to create the record. Basically it would be creating two new PKs (the AutoNumber for the new request, and the TMIN for the new pub.)

Thank you for your offer to look at the project. I'm removing personal data from the database and will upload a "clean" version once that's done (later in the day).

As far as the FullName goes, that is a bit lazy and I know it's not the best practice, but I have some reasons for it: I work in a military environment and refer to my contacts by rank and name, which would create the need for 3 name fields. Putting the rank in with the first or last name wouldn't work for my purposes. It also makes it less likely that there will be two people with the same name and rank, especially as I work with less than 100 people. I will consider breaking the FullName field up in the future though.

[Nailati]

Okay, I've uploaded a stripped-down version here:

Download DB

(I'd attach to the post, but it's 3MB.)
Names are fake. Let me know if you have any problems downloading and I'll upload somewhere else. Thanks again!

Edited to add: The form in question is form_Requests_DataEntry.

[June7]

Rank would also be in its own field.

Options:

1. Form/Subform: main form bound to Publications, subform bound to Requests.

2. TMIN could be a combobox so user can select from existing publications. Their selection can be limited to the list. Or can allow entry and use the Not In List event to deal with this new publication.

[Nailati]

Okay, that makes sense. I won't have time to play with the DB until tomorrow, but the second option (with the Not In List event creating a new record in Publications) sounds like what I was trying for--if I can't get that to work, I'll try a subform. Thanks so much for your help.

[June7]

Review this article http://support.microsoft.com/kb/197526