I have 4 interrelated tables. A questionnaire table A list of recipients of the questionnaire (which companies the questionnaire was sent to) A list of mailings per questionnaire (how many times the questionaire has been sent out to the recipients) A set of reponses received back per receipient per mailing. The primary key to the questionnaire table is the questionnaire code. The primary key to the questionnaire recipients table is the questionnaire code plus the recipient code The primary key to the questionnaire mailings table is the questionnaire code plus the mailings code. The primary key to the questionnaire responses table is the questionnaire code plus the mailings code plus the recipient code. I want to have a ‘questionnaire responses’ form which allows the user to select a mailing for a questionnaire, choose a recipient from a tabular subform and then enter the recipient’s responses via a command button. The source table for the form is the mailings table. The header of the form has the questionnaire code and the mailing code. I want the operator to be able to select the mailing for the questionnaire by entry of the questionnaire code and the mailing code. The mailings table will not get modified from this form. Entry of the questionnaire code gets the first mailing code for that questionnaire. If the operator enters a different mailing code then the system treats this as a new value for the currently selected record (a rename operation). I have tried selecting a different record from the OnValidate trigger for the mailing code field on the form, and this changes the values on the form, but the table must still be positioned to the first mailing becase I still get the ‘rename’ option. I know I could split this form into 2 and achieve what I want, but I would rather keep it as a single form (the way I specced it). So, is it possible to select a record in the mailing table by entry of the questionnaire and mailing codes ? I apologise for asking you to wade through all that and I hope it makes sense.
My first question, is what is the connection between the Recipients table and the Mailings table. Why are you sending the same document multiple times to the same recipient? At first glance it apears that having one table to track these could solve the problem in part, but I may be making a silly assumption. In any case, you are at some point trying to change a vaue in a primary key, and that needs to be resolved. In answer to your next question — Yes, it can be resolved by code, but it is better to fix the design if possible.
I am sorry. I think I have confused the question with too much detail. The design doesnt require a primary key to be changed. The client wants to resend a questionnaire to the same set of recipients after 6 month, 12 months etc. to see if their answers/responses change over time. There will be a mailing code for ‘First Mailing’, ‘After 6 months’, ‘After 12 months’. I think a better way to have framed my question would have been : Is it possible to have both fields of a primary key on a mainform and for the current record in the SourceTable to be selected by the combined values of both fields. The SourceTable in question is the ‘mailings’ table and the primary key is ‘questionnaire code’ plus ‘mailings code’. I want the user to enter a ‘questionnaire code’ and a ‘mailing code’ and for the appropriate mailing record to be loaded. Thanks for taking the trouble to think about this.
Try this. Create two new controls in the form, these being the keys of the record. Then have each of these link to the appropriate tables. Create variable for each of these fields of type record. In your case: QuestionCode:Record of “Questionaire Record”; MailCode:Record of “Mailing Record”. The new controls will have the source expressions: QuestionCode.Code and MailCode.Code. Set the Table relation to the Question table and the Mail table. In the on validate of each of the controls, add the following code: IF rec.get(QuestionCode.Code,MailCode.Code) then ; Should work, or at least get you started.
Thanks David Yes I can see how this could work. I would need to keep these controls updated with the corresponding values from the sourcetable if the user stepped through the file … and to initialise them with the values from the sourcetable in the OnInit command of the form ?
Yes, there is some maintinence involved, but it is minimal.