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

[Heathey94]

By the way, my query regarding the Messagebox was on the AssessmentRecord Form (I added the code to the AfterUpdate for DocumentPK too), it seems to not work here, but works fine on the ChildRecord form (although SetFocus doesn't)

[aytee111]

if I try to change the ID on a current record

Had to change things around a bit:

Private Sub LiberiID_AfterUpdate()
    Dim HoldLiberiID As Long
    HoldLiberiID = Me!LiberiID
    If IsNull(DLookup("LiberiID", "tblChild", "LiberiID=" & Me!LiberiID)) Then
    Else
        Dim Msg, Style, Title, Response
        Msg = "This Liberi ID has already been entered onto the system. Please check you have entered the correct ID and the child is not already in the system. Do you want to view the record for the child with this ID?"
        Style = vbYesNo
        Title = "ID already exists!"
        Response = MsgBox(Msg, Style, Title)
        If Response = vbYes Then
            Me.Undo
            Me.Filter = "LiberiID=" & HoldLiberiID
            Me.FilterOn = True
            Me.Requery
        Else
            Me.Undo
            LiberiID.SetFocus
        End If
    End If
    
End Sub

it will just create a new record, forgetting the other information

Don't get this - can you iterate the steps to get here?

[Heathey94]

Don't get this - can you iterate the steps to get here?

Your code seems to have fixed it, so don't worry about that - but the Set Focus seems to be ignored for some reason, the focus is still on whatever cell you selected.

[aytee111]

The OnLoad of AssessmentRecord:
All that stuff you added shouldn't be there. Firstly, you have way too many unbound fields on the form where they should be bound. Change your Record Source to include those fields. Second, you are opening the form - which form? The one that is already open and you are now loading? Doesn't make sense. Clean it all up and let's start this issue again.

Note: Your code for those fields (CP, etc) is repeated in a few places. It is not recommended to repeat code - too many places to make changes when errors are found. Create a new procedure, Private Function (or Sub) SetFields(), move all the code into there, and call it whenever you need it: Call SetFields

[aytee111]

I also tried to do that set focus, couldn't get it to work!

[Heathey94]

One of the unbound fields I had just forgotten to bound (AssessmentPostcode), but the others I just want to display the text, not remember it (other than Allocated District/Organisation, but

Note: Your code for those fields (CP, etc) is repeated in a few places. It is not recommended to repeat code - too many places to make changes when errors are found. Create a new procedure, Private Function (or Sub) SetFields(), move all the code into there, and call it whenever you need it: Call SetFields

So would I put it all in one section, call it something and then whenever it should run just mention the name of the code?

I also tried to do that set focus, couldn't get it to work!

I've tried a few methods of mentioning the name of it as well - Me.LiberiID, Forms!ChildRecord!LiberiID - nothing seems to work.

[aytee111]

All of those fields are already "remembered"! They exist already in your tables. To bind a field all you have to do is have it in your record source - what is your objection to that?

Yes, just do like I said.

We'll get that to work when the db is more stable.

[Heathey94]

Correct me if I'm wrong, but is the only way to display the Team/District/Organisation and their respective ID not to have a Combo box - and both are required to do as you said, display the text, bind the ID?
If so, Combo boxes look like something the user needs to do something with, but in these cases, they do not - so I want it to be as obvious that that is the case as possible.


[Also, how do I create a function? I've googled it, but half the results are for excel (despite searching for access) and the other half leave my first line (which I assume is where the error is) in red.]
Ignore that bit, I managed to find one, the code just needs working on now.

[aytee111]

I described how to do it in post #79 - what don't you understand????

I'll think about the t/d/o.

[aytee111]

That whole IF statement we put in when opening the AssessmentRecord from the child was to display the max assessment - that is removed? Is that right? Now it displays the first one.

[Heathey94]

I couldn't get the function to recognise it was a function... but I've got it now, so it's fine.

[Heathey94]

No, I removed it because you said it doesn't need an IF statement... I think?

[aytee111]

That was after you had removed the dlookup for the max and made them both the same!

This is now the record source for your form:
SELECT TblAssessment.*, TblChild.LiberiID, TblChild.Surname & ", " & TblChild.Forename AS Expr1 FROM TblChild INNER JOIN TblAssessment ON TblChild.ChildID = TblAssessment.ChildID;
- don't have fields called "Expr..", very bad form!
- Make all the fields on your form bound to that, fix the control source of all your fields
- put all assessor's information on the subform (I think we have been thru this before?) - they must not be allowed to change it here
- don't have nonsensical fields on the form, such as post code.

One of the rules for database normalization is to keep names the same across tables. That is why I was so confused when you first starting using the word "allocated". It doesn't make sense, the t/d/o on the assessment record table aren't allocated, they are actual. "This is the team for this assessment", that is how it would be described in English. To answer the question - which team was this assessment done for? In relationships, this field would be linked directly to the team table and now the names are different. Bad practice.

[Heathey94]

That was after you had removed the dlookup for the max and made them both the same!

Oh, I must have done it by accident or something... I'll have a look.

don't have fields called "Expr..", very bad form!

I change it where it was going to be shown (in the headings for a combo box) but didn't think it was necessary to do so for the others, that shouldn't take too long to fix.

Make all the fields on your form bound to that, fix the control source of all your fields

I think I should be able to get most of that to work because of your next point... Not sure about Allocated District and Organisation though.

put all assessor's information on the subform (I think we have been thru this before?) - they must not be allowed to change it here

It was changed so the user could select organisation/District/Team/Assessor, but I thought earlier that it could be changed.

don't have nonsensical fields on the form, such as post code.

How is Post code nonsensical?

Allocated is the term the user would be used to - the child is allocated to that social worker (who is part of this team/district/organisation)

[Heathey94]

Also, what's wrong with this code?

If CaseStatus = "SCS" Then
    If LAC = True Then
        CIN = False
        CIN.Enabled = False
        If CP = True Then
            CIN = False
            CPStatus.Enabled = True
            CIN.Enabled = False
        End If
    End If
    Else
    If CP = True Then
        CIN = False
        CPStatus.Enabled = True
        CIN.Enabled = False
    Else
        CIN = True
        LAC = False
        CP = False
        CPStatus = ""
        LAC.Enabled = True
        CP.Enabled = True
        CPStatus.Enabled = True
        CIN.Enabled = True
    End If
Else
    CIN = False
    LAC = False
    CP = False
    CPStatus = False
    CIN.Enabled = False
    LAC.Enabled = False
    CP.Enabled = False
    CPStatus.Enabled = False
End If

It says compile error: Else without if, but it looks like there are the correct number of IFs, Else and End Ifs to me.