Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Feb 2009
    Posts
    30

    Unanswered: Combo box to make mass change to field

    I have a table that keeps track of patients in our hospital with Physician being one of the fields. What I need is to build a query (and present it through a form) that selects all the patients for a particular doctor (selection criteria for Dr A) and then changes those patients to Doctor B (with a selection from a combo box). Basically, when the doctors change shifts, he's handing all of his patients to the new Doctor coming on. How can I do this easily on a mass scale opposed to changing them one at a time? I'm guessing I need to do it through an update query, but don't know how to go about it through a form and prompting with combo boxes. Any help in the right direction would be appreciated.

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Welcome to the forums.

    First off, this kind of sounds like a http://www.dbforums.com/microsoft-ac...l#post4534484; is it? This time of year there tends to be a lot of kids posting their homework problems on here as the end of the terms draw near.

    Secondly, I'd recommend this tutorial to get you started on building a query from VBA.
    Me.Geek = True

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You're going to need a custom dialog to prompt the user with combo boxes and the OK button for it would have code behind it to run your update query.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Feb 2009
    Posts
    30
    Glad you could make yourself look good by insulting my intelligence Nick, but no, I am not a student. I'm a rusty old Access programmer that is trying to get back into Access and I'm having a mental block on this issue and I don't seem to know where suffient resources are so that I can read up and learn how to do it properly. Your comments were not helpful in the least. I will, however, try to read up more on what StarTrekker suggests, but will have to seek out extra resources. If there are some good resources for Access that anyone can suggest, I would appreciate it.

  5. #5
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    In Nicks defence, he did post some useful links;

    OK - I would maybe do this;

    Create a continuous form which returned all the Doctors currently on shift.

    On the continuous form, have the Doctor name and an combo box which contains all the other doctors (Maybe only the ones not on shift).

    Also have an 'OK' button as ST suggests and then behind each OK button have VBA to transfer the patient records from Doctor specified in that row to Doctor selected from Combo in that row.

    I would also prompt the user to confirm but on confirmation, run an update query - You refer to Nicks link on how to build your SQL but it would be something like.

    UPDATE yourtablewithpatientassignemnts SET patientdoctorid=forms!yourContinuousForm.yourcombo ofnewdoctors WHERE patientdoctorid=forms!yourContinuousForm.yourcurre ntdoctorid;

    Hope this assists - let us know when you get to the next stage...
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Accessguy - I'm a mod on the forum.

    We get an enormous number of students coming on here with the intention of getting help ranging from a prod and some guidance, to having their coursework done for them. Many attempt to disguise the fact they are students.

    One thing we are absolutely as one about is that no student gets to cheat by having their coursework completed for them (explicitly or through deception).

    Put it all together and sometimes we have to be assumptive, or challenge posters. That sometimes means we put noses out of joint.

    By the sounds of it you are big enough and ugly enough to take a little bit of roughing up, and will appreciate the intent. Better to be challenged than to find some incompetent little runt working in your team who's sole qualification was getting someone online to do his work for him.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2009
    Posts
    30
    OK, I guess I'll take it with a grain of salt. The books I have don't hit up the real life problems in Access and it gets frustrating after awhile. I'm trying to search this forum for helpful information and have found none with this subject so have posted my first thread. I'm just a working guy trying to improve my knowledge and do my job and I guess I don't realize there are people that are getting others to do their homework for them. Enough said.

    Thank you Garethdart, you did a very good job at describing what I need to do. That helps me get to the next step which I will work on now, I just couldn't get the concept in my head for some reason. I may not reach the next step until early next week, but will start working on it now. Thanks again.

  8. #8
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Good luck and just to say I think you are in the right place for advice and support.

    You're right about books - Whilst they can sometimes be intricate in technical detail, they don't always teach you how to apply techniques in the real world.

    I think that is one of the benefits of a forum such as this, where you can share ideas and tips and get the benefit of experience with like-minded individuals.

    Let us know how you get on mate - I'm off for a beer!
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  9. #9
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    PS Just had an idea for you which may be of interest;

    On that continuous form of doctors, also have a 'assigned patients' button;

    If the user clicks this, open another continuous form with all the assigned patients for that particular doctor - also have a tickbox for each row / patient.

    In the header of the continuous form, have a combo with doctors coming 'on shift' and a button on the footer 'Assign'.

    You could then allow the user to assign certain patients to one doctor and the remainder to someone else.

    Just a thought anyhow.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  10. #10
    Join Date
    Feb 2009
    Posts
    30
    I'm close... yet I may be very far. I'm confusing myself at this point, because I'm in new territory for me. I'm not too good with variables and am probably doing something terribly wrong. I made a form with 2 dropdowns, both fields picking from a query I built that draws from a list of Doctors from the main table and eliminates duplicates, so the doctor only shows once. The field names are test1 and test2 I then put a button that goes to my Update query that I built. The SQL for the query goes as
    UPDATE [Main Table] SET [Main Table].[Hospitalist Phy] = '"& me.test2.value"'
    WHERE ((([Main Table].[Hospitalist Phy])='"& me.test1.value"') AND (([Main Table].[Date of Discharge]) Is Null));
    I'm trying to get all current patients that haven't been discharged. Every time I run it, selects 0 records. It's probably obvious to you, but not so much to me.

  11. #11
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Question Update query

    Hi Accessguy,

    OK - Firstly, how are you implementing this SQL Update query?

    Are you
    i) Setting the SQL based on form values when a button is pressed or
    ii) just running a pre-saved query when the button is pressed? (This would reference forms!yourformname.yourcontrolname - me. would not work)
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  12. #12
    Join Date
    Feb 2009
    Posts
    30
    by clicking a button that is setup to run a macro that simply opens the query. I'm not sure how to pass values, obviously. I appreciate your help on this. I think if I can learn through this example, it will help me for many things down the road, that's the only reason I'm really making an effort through this forum to get this.

  13. #13
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    No worries;

    Try replacing

    me.test1.value

    with

    forms!yourformname.test1

    For all examples with me. in them (test1 / test2 etc in the above example should be the name of the control on the form).

    By doing this the query will reference the value of that control - we can expand on this but for now give that a go.

    Once you've tried (successful or not) go to design view for the query - click view > SQL and then copy and paste the code here pls.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  14. #14
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Second thoughts...

    Are you able to post a .zip of your database?

    There may be a few table design issues worth addressing before you get too far down the line;

    i.e. The 'Doctors' table - what is the Primary Key? From your post I get the impression it might be the name - If so you should probably change this (One reason alone being names are not always unique)?

    Anyhow, let us know if you can post a copy - might enable the forum to get you up to speed a little quicker.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  15. #15
    Join Date
    Feb 2009
    Posts
    30

    Attached program with problems.

    It didn't work, not sure what I did wrong. Below I am attaching the code you had me change. I've attached the database. It's not terribly exciting, but the part I'm playing with that is described in this post is the "Transfer Patients to another Hospitalist" form. I'm not too worried about the key for physicians since this is our small hospital and we don't want duplicates so I'm not allowing them. I haven't finalized that part, but I can have them put in their Dr. #, which is unique.

    UPDATE [Main Table] SET [Main Table].[Hospitalist Phy] = '"& forms!Transfer Patients to another Hospitalist.test2"'
    WHERE ((([Main Table].[Hospitalist Phy])='"& forms!Transfer Patients to another Hospitalist.test1"') AND (([Main Table].[Date of Discharge]) Is Null));
    Attached Files Attached Files

Posting Permissions

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