Question Possible Corrupted Database

[NLillith]

So some, or many of you have noticed that I've been posting quite a bit in the last week about this crazy database I am working on for work.

This is all in Access 2010

Now, I'm having a bit of an issue with this database. Keep in mind, I know next to nothing about access. I have a table called "tapes", in there it had a few fields such as "date new/cleaned" well, I deleted those from the table, then went to the form that was using it and deleted those as well. Now, I get a popup box asking for the date new/cleaned every time I open this particular form.

I have tried compacting and repairing the database. This has not fixed the issue. I am not sure what I can do to get this pop-up to go away. The database is essentially calling a section on the form that no longer exists and I cannot get it to go away.

Does anyone know where I can start to try to fix this?

 

[jzwp22]

Is the record source for the form the table or a query that was based on the table? If it was a query, did you remove the deleted field names from the query as well?

 

[NLillith]

No, the query did not call that particular record from the table.

Please see the photos attached.

The database 4 refers to the table where the record was stored
database 3 refers to the form where the input box was located
database 2 shows the error
database 1 is the overview of the database as it stands now.

 

[jzwp22]

Open the tape input form in Design View, go to the property sheet. Under the Data tab-->Record Source what is there? Could you please post the text shown in the Record Source property?

 

[NLillith]

Sure can.

SELECT Tapes.[Tape #], Tapes.Status, Tapes.System, Tapes.[Backup Date], Tapes.[Backup Date Text], Tapes.[Backup Desc], Tapes.Comments, Tapes.[Date Sent Out], Tapes.[Date Due Back], Tapes.[Tape Type], Tapes.[Date New/Cleaned], Tapes.[Type of Backup] FROM Tapes;

Ah! I see the error. I can't believe it's still in the code.

Thank you!

 

[gemma-the-husky]

the form (or even the form's source query) may have used the now-deleted fields as a filter, or for a sort order. That may be why you are being prompted for the details. Look carefully at the properties for the form and the query.

 

[jzwp22]

You're welcome. As gemma-the-husky suggests, there may be other properties to check as well.

 

[NLillith]

Hopefully this is the last question for a while.

I have a query that I print out into a report and the query is based on a form that has a combo box in it. When I run the query, the combo box populates with the correct information that was input into the table, but as soon as I run the report, the information that was in the combo box does not appear in the report.

So what I am wondering is, how can I go about fixing this so that the information that is in the form, that populates the query, will then populate on the report?

 

[jzwp22]

...and the query is based on a form...

A query can only be based on a table or another query. It cannot be based on a form. You can, however, use data in a form to filter the query results.

You say that the report is based on a query. Can you show that query? (open the report in design view and go to the property sheet-->data tab-->Record source)

By the way, do you have lookup fields (list boxes or combo boxes) in your tables? If so, this can cause many problems as described here.

 

[NLillith]

Yes, the table has a few combo boxes, but the odd part is that the other combo boxes are populating fine, it's just the one that is not doing it.

As for the query, I could not find what you were looking for specifically.

The report is based on the query:

Tape Query

Which has a SQL view of:

SELECT DISTINCT Tapes.[Tape #], Tapes.Status, Tapes.System, Tapes.[Backup Date], Tapes.[Backup Date Text], Tapes.[Backup Desc], Tapes.[Type of Backup]
FROM [Type of Backup], Tapes
WHERE ((Tapes.[Tape #] like "*" & [Enter Tape Number] & "*"));

 

[jzwp22]

It is probably something subtle. Is it possible for you to zip and post your database with any sensitive data removed?

 

[NLillith]

Being that we had one record for testing purposes only, I have removed that. But you will need to input a number to do any testing. It is attached.

 

[jzwp22]

I cannot even get the status to populate in the table directly. You have the bound column property set to 2 but the row source only has 1 column, so there is nothing in column 2 for Access to use. If it doesn't populate in the table, you will not see it in the report.

I would definitely recommend getting rid of the table level lookups because of all of the problems they can cause. Additionally, it is best not to have spaces or special characters in your table or field names.

Also, you had no relationships established in the relationship window. Doing so will make creating queries much easier.

I have made the corrections in the attached database per my recommendations above. Of course, now that I altered the tables, the forms/queries/reports you had, will not work now, so I deleted those.

But, I think you still need to work on your tables/relationships further, so here are some questions that might help further refine the table structure. Only after the table structure is final, would I start on forms/reports.

What happens to a tape when it is returned?

When a tape is returned, can it be used on a different system?

Is there a set time period for when a tape is returned after it is sent off-site?

Is it important to keep track of how many times a tape is used?

Can certain tape types only be used on certain systems?

 

[NLillith]

Okay, the way I have it set up is so that when you are entering data, the status is a combo box that you have to click on and select. It was done that way on purpose. We do not have relationships because those were not necessary at the point.

The first two questions are answered by this: they are in storage until 1 year later.

This also answers question 3: that's why in the input form, date due back, it auto populates one year after the back up tape date.

As for the type of tapes, we know which ones go to what system and that's more of a confidential sort of thing.

 

[jzwp22]

Since the date due back is on year from the date it was sent, there is no need to store that date, it can be calculated very easily. In only rare cases are calculated values stored in a table; your case is not one of those rare cases.

As for the type of tapes, we know which ones go to what system and that's more of a confidential sort of thing

If certain tapes can only be used on certain systems, that is a relationship that you must address in your table structure.

When a tape is returned, is it erased and used over again?

If so, how many times do you reuse it before it is discarded?

Additionally, the dates you have: backup date, date sent out

These actually represent 2 separate actions. As such this describes a one-to-many relationship, so the actions related to a tape should be in a separate table

tblTapeActions
-pkTapeActionID primary key, autonumber
-fkTapeID foreign key relating back to the tape table
-dteAction (date of the action)
-fkActionTypeID foreign key to tblActionTypes

tblActionTypes (2 records: backup; sent off-site (other added if needed in the future)
-pkActionTypeID primary key, autonumber
-txtActionType

 

[NLillith]

We need the date due back and the back up date in the table to print to the report because as I said, we re-use the tapes. Also, like I said, we are not going to be using relationships and they are not necessary at this time.

The back up date is the date that the tape was taken out of the system. The date due back is one year after the tape is taken out of the system to be put back into circulation.

IE: Tape 000112 is a July 2009 back up tape. In July 2010, we can then reuse that tape.

I understand what you are getting at, but the person I am doing this for wants it in the format that I gave it to you. This person does not want relationships established and other such things.

What I am just trying to figure out now is why the one combo box is not populating in the field when the others (as in the other combo boxes) are.

 

[jzwp22]

We need the date due back and the back up date in the table to print to the report because as I said, we re-use the tapes. Also, like I said, we are not going to be using relationships and they are not necessary at this time.

The date due back can be calculated and displayed wherever you need it; you should not store it.

Since Access is a RELATIONAL database using it without relationships doesn't make sense.

What I am just trying to figure out now is why the one combo box is not populating in the field when the others (as in the other combo boxes) are.

I answered this question. Go to the table and look at how you set up the combo box in the table. The row source only has 1 field specified, but you have the bound field as field 2 which does not exist. You'll have to look at your original version since I got rid of all of the table-level combo boxes in the revision I posted.

 

[NLillith]

I appreciate everyone's help throughout this process. All seems good to go now and the data can finally be added to the database.