Help! Data still in table but some missing from form, query, repor

[Guest]

Please help! I am very new to Access – this is the first database I have ever
created & so I have no idea what has happened but all of a sudden I seem to
have sort of lost some data (sort of because the data is still in the table
but doesn’t show up on the form or the queries or therefore the reports).
Please see below for details:

I have entered data into a ‘module’ form (module title, code etc) in the mde
database file.
On this form I have a Combo Box from which I have choosen one or more
‘courses’ (these courses have all been entered in the first instance into a
‘course’ form).
Pressing the Add command button then transfered the ‘course/s’ to a List Box
above. So for each ‘module’ record there is a List Box with one or more
‘courses’ in it.

The main ‘module’ data on the form (module title, code etc) has gone into my
‘module’ table.
I then have another table – a link table – that kholds a list of the
‘modules’ and the ‘course/s’ that I attached to each ‘module’ (i.e. from the
List Box on the ‘module’ form).

Since attaching ‘courses’ to ‘modules’ I have been working on various other
parts of the database (i.e. more data entry, report design, form alteration –
although I’ve not changed anything on the ‘module’ or ‘course’ forms
mentioned above).

It was only when I came to produce a report that I realised I had a major
problem. I went to look at the ‘module’ form and found that some but not all
of the modules no longer had the ‘courses’ attached. This is the case on the
mde file and on the mdb front-end file.
So, I then went to the mdb back-end file to look at the link table & found
that according to the table everything is fine – the modules & the courses I
attached to them are all there.

I ran the queries for all of the relevant reports & they are also missing
the data. However, the queries refer to the link table that seems to be fine
so this doesn’t make any sense.

The database is held in a folder (as I am working on the database, the mde &
mdb files are all in the same place & I am the only person using these files)
on a network. Again, I’m afraid that I don’t have any knowledge of networks
so I don’t know if the network rather than Access could be causing this
problem.

Any help would be greatly appreciated so I can not only fix it but also,
hopefully, prevent it happening again.
Thanks in Advance
Heidi

 

[Guest]

Further weirdness which I guess is also related to my initial problem:

When I run reports that use the 'courses' I find that those 'courses' that
have mysteriously un-attached from 'modules' are then not included in my
reports. This is particularly odd because this happens in the reports that do
not in anyway refer to or use the 'module' information - only the 'courses'
from the 'course' table. These reports do not use the 'course' - 'module'
link table nor do they list the 'modules' at all so I don't know why the
'module'/'course' problem should affect these reports at all.

I am really concerned now that my database is coming apart at the seams!
Heidi

 

[Vincent Johns]

Since you have both *.MDB and *.MDE files, I suggest that, after making
backup copies, you make further changes only to the *.MDB and make a new
*.MDE from it only when you need to run a test. *.MDE files omit most
of the features that you might want to use for maintenance. This makes
them small but hard to maintain.

Further weirdness which I guess is also related to my initial problem:

When I run reports that use the 'courses' I find that those 'courses' that
have mysteriously un-attached from 'modules' are then not included in my
reports. This is particularly odd because this happens in the reports that do
not in anyway refer to or use the 'module' information - only the 'courses'
from the 'course' table. These reports do not use the 'course' - 'module'
link table nor do they list the 'modules' at all so I don't know why the
'module'/'course' problem should affect these reports at all.

I am really concerned now that my database is coming apart at the seams!
Heidi

:

What is the underlying dataset (Row Source) for your list box? If it's
a Query or an SQL statement, when you look at the Query in Datasheet
View, do the data look consistent with what you think they should be?

Could there be an active filter which is hiding some of your data?

What do you see when you run the Query on which your Report is based?
If it's based on SQL instead of a Query, you might try copying that SQL
to a named Query so that you can run it and see if those results look OK.

If you do suspect a problem there (and if you have a license to use
Access on your workstation), you might try copying your database file to
your local computer to see if you get the same behavior. (But I doubt
that that would have anything to do with it.)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[gls858]

Further weirdness which I guess is also related to my initial problem:

When I run reports that use the 'courses' I find that those 'courses' that
have mysteriously un-attached from 'modules' are then not included in my
reports. This is particularly odd because this happens in the reports that do
not in anyway refer to or use the 'module' information - only the 'courses'
from the 'course' table. These reports do not use the 'course' - 'module'
link table nor do they list the 'modules' at all so I don't know why the
'module'/'course' problem should affect these reports at all.

I am really concerned now that my database is coming apart at the seams!
Heidi

Many times this can be the result of a join. Sometimes by adding a table to
a query or setting the join you can inadvertently exclude data. Right click
on the join line in the queries, select properties and you will see the
different types. Have you added any tables to any of the queries? If
so that would be the first place I would check.

gls858

 

[Guest]

OK - so now I feel incredibly stupid! Starting afresh this Monday morning I
studied the details of all of the missing data, trying to find some
commonality to see if that would help solve the mystery.
I realised that all of the missing data had a simple fact in common - the
courses have all had a letter added to the end of their titles to mark them
out (for reasons nothing to do with the databse). Unfortunately, I added this
letter after I had attached said courses to modules. Thus, those modules are
'looking' for courses with a different tile, i.e. without the extra letter,
doh!
Although this is a fantastic result for my database - i.e. there's nothing
wrong with it, I do feel rather stupid for not realising what would happen
when I added the letter!

Please accept my apologies for wasting your time (Vincent Johns & gls858) &
thanks for your help!

Heidi

 

[Vincent Johns]

OK - so now I feel incredibly stupid! Starting afresh this Monday morning I
studied the details of all of the missing data, trying to find some
commonality to see if that would help solve the mystery.
I realised that all of the missing data had a simple fact in common - the
courses have all had a letter added to the end of their titles to mark them
out (for reasons nothing to do with the databse). Unfortunately, I added this
letter after I had attached said courses to modules. Thus, those modules are
'looking' for courses with a different tile, i.e. without the extra letter,
doh!
Although this is a fantastic result for my database - i.e. there's nothing
wrong with it, I do feel rather stupid for not realising what would happen
when I added the letter!

Please accept my apologies for wasting your time (Vincent Johns & gls858) &
thanks for your help!

Heidi

Thanks for letting us know what happened.

Incidentally, what happened to you is an example of why I often like to
use random Autonumber fields as key values, instead of something
meaningful like course numbers. The Autonumber occupies an extra field,
which on the surface looks wasteful, but then you can more easily make
needed changes to other fields without interfering with the linkages,
since you're very unlikely to be tempted to fiddle with the raw key values.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.