Can't Make this Work

  • Thread starter Thread starter iamnu
  • Start date Start date
I

iamnu

I create a new Datasheet form from tlPB and get the "FIRST SQL VIEW".
I then select DirectoryID and from the Row Source, I get the "SECOND
SQL VIEW".
I then select DeptID and from the Row Source, I get the "THIRD SQL
VIEW".

DirectoryID and DeptID are both Combo Boxes.

Problem
I want only those DeptID's that are related to the DirectoryID that
has been entered in the Datasheet.

Can someone show me how this is done?
--------------------------------------------------------
FIRST SQL VIEW
SELECT tblPB.PBID, tblPB.DirectoryID, tblPB.DeptID
FROM tblPB;

SECOND SQL VIEW
SELECT zDirectories.DirectoryID, zDirectories.Directory
FROM zDirectories;

THIRD SQL VIEW
SELECT zDepartments.DeptID, zDepartments.DirectoryID,
zDepartments.DeptName
FROM zDepartments
ORDER BY zDepartments.DeptName;
--------------------------------------------------------
 
Iammu -

You need to add a WHERE clause to the SQL that represents the recordsources
for the following combo boxes. Like this (but use the name of the combo
boxes on your form):

SECOND SQL VIEW
SELECT zDirectories.DirectoryID, zDirectories.Directory
FROM zDirectories
WHERE zDirectories.DirectoryID = Me.<firstcombobox>;

THIRD SQL VIEW
SELECT zDepartments.DeptID, zDepartments.DirectoryID,
zDepartments.DeptName
FROM zDepartments
WHERE zDepartments.DeptID = Me.<secondcombobox>
ORDER BY zDepartments.DeptName;
 
Iammu -

You need to add a WHERE clause to the SQL that represents the recordsources
for the following combo boxes.  Like this (but use the name of the combo
boxes on your form):

SECOND SQL VIEW
SELECT zDirectories.DirectoryID, zDirectories.Directory
FROM zDirectories
WHERE zDirectories.DirectoryID = Me.<firstcombobox>;

THIRD SQL VIEW
SELECT zDepartments.DeptID, zDepartments.DirectoryID,
zDepartments.DeptName
FROM zDepartments
WHERE zDepartments.DeptID = Me.<secondcombobox>
ORDER BY zDepartments.DeptName;
Click to expand...

I left the SECOND SQL VIEW as it was, as it provides me with the
proper selection.

I don't really understand what you are suggesting, but I have tried
three different WHERE clauses (shown below) for the THIRD SQL VIEW.
Each one asks me to "Enter Parameter Value"

Do you have another suggestion?

SELECT zDepartments.DeptID, zDepartments.DeptName,
zDepartments.DirectoryID
FROM zDepartments
WHERE (((zDepartments.DirectoryID)=[zDirectoryID].[DirectoryID]))
ORDER BY zDepartments.DeptName;

SELECT zDepartments.DeptID, zDepartments.DeptName,
zDepartments.DirectoryID
FROM zDepartments
WHERE (((zDepartments.DirectoryID)=me.[cboDirectory]))
ORDER BY zDepartments.DeptName;

SELECT zDepartments.DeptID, zDepartments.DeptName,
zDepartments.DirectoryID
FROM zDepartments
WHERE (((zDepartments.DirectoryID)=[Forms]![PhoneBookSubForm].
[cboDirectory]))
ORDER BY zDepartments.DeptName;
 
Iamnu -

If it is asking for the parameter value when you are in design mode working
on the recordsource, then that is because the form is not open with the value
available. If this is the case, save the recordsource (even though it comes
up with a parameter question), and test it from the form view.

--
Daryl S


iamnu said:
Iammu -

You need to add a WHERE clause to the SQL that represents the recordsources
for the following combo boxes. Like this (but use the name of the combo
boxes on your form):

SECOND SQL VIEW
SELECT zDirectories.DirectoryID, zDirectories.Directory
FROM zDirectories
WHERE zDirectories.DirectoryID = Me.<firstcombobox>;

THIRD SQL VIEW
SELECT zDepartments.DeptID, zDepartments.DirectoryID,
zDepartments.DeptName
FROM zDepartments
WHERE zDepartments.DeptID = Me.<secondcombobox>
ORDER BY zDepartments.DeptName;
Click to expand...

I left the SECOND SQL VIEW as it was, as it provides me with the
proper selection.

I don't really understand what you are suggesting, but I have tried
three different WHERE clauses (shown below) for the THIRD SQL VIEW.
Each one asks me to "Enter Parameter Value"

Do you have another suggestion?

SELECT zDepartments.DeptID, zDepartments.DeptName,
zDepartments.DirectoryID
FROM zDepartments
WHERE (((zDepartments.DirectoryID)=[zDirectoryID].[DirectoryID]))
ORDER BY zDepartments.DeptName;

SELECT zDepartments.DeptID, zDepartments.DeptName,
zDepartments.DirectoryID
FROM zDepartments
WHERE (((zDepartments.DirectoryID)=me.[cboDirectory]))
ORDER BY zDepartments.DeptName;

SELECT zDepartments.DeptID, zDepartments.DeptName,
zDepartments.DirectoryID
FROM zDepartments
WHERE (((zDepartments.DirectoryID)=[Forms]![PhoneBookSubForm].
[cboDirectory]))
ORDER BY zDepartments.DeptName;
.
Click to expand...
 
I think this is what Daryl intends...It is how I do it anyway.

Set the RowSource of your department to be:
SELECT zDirectories.DirectoryID, zDirectories.Directory
FROM zDirectories
WHERE (((zDirectories.DirectoryID) =
[Forms]![formname]![DirectoryComboboxName]));

In the After Update event of the Directory combo box, enter the code:

DoCmd.Requery "DepartmentComboboxName"

I would also add criteria to the Department Row Source in case the user
selects the Department combobox first (leaving the Directory Null) or you can
just hide the Department Combobox until the Directory has been selected.
Jill
 
Back
Top