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

[aytee111]

No, it is the manual placement of the controls.

[Heathey94]

As in where they are placed on the form?

[aytee111]

Yes, your form design. Colour is always nice too! First form can have the name of the system as a heading, maybe a logo or image of some kind. Gives the database some energy!

[Heathey94]

Oh, I see - I thought you meant it would automatically scroll to the top left.

The banner in the heading will have logos and colour, other than that I'll probably leave it until I get some feedback.

That just leaves the Add Assessment problem now, then I think (hope) it's ready for testing! (Oh, and I need to do the back buttons)

I saw your post by the way, so I followed the instructions and cleared the named macros, so hopefully there'll be no more problems...

[aytee111]

1 - this is for the Child form:

Private Sub ViewAssessments_Click()
If IsNull(DLookup("AssessmentID", "tblAssessment", "ChildID=" & Me!ChildID)) Then
 DoCmd.OpenForm "AssessmentRecord", , acNewRec, "ChildID=" & Me!ChildID
 Else
DoCmd.OpenForm "AssessmentRecord", , , "AssessmentNumber=" & DLookup("Max(AssessmentNumber)", "TblAssessment", "ChildID=" & Me!ChildID) & " AND ChildID=" & Me!ChildID
 End If
  Forms!AssessmentRecord.HoldLiberiID = Me.LiberiID
 Forms!AssessmentRecord.HoldChildID = Me.ChildID
 DoCmd.Close acForm, "ChildRecord"
End Sub

2 - Assessement form - add behaviours:

Private Sub Behaviours_Click()
Dim Response
If CP = True And CPStatus = "" Then
        Response = MsgBox("A Child on a CP Plan must have a Primary Category selected, please either select a category or change CP to No. Was the child on a CP Plan at the time of the assessment?", vbYesNo, "CP Category Error")
        If Response = vbNo Then CP = False
End If
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenForm "Behaviours", , , "AssessmentID=" & Me!AssessmentID
DoCmd.Close acForm, "AssessmentRecord"
End Sub

3 - fix assessment number

Private Sub Form_BeforeInsert(Cancel As Integer)
    Me!ChildID = Me!HoldChildID
    
    Dim NewNbr As Long
    On Error Resume Next
    NewNbr = DLookup("AssessmentNumber", "TblAssessment", "ChildID=" & Me!HoldChildID)
    If NewNbr > 1 Then
        Me!AssessmentNumber = NewNbr + 1
    Else
        Me!AssessmentNumber = 1
    End If
End Sub

4 - fix ChildID error:

Private Sub Form_Load()
If Not IsNull(Me!AssessmentID) Then
Me.SearchAssessment = Me!AssessmentID
Me.DistrictID = Me.TeamID.Column(2)
Me.OrganisationID = Me.TeamID.Column(4)
Me.District = Me.TeamID.Column(3)
Me.Organisation = Me.TeamID.Column(5)
StatusUpdate
End If
End Sub

[Heathey94]

Ok, so I've pasted that code, and the Add New Assessment button works, but if you start with a child with no prior assessments, the Liberi ID and Child's Name fields still appear blank (until you use the Add New Assessment button, then this information populates).

Update: I've added a line of code and this now populates.

Also, the assessment number combo box is blank for a child with no prior assessments (even after using the "Add New Assessment" button), ideally, I'd like this to display a number as it could cause confusion in the current format - I see you've got it to create the number, but it isn't currently displaying it. Also, for a child who has had a previous assessment, the assessment number doesn't change in the combo box, which I think will definitely case confusion.

Update: I don't think your code worked if the assessment number was 1, so I've added another IF statement. It may not be the simplest way of doing it, but it should work.

Also, the subform is still not displaying until an assessor has been selected, despite the fact I've tried "send to front" and I believe the settings indicate it should always appear.

I've attached my database again.

[aytee111]

the assessment number combo box is blank for a child with no prior assessments

If you look at the SQL for this combobox, you will see that the data comes from tables. As there is no data at this point the query will always come up blank. It is only after data is added to the tables that an assessment number can be displayed. Do you want to make this control invisible until there is data available?

the subform is still not displaying until an assessor has been selected

That is the nature of bound forms, there is nothing you can do to change it. You can fiddle with the record source, make it blank until an assessor is selected then populate it again. You could put a border around it which will always show, with a label saying Assessor Details or something which would then be more intuitive.

[Heathey94]

If you look at the SQL for this combobox, you will see that the data comes from tables. As there is no data at this point the query will always come up blank. It is only after data is added to the tables that an assessment number can be displayed. Do you want to make this control invisible until there is data available?

Ok, well I just attempted it, and it created another AssessmentNumber 2, despite there already being 8 on the system for that child, any idea what could have caused that?

That is the nature of bound forms, there is nothing you can do to change it. You can fiddle with the record source, make it blank until an assessor is selected then populate it again. You could put a border around it which will always show, with a label saying Assessor Details or something which would then be more intuitive.

Ok, I've got an idea on how to make it appear fine if that's the case, I was hoping there was a simple method of simply making it appear, but evidently not. I'll have a tinker.

[aytee111]

What did you "attempt"?

I have changed the code numerous times, maybe I didn't pass that information on! When you get the next assessment number you must add "Max" to your dlookup.

[Heathey94]

Where should I add it? I tried "Max(Dlookup(...", and an error message appeared "Compile error: Sub or function not defined", I assume I just put it in the wrong place?

[aytee111]

You know, you have done these things for yourself elsewhere! Confidence, that is what you need! I see even my code above doesn't include it, sorry. DLookUp("Max(Ass...

[Heathey94]

Have I? I don't remember that... It still doesn't like it though, did you mean like this?

    NewNbr = DLookup("Max(AssessmentNumber)", "TblAssessment", "ChildID=" & Me!HoldChildID))

It's saying there's a syntax error

[aytee111]

When you click on the Assessments from the Child form there is a Max there.

[Heathey94]

oh yeah... well I tried copying that code, and there's still a syntax error:

NewNbr = DLookup("Max(AssessmentNumber)", "TblAssessment", "ChildID=" & Me!HoldChildID)

[aytee111]

Not obvious - what is the exact error? Is there a value in HoldChildID?