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

[Heathey94]

"Compile error: syntax error" and it highlights that line of code, this is how the top fields look before I hit the "AddNewAssessment" button (Apologies for the writing):



And this is after:

[aytee111]

I'm not sure why yours is behaving differently to mine, did you make changes since your last database? Post here just the code in the Add New button.

[Heathey94]

I've just realised it's not from the Add new button, it's from the Form before insert event:

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

The code in the button doesn't seem to be a problem now I've realised this, here's the code anyway:

Private Sub Cmd_New_Click()
Dim NewNbr As Long, Response
If CP = True Then
If 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
Else
DoCmd.GoToRecord , , acNewRec
Me!LiberiID = Me!HoldLiberiID
On Error Resume Next
NewNbr = DLookup("Max(AssessmentNumber)", "TblAssessment", "ChildID=" & Me!HoldChildID)
If NewNbr > 1 Then
Me!AssessmentNumber = NewNbr + 1
Else
Me!AssessmentNumber = 1
End If
'add the new number to the combobox
Me!SearchAssessment.Requery
AssessorUpdate
End If
Else
DoCmd.GoToRecord , , acNewRec
Me!District = ""
Me!Organisation = ""
Me!LiberiID = Me!HoldLiberiID
On Error Resume Next
NewNbr = DLookup("Max(AssessmentNumber)", "TblAssessment", "ChildID=" & Me!HoldChildID)
If NewNbr > 1 Then
Me!AssessmentNumber = NewNbr + 1
Else
Me!AssessmentNumber = 1
End If
'add the new number to the combobox
Me!SearchAssessment.Requery
StatusUpdate
End If
End Sub

[aytee111]

Duh! There are 2 brackets at the end

[Heathey94]

wow that was stupid. ok then!

Right, last thing - I think - to get the subform to appear there, I've tried creating the fields again which are locked and unbound, and appear in the same spot as the actual fields, but the subform is still obscuring them, even though I have set it to be invisible when the AssessorName Combo is null or "" (I tried different methods) I've attached my database again so you can see what I mean.

[aytee111]

Sorry to keep on about this - your code contains repeated stuff. What this means that every time your repeat something you have to make any and all changes in ALL places, not just one or two. Your choice - repeat and maintain multiple, or only do things in one place.

[Heathey94]

I'll look into that now. Am I right in thinking you can't get it to reference code in another forms' VBA, and therefore any code will need to be repeated on each form it is required?

Also, any suggestions on the problem I mentioned above?

[aytee111]

Start the form with the subform invisible.

If you need to repeat code:
- if it is on the same form then make a private sub which does it and call it each time you need it
- if the code is used in multiple forms and/or queries, then create a global module and make it a public sub

[Heathey94]

That worked, but now it won't become visible if there is something entered in the Assessor's Name combo. Also, when I change the Assessor using the Assessor's Name combo, this error appears:
"The expression After Update you entered as the event property setting produced the following error: Member already exists in an object module from which this object derives.
*The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event or macro."

The code entered in that event references the following function:

Function AssessorUpdate()
If IsNull(Me.AssessorID) Then
Me.AssessorDetails.Visible = False
Else
Me.AssessorDetails.Visible = True
End If
End Function

Also, when opening the Assessment Record, the following error appears: "Run-time error '40036': Method 'Item' of object 'Forms' failed", this highlights the below code in the code for the "ViewAssessments" button.

 Forms!AssessmentRecord.HoldLiberiID = Me.LiberiID

Finally, I tried pasting this code into a global module:

Dim BehID As Long
 On Error Resume Next
 BehID = DLookup("BehaviourID", "TblAssessmentDetails", "BehaviourID=" & Me!BehaviourID & " AND AssessmentID=" & Me!AssessmentID & " AND ResponseID<>" & Me!ResponseID)
 If BehID > 0 Then
 MsgBox "This behaviour has already been selected."
 Cancel = True
 End If

But it doesn't like the "Me!" section, I'm not sure if changing it will still mean it is able to do what it is meant to do and check the row just added.

[aytee111]

First issue - not happening for me, there must have been some other changes which have created this problem.

Yes, can't use "me' as there is no object in a global mod. Change it to Forms!formname!controlname

[aytee111]

Or better, pass the control as a parameter, then it doesn't matter which form/query is calling it.

[Heathey94]

Maybe it was when I was attempting to restrict the number of repetitions. I'll restart with the version I had this morning and go from there.

Yes, can't use "me' as there is no object in a global mod. Change it to Forms!formname!controlname

Yeah, I thought that was the case, but I'm not sure if it will then still look at the row which was just changed/added? Also, its on the subform, so do I reference that? If so, there's no point in having a global module because it'll be different on each one anyway.

[aytee111]

What is this subroutine doing?

[Heathey94]

It checks to see if the behaviour is already in the table for that assessmentID, if so it undoes the last action (changes it back to the behaviour already selected or deletes the row if it's just been added) - well, that's what I want it to do, not sure what (if any) of the brackets it actually does.

[aytee111]

Good idea. All you need to pass to the global sub is the behavior ID and the Assessment ID, then return error true or false.