Requery method for combobox on subform

G

Guest

I'm having trouble getting a requery to work on combo boxes on a subform.
I have a main form that queries project ID from tables. The subform queries
from those fields additional information for a particular project id. It
works fine (yeah!). I want to have the combo boxes on the subform update
when entering new additional information for a project id. I have placed
combo boxes on two fields in the subform as a test. The combo boxes are not
updating with each project id selected. Any help is GREATLY appreciated!
(Combo boxes will be going on the majority of the fields on the subform once
I get my test case working.)

Here is what I have.

-----------
Main Form:
QueryProjectPhaseStage
Fields: PROJID
PROJECT_DESCR
PHASE
PHASE_DESCR
STAGE_OF_PROCESS
STAGE_DESCR
Record Source: Stage of Process Lookup - query

Stage of Process Lookup query table relationship
CAET_ML_PROJECT_CD --- CAET_STAGE_OF_PROCESS_CD --- CAET_PHASE_CD

Stage of Process Lookup query:
SELECT CAET_STAGE_OF_PROCESS_CD.*, CAET_PHASE_CD.*, CAET_ML_PROJECT_CD.*
FROM CAET_ML_PROJECT_CD INNER JOIN (
CAET_PHASE_CD INNER JOIN CAET_STAGE_OF_PROCESS_CD ON
CAET_PHASE_CD.PHASE_CODE = CAET_STAGE_OF_PROCESS_CD.Phase) ON
CAET_ML_PROJECT_CD.PROJECT_CODE =
CAET_STAGE_OF_PROCESS_CD.Projid
ORDER BY CAET_STAGE_OF_PROCESS_CD.Projid;

---------

SubForm:

QueryLettersForm
Link Child Fields: PROJID;PHASE;STAGE_OF_PROCESS
Link Master Fields: Projid;Phase;Stage_of_process
Record Source: QueryLettersNew - query

QueryLettersNew query table
CAET_LTR

QueryLettersNew query:
SELECT CAET_LTR.* FROM CAET_LTR
WHERE
(((CAET_LTR.PROJID)=[Forms]![LetterInputTest2]![QueryProjectPhaseStage].[Form]![Projid]) AND
((CAET_LTR.PHASE)=[Forms]![LetterInputTest2]![QueryProjectPhaseStage].[Form]![Phase]) AND
((CAET_LTR.STAGE_OF_PROCESS)=[Forms]![LetterInputTest2]![QueryProjectPhaseStage].[Form]![Stage_of_process]))
ORDER BY CAET_LTR.LTR_NO;

ComboBoxes on SubForm:

ComboBox: cboResponseType
Control Source: RESPONSE_TYPE
Row Source Type: Table/Query
Row Source: SELECT CAET_RESPONSE_TYPE_CD.RESPONSE_TYPE FROM
CAET_RESPONSE_TYPE_CD WHERE
(((CAET_RESPONSE_TYPE_CD.PROJID)=[Forms]![LetterInputTest2]![QueryProjectPhaseStage].[Form]![Projid])
AND
((CAET_RESPONSE_TYPE_CD.PHASE)=[Forms]![LetterInputTest2]![QueryProjectPhaseStage].[Form]![Phase]));


ComboBox: cboDELIVERY_TYPE
Control Source: DELIVERY_TYPE
Row Source Type: Table/Query
Row Source: SELECT CAET_DELIVERY_TYPE_CD.DELIVERY_TYPE FROM
CAET_DELIVERY_TYPE_CD WHERE
(((CAET_DELIVERY_TYPE_CD.PROJID)=[Forms]![LetterInputTest2]![QueryProjectPhaseStage].[Form]![Projid])
AND
((CAET_DELIVERY_TYPE_CD.PHASE)=[Forms]![LetterInputTest2]![QueryProjectPhaseStage].[Form]![Phase]));

---------

Tried this first:
subform combo boxes
On Click: Forms("QueryProjectPhaseStage").cboResponseType.Requery
On Click: Forms("QueryProjectPhaseStage").cboDELIVERY_TYPE.Requery


Then tried this second:
subform combo boxes
On Click:
[Event Procedure]
Private Sub cboDELIVERY_TYPE_Click()
Me.QueryProjectPhaseStage.Form!cboDELIVERY_TYPE.Requery
'Me.QueryLettersForm.Form!cboDELIVERY_TYPE.Requery
'Forms("QueryProjectPhaseStage").cboDELIVERY_TYPE.Requery
'[Forms]![QueryProjectPhaseStage].[cboDELIVERY_TYPE].[Requery]
'Me.cboDELIVERY_TYPE.Requery
End Sub


Tried this third:
Stage of Process Lookup query on main form
After Update:
[Event Procedure]
Private Sub Form_AfterUpdate()
Me.QueryLettersForm.Form!cboResponseType.Requery
Me.QueryLettersForm.Form!cboDELIVERY_TYPE.Requery
End Sub


THANKS AGAIN!!!!!!
 
G

Guest

Thank you very much for directing me to this web site. I was not familiar
with it. Please forgive me, I'm a little new to Access, where/how do I
determine the "CONTROL" in the statement at that web site: "Subform1 is the
name of the subform CONTROL on mainform"?
 
R

RuralGuy

Thank you very much for directing me to this web site. I was not
familiar with it. Please forgive me, I'm a little new to Access,
where/how do I determine the "CONTROL" in the statement at that web
site: "Subform1 is the name of the subform CONTROL on mainform"?

Hi,

SubForms are displayed on forms by putting them in SubFormControls.
SubFormControls are for displaying Forms on other Forms. Most of the time
the developer doesn't change the name and I think the default name is the
same of the SubForm, but it doesn't have to be! At any rate the
SubFormControl has it's own name and the the form displayed within it (the
SubForm) has another name, that can be the same if you so choose.

Hope that helps.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top