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

[aytee111]

I tried switching them, but it doesn't seem to have changed anything

Post your code.

Your fix just wipes the duplicate ID that is entered, a message box doesn't appear first

Why are these things working for me and not for you? You can see by the code that the message should be displayed first.

if the Risk Present changes to no, Assessment and 3rdPartyIntelligence should also revert to no

This can be done in VBA in the AfterUpdate of RiskPresent. You are dealing with data this time, not the appearance of controls on the form.

[Heathey94]

View Assessments - It appears that it does this when the child does not have any assessments already, if they do then the child is continued over:

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

Duplicate ID Messagebox:

Private Sub LiberiID_AfterUpdate()
    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!"
        If Response = vbYes Then
            Me.Filter = "LiberiID=" & Me!LiberiID
            Me.Undo
            Me.FilterOn = True
            Me.Requery
        Else
            Me.Undo
            DoCmd.GoToRecord , , acNewRec
        End If
    End If
End Sub

This can be done in VBA in the AfterUpdate of RiskPresent. You are dealing with data this time, not the appearance of controls on the form.

Oh, I see, I'll give that a go too then. Currently checking my code for the Status/CIN etc works under every circumstance.

[aytee111]

You are starting to NOT follow my instructions!

See post #53 - you have your DoCmd.OpenForm the wrong way around

You changed it all.

Duplicate ID Messagebox:

What does that mean? And where is Response?

[Heathey94]

I did just change that around originally, but it didn't work so I thought maybe you meant that, so I moved that as well, here is my first attempt:

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

"Duplicate ID Messagebox" is just what I called it so it was clear what code was in there. What do you mean "where is Response?" It's in both places it should be... I think

[aytee111]

If Response = vbYes Then

What is Response equal to at this point?

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

[Heathey94]

oooooh.... It looks like when I copied your code over, I missed a line (below) it now works, thanks

Response = MsgBox(Msg, Style, Title)

I can't spot the difference between our codes?

[aytee111]

OK. Now I've lost track of the original issue again! Is this not doing what you want it to do?

[Heathey94]

The message box bit is working (yay!), but the View Assessments button is still taking me to the AssessmentRecord page with childID 3 - if I choose a child with no assessments already on the system, instead of allowing an assessment to be added for that child.

[aytee111]

You changed the OnLoad event of the AssessmentRecord form, added a whole bunch of stuff including opening itself. I see there are a few things changed, throws things out of whack.

When you open the Assessment record form from the ChildRecord, now there should be no IF statement - it doesn't matter whether records exist or not.

What is DocumentePK? Again you have two primary keys on a table (TblChild is the other one). I guess I can enter any old number. Users don't understand technical words, by the way, best to keep them separate (PK).

[Heathey94]

Like Liberi ID, document PK is an ID generated by another system. I want there to be the ability to change it, in case the wrong ID was entered originally (again like Liberi ID) It's called Document PK on the system, but I may have the ability to change what that says, so could potentially change it.

Okay, I'll have a look at those things you mentioned tomorrow.

[aytee111]

Thought you'd gone to bed! OK, makes sense what you say, best to keep the names the same.

[Heathey94]

I meant to reply once I got home but forgot until then!

I've found two problems with the Messagebox (one I managed to partly fix) - if I try to change the ID on a current record, selecting yes on the messagebox means it tries to keep the ID left on there (I believe)
This is the message that appears:

and it highlights
the code in italics

Private Sub LiberiID_AfterUpdate()
    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.Filter = "LiberiID=" & Me!LiberiID
            Me.Undo
            Me.FilterOn = True
            Me.Requery
        Else
            Me.Undo
            DoCmd.GoToRecord , , acNewRec
        End If
    End If
End Sub

Finally, if the user was viewing a current record (or completed the rest of the form first), it will just create a new record, forgetting the other information. As a result, I'll remover the "DoCmd.GoToRecord , , acNewRec" section of the code, but to make sure they don't try to keep the ID as the duplicate, I'd like to ensure the Liberi ID is selected again - so they go in a loop unless they change the ID or select "Yes", is there a way to do this I thought "LiberiID.setFocus" would work, but it doesn't seem to make a difference. Any suggestions?

Also, I have been able to change the DocumentPK on the other system, so I have change the versions the user can see to "Document ID", but left the object name as DocumentPK.

[Heathey94]

Also, if I comment out the bits as you suggested:

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

it says there is a syntax error (missing operator) in query expression 'ChildID=' and highlights the code below on the form load event of the AssessmentRecord Form

DoCmd.OpenForm "AssessmentRecord", , , "AssessmentNumber=" & DLookup("Max(AssessmentNumber)", "TblAssessment", "ChildID=" & Me!ChildID) & " AND ChildID=" & Me!ChildID

Would I be correct to assume this is because it is trying to find the maximum AssessmentNumber for a Child which does not have an assessment recorded already, and so the if statement needs to move to here?

[aytee111]

Can you post your latest database? Mine is a bit outdated.

[Heathey94]

Here you go.