No, it is the manual placement of the controls.
No, it is the manual placement of the controls.
As in where they are placed on the form?
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!
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...
1 - this is for the Child form:
2 - Assessement form - add behaviours:Code: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
3 - fix assessment numberCode: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
4 - fix ChildID error:Code: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
Code: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
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.
Last edited by Heathey94; 01-06-2017 at 06:25 AM.
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 assessment number combo box is blank for a child with no prior assessments
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.the subform is still not displaying until an assessor has been selected
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?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, 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.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.
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.
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?
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...
Have I? I don't remember that... It still doesn't like it though, did you mean like this?
It's saying there's a syntax errorCode:NewNbr = DLookup("Max(AssessmentNumber)", "TblAssessment", "ChildID=" & Me!HoldChildID))
When you click on the Assessments from the Child form there is a Max there.
oh yeah... well I tried copying that code, and there's still a syntax error:
Code:NewNbr = DLookup("Max(AssessmentNumber)", "TblAssessment", "ChildID=" & Me!HoldChildID)
Not obvious - what is the exact error? Is there a value in HoldChildID?