Welcome to SPN

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

Sign Up Now!

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

    >>
    >>
    >>
     

Share This Page