Solved 'Refresh' form after changing a table field (1 Viewer)

[db-noob]

Hello, I tried searching the forums, but unfortunately I don't think I have the right terminology for it.

In the past when I've changed a field on a linked table, like long int to decimal, or short text from 255 to 30, or added a validation rule, etc., I've had to delete the corresponding text box on the form and replace it with a new version of the field. Then it takes a while to get all the formatting and whatnot to match up to the old one. Is there a way to 'refresh' the form so its text boxes, combo boxes, and others are updated with the changes I made to the table field(s)?

For instance, I'm thinking of updating the State field size, increase it from 2 to 3. In doing some data cleaning, I discovered a record with a Canadian address. Thankfully, Canada's province abbreviations are also 2 characters. Then I got to thinking if the company did future work in Mexico. Unfortunately, some of Mexico's state abbreviations are duplicates of US and Canada territories, so I figured they'll use 3 characters.

Not sure if I'll do that State field modification anytime soon, unless the company decides to expand into Mexico sooner rather than later, but the question remains. Even then I might go back and change other fields' field sizes, default values, validation rules, formats, input masks, decimal places, calculated result types, etc.

Thanks for reading!

 

[pbaldy]

Interesting, I don't think I've ever needed to do anything but refresh the linked table. I could see maybe having to change the decimals displayed or something, depending on what properties you had set, but the change to the data type should be seen as soon as you refresh the linked table.

 

[Isaac]

Couldn't help but take this chance to point out this is a perfect example of why when people use natural keys they are taking a big risk (IMO) by assuming the data won't change to become non-unique.
If a person used postal codes as their own key - then decided to do business in MX, suddenly there would be duplicates. The whole system would break.

PS - i'm not saying you are doing that - just an aside.

like Paul, I don't remember having to re-fresh the form controls. sometimes when i change a table design, I have to re-drag it onto a query grid, but not a form

 

[MajP]

I am guessing you could do this but it would be a bit of code.

Loop the tabledefs in dao
  loop the fields of each tabledef
    loop the properties of each field  (lets call the FieldProp)
      loop the forms of allforms
         put the form in design view
        loop the controls
          check the control source if the control has it
             Loop the properties  (lets call it control prop)
                If the control pop = field prop then set the property

close the form and save

Now do the same with the reports

 

[db-noob]

@pbaldy @Isaac

So it's as easy as refreshing linked tables for the changes to go all the way through to the form controls?

 

[pbaldy]

In my experience, yes. Like I said, if you specified (for example) a format of 0 decimal places and change the data type to something with decimals, you may have to manually change that.

 

[db-noob]

In my experience, yes. Like I said, if you specified (for example) a format of 0 decimal places and change the data type to something with decimals, you may have to manually change that.

Got it! Thanks!

 

[HiTechCoach]

DB-noob, are you using a none ACE/JET (accdb/mdb)backend, like MS SQL Server, MySQL, etc)?​

 

[The_Doc_Man]

Actually, I've run across that behavior before. When you have a form directly bound to a table and change the BE table, I believe there is some ID in the table that changes (i.e. to let you know this is a new version.) The form thinks it is still bound to the old version and finds a mismatch. Sometimes opening the form even asked me for the location of the table. However, I started using what I call single-table queries - the equivalent of SELECT * FROM table ; though I actually enumerated the fields to have them in a good order of appearance and also to have an ORDER BY involved, since "raw" table order is not predictable over time. When I did that, the form no longer cared about change in the table's field size. The form no longer cared where the table was either, because the query was local to the front-end, as was the form.

 

[Pat Hartman]

When your BE is Jet/ACE, Access automagically picks up the new data types. If the BE is ODBC, then you MUST refresh the link for Access to "see" the schema changes.

As far as forms go, it doesn't matter what the BE is, you would not have to change the form if you changed a data type unless you wanted to change something about the control like its format property. For example, if you went from a date to a double and had the control formatted for a date, Access wouldn't change that. The number would be formatted as a date.

 

[db-noob]

DB-noob, are you using a none ACE/JET (accdb/mdb)backend, like MS SQL Server, MySQL, etc)?​

Both the FE and BE are Access (accdb).

 

[Pat Hartman]

Did you change the linked table while the FE was open? You might not see updates in that case. Close the FE and reopen it.

 

[db-noob]

Did you change the linked table while the FE was open? You might not see updates in that case. Close the FE and reopen it.

I decided not to change it after all.

Any changes I make to the tables, I close the FE and open the BE with exclusive rights (or whatever it's called). Save the changes, close the BE. Open the FE and use the Linked Table Manager to refresh the tables (just in case).