Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 37
  1. #16
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I threw that form together just to show you the method, you must make it your own. Use the way it is done, add and remove items as desired.

    Each combobox has an SQL statement (query) in its Row Source and here you collect any fields that you want from any table, add criteria, etc. Remember to keep the Column Count and Column Widths in sync. Once the user makes a selection you now have access to those fields and can display them. See how I have done it, on the AfterUpdate of the combobox I populate the fields on the screen, also they are available for use while the form is open. I don't know if you can do this in a macro, I don't use macros. You can play around with that. Another feature is to make fields invisible, such as the ChildID - you need the field later on but the user doesn't necessarily have to see it, it would just clutter up the form.

    Another (maybe easier) way to display the data is to create a subform from the Assessor table and then when the user makes a selection in the combobox the subform will be linked to it via the ID and the data will be displayed. This will avoid manual population of each field (as I have done), which is fine for one or two fields but when you are displaying the whole table this is probably a better way.

  2. #17
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    I (thought) I had done the subform version previously, but it couldn't seem to find the assessor ID - the problem I mentioned at the beginning of this thread.

  3. #18
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The assessor ID will be available once the user makes a selection in the combobox. Your first problem had no assessor selected yet.

  4. #19
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    But it didn't work even when I did select the assessor?

  5. #20
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Can't remember where the assessor was selected. Somewhere along the way there was a link in the chain that was broken. But, leave the old troubles behind - work on getting it working this new way!

  6. #21
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    OK, I'm trying the subform way since I already have the AssessorDetails form which I think should do what I want it to do... but after I pulled the AssessorDetails form in to make it a subform, it automatically pulls through the information for Mickey Smith (the last record in tblAssessor) - and doesn't change when I select another Assessor from the dropdowns.

    Also, I can't see the Row Source in the Property Sheet section, how can I view it to look at how you've done it if I do try the former version (I think it will be helpful on a later form)?

    On another note, I do like your suggestion of assigning an assessor to a child, but I think there would be many children assigned to each assessor, and each child could have multiple assessors, creating a many-to-many relationship. Do you have any thoughts on this?

  7. #22
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    See new Form1. I created a subform based on TblAssessor. The link fields from the main form to the subform is the AssessorID. So when that ID changes so will the data in the subform.

    Each control on the form has a set of properties. Click on a field to see its properties. Click on the Assessor combobox and see its properties, there you will find Row Source.

    If there isn't a link between child and assessor then there is no reason to add it. It is on the details table and it seem that is where it belongs.
    Attached Files Attached Files

  8. #23
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    Oh, I didn't realise it was from the combobox. It's now almost perfect. I say almost, because it seems to be populated already, before I select anything in the Assessor dropdown box - I think it is "remembering" what was in there last time, is there a way to stop this? I have tried an event on load - the assessor's name field is emptied, but the text still appears initially, before being removed - can it be done automatically?

    My description may not be particularly clear so I'll add the database when I've compacted it etc, but since it may be a simple fix you already know about, I thought I'd post first.

  9. #24
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I'm not sure what you mean when you say "it" is populated already. When I open the form it is blank. The form has no record source so all fields are empty on first opening it. Are you opening it from somewhere else? You can set the Assessor ID field to Null, but rather track down exactly what is happening first.

  10. #25
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    Maybe it's because I am opening it after there has already been data in it? I've tried opening the form directly and by following the form process - search ChildID->ChildRecord->AddNewAssessment with the same result (changing the assessor in the dropdown before closing, does change what details appear when it is next opened)
    Attached Files Attached Files

  11. #26
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    What did you name the new form? Or where are the changes?

  12. #27
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    The changes are on the AddAssessmentRecord form
    Last edited by Heathey94; 09-23-2016 at 09:16 AM. Reason: Got carried away and didn't check the name of the form before posting...

  13. #28
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    "AddAssessmentRecord" form? Can't see anything new.

  14. #29
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    The subform below assessor's name with Job Title, Organisation, Work Number etc?

    I just checked and I did upload the right version...

  15. #30
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    It works for me, maybe you have to exit out of the field. It is on the AfterUpdate event which only fires up after you get out of the field.

Page 2 of 3 FirstFirst 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Subform Requery
    By MTSPEER in forum Programming
    Replies: 15
    Last Post: 04-29-2015, 09:14 AM
  2. Subform Requery
    By MTSPEER in forum Forms
    Replies: 1
    Last Post: 03-12-2015, 08:20 AM
  3. requery subform
    By nswhit in forum Forms
    Replies: 2
    Last Post: 05-16-2013, 09:34 AM
  4. requery in subform
    By putte11 in forum Forms
    Replies: 7
    Last Post: 04-22-2013, 02:43 PM
  5. Replies: 3
    Last Post: 04-17-2012, 10:28 AM

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