ACCESS FEED: Re: [MS_AccessPros] Control Record source of a combo on a subform from another combo

Rabu, 24 Agustus 2011

Re: [MS_AccessPros] Control Record source of a combo on a subform from another combo

The record source works great! Thanks. What would the requery code look like?

With warm regards,

Arthur Lorenzini| SQL Server/Access Developer l alorenzin@live.com
Office: 605-338-0947| Mobile: 605-857-9137 | Fax: 605-338-0947

1316 E. 7th Street
Sioux Falls, SD 57103
SQL Server Development
Database Adminstration Services
Microsoft Access Development
Grant Writing TA Services
IT Assessment Services
Software Application Training

From: John Viescas <john@viescas.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Wednesday, August 24, 2011 10:57 AM
Subject: RE: [MS_AccessPros] Control Record source of a combo on a subform from another combo

Art-

Try this:

SELECT tblTrainingGroup.TrainingGroupID, tblTrainingGroup.GroupTypeID,
tlkpTrainingCode.TrainingCodeName, tlkpEmployeeType.EmployeeTypeName,
tblTrainingGroup.TrainingGroupName FROM (tblTrainingGroup INNER JOIN
tlkpTrainingCode ON
tblTrainingGroup.TrainingCodeID=tlkpTrainingCode.TrainingCodeID) INNER JOIN
tlkpEmployeeType ON tblTrainingGroup.GroupTypeID=tlkpEmployeeType.EmployeeTypeID

WHERE (tblTrainingGroup.GroupTypeID = [Forms]![frmEmployee]![cmbGroupType]) Or
([Forms]![frmEmployee]![cmbGroupType] IS NULL)
ORDER BY tblTrainingGroup.TrainingGroupName;

If cmbGroupType is a bound control, but a Requery of the combo on the subform in
the outer form's Current event. Also put a Requery in the AfterUpdate event of
cmbGroupType.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Art
Sent: Wednesday, August 24, 2011 5:25 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Control Record source of a combo on a subform from
another combo

I have a form called frmEmployee. This from contains a tab control. On the first
tab there is the usual employee info (name, address, phone, etc). Also on this
tab is a combo box the cmbGroupType.

Record source is

Select grouptypeid, grouptypename, from tlkGroupType

Data in this table is
First Responders
Supervisor
StrikeForce
Command

On the second tab is a subform called sfrmEmployeeTraining

The subform is just a datasheet with the following recordsource:
SELECT tblEmployeeTraining.TrainingDate, tblEmployeeTraining.CompletedFlag,
tblEmployeeTraining.TrainingGroupID, tblEmployeeTraining.EmployeeID,
tblTrainingGroup.TrainingGroupName FROM tblEmployeeTraining INNER JOIN
tblTrainingGroup ON
tblEmployeeTraining.TrainingGroupID=tblTrainingGroup.TrainingGroupID;

On the subform is a combo called cmbTrainingGroup with the record source of:

SELECT tblTrainingGroup.TrainingGroupID, tblTrainingGroup.GroupTypeID,
tlkpTrainingCode.TrainingCodeName, tlkpEmployeeType.EmployeeTypeName,
tblTrainingGroup.TrainingGroupName FROM (tblTrainingGroup INNER JOIN
tlkpTrainingCode ON
tblTrainingGroup.TrainingCodeID=tlkpTrainingCode.TrainingCodeID) INNER JOIN
tlkpEmployeeType ON tblTrainingGroup.GroupTypeID=tlkpEmployeeType.EmployeeTypeID
ORDER BY tblTrainingGroup.TrainingGroupName;

