Results 1 to 6 of 6
  1. #1
    tim19889 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2014
    Posts
    3

    Update Record in Related Table Without Entering Primary Key on Form

    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.

    Click image for larger version. 

Name:	Relationships Access.jpg 
Views:	15 
Size:	49.1 KB 
ID:	15408

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    tim19889 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2014
    Posts
    3
    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
    Last edited by June7; 02-15-2014 at 07:22 PM.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    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
    Attached Thumbnails Attached Thumbnails BoardsAndProblems.jpg  

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    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
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    tim19889 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2014
    Posts
    3
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 07-02-2012, 10:50 PM
  2. Replies: 3
    Last Post: 03-11-2012, 08:20 AM
  3. Go To Record with related table Primary Key
    By jamiebull21 in forum Forms
    Replies: 3
    Last Post: 02-17-2012, 09:36 AM
  4. Replies: 11
    Last Post: 11-13-2011, 06:57 PM
  5. Replies: 2
    Last Post: 10-19-2011, 06:47 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums