A big problem: need help in "looping" fields in a form

[xwnoob]

hello,

I really need some help in a problem im facing right now which has made me frustrated.

Please take a look at the attached pic which shows my latest form design.

Right now i have unwanted duplicate fields in my form which is the borrower's name. In my case, for each borrower that the user want to add (under the same title of borrower and SerialID), each borrower name will have his own country,address and up to 5 contact parties with each contact party having his own tel and fax no. The reason why there is 5 fields of name in my form is because at the time i actually thought of putting 5 borrowername fields and the relating fields in the form which is a bad idea. Its sloppy work and i read about normalisation so i must avoid duplicate data . Also it would waste alot of space if one case only has one borrower under it at first and many fields in the form would be empty.


Heres how i want the improved form to work:
for the new form, the design will be the same just that there will be one field of borrower name. If there are 3 borrowers under a title( or a case), i want to add the first borrower details in the respective field. Then,by pressing a button(or having a function, the form will clear the contents in the borrowername,contactparty,faxno and tel no and then let the user type the second borrower info while the rest of the fields remain the same and so and so forth. I just want the serial ID to link to the second borrower and beyond so that i can perform queries using this link.



So how do i do it?using subforms? or some other functioN? the problem i see with my idea is that how can I prevent the serialno from incrementing as it is a autonumber and i want to make sure that the borrowers i add fall under the same serialID.

Also, if I only add 3 borrowers at first, but later i need to add 2 borrowers to the same serialID is it possible?


If you have a better method than this, do tell me

 

[spikepl]

1. Show your data structure: what tables do you have how are they related. Show screenshot from the relationships window. Proper data structure is the starting point for everything else.
2. Pay attention to titling your posts. The "A big problem need help" is completely superfluous - all posts concern some issue or other, and are a call for help. Stick to the essence. That allows the readers to know, roughly, what it's all about without having to open the post, and also to find it later, if some brilliant solution is found.

 

[xwnoob]

1. Show your data structure: what tables do you have how are they related. Show screenshot from the relationships window. Proper data structure is the starting point for everything else.
2. Pay attention to titling your posts. The "A big problem need help" is completely superfluous - all posts concern some issue or other, and are a call for help. Stick to the essence. That allows the readers to know, roughly, what it's all about without having to open the post, and also to find it later, if some brilliant solution is found.

Alright ill take note although for this problem i havent even created relationships yet because i the problem i have is related to one table( I think...) although i think i already found a solution but i have not tried it yet

 

[spikepl]

The problem *is* related to your data structure, or rather lack thereof. If you have a "case" involving one or more borrowers, you have tblCases, tblBorrowers, and if one borrower can appear in more than one case, then you have tblCasesBorrowers, that links arbitrary borrowers to arbitrary cases. If one borrower can only ever appear in one Case then tblCases and tblBorrowers can be linked directly