Page 2 of 16 FirstFirst 123456789101112 ... LastLast
Results 16 to 30 of 233
  1. #16
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    Unfortunately, the sound system on my work computer just emits a lot of background noise (apparently someone spilt a drink on it before I started) so I have to rely on subtitles, which aren't exactly accurate. I'll give it a go though.



    Regarding my Team/District query for the assessment - I can't think of a way of doing this without the Team/District being overwritten by the assessors new Team/District if the assessment is opened at a later date. Any suggestions?

  2. #17
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    If you want to keep that information you will have to store it on a table. If the Team and District apply strictly to the assessment and must not change then that is the table to put them. Your original way of entering those fields then will be the correct way but they must be bound to TblAssessment.

  3. #18
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    I think I've found it - I created a link between the subforms and the form, using the "Link Master fields" section was this correct? it's the opposite way round to what you put, but was an obvious thing I hadn't done but you had (I think maybe you put linked when you meant not linked?)

  4. #19
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    Regarding your other comment (which I forgot to reply to), the Team/district the assessment should be recorded next to, is decided by the team and district the Social worker is allocated to at the point of the assessment. In most cases (and everywhere here so far) I have assumed that the assessment is completed by the social worker, and therefore the social worker and assessor are one and the same. As there is the possibility that this is not the case, I think I'll just have it as a separate field, as you suggest.

    Going back to one of my original queries, is it possible to create a message box to have the options I described at the start - provide the option to go to the child's record/stay on the page to change the LiberiID?

  5. #20
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Don't get what you are asking. Which form are you referring to?

  6. #21
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    Ok, It's the ChildRecord form under the new layout. If the user attempts to enter a Liberi ID which has already been entered onto the table, I'd like a message box to appear to give them the option of either:
    Viewing the record of the child, of the other Liberi ID which was entered
    Going back to the Current ChildRecord form so they can change the Liberi ID which they are attempting to enter.

    Also, I've changed the AssessmentRecord form so that it is easier for the user to choose the assessor, when entering the details of a new assessment. Now, they will select an organisation, district, team then assessor, however I'm not sure how this would work when viewing an assessment which has already been entered onto the system. Also, nothing is appearing in the Team combo box now, and I can't work out why. The Organisation and District fields are working fine, the query behind the Team combo box is working (I changed the Criteria to '4' to check that wasn't the problem) I can't work out how to check the actual criteria is working, but I just ran a query with exactly the same information and criteria and that worked, and I've replaced the criteria multiple times with the same result.

    Any ideas? I've attached the latest version, for your reference.
    Attached Files Attached Files

  7. #22
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Child form: this doesn't make sense, there seems to be multiple ways to add a new child? You are adding serious maintenance issues unnecessarily, why are you doing it this way? Also, are you saying that they want to be able to change the LiberiID for an existing child? I need to understand the user process flow for this, that field is unique so you have to be careful about changes.

    Assessment form: I would allow the user to enter the Assessor first and default to the existing data from the Assessor record. Unless those 3 fields are different every time an assessor makes an assessment?
    You've done it again - created a field that is unbound (Organisation). Don't make them enter yet another field, default it from TblDistrict. Make things as quick and easy as possible for entering. Same with District, default it to TblTeam.
    This will make your next problem (blank Team) go away! However, whenever you have a combobx that is dependent on another field on the form, once the user enters that field the row source needs to be recreated: in the AfterUpdate event of district Me!Team.Requery.

    Never store fields twice in your database - one of the rules of normalisation. - only pointers. TblAssessment now contains team and district but those fields are already stored in the database. AND they are unbound!!!!!!!

  8. #23
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    I thought there was only one way? The "Add Child" button on the Homepage takes you to the same form, it just begins with a new record appearing. Alternatively, if they know the child isn't on the database, they can add them from the homepage, rather than having to search for one first. I have done it this way as the user may not know if the child is already listed, so they search, find out, and act accordingly (add the child or continue to adding the assessment).

    Yes, there is the possibility that the user will need to change the Liberi ID as they may have entered it incorrectly originally. It shouldn't happen, but it could.

    If they enter the assessor first, they would have to select one from hundreds, this way they narrow it down by selecting the organisation, district and then team - there are only around 6 assessors per team. I created the Organisation combo box as - as described above - in the instances the assessor is not from the council, the assessor would not fit into any of the current groupings.

    The VBA was there, it just hadn't linked it for some reason. It has indeed made it go away, but now it's coming up with an error that it can't empty the AssessorID field.

    It's not exactly the same - the assessor team/district is specific to the assessor, the assessment team/district is the team/district the social worker was in at the time of the assessment (where the social worker may be, but is not necessarily, the assessor).

  9. #24
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    There is a button on the Child form to add a new child, there is also one on the homepage.

    How do they search for a child to see if it is there already? You give them no method. I know you downloaded the form - is the child's name not on it, only the number? In which case there will be no need to search, either it is there or it isn't.

    Because LiberiID is a unique field, if they need to change it you first need to see if the new one exists already. You can use DLookUp for this.

    A combobox for the assessor would be sorted in alphabetical order by surname - the user won't have to search thru hundreds, only one or two duplicates.

    Not sure what your "empty AssessorID" is referring to.

    Now I am thoroughly confused - where did social worker come from all of a sudden?

  10. #25
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    In what I think is post 19 (can't see post numbers on my phone) I mentioned that all social workers are assessors, but I had just realised the reverse is not necessarily true - assessors may not be social workers.

    Yes, there are two buttons to add a child, but they both take you to the same page, the homepage button just means you can skip the search for a child if you want to.

    The child name and ID are both on the form, I felt it would be easiest to search for the ID rather than the name since there could be more than one child with the same name.

    I thought you would need to search, since you don't know if the child has already been added to my database? Like I said ( it sure if this is the confusion) Liberi ID is the primary key on another database (not access or anything that can be linked to access).

    Ok, I'll have a look at adding a dlookup on Monday.

    Oh, you can search in the combo box and it will return values that still match?
    Also I just thought - what if there is an assessor who shares their name?

    I'll look at the assessorId on Monday again too.

    Finally, I mentioned it briefly before - on the assessment record page, instead of searching for the AssessmentID, can I create a calculation which will look at how many assessments the child has had before? So basically:
    Count where ChildID (all in table) equals Child ID (current record) and Date of Assessment (all in table) is < Date of Assessment (current record)

  11. #26
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Your last point - that too is a dlookup, or a DCount, which will return the value you are looking for to be displayed as a textbox on the form.

  12. #27
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    Can it be in a combo box, or does it have to be a textbox?

  13. #28
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Is this a display-only field, or do you want them to enter the number of records? Am I missing something?

  14. #29
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    I want them to choose which assessment they want to look at - so instead of showing the assessment ID (which means nothing to them), I'd like the assessment number for that child to be displayed.

    I've changed to your way of thinking - regarding selecting Team/District/Organisation - following discussions with some potential users, however I have hit a few stumps:

    The SearchAssessor feature (on the Homepage) isn't pulling the AssessorID through to the AssessorRecord form.

    When starting with a blank assessor record, my DLookups in District and Organisation work, but #Error appears when there is nothing entered into the team field. I tried entering an IIF, which removes the error, but it doesn't seem to get to the Dlookups.

    They did also work with assessors already selected before I changed the method of selecting assessors and adding the IIF, so this may or may not still work.

    My database is attached for reference.
    Attached Files Attached Files

  15. #30
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    SearchAssessor homepage:
    - sort it
    - usually it is best to separate first name and surname into 2 separate fields on tables, then sort it by surname
    - the control source of the assessor form is a query (!) - change it to TblAssessor; it is filtering on assessor name = assessor number! You must have only one filter anyway

    Adding a new assessor:
    - team, district and org all need to be combobboxes bound to the assessor table, not dlookups (are these even need to be here when you are going to be re-entering them on the assessment screen?)

    Selecting an assessment:
    - there is currently no assessment number entered
    - once you fix that then add a combobox which allows them to select one

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 13
    Last Post: 04-21-2016, 03:33 AM
  2. Macro to Open Form Triggers Exclusive Access Message
    By snakatsu in forum Database Design
    Replies: 5
    Last Post: 11-10-2015, 10:46 PM
  3. Replies: 6
    Last Post: 09-30-2015, 03:14 PM
  4. Multiple options based on a tree structure...
    By blue22 in forum Database Design
    Replies: 3
    Last Post: 01-09-2014, 05:58 AM
  5. Replies: 1
    Last Post: 08-01-2011, 04:17 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums