Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

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

Discussion in 'Information Technology' started by Rabbie Williams, Jul 28, 2006.

  1. Rabbie Williams

    Rabbie Williams
    Expand Collapse
    Guest

    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
     
  2. Loading...


  3. Al Camp

    Al Camp
    Expand Collapse
    Guest

    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" <Rabbie 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
    > 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. Rabbie Williams

    Rabbie Williams
    Expand Collapse
    Guest

    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" <Rabbie 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
    > > 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

    >
    >
    >
     
  5. Al Camp

    Al Camp
    Expand Collapse
    Guest

    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
    --
    hth
    Al Camp
    Candia Computer Consulting - Candia NH
    http://home.comcast.net/~cccsolutions

    "Rabbie Williams" <RabbieWilliams@discussions.microsoft.com> 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" <Rabbie 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
    >> > 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

    >>
    >>
    >>
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page