Results 1 to 4 of 4
  1. #1
    jamiebull21 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Posts
    3

    Go To Record with related table Primary Key

    Sorry if the title doesnt make any sense but im really struggling with this one! I don't have extensive knowledge of Microsoft Access and everything I have learnt is self taught so go easy with me.

    I have a database with the 2 main tables; tblContracts and tblEvents. It is linked with the primary key- ID in tblContracts to ContractID in tblEvents so its one one to many relationship.

    I have 3 main forms, frmContactDetails, frmEvent subform and frmEvent. the frmContactDetails is my main point of call and has to 2tabs, 1 which has all the general information of the contract and another tab that shows all the events for that contract. This comes from a subforms (frmEvent subform) and is just a basically summary of any events for that site in datasheet view. I added a button that allows you to open that record, or create a new record and opens the form frmEvent as here you can enter more details.



    If an event has already been created its fine because the ContractID already links to that tblContract. However when you click new and are presented with the frmEvent, the field for ContractID is blank, meaning it doesnt link to that contracts record.

    I have an open form macro with a where condition to take it to the specific record, I wonder if this is where it needs to be sorted out.

    I have attached some images so if anyone has any advice that would be great.
    Thanks,

    Jamie

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I added a button that allows you to open that record, or create a new record and opens the form frmEvent as here you can enter more details
    Since viewing an existing record is different from adding a new record, I think you need separate buttons.

    To add a new record, you have to open frmEvents with it's data entry property set to yes and you have to pass the value of the key field (contractID) to the corresponding control on frmEvents. You will only be able to add 1 new record because if you try to add a second, the contractID will not carry over to that second new record.

  3. #3
    jamiebull21 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Posts
    3
    Ok I'll give it a go with seperate buttons. That leads me on the my next question. The current open button appears in the first column of the datasheet. The control source of this cell is =IIf(IsNull([EventID]),"(New)","Open") with an open form macro attached. I understand this control is saying to show as 'open' if there is a record there, and 'new' to create a new record. Is there anyway I can disable the 'new' option and therefore you only have the option to 'open' existing records?

    Thanks for your help.

  4. #4
    jamiebull21 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Posts
    3
    Quote Originally Posted by jamiebull21 View Post
    Is there anyway I can disable the 'new' option and therefore you only have the option to 'open' existing records?
    No need. Ive found out how!

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

Similar Threads

  1. Replies: 11
    Last Post: 11-13-2011, 06:57 PM
  2. Replies: 2
    Last Post: 10-19-2011, 06:47 AM
  3. Replies: 12
    Last Post: 09-09-2011, 11:14 AM
  4. Replies: 2
    Last Post: 03-25-2011, 12:22 PM
  5. Foce new record in related table?
    By thekruser in forum Access
    Replies: 1
    Last Post: 11-08-2010, 02:00 PM

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