The tblTrainingGroup contains data as follows:
TrainingGroupID TrainingGroupName GroupTypeID TrainingCodeID
3 EL/FR ICS-300 Entry Level/First Responder ICS-300
4 EL/FR ICS-400 Entry Level/First Responder ICS-400
5 EL/FR FEMA IS-700 Entry Level/First Responder FEMA IS-700
6 EL/FR FEMA IS-701 Entry Level/First Responder FEMA IS-701
7 EL/FR FEMA IS-702 Entry Level/First Responder FEMA 11 EL/FR
ICS IS-100.HE Entry Level/First Responder ICS-100.HE
12 EL/FR ICS IS-100.HC Entry Level/First Responder ICS-100.HC
13 EL/FR Trainer Cert Entry Level/First Responder Trainer Cert
14 EL/FR Wide Area Search Entry Level/First Responder Wide Area Search
15 EL/FR TLO Entry Level/First Responder TLO
16 EL/FR S BELT Entry Level/First Responder S BELT
17 EL/FR First Aid/CPR Entry Level/First Responder First Aid/CPR
18 EL/FR IS-00420 Entry Level/First Responder IS-00420
19 EL/FR E-580 Entry Level/First Responder E-580
20 EL/FR E-581 Entry Level/First Responder E-581
21 EL/FR FEMA IS-650 Entry Level/First Responder FEMA IS-650
22 EL/FR FEMA IS-00001 Entry Level/First Responder FEMA IS-00001
23 EL/FR FEMA IS-00775 Entry Level/First Responder FEMA IS-00775
24 EL/FR Avian Influenza Entry Level/First Responder Avian Influenza
25 EL/FR Standards Assessment & Audits Entry Level/First Responder
Standards Assessment & Audits
26 EL/FR CERT Entry Level/First Responder CERT
30 EL/FR AED Entry Level/First Responder AED
32 SUPER ICS-100 Supervisors ICS-100
33 SUPER ICS-200 Supervisors ICS-200
34 SUPER ICS-300 Supervisors ICS-300
35 SUPER ICS-400 Supervisors ICS-400
36 SUPER FEMA IS-700 Supervisors FEMA IS-700
37 SUPER FEMA IS-701 Supervisors FEMA IS-701
38 SUPER FEMA IS-702 Supervisors FEMA IS-702
39 SUPER FEMA IS-703 Supervisors FEMA IS-703
40 SUPER FEMA IS-704 Supervisors FEMA IS-704
41 SUPER FEMA IS-800.A Supervisors FEMA IS-800.A
42 SUPER ICS-100.HE Supervisors ICS-100.HE
43 SUPER ICS-100.HC Supervisors ICS-100.HC
67 STRIKE FEMA IS-700 Strike Team/Unit Leaders FEMA IS-700
68 STRIKE FEMA IS-701 Strike Team/Unit Leaders FEMA IS-701
70 STRIKE FEMA IS-702 Strike Team/Unit Leaders FEMA IS-702
71 STRIKE FEMA IS-703 Strike Team/Unit Leaders FEMA IS-703
72 STRIKE FEMA IS-704 Strike Team/Unit Leaders FEMA IS-704
73 STRIKE FEMA IS-800.A Strike Team/Unit Leaders FEMA IS-800.A
74 STRIKE ICS-100.HE Strike Team/Unit Leaders ICS-100.HE
75 STRIKE ICS-100.HC Strike Team/Unit Leaders ICS-100.HC
76 STRIKE Trainer Cert Strike Team/Unit Leaders Trainer Cert
77 STRIKE Wide Area Search Strike Team/Unit Leaders Wide Area Search
78 STRIKE TLO Strike Team/Unit Leaders TLO
120 COMM CERT Command Staff CERT
124 COMM AED Command Staff AED
128 EL/FR ICS-100 Entry Level/First Responder ICS-100
129 EL/FR ICS-200 Entry Level/First Responder ICS-200
130 SUPER IS-00522 Supervisors IS-00522

When you click on the cmbTrainingGroup combo the list contains all the entries.
What I would like to see is when they select a group type from cmbGroupType on
the first tab. I want only to see that group type listed in the cmbTrainingGroup
combo on the subform.

Thanks

Art Lorenzini

------------------------------------

Yahoo! Groups Links

[Non-text portions of this message have been removed]