Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    update table field with value in open form (Acc2000)

    What's the easiest way to update a table with a value retrieved from the current record on an open form? Nothing I'm seeing seems quite right. Any direction would me much appreciated.

    Here's the context:
    1. "Enter Client" form with cmd button to open unrelated form of program objectives.
    2. Check boxes used to select objectives.
    3. Make table query holds those selected records.
    4. Update query currently uses a [enter client ID] parameter in a previously null field to create the "child" field in this new table.
    5. Append table query adds these newly created individual client objectives to the main client objectives table.
    6. The client table and the main program objectives table now have a parent-child link on ClientID.
    7. Select query serves as the record source for the EditClientData form.

    The prototype does this with a macro. I'm in the process of converting this macro to code but have gotten stuck at step 4. I'm sure I'm doing this the hard way, so any nudges in the right direction will be welcome.

    Elizabeth

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: update table field with value in open form (Acc2000)

    How did you do step 4 in a macro?
    Pat

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: update table field with value in open form (Acc2000)

    I am not sure that I understand what you are doing, but it looks to me that you are just setting objectives for each client, choosing from a list of possible objectives. The selected objectives are to be stored in a client objectives table.
    Why not just put the objectives in a subform on the client form. Provide a combo box of objectives to pick from. Set its rowsource to be a query that selects any objectives that have not already been used for that client, so it would need to be requeried in the oncurrent event for the subform. The linking of the child form to the master via client ID will automatically put the clientID in the clientobjectives table.
    Regards
    John



  4. #4
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: update table field with value in open form (Acc2000)

    I'm having to manually input the number each time and that's what I want to get around.

    Elizabeth

  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: update table field with value in open form (Acc2000)

    Hummm...sounds very interesting, wish I'd thought of that.

    Now for a complication, there are 7 different programs that the contact may be registered in and the objectives are specific to the individual programs. So would your solution have to have 7 different subforms, or is there a better way?

    Elizabeth

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: update table field with value in open form (Acc2000)

    No you don't need 7 subforms. For each client you identify the program, on the client part of the form. Set the query that provides the data for the combo box in the subform to select only the objectives relevant to that program.
    (if any objective is relevant to only one program, you can put a programid in the objectives table. If an objective is relevant to multiple programs, you have an many-many relationship and so need an intermediate table linking objectives to programs)

    All this assumes a client registered in just one of the 7 programs. If they can be in more than one you need a more complicated table structure.
    Regards
    John



  7. #7
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: update table field with value in open form (Acc2000)

    Most clients are registered in 2 programs but conceivably could be registered for all 7. Kind of throws a spanner in the works. Currently I have a table for each of those programs. Are you seeing a way around that?

    Sorry for taking so much of your time. This is very helpful to me and I appreciate it.

    Elizabeth

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: update table field with value in open form (Acc2000)

    It looks to me that the tables you need are:
    tblclients clientid as key any info about the client, such as names and address etc
    tblprograms programid as key hold any info just about a program, e.g. name of program
    tblclientprograms clientprogramid as key [clientid] and [programid] as foreign keys. Holds info about a client's participation in a program. Could include start and end dates etc
    tblobjectives objectiveid as key holds info just about an objective, e.g. name
    tblprogramobjectives programobjectiveid as key [programid] and [objectiveid] as foreign keys Holds info about which objectives apply to which programs. It may have no other fields, but perhaps could.
    tblclientprogramsobjectives clientprogramobjectiveid as key [clientprogramid] and [objectiveid] as foreign keys. Holds info about the objectives that apply to one client's participation in one program.

    If you are not used to this sort of structure, it probably looks un-necessarily complicated. But it works and you get used to it.

    You would then need a client form, with a continuous subform for the programs the client is enrolled in. Continuous subforms can't have subforms, so you would need a different way to show objectives. Perhaps a related form. There are lots of options and it is hard to say what's best without knowing what other fields you have.
    Regards
    John



  9. #9
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: update table field with value in open form (Acc2000)

    Here's what the tables look like so far. Does this change your thinking re a subform and related form?

    Elizabeth

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: update table field with value in open form (Acc2000)

    Had you intended to post an attachment?
    Charlotte

  11. #11
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: update table field with value in open form (Acc2000)

    I'll try sending the attachment once more. My computer is locking up each time I try to send the attachment. I think my operating system upgrade may be putting a spoke in my wheel. I've shut down a couple of programs and am trying again.

    Elizabeth

    p.s. I see it made it through this time. Maybe the firewall was the problem...
    Attached Files Attached Files

  12. #12
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: update table field with value in open form (Acc2000)

    Is the problem that the field is not yet saved, since you did not leave the record? Maybe force the form to be saved, then it will recognize that record?

  13. #13
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: update table field with value in open form (Acc2000)

    Hi Elizabeth,

    After looking at your ER diagarm, it appears to me that you may be missing some data - aren't you trying to get an assessment of how effective a particular program was for a specific consumer? I don't see any table where that would be captured, and the triple linking tables suggests there is some sort of structural problem. It isn't obvious to me what changes should be made, but perhaps this will trigger something with you.
    Wendell

  14. #14
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: update table field with value in open form (Acc2000)

    Elizabeth
    Your diagram is very similar to my suggestion, except that I think you can link directly from tblConsumerProgObjectives to tblbjectives. (see atachment). The point of this link is to identify the objectives that apply to a particular consumer program.
    Also I suspect that a lot of the fields you have listed are the the wrong tables. I am not sure about any particular field because I don't know exactly what they are.
    But, any fields in tblObjectives, for example, are attributes of the objective itself. If you want them to be an attribute of an objectives when it is applied to a particular program and consumer, then the field needs to be in tblConsumerProgObjectives. It looks to me that Achieved, whenassessed, staffevaluator might be in this category.Perhaps quite a few others. (this relates to Wendell's comment as well)
    I think the same applies to tblPrograms and tblconsumerPrograms. Many of the fields you have listed in tblPrograms look to me as if they belong in tblconsumerprograms. In fact all of them except the first three.
    Now to the question of adding records. Adding records in related tables is one fo the everpresent issues.
    Using a subform is one of the standard ways of doing it, but there are some limitations. Generally you cn't show a great deal of information on a subform, so it is not a good choice if you need to fill in lots of fields each time you create a record.
    So creating records in tblProgramObjectives is a perfect candidate. From a tblprograms form, have a subform listing all the objectives that apply to that program. Adding a new record involves just adding a new row to the subform, and I would provide a combo box to select the new objective to add.

    Adding a new program to a client is a possible candidate for a subform, it depends on how many fields you end up with in tblConsumerprograms, and how many of them you have to fill out whenever you create a new record. You could have a subform on which you complete the three or four essential fields, then a "show Details" button that opens another form (frmConsumerPrograms) with all the rest of the fields for that consumerprogram on it.
    Adding records in tblConsumerProgObjectives might be done via a subform of the frmConsumerPrograms form. Again how many fields have to be completed whenever you create a new record. Again you would need a "show Details" button that took you off to frmConsumerProgObjectives to show you all the fields for a particular objective.
    If you couldn't add new objectives via the subform because there were too many fields to complete, instead I would have an "Add Objhective" button on frmConsumerPrograms, that opened form frmConsumerProgObjectives to a new record, and created the linking field in code via
    Forms! frmConsumerProgObjectives!consumersprogramsid = me!consumerprogramsid

    That is probably enough for now.
    --------
    Sorry I posted it without the attachment, so I have edited it to include the attachment.
    Attached Images Attached Images
    Regards
    John



  15. #15
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: update table field with value in open form (Acc2000)

    John,

    The light finally went on re not having ConProgObj include both a ConProg foreign key and a ProgObj foreign key, and thus the triple linking. I've restructured the table relationships per your suggestion and eliminated that.

    You and Wendall were both dead on about fields being in the wrong tables. I had not sifted to anywhere near the "lowest common denominator." That too is corrected. Attributes of programs are in tlkpProg, attributes of objectives are in tlkpObj, and attributes of the linkage are in tblProgObj...and so on.

    Now for the tough part. While I'm clear on how to set up a form to edit/update information about consumers, programs and objectives, I'm drawing a blank on how to go about setting up the process of assigning programs and their corresponding objectives to consumers. I reread your suggestions about form, subforms and related forms but couldn't get them to gel with the process I'm envisioning:

    1. enter consumer info (tblConsumer stuff) then
    2. select from a pick list of programs the first program to assign to that consumer, then
    3. select from a separate pick list of all that program's objectives to assign to the consumer
    4. repeat 2 & 3 as needed

    Would a frmConsumer with a multiselect combo box of programs and a second multiselect combo box of objectives filtered by the first combo box work?

    Or, given the limited screen real estate and the amount of data there is to enter for consumers, consumer programs, and consumer program objectives; perhaps instead of combo boxs a related frmPrograms, followed by a related frmProgramObjectives would work better.

    Do you have any insight into the best way to do the program and program objectives selection?

    Again, thanks for all your input. I'm spending a lot less time "circling the drain" with your assistance.

    Elizabeth
    Attached Files Attached Files

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
  •