how to use a combo box result in a sub form combo box / query

[Rabbie Williams]

I'm trying to add some more complex functionality to a database that i've set
up but wonder whether it is beyond my scope and whether I need to seek
professional help.

The database is a medical reps call report. The user selects the hospital
he has visited on the main form. A continuous sub form is used to display
the individuals of that hospital that he has seen. The sub form has a combo
box where the user selects the individual. I would like the sub form combo
box to show only those individuals who are associated with the particular
hospital selected rather than all the individuals of all hospitals who number
thousands.

I presume I need to delve into VBscript; to add the hospital name to a
variable. But how do I get the query (which is used by the sub form combo
box) to read this variable - is it possible and if so, what is the syntax?

Many thanks for any help.

S Miller

 

[Al Camp]

Rabbie,
On your main form, you should... beside the HospitalName... have a HospitalID (key
unique value)
This value should be the Parent/Child link between the main and the sub. (Your sub
records should also have a HospitalID) so that they will be linked to the main at all
times.
Use that sub HospitalID as a criteria in your sub combo box, so that only personnel
with the associated HospitalIDs will be listed for selection.

Only rep records with the associated HospitalID will show in the subform, and only reps
with that ID can be selected from the combo, as susequent visits occur.

 

[Rabbie Williams]

Unfortunately this doesn't work as hospitalID isn't the primary key - there
may be multiple call reports per hospital for every visit.

With my (albeit limited) programming knowledge what I think is required is
that the query from which the sub form combo is created, need to access the
main form combo result e.g.

SELECT Hospitals.AddressID, Hospitals.Name,
Individuals_area3.ContactLastName, Individuals_area3.ContactFirstName,
Individuals_area3.contactID
FROM Hospitals INNER JOIN Individuals_area3 ON
Hospitals.AddressID=Individuals_area3.AddressID
WHERE (((Individuals_area3.AddressID)=(Combo22.theResult)));

Though this level of functionality may be beyond me.

Cheers,

S Miller

 

[Al Camp]

Well, I can't say that I would go along with using an AddressID as the key field, but
let's put that aside.
If you have a field on the main form that can be used to limit the selections in the
subform combo, then you would use this "example" as a criteria in your sub combo. (use
your own object names)
= Forms!frmYourMainForm!SomeFieldThatLimitsTheRepsDisplayed