Dynamic Form Fields? (1 Viewer)

[shadowvixen]

I have a form in access that tracks customer service events. Each event is listed in a drop down with a dependant drop down that lists the tasks that must be completed. My new requirement is to also track reasons for events. For instance, if a customer wants to cancel, I need to know why they are cancelling. I wanted to add a sub form and dynamically display the appropriate reasons based on what was chosen as the event. Can someone please point me to a reference that will tell me how to do this? I am pretty sure I could get one set of reasons to display based on the event but what if there could be two sets of reasons? An example would be a Sales Event where I want to know what product they were sold and if it was a rental, purchase, or lease. Any suggestions are appreciated.

 

[Tim L]

Welcome to the Forums!

I guess that the 'reason' for an Event should be recorded in whatever table you use to record an event instance, if there is only one reason for any particular event. So this would be a single attribute of the event table.

If each Event could be run for more than one reason then you have a 1:m relationship, so you will need a new table, tblEventReasons, in which to record the reasons for an Event instance.

If you are going to record cancellations and the reasons for such, then I would suggest that you have a seperate table right from the start, even if it was only possible to have a single reason (1:1 relationship with Event) for cancellation because you are likely (I hope) to have far fewer records of cancellations than actual bookings and you don't need to take up space with a bunch of empty records.

So, we now have three tables, tblEvent (PK:eventPK), tblEventReason (PK:eventReasonPK, FK:eventPK) and tblEventCancellationReason (PK:cancellationPK, FK:eventPK, FK:eventReasonPK).

Now, it should be possible to control which records are related to which through relationship management. I've attached a db that hopefully demonstrates what you are looking for. Note, you can't add records in tblEventCancellationReason outside of the form (i.e. in table view) without manually entering the PK from tblEvent; the form handles this for you automatically! I've left the PK and FK fields visible in the cancellation subform, in practice you would hide them (in fact you wouldn't even need to actually place them on the form).

You can now create a table for the options for event reasons say, tblEventReasonOptions. Likewise do the same for cancellations, tblCancellationReasonOptions. Tie these together so that they have at least one matching field (say eventOptionPK) then it should be a simple matter of creating a drop-down box that only shows cancellation options which are compatible with each reason (filtering using the eventReasonPK_FK field in tblEventCancellationReason and eventOptionPK). This is in the attached example.

To put the icing on the cake of the reason cancellation options you would also refresh the query after they the user adds one reason, so that the resulting list for any additional reasons, based on the same reason for attendance, is reduced. You could, perhaps, also detect if there is only one reason, or no more reasons, available and take appropriate action, although that, I think, should be another thread.

Ensuring that the user fills in all cancellation reasons might be tricky, however and I haven't yet considered how you might do that... It would probably involve some VBA and might even be best to have separate forms for entering the reponses, i.e: when someone starts the cancellation process to have a system that presents them, systematically, with each of the reasons for attendence in turn and doesn't progress until they've selected a valid (i.e: from the available list) reason for cancellation. Again, this is probably a matter for another thread.

HTH

Tim