Cascading Combo Boxes

[ItsMe]

OK, the corrupted stuff will still be there later, when you have time to deal with it. As I mentioned, there may be queries that are dependent on each other and all of them may be afflicted. I feel the compact and repair is an important step and before doing it, you want to make sure the system tables are not expecting something to be there (a dependency form, report, etc).

So, you may have a few objects that are actually causing the issue.

[Heathey94]

I'm going to try to recreate the query and form from scratch, but I'm unsure how to get it to do what I want - before I was just building and changing as I went - not the best method, I know.

So I want the AssessorRecords Form to be accessed from a few different routes:
The Homepage, where the user enters just the name of the assessor
AddAssessor, where the user has just added the assessor to the database,
AssessorCases, just the assessor name (and details for each child where they have completed forms, but that's not necessary for my query), accessed if the user presses the back button

Is there a way to get one form to do all of this, or do I need identical forms, one for the search version, one for the other two?

[ItsMe]

I am having trouble understanding what you want your queries to do. Over time, you will begin to separate the User from the data. In other words, right now, you are viewing everything from the User's perspective. For instance, the user needs to 'cook dinner'. Well, if you are engineering the database side, you need to consider the ingredients. You need to Normalize your data by identifying the ingredients (Entities) and provide the various Entities proper homes (Tables). Now you can work on a query of the Data, considering the Entities.

If I am considering the User's need to "Go Back" because they burnt dinner, as a developer, I can adjust my mind to focus on the entities as I create the query. I can understand that the User is going to need to query specific ingredients.

I say fix the corrupt stuff first. You wont be able to test any theory when you DB is providing unexpected results.

You can get to the SQL via SQL View. Here, you can copy the SQL to notepad. To create a new query, use Design to create a new one and then switch to SQL View. Then, you can paste from Notepad to SQL view.
.

[Heathey94]

My "Back" buttons are more so that they can view the dinner at a different stage of the cooking process, and if necessary edit the ingredients. Well, I think that's how it fits in with your analogy anyway...

I'm also unsure how copy and pasting the SQL code will fix the problem - will it not just copy the problem across?

[ItsMe]

As for the analogy, yes. However, it takes time to train yourself to view things from the perspective of the database. Otherwise, you stick with what you know, e.g. what it looks like to the user (the tool the user is provided with).

As for copying the SQL, no. Copying the SQL is only copying the code. The code is what is needed to rebuild. The corruption lies inside the Object, the saved and named Query Object. Remove the Object and also remove references to it from the hidden System Tables. You can correct the hidden system tables by removing the Object and doing a Compact and Repair. The non-existence of the corrupt Object(s) and its dependent object(s) during a Compact and Repair will purge any references within the System Tables.

[Heathey94]

OK, well I've just done the above and it hasn't changed it, so it looks like the form might be the problem, I'll give that a go now. Should I delete the new query as well?

[ItsMe]

Because you are referencing a form Object within the query, I recommend rebuilding the query and the Form at the same time, before the compact and repair.

With the Form Object, you can copy over corruption by copying a control. So you will want to add new controls to a new form. I do this by using a copy of the DB. In your new copy of the DB, delete all of the Forms and Queries you intend to rebuild, keeping track of Object names in Excel or Notepad. Do your Compact and Repair. Then, start the Rebuild process by opening an old copy of the Corrupt DB to reference the Form(s). I will typically use Notepad to store VBA code from corrupt forms. I do this because it can be confusing sometimes to work in two VBA editor windows.

[Heathey94]

Unfortunately, it didn't work. I still have the same problem of not being able to select an item from the dropdown.

I have attached my database again, so you can have another look after the changes I've made.

[ItsMe]

I am having difficulty understanding what you changed/did and what did not work. Also, "...not being able to select an item from the dropdown.", is not really describing the issue.

Do the following screenshots mean anything to you?

.

[Heathey94]

I removed the form and query, compact and repaired the database, then recreated them both as you suggested (well, I think I did it right, there's every chance I didn't)

So I am able to click on the drop down box as normal, but when I try to select an item from the list, nothing happens - the list stays open and on the option that was there already. Unfortunately I probably can't get a screenshot until tomorrow as I don't have access to the database (I may be able to if it is absolutely necessary, but I'm not sure if I have Microsoft Access on my home laptop)

[ItsMe]

It seems I was able to get further down the road than you. I was able to populate the fields and click the button control (Add). Then, I got the error posted in #24. I was careful to select a District prior to a Team. I typed values in the other fields.

Working in the AddAssesor form, I am unable to reproduce the symptom you described.

[Heathey94]

It seems I was able to get further down the road than you. I was able to populate the fields and click the button control (Add). Then, I got the error posted in #24. I was careful to select a District prior to a Team. I typed values in the other fields.

Working in the AddAssesor form, I am unable to reproduce the symptom you described.

I've found where we've been going wrong (a bit) my problem explained above was on the AssessorRecord form.

The problem you mentioned is because I decided to rename the AssessorRecords form to AssessorRecord and hadn't had chance to go through and update the links, I've now done it on the AddAssessor form, but none others - hopefully I will have done by the time I next upload the database.