none
Problem adding Value to Field in Data Entry Form RRS feed

  • Question

  • This is another episode in my quest to build the idea set of data entry forms. Previous episodes are available in this forum. :0)

    First some backstory:

    I have to tables, Clients and Cases. Client is a table of...well..clients. I have a primary key named ClientID which is an autonumber. Cases is a table of cases that are associated with these clients. One client has many cases. The Cases table has as a foreign key which is the ClientID that matches the ClientID in the Client table. That's how I join them. The ClientID in the Cases table is a long integer. 

    I have a form for each table. I've completed the Client form that allows users to enter new clients. All the fields are bound to the fields in the table and the form is set to data entry = true. When the user enters a new client the ClientID is set by the database. I have a submit button that updates the table with the new client. It works. 

    I have another form that has two parts. On the left are two text boxes, first name and last. User enters at least a last name if not the first and last name then clicks a button that queries the Client table and returns results to a list box. So the user may enter Smith as the last name and all the clients whose last name is smith appear in the listbox along with their respective ClientIDs. So then the user can double click the name they want. 

    On the right side of the form (I promise I'm almost done) I have fields that are bound to the Case table, EXCEPT for the ClientID, first name and last name. When the user double clicks the name from the list box I enter the first and last name to the right side just for the users benefit. It doesn't get updated to the Cases table. 

    HOWEVER, I want the ClientID from the listbox to poplulate the ClientID field on the right side. I tried using the ClientID field from the Cases table, but I get an error that the value can't be entered to that field. So I tried an unbound field for ClientID. That solved the error problem, BUT now I don't know how to update it into the Cases table. All the other fields get updated because they are bound to the table. How do I get the ClientID to go with it? OR..better yet how can I just put the ClientID field from the Cases table and have it poplulate?

    If you need more info. the..you really do have a lot of free time! However, just ask and I will reply. 

    tod

    Wednesday, June 1, 2016 11:52 PM

Answers

  • It sounds like you are trying to use your list box as a search feature to bring up a client... AND related cases (if any exist). Is that correct?

    If so, lets break this down into two parts:

    1. Displaying clients and related cases

    2. Using the listbox to filter/display a particular client

    For the first step (Clients and related cases), Access subforms are specifically designed to handle this type of parent/child relationship.  See this tutorial:

    Create a form that contains a subform

    That tutorial uses employees/orders as an example.  In your case, the mainform would be bound to your client table, the subform would be bound to your cases table, and the ClientID would be the master/child link between the two.  With this setup, existing case records will automatically display with their respective clients, and the master/child links will ensure that the clientID automatically gets inserted into new case records.

    Regarding the second part of your question, the listbox search feature, the layout you're describing with the textboxes and listbox on the left hand side is fine... just noting that they should go in the MAIN form, not the subform.  I'm assuming these are unbound controls (blank control sources), and that the first column of the listbox is the ClientID.

    You can use VBA as follows in the double-click event of your listbox to filter the mainform to the selected client:

    Me.Filter = "ClientID = " & Me.listboxName
    
    Me.FilterOn = True
    
    Filtering the main form will automatically make the subform display the case records associated with the selected client.

    Miriam Bizup Access MVP

    • Marked as answer by todtown Thursday, June 2, 2016 11:44 AM
    Thursday, June 2, 2016 10:13 AM

All replies

  • It sounds like you are trying to use your list box as a search feature to bring up a client... AND related cases (if any exist). Is that correct?

    If so, lets break this down into two parts:

    1. Displaying clients and related cases

    2. Using the listbox to filter/display a particular client

    For the first step (Clients and related cases), Access subforms are specifically designed to handle this type of parent/child relationship.  See this tutorial:

    Create a form that contains a subform

    That tutorial uses employees/orders as an example.  In your case, the mainform would be bound to your client table, the subform would be bound to your cases table, and the ClientID would be the master/child link between the two.  With this setup, existing case records will automatically display with their respective clients, and the master/child links will ensure that the clientID automatically gets inserted into new case records.

    Regarding the second part of your question, the listbox search feature, the layout you're describing with the textboxes and listbox on the left hand side is fine... just noting that they should go in the MAIN form, not the subform.  I'm assuming these are unbound controls (blank control sources), and that the first column of the listbox is the ClientID.

    You can use VBA as follows in the double-click event of your listbox to filter the mainform to the selected client:

    Me.Filter = "ClientID = " & Me.listboxName
    
    Me.FilterOn = True
    
    Filtering the main form will automatically make the subform display the case records associated with the selected client.

    Miriam Bizup Access MVP

    • Marked as answer by todtown Thursday, June 2, 2016 11:44 AM
    Thursday, June 2, 2016 10:13 AM
  • Thanx for a very detailed response. (I don't feel so bad now about my lengthy post.) I will keep your solution for future reference. I was able to resolve my problem last night by setting the ClientID field back to its controlsource. Don't know why that worked, but my deadlines are so tight that I'll take it. 

    For my search I have a Case form that was created by the wizard based on my Cases table. The once the form was created I widened it to have to sections. On the left I added some unbound controls where I allow users to enter a name and have it query the client table for all names that meet their criteria and put those names in a listbox. When the user doubleclicks the name they want it populates the clientid and name fields on the right. Once they complete the rest of the fields they click my Submit button and the record along with the clientid get updated to the cases table. 

    tod

    Thursday, June 2, 2016 11:48 AM
  • Glad you've got it working.  Good luck with your project.

    Miriam Bizup Access MVP


    • Edited by mbizup MVP Thursday, June 2, 2016 12:31 PM
    Thursday, June 2, 2016 12:31 PM