I have a main form, which is bound to table MainTable, and a sub form, bound to table SubTable, with a one-to-many relationship. The records in MainTable can be one of two types and there is field ‘Type’ to hold the type. Each type has its own sequential number held in fields ‘One’ and Two’ – if there is a number in field ‘One’, field ‘Two’ holds ‘-‘, and vice versa. The ‘seed’ for the sequential numbers are held in a table with one record and two fields – one field holding the last number for type ‘One’ and the other holding the last number for type ‘Two’.
A reference number is created and displayed on the main form in a text box. The reference is made up from the contents of two text boxes (actually, they are Combo boxes) on the Main Form, two text boxes on the SubForm, and the relevant sequential number. So, if the main form boxes were A and B, and the subForm boxes were C and D, the reference would be ‘Aa/Bb/Cc/Dd/XX’ where Aa represents the value in box A, etc, and XX represents the current value of the sequential number for the particular record type. The second record type could be based on a different set of boxes, say ‘Ee/Ff/Gg/Hh/YY’, with a similar split between main form and subform, but once I know how to do it for one set, I’m hoping I can adapt it for the second set.
I have written the code to extract the relevant sequential number, increment it ready for next time, create the reference string and display it in a text box. I could ‘trigger’ the creation and display of this reference from a button on the form but what I would like to do is have it created automatically after all four boxes (A, B, C and D) have been filled. I could put a trigger on each box and build up the reference a section at a time but that seems to be rough and ready and I would prefer a more elegant solution. Is there a way of managing the input to the four boxes, bearing in mind that two are on the main form and two are on the sub form, to trigger the procedure to create the reference when, and only when, all four boxes have been ‘filled’.
A second problem I have, which in some ways is an extension of the first, is how to ensure that all the mandatory fields on the mainform/subform have been completed before moving to another record or creating a new record. Note that only a selection of the fields on the forms have to be completed, its not every field.
A reference number is created and displayed on the main form in a text box. The reference is made up from the contents of two text boxes (actually, they are Combo boxes) on the Main Form, two text boxes on the SubForm, and the relevant sequential number. So, if the main form boxes were A and B, and the subForm boxes were C and D, the reference would be ‘Aa/Bb/Cc/Dd/XX’ where Aa represents the value in box A, etc, and XX represents the current value of the sequential number for the particular record type. The second record type could be based on a different set of boxes, say ‘Ee/Ff/Gg/Hh/YY’, with a similar split between main form and subform, but once I know how to do it for one set, I’m hoping I can adapt it for the second set.
I have written the code to extract the relevant sequential number, increment it ready for next time, create the reference string and display it in a text box. I could ‘trigger’ the creation and display of this reference from a button on the form but what I would like to do is have it created automatically after all four boxes (A, B, C and D) have been filled. I could put a trigger on each box and build up the reference a section at a time but that seems to be rough and ready and I would prefer a more elegant solution. Is there a way of managing the input to the four boxes, bearing in mind that two are on the main form and two are on the sub form, to trigger the procedure to create the reference when, and only when, all four boxes have been ‘filled’.
A second problem I have, which in some ways is an extension of the first, is how to ensure that all the mandatory fields on the mainform/subform have been completed before moving to another record or creating a new record. Note that only a selection of the fields on the forms have to be completed, its not every field.
