Update Record in Related Table Without Entering Primary Key on Form

[tim19889]

Hi All,



I am a total Access newbie, so please bear with me here. This is the very first Access database I've ever created. It's almost finished, but I want to make one change to it, that I can't seem to figure out. I'm sure it's something simple to do, but as I said, I'm totally new to Access so I haven't been able to figure it out unfortunately. So first of all, here is what my database currently looks like...

Version: Access 2007

I have three tables, with the following fields:

Table1: Boards
Fields: Board_ID (primary key), Model

Table2: Problems
Fields: Problem_ID (primary key), Problem, Cause, Solution, Category, Comments, CurrentDate, Board_ID, Inspector_ID (these last two fields have relationships with the fields in the other two tables).

Table3: Inspectors
Fields: Inspector_ID (primary key), FirstName, LastName, Email

Table2 is my main transaction table. I have a form called, "Register Board Problem" that comes up by default when the database is open. Currently, when an inspector identifies a problem with a board, they have to enter the primary key (Board_ID) for that board in the form, to update the record in the "Boards" table. I have a button on the form that opens up a report, that lists the board id's and their corresponding model numbers (the model field is set to 'must be unique'). The inspectors can find the board's model number that they are working on, and the corresponding board id, and that is how they enter it into the form to create the new record. What I want to do, if possible, is do away with requiring them to enter the primary key (board_id) on the form at all, and just have them enter the board model number. This would save them time, because they could just type in the board's model number, without having to open up a report that lists a hundred boards, and find the corresponding 'board id' for a specific model of board. Is this possible to do, and if so, how would I do that? Any input would be greatly appreciated! Thanks ahead of time.

Below is what the relationships look like in the database right now.

[June7]

Maybe use combobox? http://www.datapigtechnologies.com/f...combobox3.html

[tim19889]

Hi June,

On the form, if I remove the box where they type in the Board_ID, and try to fill out the form using only the model, it gives me this error when saving: The Microsoft Office Access database engine cannot find a record in the table "Boards" with key matching field(s) "Board_ID".

Here is the database if you want to look at it yourself.


BoardShop Problem Recording.zip

[orange]

I don't know your business, but I have attached a jpg of a model that may be useful to you.
Again, you know your business, so if the model doesn't apply, just ignore it.

Good luck with your project

[June7]

A form can enter/edit records for only one table. An INNER JOIN in query requires records in BOTH table to exist for data to show. Options for form arrangement are:

1. form bound to Problems with comboboxes to select board and inspector

2. form bound to Boards with subform bound to Problems and combobox in subform to select inspector

3. form bound to Inspectors with subform bound to Problems and combobox in subform to select board

[tim19889]

Thanks all for your replies. I think I've finally got it figured out. I decided to change the primary key in table "Boards" from Board ID (autonumber) to Model (text). I then created a one to many relationship between the Model field in table Boards to the model field in table Problems. I created a new form for the table Problems, and now the inspectors will be able to simply type in the model number of the board on the form, along with all the other information, and it will update the record in table Boards with the info.