Entries have vanished is this possible ?

[DBenz]

Hi,


I have over the years of using Access sometimes thought I was losing my sanity when discovering entries I was sure I had filled in, were no longer there. I have just visited my database which shows me which Hard drives my videos are stored on and keep finding blank entries for the location field, the record is there, just that field is blank, now I place the column in order I see 375 empty entries.
I would not have made that many mistakes.

is this a known thing ?

any solution to this ?

In another issue the description of scale modelling items I have bought when revisited a few years later has been truncated so I have to retype it out in full.

DBenz

[orange]

Do you have some backups to refill your tables that show this effect?

I am not aware of any system related cause for what you describe.

[isladogs]

Not sure whether you are referring to data in a table or as viewed in queries
The following links may or may not be relevant:
http://allenbrowne.com/casu-02.html
http://allenbrowne.com/ser-63.html

[Micron]

For the first issue, consider posting the sql statement(s) that populate the table & fields where the values are missing, or if the append/updates are done via code, it would be better to see a copy of the db. There may be a failure in a query WHERE clause, or a coded variable that isn't always assigned a value. Either would probably be too hard to spot just by reading your sql or code, so looking at the db itself might be the only way. Or you can try to modify one of the existing problem records first and see if it will update through your form. You are using forms and not editing directly in tables, I hope.

EDIT: as an afterthought, by any chance have you deleted records from a table and what you have going on there is a Cascade Delete because of relationships you've created?

[DBenz]

For the first issue, consider posting the sql statement(s) that populate the table & fields where the values are missing,

How do I get this sql statement?

or if the append/updates are done via code, it would be better to see a copy of the db.

not done via code

There may be a failure in a query WHERE clause, or a coded variable that isn't always assigned a value. Either would probably be too hard to spot just by reading your sql or code, so looking at the db itself might be the only way. Or you can try to modify one of the existing problem records first and see if it will update through your form. You are using forms and not editing directly in tables, I hope.

um...editing into table as form doesnt give me the overall view this dbase needs. is that the cause ? How do I see all records at once with scroll bar at right when entering data and also when performing a search ?

EDIT: as an afterthought, by any chance have you deleted records from a table and what you have going on there is a Cascade Delete because of relationships you've created?[/QUOTE]
just typing into table because in this dbase I need that sort of view of all records as opposed to one at a time. No relationships set up. Just a table I add records to.

Orange...Do you have some backups to refill your tables that show this effect?

No backup as such, there again I have backed up the entire D drive the dbase is on a while ago.

DBenz

[moke123]

um...editing into table as form doesnt give me the overall view this dbase needs. is that the cause ? How do I see all records at once with scroll bar at right when entering data and also when performing a search ?

Use a continuous form

[Micron]

How do I get this sql statement?

In query design, sql view.

editing into table as form doesnt give me the overall view this dbase needs. is that the cause ?

Not likely

How do I see all records at once with scroll bar at right when entering data and also when performing a search

Don't see why that's important. Forms based on queries are designed to filter or limit the number of records you see so that you don't see the ones that don't meet your criteria. If you're saying you don't want to be bothered with scroll bars, then you'll have to create another table when you have too many records to fit on one screen. Surely that doesn't make sense to you.

I need that sort of view of all records as opposed to one at a time

Then you use a continuous form or datasheet, not a single record type of form.

This is beginning to be about design rather than figuring out why your data is "missing". It may not have ever been there, or it may have been removed, or it is there but not visible.
The latter is probably the last suggestion I can make on this, because you're not using queries to update or append, plus you're not using forms. Thus I'd have to rule out data loss from those things. Try expanding the width and height of your table rows/fields. You may have a line wrap at the beginning of the "missing" ones and it's on the next line (in the same record) which would make it appear as if it's not there at all.

[ArviLaanemets]

So you enter data directly into tables! Then it is clear, why you have problems!

1. Access saves all changes made to record immediately when another record is selected. So when you select some field in record, press Del accidently, and move to next record, this is done. Unless you see this, remember the old value, and edit the change back, you have no way to restore the old value short of restoring the database from backup;
2. When there are linked entries in several tables, you have to add/edit records in all those tables yourself. There are no events on table level, which check the values, enter links automatically, etc. And when you enter e.g. into some foreign key 1111 instead of 111, Access is not protesting at all.

When all data management is made only through forms, there will be tools which take care with all this: you write events, which will check are all needed fields filled, allow/disallow data entry for certain fields depending on entries in another fields, enter automatically foreign keys into linked tables, check record before saving it for all needed conditions and aborts saving when those conditions aren't filled, and much more.

