Help Me Build This Form 2 RRS feed

  • Question

  • I have two tables (Client and Case). I have a form for the Client table that is set as a Data Entry form. Users type in new clients and it assigns a new ClientID to that client. I want to create a similar form for the Case table where I create cases for clients that already exist in the Client table. 

    On the Case form I want the user to first select a client name from the Client table. The user can then enter the case information. When the record is saved to the Case table it will include the ClientID associated with the Client chosen by the user. That's one thing I need help with. 

    I don't want to trust the user to simply type the client name into the Case form. I'd like to allow them to do a search. So maybe the Case form could have two sections. The left section allows them to search the Client table for the client they want. They select the one they want from that list and it populates the name on the right side where the remaining case data will be entered.

    Am I making any sense to anybody?

    Suggestions are welcomed.


    Thursday, May 26, 2016 10:39 PM


  • Yes makes sense but it depends on your skills as a developer.

    The much simpler solution is:
    Create frmClients in Form View (one record at a time) based on Clients table.
    Create sfrmCases in Continuous Forms view based on the Cases table.

    Drop sfrmCases on frmClients. Answer the question on how to link the two. The exact behavior will depend on the quality of your database design: make sure you have both tables in the Relationships diagram, drag and drop ClientID from tblClients to tblCases.ClientID, and in the subsequent dialog enforce the relation.

    That's all you need to do. As you scroll through the clients the cases in the subform automatically are the ones for the current client.

    User can use standard Access tools to search for a client in the main form, such as Ctrl+F or right-click a field and filter. If not found, click the * button in the bottom navigation which takes you to a new record, and type away, and save.

    Btw, ClientName is NOT a field in tblClients, only ClientID.

    -Tom. Microsoft Access MVP

    Friday, May 27, 2016 1:22 AM