Macro to select value from Form to enter into new record RRS feed

  • Question

  • I am creating an database with a Client table and a Contracts table. Clients can have multiple contracts.

    I would like to create a macro  to add to a form that takes a value (ClientID) from the Client table to enter into a form to create a new Contract record. 

    So far, I've only be able to figure out a way to open a form that shows the existing Contracts for a specific ClientID or open a blank form. 

    How can I make this macro happen?

    Thank you

    Wednesday, March 29, 2017 11:48 PM

All replies

  • There is no such need for a macro or any other code if you do this:

    1. Create parent form based on tblClients

    2. Create subform (form in datasheet view) based on tblContracts

    3. Drop the subform on the parent form.

    4. Either the wizard will prompt you (finer points omitted) or you will have to set this yourself: the subform's LinkMasterFields and LinkChildFIelds each would be set to ClientID.


    Observe that Access will automatically insert the ClientID FK in the child record. Zero code needed.
    This technique is part of "going with the grain" of how Access is supposed to be used.

    -Tom. Microsoft Access MVP

    Thursday, March 30, 2017 4:29 AM
  • Hello,

    Where do you want to get the ClientID value?You could get value which is in the form based on Client Table via Forms!FormName!ControName.Text.
    And then you could add the value into your insert sql string.After you create the insert sql string with the other info you need,you could use DoCmd.RunSQL to run the sql.
    Here is the example

    Dim IDStr, NameStr, SubStr, InsertSql As String
    IDStr = Forms!client!ClientID.Text
    NameStr = "New Name"
    SubStr = "New Sub"
    InsertSql = "Insert into Contracts(ClientID,ContractName,ContractSubject) values('" & IDStr & "','" & NameStr & "','" & SubStr & "')"
    DoCmd.RunSQL InsertSql

    Best Regards,

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 30, 2017 9:31 AM