Access Structure Design (form process), open message box with multiple options on error

[aytee111]

I change it where it was going to be shown

These things will come back and bite you. I am going to foretell your future - you will be coming back to this database to make changes after not touching it for months and you will have forgotten everything. Do your utmost at this point to tell that future you what is going on with everything - good names, comments in the code, queries that make sense, etc. Much of what I have been telling you to do has been based on this.

How is Post code nonsensical?

It isn't used or referred to anywhere.

Allocated is the term the user would be used to

There is the tech side of the database and the user side, keep them very separate.

[aytee111]

Line-up problems. Most important to line up If/Else's.

[Heathey94]

These things will come back and bite you. I am going to foretell your future - you will be coming back to this database to make changes after not touching it for months and you will have forgotten everything. Do your utmost at this point to tell that future you what is going on with everything - good names, comments in the code, queries that make sense, etc. Much of what I have been telling you to do has been based on this.

Yeah, OK - I'll give that a go - it makes sense, even if some of them are obvious eg name.

It isn't used or referred to anywhere.

(As I mentioned before) I'd accidentally forgotten to bind the postcode before I sent you the file, it is required, I just hadn't thought of how it would be needed there until today.

There is the tech side of the database and the user side, keep them very separate.

I also know it as Allocated, which is why I used it which is why I used that terminology.

Line-up problems. Most important to line up If/Else's.

I had, but couldn't see the problem - leaving the screen for a bit and coming back to it seemed to help.

[aytee111]

I also know it as Allocated

First and foremost is the table design, that is the foundation upon which everything else is built. It is most important to get this right over and above everything else. Setting up links in the relationships window will show these errors, not errors exactly just not following rules for good design.

I had...

No comment!

[Heathey94]

So... more like this?

I had...

No comment!

Well, I thought I had, when I got chance to look at it this morning, that appeared to be false - it works now though.

[aytee111]

Excellent! So neat and tidy and self-explanatory.

[Heathey94]

Ok, so this is my database now:
Assessment Database.zip

I managed to find a way of binding Allocated District and Organisation (on the AssessmentRecord form - just create another subform), but changing the Team means the subform disappears, and I can't work out why, because its set up in the same way as the other subform on that page - as far as I can see, anyway.


Also, opening the AssessmentRecord form creates run time error '13' Type mismatch, and references this:
[CODE][Private Sub Form_Load()
If Error(DLookup("AssessmentID", "tblAssessment", "ChildID=" & Me!ChildID)) Then/CODE]
I've just checked, and ChildID is a number field, so I don't know why this has happened.

Also, is it possible to change what a label displays dependent on other values, or does it have to be a text box? It's not a big problem to use a text box, just curious - and otherwise it would mean another unbound field.

[aytee111]

Still a bit of confusion on the AssessmentRecord form. The record source of the form should only contain fields that are needed. There are two AssessorID's being brought in - not only a repeat but they have exactly the same value! The assessor information is not being used at all. So that is the first thing you must fix.

Secondly, this second subform that you have added is unnecessary and I feel there is a misunderstanding of the way this form works. The main form is tied to TblAssessment which contains all three fields, t/d/o, so they should just be another three fields on the form, not treated any differently to any of the others. Design the form according to what the user can enter/modify - e.g. first they enter TeamID, a combobox, then requery DistrictID combobox, if they can enter it, otherwise display it from TeamID. Same with org.

The only time you need a subform is when you want to display DIFFERENT information to the main form's record source, such as the one for assessor, all that information is coming from a whole new table. All the way along you have been messing around with this form and its fields and where the information is coming from, setting it in VBA and all that, when all along all that has been required is TblAssessment (and the child info) and the rest falls into place.

I cannot repeat strongly enough that your control on the form match the control source. This would go a long way towards removing the confusion. You call it allocated when it is an ordinary old TeamID, etc, trying to be a user while doing technical work. Your poor brain has thrown its arms up in horror!

[aytee111]

You are still opening the form again - you open it from ChildRecord then you open it again once it is open and you are loading it. I have mentioned this a few times but you don't believe me!

[Heathey94]

Still a bit of confusion on the AssessmentRecord form. The record source of the form should only contain fields that are needed. There are two AssessorID's being brought in - not only a repeat but they have exactly the same value! The assessor information is not being used at all. So that is the first thing you must fix.

I had wandered why the control Source looked weird, but I just didn't connect the dots, this has now been corrected.

Secondly, this second subform that you have added is unnecessary and I feel there is a misunderstanding of the way this form works. The main form is tied to TblAssessment which contains all three fields, t/d/o, so they should just be another three fields on the form, not treated any differently to any of the others. Design the form according to what the user can enter/modify - e.g. first they enter TeamID, a combobox, then requery DistrictID combobox, if they can enter it, otherwise display it from TeamID. Same with org.

That was just a way of binding the forms, I've reverted it back to how it was, but for some reason it doesn't display it on load (this is the same with many things in the on load event, including the text in the StatusUpdate function I created - even if it works elsewhere)

[Heathey94]

You are still opening the form again - you open it from ChildRecord then you open it again once it is open and you are loading it. I have mentioned this a few times but you don't believe me!

It's not that I don't believe you, I just get distracted by other stuff and forget to ask what you mean.

[aytee111]

Once you have fixed these things, post your db. Also, figure out what you want to happen on opening the Assessment Record form, e.g.:
- from the ChildRecord form, if the child exists and there are records for it, show the latest assessment
- if this is a new child, show a blank form

Get this straight then you will know how to open the form and where/how to populate the fields.

[Heathey94]

Ok, I've made the changes, just have a quick query before I post, can I ensure that a form is kept open if someone closes it? (I want to make sure a CP Category is selected if CP was selected as yes), I was going to put it in the OnClose event

[aytee111]

Not too sure why you need to hold on to that value once the form closes? You will see that I use "Hold" fields due to the fact that the forms keep closing and the value is lost. You may think about this or provide further details as to the process.

[Heathey94]

I wanted to make sure that the user didn't select CP as yes, not select anything from the CPStatus combo, and close the page through the close window button (instead of through the buttons, where I have set the message up), if it can't be done, it can't be done I guess.