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.
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.
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.
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.
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.
Not everywhere, just where it makes sense (such as the LiberiID on the main page). It gives users a choice.So are you suggesting I have combo boxes everywhere instead of text boxes?
For SearchAssessment, in the OnLoad of the form you can set it to the value from the table (Me!SearchAssessment = Me!AssessmentID).
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.
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.
I did try something similar, but it didn't work, thanks.
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.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.
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?
How are you making this happen, are you not allowing them to enter something by disabling the field on the form?I don't want Teams from the Police etc to appear in the "AllocatedTeam" etc dropdown at the bottom
I found a few problems here.can I get a message to appear which could take the user to the profile of the child they are trying to add
- 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
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.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 liberiid, you can do a dlookup and proceed the same.
[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.
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:
This is what happens after I update:Code: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
I've attached the database again to keep you up to date with where I am.
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):
Code:Private Sub CaseStatus_AfterUpdate() If CaseStatus = "SCS" Then Else CIN = "No" LAC = "No" CP = "No" Category = "" End Sub
See post #53 - you have your DoCmd.OpenForm the wrong way around.when I clicked assessments it took me to the AssessmentRecord for Child ID 3
Your compile error was purely a syntax error, you should be fixing those yourself in order to learn.
Code: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
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.
I tried switching them, but it doesn't seem to have changed anything - ID 3 is still the ID which is selected.
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.Your compile error was purely a syntax error, you should be fixing those yourself in order to learn.
Code: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
Ok, I'm working on it...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.
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?