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

[Heathey94]

I assume you mean fields 0-9 right? How do I get rid of them? I can't see anyway of removing them, since I can't see where they're coming from in the first place.

[aytee111]

The first field in your SQL is an "*" which means show everything on the table. Then you repeat all the fields later on from the same table.

[Heathey94]

So are you suggesting I have combo boxes everywhere instead of text boxes? It just seems a little odd and pointless to have a combo box for a date etc.

[Heathey94]

The first field in your SQL is an "*" which means show everything on the table. Then you repeat all the fields later on from the same table.

Oh, I see! I didn't realise that was the case. That works now, thanks

The SearchAssessment combo box doesn't display the AssessmentNumber when you first open the AssessmentRecord page, can we get it to add it? I know we've set it to go to the assessment with the highest assessment number, but can we get it to display there from the beginning? I can see there being some confusion if there is a blank box.

[aytee111]

So are you suggesting I have combo boxes everywhere instead of text boxes?

Not everywhere, just where it makes sense (such as the LiberiID on the main page). It gives users a choice.

For SearchAssessment, in the OnLoad of the form you can set it to the value from the table (Me!SearchAssessment = Me!AssessmentID).

[aytee111]

Trying to sort out team/district/org:

The assessor has all three, however the assessor could have been in a different place when the assessment was done, hence the need to store these values on TblAssessment. When an assessment is entered, an assessor will be selected and the record will default to those values from TblAssessor, however the user can change the team (which will change the other 2).

I don't see how the job title of assessor makes any difference to the process.

[Heathey94]

Not everywhere, just where it makes sense (such as the LiberiID on the main page). It gives users a choice.

The LiberiID is a 6 or 7 digit number, which I thought would make it tougher to find the Child the user was searching for, but I guess at least that way they do have the option.

For SearchAssessment, in the OnLoad of the form you can set it to the value from the table (Me!SearchAssessment = Me!AssessmentID).

I did try something similar, but it didn't work, thanks.

Trying to sort out team/district/org:

The assessor has all three, however the assessor could have been in a different place when the assessment was done, hence the need to store these values on TblAssessment. When an assessment is entered, an assessor will be selected and the record will default to those values from TblAssessor, however the user can change the team (which will change the other 2).

I don't see how the job title of assessor makes any difference to the process.

There's only one difference - I don't want Teams from the Police etc to appear in the "AllocatedTeam" etc dropdown at the bottom, but yeah - I think you've got the jist of it.

I've got most of it working properly now, theres just a few things left - can I get a message to appear which could take the user to the profile of the child they are trying to add, if the Liberi ID has already been entered (as mentioned in the first post)?

Can I get the "Search" buttons to look if the child/assessor does already exist on the system, and provide the user with a message box to either go to the add child/assessor screen, or re-enter the details?

[aytee111]

I don't want Teams from the Police etc to appear in the "AllocatedTeam" etc dropdown at the bottom

How are you making this happen, are you not allowing them to enter something by disabling the field on the form?

can I get a message to appear which could take the user to the profile of the child they are trying to add

I found a few problems here.
- if I click on Add Child from the homepage, then enter a child, then click Add Child it gives me an error
- if I add a child record and then click on assessments it doesn't save the new record (not sure why)
- I added a save record here
- then an error happens as there are no assessments for this new child so added:
If IsNull(DLookup("AssessmentID", "tblAssessment", "ChildID=" & Me!ChildID)) Then
DoCmd.OpenForm "AssessmentRecord"
Else

Back to the issue at hand - as soon as the LiberiID is entered (AfterUpdate) do a dlookup on the liberiid and give an error message if it exists, with a Me.Undo

[aytee111]

Can I get the "Search" buttons to look if the child/assessor does already exist on the system, and provide the user with a message box to either go to the add child/assessor screen, or re-enter the details?

For the assessor, it being a combobox, you can use the NotInList event to ask the user if they wish to add it and do the logic depending on their answer.

For the liberiid, you can do a dlookup and proceed the same.

[Heathey94]

[QUOTE=aytee111;340809]How are you making this happen, are you not allowing them to enter something by disabling the field on the form?[\QUOTE]

No, I'm just limiting the query - if the assessor is from the police, the allocated team still needs to be completed, it will just have the team of the social worker.

