Filter specific records on sub form

[foxtet]

Hi Khalid..

I appreciate your help on the issues posted earlier.. I want you to look on the issue identified here pls do me a favor..
In the assignment Im working on consists tblService which handles rates for foreigners and local.



I want have a subform which filters the rates for the selected Patient. if a patient is a foreigner than the subfrom on the frmService should have charges for foreigners only....

Here is a copy of mdb attached for your reference..pls hlp me
fox

[June7]

You need a reference to the country field. If the patient info form is open then can get by reference to the country field on that form.

Calculate the appropriate price based on country field, in textbox or in query: IIf(IsNull([Forms]![frmPatientInfo]![Country]), [UnitPriceLocal],[UnitPriceForeign])
If you want to save value to tblServiceDetails, will require code.



Textbox ReferenceID has invalid ControlSource.

Suggestion about patient age: Don't save age, just birthdate and calculate the age whenever needed.

[foxtet]

Thank you for the reply
There is field called PatientIs in this field foreigner or local will be filled at the time of filling frmPatientInfo..its better to get reference to that field on frmPatientInfo

I want a query to filter the result..

can you provide me the coding to save values on the tblServiceDetails


fpx

[June7]

Okay, simple to change the field reference for the conditional statement.

Where do you want the query to filter result?

Code to save the determined UnitPrice could be: Me!UnitPrice=Me.the textbox that has the suggested expression
Big question is what will trigger this code - what event should it be in. Maybe a button Click or form Close.

[foxtet]

Thank you for the reply..

There is a table called tblService. This table holds all the services provided to both locals and foreigners separately.

What I need is that on the frmPatientInfo when you click the Generate Memo Data button there opens frmServiceObtained. The filtered data need to be displayed or need to be selected from a sub form in the Service obtained form…

[June7]

First, the form opened by Generate Memo Data button is called frmService, frmServiceObtained not in the project.

Next, tblServices cannot be 'filtered' by criteria of local or foreign because there is no single field with these values. You must use the conditional statement I suggested to return a value from one of the two price fields based on criteria from tblPatientInfo.

Third, there is no place on the frmService or its subform to select the price. You want the price to be automatically determined then need the suggested expression. Create a field in qryServiceExtended using this expression. Use that constructed field in the ExtendedPrice calculation. Bind the UnitPrice textbox to this constructed field. Use code to save the unit price to tblServiceDetails. Or not and calculate the unit price with the expression whenever need. However, saving the value does assure that the charges to that patient are permanently recorded, even if your rates change.

If you do create the field in qryServiceExtended and the expression references frmPatientInfo to get the 'Local' criteria, then the query is dependent on that form. Running the query without the form open will fail.