i have a form called 'Trtmnts_Admnstrd' which lets a user input his/her choice of 'Patient Number', 'Cycle' and 'Medication' to be printed when the Print command button activates a macro on getting clicked. the first two unbound combobox controls are numeric and the third unbound combobox control ('Medication') is text.
were you to look at the Row Source property of the 'Medication' control you'd find the following SQL code there:
SELECT [Protocol Medications].Medication
FROM [Protocol Medications]
WHERE ((([Protocol Medications].[Patient Number])=[Forms]![Trtmnts_Admnstrd]![Patient Number]) AND (([Protocol Medications].Cycle)=[Forms]![Trtmnts_Admnstrd]![Cycle]));
and some VBA hiding behind the scenes would come in as follows:
Option Compare Database
Private Sub Cycle_AfterUpdate()
Medication = ""
Medication.Requery
End Sub
Private Sub Medication_GotFocus()
Medication.Requery
End Sub
Private Sub Patient_Number_AfterUpdate()
Medication = ""
Medication.Requery
End Sub
this works fine just as-is and i would like to express an interest in trying to 'kick it up a notch' if possible....i.e., how would you go about making the 'Cycle' control context sensitive to just the levels of the 'Patient Number' control while still maintaining the current level of functionality wrt the 'Medication' control. in case this isn't all that clear, maybe this'll help: currently, clicking on 'Patient Number' and 'Cycle' produces a litany of choices within each combobox of all the possible options available. however, at any given point in time, there's only a subset of the available 'Patient Numbers' and 'Cycle' numbers in the 'Protocol Medications' form and what i'd like to have the user choose from is only those 'Patient Number's in 'Protocol Medicatons' table and then only those 'Cycle' numbers appropriate to the choice of 'Patient Number' (just as currently, 'Medication' is contingent upon the joint choice of 'Patient Number' AND 'Cycle' number. i'm a 'newbie' (although hopefully before year's end i'll have vba training under my belt) to this so i'd really be grateful for some explicit ez 2 follow instructions from any who've been here.
were you to look at the Row Source property of the 'Medication' control you'd find the following SQL code there:
SELECT [Protocol Medications].Medication
FROM [Protocol Medications]
WHERE ((([Protocol Medications].[Patient Number])=[Forms]![Trtmnts_Admnstrd]![Patient Number]) AND (([Protocol Medications].Cycle)=[Forms]![Trtmnts_Admnstrd]![Cycle]));
and some VBA hiding behind the scenes would come in as follows:
Option Compare Database
Private Sub Cycle_AfterUpdate()
Medication = ""
Medication.Requery
End Sub
Private Sub Medication_GotFocus()
Medication.Requery
End Sub
Private Sub Patient_Number_AfterUpdate()
Medication = ""
Medication.Requery
End Sub
this works fine just as-is and i would like to express an interest in trying to 'kick it up a notch' if possible....i.e., how would you go about making the 'Cycle' control context sensitive to just the levels of the 'Patient Number' control while still maintaining the current level of functionality wrt the 'Medication' control. in case this isn't all that clear, maybe this'll help: currently, clicking on 'Patient Number' and 'Cycle' produces a litany of choices within each combobox of all the possible options available. however, at any given point in time, there's only a subset of the available 'Patient Numbers' and 'Cycle' numbers in the 'Protocol Medications' form and what i'd like to have the user choose from is only those 'Patient Number's in 'Protocol Medicatons' table and then only those 'Cycle' numbers appropriate to the choice of 'Patient Number' (just as currently, 'Medication' is contingent upon the joint choice of 'Patient Number' AND 'Cycle' number. i'm a 'newbie' (although hopefully before year's end i'll have vba training under my belt) to this so i'd really be grateful for some explicit ez 2 follow instructions from any who've been here.