Access 2000 - Combo Box problem

[Spider]

Hi all,
I am working on a database for hospital patient procedures.
I have Patient table, and Encounter table (plus list and detail tables).
A patient may have many encounters.

tblPatient – (PatientID, LName, FName etc...)
tblEncounter – (Facility, Date, Nursing Unit, Procedure etc...)
tblFacility - (Facility ID, Facility Name)
tblUnit - (UnitID, UnitName, FacilityID)


On the data entry form, I have the Patient info as the main form ‘frmDataEntry’ and a subform from the Encounter table ‘frmDataEntrySub’.

We have 5 facilities at this hospital; each facility has its own set of nursing ‘Units’ (floors).

On the subform there are two combo boxes cmbFacility and cmbUnit

I need the Unit to populate based on the Facility chosen – I know this is not a new question, but I have been trying to make this work for a couple of days.

For cmbUnit:

Row Source Property =
SELECT tblUnit.UnitID, tblUnit.Unit, tblUnit.Location, tblUnit.FacilityID FROM tblUnit WHERE (((tblUnit.FacilityID)=Forms!frmPatientEncounters!FacilityID));
Row Source Type = Table/Query
Column Count = 3
Column Widths 0”;2”;0”

In the After Update property of the subform, I used:

Private Sub Unit_AfterUpdate()
Me.Unit = Null
Me!Unit.Requery
Me.Unit = Me.Unit.ItemData(0)
End Sub
but...I’m not sure if it belongs in the main form or subform -I have it in the subform.

A parameter box pops up asking for “Forms!frmPatientEncounters!FacilityID” when I try to view the form in form view.
The FacilityID is on the subform.
I have attached a stripped down version if needed -if someone could help me figure out what I am doing wrong.
Thanks so much,
Vicky

[HansV]

Hi Vicky,

To refer to a control on a subform, you need to use the syntax

Forms!MainFormName!SubFormName!ControlName

so the row source of Unit on the subform should be

SELECT tblUnit.UnitID, tblUnit.Unit, tblUnit.FacilityID FROM tblUnit WHERE (((tblUnit.FacilityID)=[Forms]![frmDataEntry]![frmEncounterSub]![FacilityID]));

Additionally, the code you have should be in the After Update event of the FacilityID combo box, for you want to requery the Unit combo box when the user selects a different facility:

Code: Select all

Private Sub FacilityID_AfterUpdate()
  Me!Unit.Requery
  Me.Unit = Me.Unit.ItemData(0)
End Sub

See the attached version.

Copy of MCHS PICC Database.zip

[Spider]

Thank you for your quick reply and clarification!
I changed the Unit row source to
SELECT tblUnit.UnitID, tblUnit.Unit, tblUnit.FacilityID FROM tblUnit WHERE (((tblUnit.FacilityID)=[Forms]![frmDataEntry]![frmEncounterSub]![FacilityID]));
as you suggested.

Then made sure that the code:

Private Sub FacilityID_AfterUpdate()
Me!Unit.Requery
Me.Unit = Me.Unit.ItemData(0)
End Sub

was in the combo box for the FacilityID (you had placed it there in the attachment).

But the Unit combo box will not populate with the units.
Is there something else I might be missing?

[HansV]

Does it work for you in the version that I attached?

[Spider]

No :( ....

[HansV]

And in this version? (I removed some unnecessary references)

Copy of MCHS PICC Database.zip

[Spider]

No....
I have deleted all old copies - and downloaded this last attachment multiple times.
Does it work for you?
I am using Access 2003.

[HansV]

It does work for me. I modified the database in Access 2007.
If you select Tools | References... in the Visual Basic Editor, are any references listed as MISSING:...?

[Spider]

I was downloading the attachment to my home computer - Access 2003. I was working on this at work earlier and now at home.

So I logged into work remotely, used our internet there, went to Eileen's and downloaded your newest version, opened it on my remote work Access and it works! Work has Access 2003 as well.

That is going to make it difficult form me to work on this from home :-)

[Spider]

I did not see any missing references- but not sure what I am looking at.
I attached screen shot.

image001.jpg

[HansV]

None of the selected (ticked) references starts with MISSING so that's OK. I can't explain why it doesn't work on your home PC.

[Spider]

I'm not worried about the home PC - I am very glad to have it working at work!
Thank you so much as usual!
Again, I learn so much here!
I can log into work if I can't figure out the home PC....

[Spider]

FYI...
I opened Access, Tools > Macro > and set the macro security to 'low' and it works at home now..

[HansV]

Thanks for the update!

[Spider]

What would you suggest I do with the default value. It currently defaults to the first 'Unit' for that 'Facility'.
I would rather have it default to blank (I know I would need to create a blank name row for that in the Unit table) or 'Unknown' - but I have only managed to find out how to do that with an unfiltered combo box.
Default Value = [Unit].ItemData(5).
Is there a way to have a default value for each unit?
Thank you,
Vicky

[HansV]

If you'd like to clear the unit combo box, use

Code: Select all

Private Sub FacilityID_AfterUpdate()
  Me!Unit.Requery
  Me.Unit = Null
End Sub

If you'd like a specific default value for each FacilityID, you could add a Yes/No field to tblUnit, and set it to Yes for one record for each FacilityID; you can then use this in the code:

Code: Select all

Private Sub FacilityID_AfterUpdate()
  Me!Unit.Requery
  Me.Unit = DLookup("UnitID", "tblUnit", "Default=True AND FacilityID=" & Me.FacilityID)
End Sub

See attached version.

Copy of MCHS PICC Database.zip

[Spider]

Thank you, thank you...
I will work on these...