Results 1 to 9 of 9
  1. #1
    Kirsti is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2012
    Location
    New Zealand
    Posts
    172

    Form not updating underlying table

    Hi, when a user updates my form, it does not automatically update the table. I also have queries running to update certain fields in the table, and this is also not updating the form.

    I have checked the Form properties, and the Control Source is the correct table.

    I have tried to get around the issue through code in the form on the AfterUpdate Event, however my code isn't working.



    Private Sub Housing_Officer_AfterUpdate()
    CurrentDb.Execute "Update tblVacantUnitStatus_ALLDATA (Housing_Officer) Value ('" & Me.Housing_Officer & "')"
    End Sub

    Any suggestions greatly appreciated!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,220
    You have form bound to table and have you bound controls to the fields? Can you provide project for analysis?
    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
    Kirsti is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2012
    Location
    New Zealand
    Posts
    172
    I don't believe that all of my controls are bound, because I updated field names in the table after creating the form - which is why I thought I would try the AfterUpdate approach.

    I have attached my database (but have removed all data from the tables as they contain some sensitive information).

    Thank you for your help.

  4. #4
    Kirsti is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2012
    Location
    New Zealand
    Posts
    172
    Strangely, when I just tested using the db I attached here - it is all updating perfectly. Even though it is a copy of my database, with the data removed from the tables.

    When this db goes into production, all tables will be blank, and updated via Forms only, so I guess that whatever problem I had is not going to be an issue.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,220
    I can't remember if Access2003 propagates field name changes to queries, forms, reports. If it didn't then you need to go fix. However, I don't see any controls in the forms showing error on ControlSource.

    Your database is not properly structured. You have 3 tables with identical fields. You should have one table with an additional field for category or status or for whatever reason it was you thought you needed separate tables. Also, these tables have similar name fields (Offer1, Offer2, Offer3). This is not normalized data.

    The number of UPDATE queries you have saved is most unusual. Why are you doing this?

    A new/edited record is committed to table when: 1. move to another record, 2. close table, query, or form, 3. run code to save. I had no problem entering a new record. It was committed to tblVacantUnitStatus_ALLDATA.
    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.

  6. #6
    Kirsti is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2012
    Location
    New Zealand
    Posts
    172
    Hi, thank you for taking the time to look at this for me.

    The reason for the 3 table with identical fields is that tblVacantUnitStatus_ALLDATA table contains all data as it's name would suggest. Then, there is a make table query that runs & puts only current data (i.e. data from the ALLDATA table minus historical data) into tblVacantUnitStatus. Then a 2nd make table query runs that takes data from tblVacantUnitStatus where field Unit_Status = RTL and dumps it into tblAvailable_Now. The reason behind this is that I have 2 end user groups. One group will be entering data into frmVacantUnitStatus. However the 2nd user group need a "spreadsheet like" view to see all units that are either currently vacant or available now.

    The reason for the UPDATE queries is because I couldn't figure out how to update one field to a number of different values based on different criteria, other than using an IIf statement, which would have had too many nested If's for Access to handle.

    As I'm sure you've guessed, this is my first attempt at building an Access database from scratch, and I really appreciate your comments and input.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,220
    Both end user groups can be accommodated with one table. Have a field called Inactive. It can be a Yes/No or a date or text type. Use filter criteria to exclude records that are Inactive. A form or report can present the 'spreadsheet like' view.

    Why does a field need to be updated and not just user entered? If too many conditions for a nested IIf, build a custom function. Are you still using the UPDATE queries or was that a one-time exercise to fix data? If you do still have need for them, they can be consolidated by use of input parameters for the update value and the filter.
    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.

  8. #8
    Kirsti is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2012
    Location
    New Zealand
    Posts
    172
    Thanks June7. I have deleted all other tables, and used forms in datasheet view, with a filter on them to replace the deleted tables.

    A field needs to be updated and not user entered to keep data integrity. For example, I have 115 buildings in a number of different suburbs. I have 9 users using entering data, and there is a possibility that one user may choose building1 from the dropdown, and enter suburbA. The next user may also choose building1, however may accidently enter neighbouring suburbB. So if I am trying to run reports based on the suburbs, integrity will be lost. Therefore I have update queries to automatically populate the suburb, based on the building name.

    Can you please give me an example of how I would consolidate these update queries?

    Thanks.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,220
    A major principal of relational database is 'don't duplicate data'. Should not be saving the suburb value. You have a table of buildings. Suburb should be a field of this table. All user has to do is select the building and the building ID is saved. Anytime you want to know what suburb building1 is in, do a query that joins these two tables. Then all the info from both related records is available.
    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.

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

Similar Threads

  1. Replies: 7
    Last Post: 01-08-2012, 06:20 PM
  2. Access Requery of underlying Form
    By tcheck in forum Access
    Replies: 1
    Last Post: 11-17-2011, 10:58 AM
  3. underlying form prints instead of report
    By usmcgrunt in forum Reports
    Replies: 1
    Last Post: 09-17-2010, 05:22 AM
  4. updating a table from a form.....
    By softspoken in forum Access
    Replies: 7
    Last Post: 04-21-2010, 09:04 AM
  5. Replies: 2
    Last Post: 08-26-2009, 10:43 AM

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 - Senior Forums