Selecting fields in a combobox where id matches

[SHABZO]

In my Patients table i have a field called PatientID (AUTONUMBER) as my primary key
I hve another problem. Is this possible as i hve been trying for two days now. I hve tried a lot of ways but i cant get it to work right.
I have a table called dependents. ID , PatientID,,Dependents,DOB,Age id being the PK AND PatientID as a number
I have a table called vitals. with ID,PatientID,PatientName( which is a dropdown of Dependents), hpp,sats etc. (This is a subform in datasheet style in another form)

In my table vitals i have the follwing for PatientName . comboxbox.
SELECT Dependents.Dependents, DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd")) AS Age FROM Dependents;

So it selects fine in a dropdown like John 48, smith 36.

The problem i have is that it selcts all the records irrespective of PatientID.

 

[CJ_London]

You need to clarify - are you using calculated fields and lookup fields in your table (both not advisable)? or do you mean form when you say table?

 

[SHABZO]

No sir,
I am busy with a request for a surgery. Basically I have three tables. The first table being the Patients. with a primary key PatientID and the other names , last names.etc.
Then i have a Dependents table with fields for all the dependents for medical aid for a patient using PatientID as a number type .Then the dependents are filled in once only on the first appointment.

Now in the form Vitals which is a subform in the main form. The link child all works well.
In the main form which is opened withPatientID 65. Dependents and Vital forms are subforms and open with the correct PatientID.

The only problem is that in Vitals there is a Field called PatientName. This gets a new record everytime the patient vists. The doc then has to select from a dropdown PatientName the dependent. It gives him all the dependents from the dependents table. Although The vitals form has a control with PatientID65 .

 

[CJ_London]

OK

So I think what you are saying is that the recordsource for the combobox needs to limit to dependents where patientID=the current patient. The combobox is in a subform in a subform control called Vitals. The controlsource for the subform control may also be called Vitals.

If I've understood this correctly then what you need to do is add soemthing like the following code to your main form current event:

Me.Vitals.form.[COLOR=seagreen]comboboxctrl[/COLOR].recordsource="SELECT PatientName FROM Dependents [COLOR=red]WHERE PatientID=" & Me.PatientID[/COLOR]

What you select is up to you - basically it will be whatever you have in the combobox at the moment plus the criteria in red.

You'll also need to change the bit in green to the name of your combobox.

Finally, you mention you also have a dependents subform. Assuming the user can add new dependents from this form you need to add a bit of code to the after udate event of the subform which would be

Me.Parent.Vitals.form.[COLOR=seagreen]comboboxctrl[/COLOR].Requery

 

[SHABZO]

Thank you , I will try and confirm if ok. Really appreciate the help.

 

[SHABZO]

OK

So I think what you are saying is that the recordsource for the combobox needs to limit to dependents where patientID=the current patient. The combobox is in a subform in a subform control called Vitals. The controlsource for the subform control may also be called Vitals.

If I've understood this correctly then what you need to do is add soemthing like the following code to your main form current event:

Me.Vitals.form.[COLOR=seagreen]comboboxctrl[/COLOR].recordsource="SELECT PatientName FROM Dependents [COLOR=red]WHERE PatientID=" & Me.PatientID[/COLOR]

What you select is up to you - basically it will be whatever you have in the combobox at the moment plus the criteria in red.

You'll also need to change the bit in green to the name of your combobox.

Finally, you mention you also have a dependents subform. Assuming the user can add new dependents from this form you need to add a bit of code to the after udate event of the subform which would be

Me.Parent.Vitals.form.[COLOR=seagreen]comboboxctrl[/COLOR].Requery

I get an error doing that.
object does not support this property or method

Private Sub Form_Open(Cancel As Integer)
Me.Vitals.Form.txtDependents.RecordSource = "SELECT PatientName FROM Dependents WHERE PatientID=" & Me.PatientID
End Sub


The combo box is a field in my Vitals table and also a control in the Vitals Form.
The field is called Dependents.
I then a form called Patients where i inserted the Vitals Form as a subform. The Patience form opens with for example ID65. The vitals subform also opens with the same ID65. The problem is that the control or combo shows all the dependents for all the patience when we just want the one for id65. The doc can then select the dependent and enter new vitals in the datasheet.

 

[CJ_London]

Sorry, my mistake, it should be

Me.Vitals.Form.txtDependents.RowSource

 

[CJ_London]

A few other questions

The field is called Dependents

if that is the case then the rowsource should be

"SELECT Dependents FROM Dependents WHERE PatientID=" & Me.PatientID

