How to add a new child record in a one to many relationship using vba

[Klingest]

Hi

I have two tables, "tblCase" and "tblProject", they are related in a one-many relationship, so that I can have many Projects for each record in "tblcase".

"tblCase" is parent
"tblProject" is child.

- I have a form with a listbox "lstProject", that shows the records in "tblProject".
- In another form I have a listbox "lstCase", that shows the records in "tblCase".
- The records shown in "lstProject" are children of the selected record in listbox "lstCase".

Now, I want to add a new child record in "tblProject" , which is related to the selected record "lstCase".

In order to do that I have some textfields where I can put in the values I want in the new child record of "tblProject". When I press an OK button, the values should be stored as described.

How can I do that?

I guess I need some vba code, which is executed as I press the OK button.

 

[CJ_London]

why not just use a form for tblCases and have on it a continuous subform based on tblProject. Providing you have defined your relationships, it will populate the family key field automatically, no code required

 

[Klingest]

why not just use a form for tblCases and have on it a continuous subform based on tblProject. Providing you have defined your relationships, it will populate the family key field automatically, no code required

I am not sure what you mean, but the current structure of the program has been decided in order to meet the final specifications. So I need two listboxes. One for tblCase and one for tblProject, and I need to show the child projects, for the selected case in "lstCase", in "lstProject". Now I want to add another child and show that too.

"TblProject" is also a parent for another table, but I haven't told you about that as I think it is irellevant.

 

[CJ_London]

In order to do that I have some textfields where I can put in the values

these will be on a form. Bind the form to tblProjects and for the foreign key, use a dropdown to select the relevant case.

otherwise you can use vba, but you have not provided a clear enough explanation as to how your system works, particularly as you seem to have a very complex way of doing things, to make any suggestions as to how this might be achieved.