Form updated w/ new field, past data not viewable!

[Greazy]

Access 2010.

I'm a returning Access user, creating something basic from a MS template for asset tracking. Thus far I've populated my file with about 30 assets, populated my employees table, asset categories table, etc.

ISSUE: I've modified my ASSETS table to include one new field after inputing 30 assets. After creating the new field in my ASSETS table, I went to design view for my form and created the new box, tied it to my newly-created Control Source (defined in the table) and now when I go to my ASSETS form, I can't see anything that was posted there before! The data is there in the Table, but the form is essentially reset. Any new assets added can be seen, but none of the old data can be seen. I can go through and add values through the table, but if I wanted to use excel I would've...used excel.

What do I need to do in order to refresh my Asset Form so I can view my data in the form posted there previously?

Curious,

Greazy

[Stewart Ross]

Hi. If you add a new field to a table that contains existing records, your existing records will not have any value in the new field - that is, the field will contain a Null. This is true for almost all field types in Access except boolean - even if you have a default value specified, if you add a new field to a table any existing records will contain nulls in that field.

If you are linking this field to another table by a JOIN, say, or as part of a specified relationship, the null values will exclude those rows from the resulting join.

If this is the case, you will need to set valid values for the new field in your existing records currently containing the null values. For example, if your field is a long integer value and 0 is an allowed value meaning, say, 'Don't Know' then update the field to 0 for the records which currently contain nulls.

Another possible explanation is that you are applying some kind of filter to the underlying records which excludes existing records, but I doubt this.

It would help if you gave us more details of your form's recordsource query, and included the type of the new field.

One thing I am certain of is that the issue is less likely to be to do with your form as such; the form is just a means to an end, and of much more importance is the underlying query (or table) you have based your form upon. You should be able to run the query directly and see whether or not your old records are visible. If they are not, it would confirm my feeling that you have nulls in the added field for all your old records, and this in turn is preventing a relationship from returning matching records.

Welcome to Bytes!

-Stewart

[Greazy]

Thanks for the prompt reply Stewart. Let me see if I can clarify.

1. yes, I did add a new field in the Table view.
2. I can still view all of my data in this Table view as well.
3. I went back to my 30 older records and put in values for the ~null~ ones.
4. I didn't do anything crazy with this: just a simple new (date) field added to a table, which the form is directly associated to. It is not being pulled from a second table or any such business. Format: Short Date. Control Source: field is linked to my new table column (and works fine w/ new items).
5. No filters are being used (to my knowledge)
6. What is the "forms recordsource query" ?

hmm...further thoughts?

Greazy

[Stewart Ross]

Hi Greazy. Your form should, ideally, be based on a query rather than directly on the table. The query then acts as the form's record source. The advantage is that you can sort the records in whatever order is appropriate, whereas when you base a form on the table you see the records ordered by whatever is the primary key field - often not the order you would want to present your records to users.

If your form is not being filtered and you simply added a date field there is no logical reason at all that you cannot see all records. Please ensure that you have no filters active - you will see any active filter in the form's Filter property in design view. You can also see the Recordsource property, which specifies the table or query on which the form is based. I'd check that property as well, just to make sure it matches what you expect it to match.

Other than what I have suggested I can see no reason for the form not to show all records.

A final thought: please make sure your form's properties are set to Allow Edits. There are three properties - Allow Additions, Allow Edits, and Allow Deletions which alter form behaviour. If the form is set to Allow Additions - Yes and Allow Edits - No you will not see existing records - you will be able to enter and review new records only.

-Stewart

[Greazy]

Hmm...I'm not sure what went wrong. I found that the properties were set to ALLOW ADDITIONS but for whatever reason it still won't show. I went and created a new Form from scratch and that works fine. I even added another field and that took as well. Strange...

Thanks for your feedback! Starting with a MS template I suspect was to blame here.

have a good weekend,

Greazy