[Micron]

There are no events on table level, which check the values, enter links automatically, etc.

Not sure I agree with that statement. Table fields have validation rules that you can create. Plus, those 'links' can be relationships which can cascade delete or update, so I think such a thing does exist.

Not saying that this means it's fine to edit directly into tables and that should be apparent from post 4. If I were a betting person, given the method of data entry, I'd say the location being added is a file path which is copied and pasted into a field. That is why I suggested the possibility that it's there, just not visible.

Time for the OP to come back and let us know what's up, seeing as how there's been 2 or 3 replies but no response from OP.

[ArviLaanemets]

Not sure I agree with that statement. Table fields have validation rules that you can create. Plus, those 'links' can be relationships which can cascade delete or update, so I think such a thing does exist.

For my taste those options are too limited. and user must be very careful with some - e.g. default values. I never use default values in tables anymore, after I ended with a lot of orphan records in database.

[DBenz]

Hi,
There is no line wrap going on, the entries come from a drop down combo and the column is with a large gap beyond the longest, I also see now that an automatic date entry that occurs as soon as I click on new record tool at foot of table, 180 of these date cells are also blank, yet they get auto filled, and I certainly dont delete them.
I will get a friend who is proficient in Access to show me how to set this up better. I am only familiar with a form that shows one record at a time, not aware of continuous forms. I need to see all the rows and columns on this and as I keep entering records I just scroll up and down to view them all., one per row. Wish I had been aware of continuous forms years ago. he had set up some of my databases but I did this one myself and never thought to ask. I always thought that tables were ok to enter records by.

Method of entry is purely by clicking the new record button at very foot of screen next to fwd backward buttons each time and working left to right along the row. The date cell autopopulates as I do this so why 180 missing I dont know.

Also I have a scale modelling database and that only gets filled one record at a time using a form yet I came back to it some time ago to find the description of the item section had seen the description of the item truncated yet it was set to 255 chars, so had to retype them in again.

Why ?

DBenz

[CJ_London]

the entries come from a drop down combo

if you are using lookups in tables, that may well be your problem

lets say you are looking up a value in a table

ID Name
1..Fred
2..Troy
3. Harry


so you see in your table Fred, Troy, whoever you have selected, but what is stored is 1, 2 3 etc

Now you delete Fred from your lookup table

You will still have 1,2 3 etc stored, but you can't see Fred because the record related to ID=1 is no longer there.

Remove the lookups from your table and see if that resolves the problem.

[DBenz]

Hi,
I am not missing a record, or many records, I am missing the entries in the fields for some of the records (about 380) that were for where my videos were stored, which drive letter .

I didnt forget to enter that many , I work my way left to right along a new record filing in every field.

I have now created, as suggested here, a continuous form, by selecting the Tbl I was using then forms > more and multiple values, it showed a black text on white box of all my fields, I then go to form view and only half the fields are visible, I enter design view and start narrowing them east west, all those visible are now as per table view, yet still no more come in, I go form properties and alter width of form from 55.691cm to 100 and get message stopping me. 'The number is too large'.
I am unable to make the form the width to get all my fields in as they are in Table view, and I have the columns adjusted to minimise width in that, that was easy enough, but unless I can make form wider I cannot use continuous form as I need all my fields not half of them !

Furthermore I need to make columns go AtoZ then revert back to previous order or make another column AtoZ and this was dead easy by selecting column and doing so, now in Continuous form view I cannot view in that way. I know you are going to say make a query then base form on a query, but I need to do that to more than one column and dont want a number of queries set up when I was doing that within seconds of each choice., retaining the record view when doing so as highlighted.

I am told by a n access user that entering data by table will not lose entries in fields.

Also is there a way of counting exactly how many blank fields I have in the PCdriveLocation column ?

DBenz

[Peter M]

Are you missing the records or do you have about 380 records that don't have any data?
If it is the first, and the records are definitely missing (i.e. you remember entering "Drive H, Name Bob, Etc." and now you cannot find it), then it is highly likely you have deleted the records somehow.
If it is the second, and you have 380 blank records, this is probably because when you get the end of the table every time Access automatically populates a new record with the date it creates a new record.

To find out how many blank records you have:
1) Create -> Query Design -> Select (Query Type)
2) add in your table to the main pane
3) add the field PCDriveLocation, add another field (doesn't matter which one).
4) Click the Totals Summation Symbol
5) GROUP BY PCDriveLocation and COUNT the other field
click run

[Micron]

Time to post a compacted and zipped copy of your db, methinks.