update table field with value in open form (Acc2000)

Topic

New Reply

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

Reply | Quote

Replies

How did you do step 4 in a macro?
Pat

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Elizabeth

Reply | Quote

Had you intended to post an attachment?

Reply | Quote

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…

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Elizabeth

I attach a sample file to have a look at.
A couple of comments. With combo boxes it is normal to hide the key field and show the name field instead, but this doesn’t work in continuous subforms – you end up seeing the same name in each.
The row source for the comboboxes is a query that includes a subquery. The subquery ensures that you can’t list something twice, by only showing the ones you haven’t already listed.
I am going away for a couple of weeks, so if you post any more questions you won’t get an answer from me for a while.

Reply | Quote

I’m back after a month off. Boy, it was nice.

Your db layout was extremely helpful. Learned a lot!

Question:

Instead of having the subform on the main form show programs the consumer is currently registered in, I did a subform that used your Consumer Programs and Objectives form (without the Objectives) as the subform. Works nicely, especially in a tabbed form layout.

I wanted to set up a second subform (continuous) that displays all the objectives appropriate to the program selected on the first subform. I can get the form I designed as the subform to list the right objectives; however, when I add/”embed” it on the main form, the objectives disappear.

Is the problem that this subform is based on the tlkpObjectives which doesn’t have any fields in common with the main form, i.e. no master/child link? I’ve done a work around and changed what I intended to be the second subform into a popup form triggered by a command button. I still interested to know if it could be done as a second subform. What do you think?

The db exceeds the attachment limit here but if seeing it would be helpful, I’d be happy to email it to you.

Elizabeth

Reply | Quote

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

Elizabeth

Reply | Quote

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?

Reply | Quote