add row to child table depending on a value...

[pablavo]

Hi there.

Sorry about the heading being ambiguous. I Think I'll need some help with this one.

The db I'm working on is for complaints. There are three stages of complaints.

I have a main form and a sub form. The main form has the complainant and the subform, the details of complaint for which there can be three complaints. (On the complaint table)

The main form has a listbox which lists the complaint stages 1-3 (if the stage reaches to three)

The underlying recordsource of the listbox bound column is the ID for the complaint table. When selected this will filter to the complaint on the subform.

What I'm trying to do is have a button on the main form that adds a new record to the complaint table with the matching complainant ID.
Because there are only three stages I also need to find a way to populate the table with the next stage.

This means that if there is only 1 stage (one record) and the user presses the button, a new record will be created on the subform and it will automatically populate the stage field with stage 2. If that makes sense. This will happen until it reaches stage three and my plan is to, perhaps add a condition so no more records are added.

I've been looking at the addnew method but don't know how to add the record so that it's adding it to the proper parent (complainant) ID.

Does anyone know of ways I can achieve both these issues?

Thank you

 

[wazz]

it sounds like you have this:
complainant -> may have 3 complaints (stages 1,2,3)

but sounds like you might want:
complainant -> may have many complaints -> each complaint may have 3 stages.

can you clarify which one you want (if either) and which one you have. i think you will need the second one and might have to redesign a bit to get it(?). can't tell.

the second version would be fairly easy i think and you would just have to add a field/combobox for which stage you are on. or, if you can only have 3 child records for each complaint then just count the records. (maybe not ideal but possible). it's not exactly what you're asking at this point but the design has to match so you can do what you need. can you tell us what you need in terms of "one to many"? like "one thing can have x-many other things". don't think in terms of forms and subforms but the things you have to deal with.

 

[pablavo]

Hey Wazz,

Thanks for getting back to me. You know, you maybe right about the design. The spec I got hadn't mentioned that but now you mention it. I'll have to ask about this because I think this could be a possibility. Thanks.

In regards to the combo box idea for the stages. Well, the details will be different for the subsequent stages. different dates, investigator etc. So it would have to be a new record and not just with a stage combo I'm afraid.

If I have to redesign the db so that it has a complainant with many complaints and three stages with each complaint, I'm still in the same predicament with the button on the main form.

What do you think?