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

#1 (**permalink**) 28-Jul-2006, 08:32 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/12860-how-use-combo-box-result-sub.html
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12860
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

#2 (**permalink**) 28-Jul-2006, 08:32 AM

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.

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

"Rabbie Williams" Williams@discussions.microsoft.com> wrote in message
news:FD2F9E92-9F2B-4024-86FF-6300AE77733E@microsoft.com...
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12860
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12860
> 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

#3 (**permalink**) 28-Jul-2006, 08:32 AM

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" wrote:

> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12860
> with that ID can be selected from the combo, as susequent visits occur.
>
> --
> hth
> Al Camp
> Candia Computer Consulting - Candia NH
> http://home.comcast.net/~cccsolutions
>
>
> "Rabbie Williams" Williams@discussions.microsoft.com> wrote in message
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12860
> news:FD2F9E92-9F2B-4024-86FF-6300AE77733E@microsoft.com...
> > 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
>
>
>

#4 (**permalink**) 28-Jul-2006, 08:32 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12860
your own object names)
= Forms!frmYourMainForm!SomeFieldThatLimitsTheRepsDi splayed
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

"Rabbie Williams" wrote in message
news:C32298C2-36B3-4EB9-92CC-94AD24A1788E@microsoft.com...
> 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" wrote:
>
>> 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.
>>
>> --
>> hth
>> Al Camp
>> Candia Computer Consulting - Candia NH
>> http://home.comcast.net/~cccsolutions
>>
>>
>> "Rabbie Williams" Williams@discussions.microsoft.com> wrote in message
>> news:FD2F9E92-9F2B-4024-86FF-6300AE77733E@microsoft.com...
>> > 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12860
>> > 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
>>
>>
>>