Results 1 to 11 of 11
  1. #1
    hilian is offline Advanced beginner
    Windows XP Access 2003
    Join Date
    May 2012
    Posts
    44

    Question Changing data in related tables from a form

    I have a database that tracks expenditures by a number of programs run by a smaller number of social service agencies (one-to-many relationship). From time to time agencies and programs are either added or dropped or change their names, I need to give the user a way to edit the two (related) lists. I can see two ways of doing this: with dependent combo boxes or with a form and a subform. I’ve tried both ways, and, for different reasons, I can’t make either one work.



    The form with dependent combo boxes is bound to a query uniting the table with agency names (tblAgencies) with the one with program names (tblPrograms). The combo boxes work, but when I make a change in the box (each box has its respective table for a control source), instead of changing the record in the table, Access adds a new record. I tried adding a delete button, but that doesn’t do the job. I also added a button to save changes, but that doesn’t make a difference, either.

    In the form/subform option, the main form is bound to tblAgencies, and the subform is bound to tblPrograms. They are united on the agencies foreign key in tblPrograms. There is a combo box on the main form that displays the agencies. The subform is in datasheet view and displays all of the programs that belong to a particular agency. The problem is that when I change agencies in the combo box, the subform doesn’t update. I tried putting a requery statement in the After Update event for the Agencies combo box, but that doesn’t help.

    Which is the better option, and what do I need to change to make it work?

    Thanks,

    Henry

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You are saving the agency name into tblPrograms? This is duplication of data. Don't save the name in tblPrograms, just the unique ID that is the key field (is it an autonumber?).

    Then the related info in tblAgencies (such as the name) can be retrieved by joining the tables on the pk/fk fields. If you want to show agency info in the subform (although I don't see reason to), make the subform RecordSource a join of the two tables and bind textboxes to the fields from tblAgencies and set them Locked Yes, TabStop No.

    Changing the name in tblPrograms would require an SQL UPDATE action.
    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
    hilian is offline Advanced beginner
    Windows XP Access 2003
    Join Date
    May 2012
    Posts
    44
    I guess I didn't explain it well. The agency name is a field in tblAgencies. There is a foreign key (an autonumber) to tblAgencies in tblPrograms (which contains the program names). The tables are joined with the agency FK from tblPrograms in the subform Record Source. What I want to do is show the name of an agency in the combo box on the main form and have the programs run by that agency show in the subform. I thought that joining the forms in the subform Record Source would do it, but there's something more that I need to do.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Your data structure might be wrong.

    It appears each agency can have many programs. Can each program can be associated with many agencies? If so, this is a many-to-many relationship and requires a 'junction' table to relate agencies and programs.

    If each program can be associated with only one agency, then the agency ID should be stored in tblPrograms, which you seem to already do. Then, main form bound to tblAgencies and subform bound to tblPrograms. The Master/Child links properties of the subform container control will synchronize the related records. Only those programs associated with the agency main form record will show in the subform. What you need to do is navigate to the agency record on the main form. This can be done several ways. One is with the intrinsic Access navigation and filter tools. Code can filter the form RecordSource or move focus to desired record. I use only VBA.

    Review this for query parameter method to filter the RecordSource http://datapigtechnologies.com/flash...tomfilter.html

    Another method of filtering is code to set the form's Filter and FilterOn properties.

    A method to change record focus uses RecordsetClone and bookmarks.
    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.

  5. #5
    hilian is offline Advanced beginner
    Windows XP Access 2003
    Join Date
    May 2012
    Posts
    44
    Each program is associated with only one agency. I followed the query parameter method from the video and it worked nicely. One further question. Do I need a button to save changes, and if I want to delete a record, do I need a button to do that?

    Many thanks,

    Henry

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You still want to save the agency name into tblPrograms? Why? Just save the agency ID (the key). Then the name can always be retrieved by a join of the tables. This means changing the agency name is simply a single edit of the value in tblAgencies. Changing the name in all the records of tblPrograms means running an SQL UPDATE action. Executing that SQL can be accomplished several ways: manually run an Access query or button click runs code and that code can run the Access query or SQL statement built in the code. A record can be deleted by selecting record and clicking the keyboard delete. If you don't want users to use the keyboard delete key, will need code to control that. There is a form event to handle that, it is called BeforeDelConfirm. Then you can have a custom button on the form to handle delete requests.
    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.

  7. #7
    hilian is offline Advanced beginner
    Windows XP Access 2003
    Join Date
    May 2012
    Posts
    44
    I didn't mean to give the impression that I want to save the agency name into tblPrograms. I don't. I have an agency foreign key in tblPrograms, and that works.

    I did some work on the form and subform, and I discovered that everything works fine as long as the combo box on the main form that allows the user to choose an agency name is unbound. When I bind it to the AgencyName field in tblAgencies, for some reason, I get the error message, "The value you entered isn't valid for this field." I checked the table, and the field is text, but in any case, the record source for the main form is a query based on tblAgencies (I followed the procedure from the video). In the combo box, I have the number of columns set to 2, so that shouldn't affect it. I have the agency primary key field in the first column, then AgencyName, and I have column widths set to 0" and 3.88."

    I can live with the unbound combo box, if necessary. I can set up a separate form just to edit agency names, and use this one for editing program names but I'd rather not do this if there's a way to fix the problem that's giving me the error message.

    Also, since agencies are dropped from time to time, I need a way to remove the names of dropped agencies from the list. I find that Access won't let me delete records from tblAgencies, since they have related records in tblPrograms--I tried it directly from the table. Is there a way to simply not make them show up in the list--declare them inactive in some way? Removing the name from tblAgencies and leaving an empty record seems to work, but it results in a blank row showing at the top of the list when the combo box is clicked. Several blank rows would make it look as if there was nothing in the list all.

    Finally, I figured out that when you edit or remove a program name, exiting the form will save the record. That's awkward for the user to do, especially if there is a need to edit a few records. I need an intuitive way to let the user know that edits have been saved. How do I do that?

    Again, thanks,

    Henry

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    New record or record edits are saved when form closes or loses focus, move to another record, or run code to save. What does user need to know? They enter data and move on. The save is taken for granted.

    Deleting is another matter. You would want to Requery the form so that it's RecordSource will be refreshed to reflect that record no longer exists. However, deleting records should rarely be done and only with strong justification.

    You can have a field in tblAgencies to flag record as active/inactive. It can be Yes/No type. Records marked Yes are active, No is inactive. Use this field as filter criteria for the combobox RowSource sql. Combobox used to input form filter criteria must be unbound. The record search should use the primary key, not the name. Use a textbox bound to name field to edit name.
    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.

  9. #9
    hilian is offline Advanced beginner
    Windows XP Access 2003
    Join Date
    May 2012
    Posts
    44
    I created another form for editing agency names or marking them inactive. I added a yes/no field called "Active" to tblAgencies and used an unbound combo box on the form to select the agency name. Then I set up a text box bound to the AgencyName field for editing agency names and adding new agencies. I added a command button to add a new record. I added a radio button for marking agencies as active/inactive.

    All of the above worked. What didn't work was: I added a new criterion to the form query for the record source on the original form. The criterion is that the "Active" field should equal Yes. When I tested the combo box on the original form, the agencies I had marked as inactive (No) still showed up.

    What am I doing wrong?

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The combobox RowSource sql needs filter criteria.

    SELECT agencyID, agencyName FROM tblAgencies WHERE Active=True;
    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.

  11. #11
    hilian is offline Advanced beginner
    Windows XP Access 2003
    Join Date
    May 2012
    Posts
    44
    I put in the filter criteria in the combo box row source, and it worked perfectly.

    Thank you very much,

    Henry

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

Similar Threads

  1. One Form to Update 2 Related Tables?
    By zannix in forum Forms
    Replies: 3
    Last Post: 01-26-2012, 08:46 AM
  2. Replies: 2
    Last Post: 07-16-2011, 07:56 PM
  3. Show related data on a form
    By Accessgrasshopper in forum Forms
    Replies: 4
    Last Post: 03-17-2011, 07:53 PM
  4. Replies: 4
    Last Post: 12-21-2010, 11:32 AM
  5. Not able to make Form from related Tables
    By a1y1a1y1 in forum Forms
    Replies: 5
    Last Post: 01-02-2010, 12:33 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