[QUOTE=aytee111;340809]
I found a few problems here.
- if I click on Add Child from the homepage, then enter a child, then click Add Child it gives me an error
- if I add a child record and then click on assessments it doesn't save the new record (not sure why)
- then an error happens as there are no assessments for this new child so added:
If IsNull(DLookup("AssessmentID", "tblAssessment", "ChildID=" & Me!ChildID)) Then
DoCmd.OpenForm "AssessmentRecord"
Else[\QUOTE]
I didn't have either of these problems when I just tried to copy what you did, although when I clicked assessments it took me to the AssessmentRecord for Child ID 3 - I tried this twice with very different LiberiIDs and it took me to the same record (I thought maybe that was the problem), I thought maybe the code you posted might solve this problem, but it didn't appear to make a difference.

Back to the issue at hand - as soon as the LiberiID is entered (AfterUpdate) do a dlookup on the liberiid and give an error message if it exists, with a Me.Undo

For the assessor, it being a combobox, you can use the NotInList event to ask the user if they wish to add it and do the logic depending on their answer.

For the liberiid, you can do a dlookup and proceed the same.

OK, so I managed to code which I thought would allow me to do what I mentioned, but it doesn't seem to work, here it is:

Private Sub LiberiID_AfterUpdate()
If IsNull(DLookup("LiberiID", "tblChild", "LiberiID=" & Me!LiberiID)) Then
Else
Dim Msg, Style, Title, Response, Action
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 DoCmd.GoToRecord( , , "LiberiID=" & Me!LiberiID) Else Me.Undo
End If
End Sub

This is what happens after I update:

I've attached the database again to keep you up to date with where I am.

[Heathey94]

Also, in a couple of places I have restricted whether a field is enabled based, on another field - at the bottom of the AssessmentRecord form is a prime example of this. There is one row which is almost entirely not enabled unless others have specific values, but I have some problems/queries.



CIN should be "No" and only enabled to allow change if Status is "SCS"
LAC and CP should be "No" and only enabled to allow change if Status is "SCS" and CIN is no.
Category should be blank and only enabled to allow change if CP is "Yes".
If the field becomes disabled again, it should revert back to its default state (No/blank).

Any suggestions on how this can happen? it makes sense in my head, but I'm not sure how to go about getting it to work. I assume making it enabled/disabled is through conditional formatting, but I don't know how to make it AND instead of OR, and forcing the text to something else seems would just be adding something along the lines of the below in the After Update event, but it hasn't done anything and can't deal with Category being blank (this is after the update of the CaseStatus field):

Private Sub CaseStatus_AfterUpdate()
If CaseStatus = "SCS" Then Else
CIN = "No"
LAC = "No"
CP = "No"
Category = ""
End Sub

[aytee111]

when I clicked assessments it took me to the AssessmentRecord for Child ID 3

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

[aytee111]

Your compile error was purely a syntax error, you should be fixing those yourself in order to learn.

Private Sub LiberiID_AfterUpdate()

    Dim Msg, Style, Title, Response, Action
    
    If IsNull(DLookup("LiberiID", "tblChild", "LiberiID=" & Me!LiberiID)) Then
    Else
        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

[aytee111]

Conditional formatting is only required on continuous forms. As this is a single record form you can use VBA to set your values.

This is how you will get there:
1 - write it down in complete sentences - pen and paper! People stare at the computer screen and move the mouse back and forth, but the brain doesn't work that way.
2 - add "IF/Else" to those sentences
3 - continue with step 2 while it becomes more and more to resemble computer code
Only then can you copy it to VBA code.

Note: start to code using the accepted looks of it, such has mine posted above. Yes, Access doesn't care but it will help a lot, far easier to read a decipher.

[Heathey94]

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

I tried switching them, but it doesn't seem to have changed anything - ID 3 is still the ID which is selected.

Your compile error was purely a syntax error, you should be fixing those yourself in order to learn.

 Private Sub LiberiID_AfterUpdate()

     Dim Msg, Style, Title, Response, Action
     
     If IsNull(DLookup("LiberiID", "tblChild", "LiberiID=" & Me!LiberiID)) Then
     Else
         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

I would have done, but I didn't know where the problem was. Your fix just wipes the duplicate ID that is entered, a message box doesn't appear first.

Conditional formatting is only required on continuous forms. As this is a single record form you can use VBA to set your values.

This is how you will get there:
1 - write it down in complete sentences - pen and paper! People stare at the computer screen and move the mouse back and forth, but the brain doesn't work that way.
2 - add "IF/Else" to those sentences
3 - continue with step 2 while it becomes more and more to resemble computer code
Only then can you copy it to VBA code.

Note: start to code using the accepted looks of it, such has mine posted above. Yes, Access doesn't care but it will help a lot, far easier to read a decipher.

Ok, I'm working on it...

On the behaviours form, the same applies - if the Risk Present changes to no, Assessment and 3rdPartyIntelligence should also revert to no, as this is a continuous form, I assume this cannot be done VBA, have you any suggestions?