Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Starting from tblFiles in the attached mdb, is it possible to create a query listing all its fields but substituting the TypistID field with a column that, if the corresponding Accepted field is checked, lists the tblFiles.TypistID field value and, if it's not checked, lists the relevant tblTypists.TypistID value(in this case having multiple rows for the same FileID)?
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='grovelli' post='774256' date='08-May-2009 19:53']Starting from tblFiles in the attached mdb, is it possible to create a query listing all its fields but substituting the TypistID field with a column that, if the corresponding Accepted field is checked, lists the tblFiles.TypistID field value and, if it's not checked, lists the relevant tblTypists.TypistID value(in this case having multiple rows for the same FileID)?[/quote]
    Do you want to describe that problem visually? I have no idea what you want to do.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    does this do what you want?

    [attachment=83660:typists.gif]
    Attached Images Attached Images
    Regards
    John



  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Pat, how's it going? :-)
    I attach an xls that exemplifies the expected query output.
    Attached Files Attached Files

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='johnhutchison' post='774261' date='08-May-2009 10:56']does this do what you want?

    [attachment=83660:typists.gif][/quote]
    Hi John, thanks for taking a look :-)
    Not quite there yet, check out Query1 which implements your suggestion and compare it with the xls file I've uploaded with my reply to Pat.
    Attached Files Attached Files

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    You did not quite implement my suggestion.
    Need to change the join type. Although you cannot see this from the picture.
    [attachment=83664:join.gif]
    Attached Images Attached Images
    Regards
    John



  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='johnhutchison' post='774268' date='08-May-2009 11:17']You did not quite implement my suggestion.
    Need to change the join type. Although you cannot see this from the picture.
    [attachment=83664:join.gif][/quote]
    That's just so perfect!! :-)
    Thanks John!

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Is there any way to make it so that, when you check the "Accepted" check box, any other row in Query1 belonging to the same FileID is automatically deleted?
    Attached Files Attached Files

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Not in a query - you'd need to create a form based on the query and use VBA code.
    You could use the Before Update event of the check box to ask whether the user is sure (and set Cancel to True if the user isn't), and the After Update event to actually delete the records from tblTypists where FileID has the same value as on the form and TypistID has a different value than that on the form.

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='774375' date='08-May-2009 20:20']Not in a query - you'd need to create a form based on the query and use VBA code.
    You could use the Before Update event of the check box to ask whether the user is sure (and set Cancel to True if the user isn't), and the After Update event to actually delete the records from tblTypists where FileID has the same value as on the form and TypistID has a different value than that on the form.[/quote]
    Hans

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='774375' date='08-May-2009 20:20']Not in a query - you'd need to create a form based on the query and use VBA code.
    You could use the Before Update event of the check box to ask whether the user is sure (and set Cancel to True if the user isn't), and the After Update event to actually delete the records from tblTypists where FileID has the same value as on the form and TypistID has a different value than that on the form.[/quote]
    I've implemented your suggestions and I've attached the form here because I'd like to know whether, on a continuous form such as this one, it's possible to hide the Accepted checkbox when its value is True.
    Attached Files Attached Files

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could place a transparent text box over the check box, with font set to Terminal 24 points, font color the same as the background color of the form, and control source

    =IIf([Accepted],String(5,"█"),"")

    See the attached version.
    Attached Files Attached Files

  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='774394' date='08-May-2009 21:33']You could place a transparent text box over the check box, with font set to Terminal 24 points, font color the same as the background color of the form, and control source

    =IIf([Accepted],String(5,"█"),"")

    See the attached version.[/quote]
    But...if you put a textbox over the check box, how can you tick the check box?

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='grovelli' post='774397' date='08-May-2009 23:43']But...if you put a textbox over the check box, how can you tick the check box?[/quote]
    You can't - so you're stuck.

  15. #15
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='774400' date='08-May-2009 21:58']You can't - so you're stuck. [/quote]
    Can the check box at least be disabled then when its value is True?

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •