forms with combo boxes 1

[uscitizen]

i have a form with just three unbound controls. Two are numeric combo boxes. the first one is 'Patient Number' and the second one is 'Cycle'. the third one is the tickler.....it should get a 'Medication' from a table when 'Patient Number' and 'Cycle' numbers on the form that's being entered by the user correspond with the values on the 'Protocol Medication' table where 'Medication' lives.

I wrote a small SQL query to define the 'Row Source' of the 'Medication' combo box on the form with the three controls:

SELECT [Protocol Medications].[Medication] FROM [Protocol Medications] WHERE ((([Protocol Medications].[Patient Number])=Me.[Patient Number]) And (([Protocol Medications].[Cycle])=Me.Cycle));

when i fill in Patient Number and Cycle and enter the 'Medication' control, i am prompted for the values of "Me.Patient Number", and "Me.Cycle". shouldn't those values have been provided by the values entered in the combo boxes on the form?

 

[KenReay]

Hi

Yes, and they have been, but Me. is not recognized within the SQL so you need

SELECT [Protocol Medications].[Medication] FROM [Protocol Medications] WHERE ((([Protocol Medications].[Patient Number])=Forms!MyForm![Patient Number]) And (([Protocol Medications].[Cycle])=Forms!MyForm!Cycle));

where MyForm is the name of your form

hope that helps


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[uscitizen]

it does, but my form's name has blanks in it, it's called:

Trtmnts Admnstrd: Entr Patient & Cycle Numbers & Trtmnt To Print

and when i substiute it for the MyForm in your example, i continue to get prompted for the values of the parameters.

 

[KenReay]

Hi

You did substitute the name of your form for "MyForm" didnt you?

Are you building this SQL in code, or in the query designer?

I did assume the query designer, if you are doing it in code it should something like

strSQL = "SELECT [Protocol Medications].[Medication] FROM [Protocol Medications] WHERE ((([Protocol Medications].[Patient Number])=" & Me.[Patient Number] & &quot And (([Protocol Medications].[Cycle])=" & Me.Cycle & &quot);"



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[uscitizen]

I renamed the form on which the three controls 'live' and modified the SQL code (using the graphi/interactive 'Design Builder' capability) to accomodate the name change; so the code would now read:

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 the above appears in the 'Row Source' property of the unbound 'Medication' combo box on 'Trtmnts_Admnstrd' form.

which 'seems' to work.

 

[uscitizen]

just a quick follow up......

although it works, i wonder if there's a way to get it to 'refresh'; i.e. if i change the Patient Number and/or Cycle number, is there a way to update the list that the SQL code results in (the list of Medications)?

 

[KenReay]

Hi

in the after update event of the otehr combo boxes put Medication.requery

and/or in the on got focus event of Medication, put Medication.requery

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[uscitizen]

neat

there's something that could stand some minor embellishment though....although the values in the drop-down (combobox) of Medication's vary according to the other comboboxes' values chosen, the older value is still being displayed. as i say, it's minor, because i suspect that chosing from the updated values of the Medication combo box is going to overwrite it.

 

[KenReay]

Hi

OK so in the after update event of teh otehr combo boxes put

Medications = ""
Medications.Requery

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[uscitizen]

Luvly! I'd almost wager that it'd be not that different to make 'Cycle' number context sensitive (using the query builder to populate the 'Row Source' property) to the 'Patient Number' field.

 

[uscitizen]

I had some time today and thought I'd try out my pet idea but met with some resistance.

Basically, using query builder, I created a query from 'Protocol Medications' as follows:

SELECT [Protocol Medications].[Cycle] FROM [Protocol Medications] WHERE ((([Protocol Medications].[Patient Number])=[Forms]![Trtmnts_Admnstrd]![Patient Number]));

which i attached to the Row Source of the 'Cycle' field. the 'idea' being that the user would only get to choose from those Cycles available w/in the 'Protocol Medications' table and not the exhaustive litany of 28 values from the combo-box. using your example, i went to the 'Got Focus' event property and tried to write: Cycle.Requery but i am getting an compiler error:'Invalid Qualifier' with obverse video over 'Cycle'? i kind of figured i had this licked but for the time required to embellish the form with added SQL statements.......?

 

[uscitizen]

....and here's the vba

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

currently, if a user were to change his mind wrt Patient Number and choose another after having expressed his preferences for Cycle and Medication, the value of Cycle from the preceding choice persists!

ps: i almost forgot, i added 'Distinct' to the SQL string in the posting i sent today to handle the issue of duplicated Cycle values in the Cycle box.

 

[KenReay]

Hi

Like you said it is just more of the same

if you want Cycle and Medication reset when you choose a new patient, then

Private Sub Patient_Number_AfterUpdate()
Medication = ""
Medication.Requery
Cycle = ""
Cycle.Requery
End Sub


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[uscitizen]

i used the code below that you told me about

Private Sub Patient_Number_AfterUpdate()
Medication = ""
Medication.Requery
Cycle = ""
Cycle.Requery
End Sub


and did nothing else to the database but press the compile button. ms a2k returned a compiler error indicating obverse video over the 'Cycle' in 'Cycle.Requery' and the msg 'Invalid Qualifier'??

 

[KenReay]

Hi

OK

What can I say,

you posted code as follows:

Private Sub Cycle_AfterUpdate()
Medication = ""
Medication.Requery
End Sub


suggesting you had valled teh combo box for Cycle "Cycle",
so I used it in my sample to you

Eitehr you have not called it "cycle", in which case adjsut the sample I gave you accordingly, or more likely I suspect "Cycle" is a reserved word (being a property of a form), so rename your combo box control, and adjust the code to suit

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[uscitizen]

hi,

i (being the newbie) didn't know 'cycle' is a reserved ms a2k word. i doubt i shall ever forget that it is. that said, i named the control on 'Trtmnts_Admnstrd' to 'CycleNum' (from 'Cycle') and adjusted the VBA code to read:

Option Compare Database

Private Sub Patient_Number_AfterUpdate()
Medication = ""
Medication.Requery
CycleNum = ""
CycleNum.Requery
End Sub

Private Sub CycleNum_AfterUpdate()
Medication = ""
Medication.Requery
End Sub

Private Sub Medication_GotFocus()
Medication.Requery
End Sub

the code appears to have compiled w/o a glitch! i changed the SQL code i was using by substituting:

[Forms]![Trtmnts_Admnstrd]![CycleNum]

for

[Forms]![Trtmnts_Admnstrd]![Cycle]

and i'm to report it works. hurrah!

i just got my next bright idea which was to further limit the range of 'Patient Number' values the user has to choose from by carving out from the 'Row Source' a look-up table of ALL 'Patient Numbers' ever registered in the database and instead to use:

SELECT DISTINCT [Protocol Medications].[Patient Number] FROM [Protocol Medications];

the query works when viewed from the design window but doesn't return any numbers at all when you click the downward facing arrow on the 'Trtmnts_Admnstrd' user input form.

 

[KenReay]

Hi

There are hundreds of reserved words, so you cannot be expected to remember them all, that is one reason to use a naming convention for variables and objects, that way you never use a reserved word

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[uscitizen]

hi,

yes, my next database will be more contrained in the lexicon department.....

in the interim, i've gottent this irrestible impulse to further limit the range of 'Patient Number' values the user has to choose from by carving out from the 'Row Source' a look-up table of ALL 'Patient Numbers' ever registered in the database and instead to use:

SELECT DISTINCT [Protocol Medications].[Patient Number] FROM [Protocol Medications];

i'm off to a great beginning because the query works when viewed from the design window --- on the other hand, this doesn't return any numbers at all when you click the downward facing arrow on the 'Trtmnts_Admnstrd' user input form.

i've tried various approaches on the theory that some sort of requery in the "Patient Number" control helps but have yet to strike gold.

any interest in pursuing this one step further?