But you should not really have a field with the sam name as the table since this can cause problems

I'm also confused about

The Patience form opens with for example ID65

I took this to mean your patientID=65. If the actual field value is 'ID65' then the sql needs to be

"SELECT Dependents FROM Dependents WHERE PatientID='" & Me.PatientID & "'"

 

[SHABZO]

Me.Vitals.Form.txtDependents.RowSource = "SELECT Dependents FROM Dependents WHERE PatientID=" & Me.PatientID

It works like a bomb. Thank you so much.

You are right i will change the field name.

Sir, can this work;
In Patients form , I want to doubleclick on the subforn Dependents PatientID and it will open the vitals form with all the RIGHT PatientID and dependents.

This way the vitals nurse doubleclicks on the dependents subform PatientID and the vitals opens and she can then fill in the tests for that dependent.

 

[pr2-eugin]

You can use DoCmd.OpenForm's Where condition inside the DoubleClick event.. http://baldyweb.com/wherecondition.htm

 

[CJ_London]

Paul's suggestion is the one to go for if you are opening a new form. But the descriptions you are using is a bit confusing so it may be you actually mean you want to refresh the existing form with a new patient or refresh the vitals subform on the patient form. Also a bit confused by your terms patient and dependant - you seem to be implying they are the same thing when I tend to think of dependant being a child or relation of the patient - a different person

 

[SHABZO]

You are right . Thr dependents are wife and kids that are dependents on a main member medical aid.

AT the moment it is not what we actually wanted. Basically I wanted in the main sheet a list with all the paitents. When you double click on it it should open another form with all that patients details .And in that form when you double click thr dependent all forns following thereafter should havre dependents name. AT the moment we still have to select the dependent

 

[SHABZO]

oKAY. So what I rally want is this.

1. Patients table with patients name. Patients nedical iad and dependents.and patientid(utonumber)
2.In vitals form have a field called dependents. and patientid.
3.in examination form have a field dependent and patiendid

Open patients form. select the dependent and click which will open vitals form with the dependent name already filled in. do your stuff here and click a button to open Examination with the dependent name already there.

At the moment WE SELECT patient name. click thw second tab to opwn vitals and then still select dependent from a drop dowm. select third tab and theen select dependent fronm drop dow,. This is bwcoming very irritating at the momwnt.

 

[CJ_London]

Might be better to upload a copy of your db with test data. Impossible to advise with the information provided!

 

[SHABZO]

Hi . Here is an attachment

 

[CJ_London]

This is getting more complicated by the minute!

I've added a dropdown for dependents and a button to open vitals to your patients form. The relevant code/controls are connected to those plus a bit of code in the patients form on current event and the vitals form on load event.

Hopefully that is what you are looking for. I've no idea what the 3rd tab means, none of your forms have tabs. However if you repeat what is in the vitals form load event for the other form and put a button with code similar to the button on the patient form (basically change the name of the form) then this should do it.

I've attached the modified db

 

[SHABZO]

Sorry

copy of medicalaid -listings.Click on that and that opens the tab forms with subforms

 

[SHABZO]

This is getting more complicated by the minute!

I've added a dropdown for dependents and a button to open vitals to your patients form. The relevant code/controls are connected to those plus a bit of code in the patients form on current event and the vitals form on load event.

Hopefully that is what you are looking for. I've no idea what the 3rd tab means, none of your forms have tabs. However if you repeat what is in the vitals form load event for the other form and put a button with code similar to the button on the patient form (basically change the name of the form) then this should do it.

I've attached the modified db

It works fine . Thank you very much. It does open the vitals form with the Dependents name. How can i also includw the PatientID and all recxords.

 

[CJ_London]

How can i also includw the PatientID and all recxords.

Sorry, I don't understand what you mean

 

[SHABZO]

Might be better to upload a copy of your db with test data. Impossible to advise with the information provided!

Thank you Sir. Works like a charm

I have the subforms History query and PatientHistory in a form Called PatientDetails.

Both subforms are open when you open PatientDetails.

Now if you doubleclick the control ID on HistoryQuery it suppose to change the record OF PatientHistory . it works with this

Private Sub txtID_DblClick(Cancel As Integer)
Const cstrForm As String = "PatientHistory" ' <-- change this
DoCmd.OpenForm cstrForm, WhereCondition:="[ID]=" & Me.txtID

The form opens in the background with the correct record. I want the subform to change and not another PatientHistory form opening in the back. You cannot see it unless you close the Mainform