Form not updating underlying table

[Kirsti]

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!

[June7]

You have form bound to table and have you bound controls to the fields? Can you provide project for analysis?

[Kirsti]

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.

[Kirsti]

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.

[June7]

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.

[Kirsti]

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.

[June7]

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.

[Kirsti]

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.

[June7]

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.