Read only query/form

W

Wayne

I have a main form called PrintRequisition with the
following query as its record source:
SELECT Print_Requisition.PrintReqID,
Print_Requisition.ProjectID, Projects.ProjectName,
Projects.EmployeeID, [LastName] & ", " & [FirstName] AS Expr1
FROM Employees RIGHT JOIN (Print_Requisition INNER JOIN
Projects ON Print_Requisition.ProjectID =
Projects.ProjectID) ON Employees.EmployeeID =
Projects.EmployeeID
ORDER BY [LastName] & ", " & [FirstName];

I then have a sub form called PrintReq_Subform with this
query as its record source:
SELECT [PrintReq_Sub Query1].PrintSubID,
PrintReq_Sub.PrintReqID, PrintReq_Sub.Date,
PrintReq_Sub.PrintsFor, PrintReq_Sub.RequestedBy,
PrintReq_Sub.Description, PrintReq_Sub.Type,
PrintReq_Sub.PaperSize, PrintReq_Sub.FirstQuantity,
PrintReq_Sub.Copies, PrintReq_Sub.PrintFee,
PrintReq_Sub.Postage, PrintReq_Sub.PostageFee
FROM PrintReq_Sub INNER JOIN [PrintReq_Sub Query1] ON
PrintReq_Sub.PrintSubID = [PrintReq_Sub Query1].PrintSubID
WITH OWNERACCESS OPTION;

This query refers to a sub query:
SELECT PrintReq_Sub.PrintSubID, PrintReq_Sub.PrintReqID
FROM Print_Requisition LEFT JOIN PrintReq_Sub ON
Print_Requisition.PrintReqID = PrintReq_Sub.PrintReqID
WHERE
(((PrintReq_Sub.PrintReqID)=[Print_Requisition].[PrintReqID]));

This was the only solution I could find so that the correct
data is displayed in the sub form according to the current
record in the main for.
The problem is that this query/subform only displays the
current data. It will not let me change any existing data
or add new records.

Can someone please tell me what i've done wrong and how to
fix this problem??

Thanks
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Not completely sure, but this has worked in similar problems:

Put DISTINCTROW in the SELECT clause of a queries: SELECT DISTINCTROW
....

With joined tables, if you want to edit/add records to both tables you
have to have each table's required fields in the SELECT clause. If you
only want to edit/add records to one table you will have to have only
that table's required fields in the SELECT clause.

You subform RecordSource query:

Use the PrintReq_Sub.PrintSubID instead of [PrintReq_Sub
Query1].PrintSubID.

In your subform's subquery:

Put the Print_Requisition.PrintReqID in the SELECT clause instead of the
PrintReq_Sub.PrintReqID, since the Print_Requisition is on the LEFT side
of the JOIN - which indicates you want all records from
Print_Requisition. Therefore, you will require a field from the "want
all records" table in the SELECT clause to get the required result.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQSZx6YechKqOuFEgEQIUsACgyQzgPewBkIbnZ2hoNcSWOBgeR1IAoPeB
e2ORLVSt8LdQjX8DU91IEpvZ
=Cs6z
-----END PGP SIGNATURE-----

I have a main form called PrintRequisition with the
following query as its record source:
SELECT Print_Requisition.PrintReqID,
Print_Requisition.ProjectID, Projects.ProjectName,
Projects.EmployeeID, [LastName] & ", " & [FirstName] AS Expr1
FROM Employees RIGHT JOIN (Print_Requisition INNER JOIN
Projects ON Print_Requisition.ProjectID =
Projects.ProjectID) ON Employees.EmployeeID =
Projects.EmployeeID
ORDER BY [LastName] & ", " & [FirstName];

I then have a sub form called PrintReq_Subform with this
query as its record source:
SELECT [PrintReq_Sub Query1].PrintSubID,
PrintReq_Sub.PrintReqID, PrintReq_Sub.Date,
PrintReq_Sub.PrintsFor, PrintReq_Sub.RequestedBy,
PrintReq_Sub.Description, PrintReq_Sub.Type,
PrintReq_Sub.PaperSize, PrintReq_Sub.FirstQuantity,
PrintReq_Sub.Copies, PrintReq_Sub.PrintFee,
PrintReq_Sub.Postage, PrintReq_Sub.PostageFee
FROM PrintReq_Sub INNER JOIN [PrintReq_Sub Query1] ON
PrintReq_Sub.PrintSubID = [PrintReq_Sub Query1].PrintSubID
WITH OWNERACCESS OPTION;

This query refers to a sub query:
SELECT PrintReq_Sub.PrintSubID, PrintReq_Sub.PrintReqID
FROM Print_Requisition LEFT JOIN PrintReq_Sub ON
Print_Requisition.PrintReqID = PrintReq_Sub.PrintReqID
WHERE
(((PrintReq_Sub.PrintReqID)=[Print_Requisition].[PrintReqID]));

This was the only solution I could find so that the correct
data is displayed in the sub form according to the current
record in the main for.
The problem is that this query/subform only displays the
current data. It will not let me change any existing data
or add new records.

Can someone please tell me what i've done wrong and how to
fix this problem??

Thanks
Click to expand...
 
W

Wayne

I have made the following changes.
Subform record source query:
SELECT PrintReq_Sub.PrintSubID, PrintReq_Sub.PrintReqID,
PrintReq_Sub.Date, PrintReq_Sub.PrintsFor,
PrintReq_Sub.RequestedBy, PrintReq_Sub.Description,
PrintReq_Sub.Type, PrintReq_Sub.PaperSize,
PrintReq_Sub.FirstQuantity, PrintReq_Sub.Copies,
PrintReq_Sub.PrintFee, PrintReq_Sub.Postage,
PrintReq_Sub.PostageFee
FROM PrintReq_Sub INNER JOIN [PrintReq_Sub Query1] ON
PrintReq_Sub.PrintSubID = [PrintReq_Sub Query1].PrintSubID
WITH OWNERACCESS OPTION;

Sub Query:
SELECT PrintReq_Sub.PrintSubID
FROM Print_Requisition LEFT JOIN PrintReq_Sub ON
Print_Requisition.PrintReqID = PrintReq_Sub.PrintReqID
WHERE
(((PrintReq_Sub.PrintReqID)=[Print_Requisition].[PrintReqID]));

This has solved the problem of not being able to edit or
add data, but it doesnt dispaly any existing records in the
sub form.
I am not very good with queries, so If you could edit the
above code for me it would be greatly appreciated.

Thanks

-----Original Message-----
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Not completely sure, but this has worked in similar problems:

Put DISTINCTROW in the SELECT clause of a queries: SELECT DISTINCTROW
....

With joined tables, if you want to edit/add records to both tables you
have to have each table's required fields in the SELECT clause. If you
only want to edit/add records to one table you will have to have only
that table's required fields in the SELECT clause.

You subform RecordSource query:

Use the PrintReq_Sub.PrintSubID instead of [PrintReq_Sub
Query1].PrintSubID.

In your subform's subquery:

Put the Print_Requisition.PrintReqID in the SELECT clause instead of the
PrintReq_Sub.PrintReqID, since the Print_Requisition is on the LEFT side
of the JOIN - which indicates you want all records from
Print_Requisition. Therefore, you will require a field from the "want
all records" table in the SELECT clause to get the required result.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQSZx6YechKqOuFEgEQIUsACgyQzgPewBkIbnZ2hoNcSWOBgeR1IAoPeB
e2ORLVSt8LdQjX8DU91IEpvZ
=Cs6z
-----END PGP SIGNATURE-----

I have a main form called PrintRequisition with the
following query as its record source:
SELECT Print_Requisition.PrintReqID,
Print_Requisition.ProjectID, Projects.ProjectName,
Projects.EmployeeID, [LastName] & ", " & [FirstName] AS Expr1
FROM Employees RIGHT JOIN (Print_Requisition INNER JOIN
Projects ON Print_Requisition.ProjectID =
Projects.ProjectID) ON Employees.EmployeeID =
Projects.EmployeeID
ORDER BY [LastName] & ", " & [FirstName];

I then have a sub form called PrintReq_Subform with this
query as its record source:
SELECT [PrintReq_Sub Query1].PrintSubID,
PrintReq_Sub.PrintReqID, PrintReq_Sub.Date,
PrintReq_Sub.PrintsFor, PrintReq_Sub.RequestedBy,
PrintReq_Sub.Description, PrintReq_Sub.Type,
PrintReq_Sub.PaperSize, PrintReq_Sub.FirstQuantity,
PrintReq_Sub.Copies, PrintReq_Sub.PrintFee,
PrintReq_Sub.Postage, PrintReq_Sub.PostageFee
FROM PrintReq_Sub INNER JOIN [PrintReq_Sub Query1] ON
PrintReq_Sub.PrintSubID = [PrintReq_Sub Query1].PrintSubID
WITH OWNERACCESS OPTION;

This query refers to a sub query:
SELECT PrintReq_Sub.PrintSubID, PrintReq_Sub.PrintReqID
FROM Print_Requisition LEFT JOIN PrintReq_Sub ON
Print_Requisition.PrintReqID = PrintReq_Sub.PrintReqID
WHERE
(((PrintReq_Sub.PrintReqID)=[Print_Requisition].[PrintReqID]));

This was the only solution I could find so that the correct
data is displayed in the sub form according to the current
record in the main for.
The problem is that this query/subform only displays the
current data. It will not let me change any existing data
or add new records.

Can someone please tell me what i've done wrong and how to
fix this problem??

Thanks
Click to expand...

.
Click to expand...
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I've never been able to get that to work in a subform. In a query, yes;
but not in a datasheet of a subform: don't know why.

A work-around was to display all possible attributes in a pop-up form
(not acDialog) so the user can see which attributes were required & if
they were filled.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQSbIqYechKqOuFEgEQL33wCfTNBsdMc1RZgcW4FIE+qfev01ADEAmgKF
qRhJ/sAQxU+bRjFQfIZsuRfK
=QW9U
-----END PGP SIGNATURE-----

I have made the following changes.
Subform record source query:
SELECT PrintReq_Sub.PrintSubID, PrintReq_Sub.PrintReqID,
PrintReq_Sub.Date, PrintReq_Sub.PrintsFor,
PrintReq_Sub.RequestedBy, PrintReq_Sub.Description,
PrintReq_Sub.Type, PrintReq_Sub.PaperSize,
PrintReq_Sub.FirstQuantity, PrintReq_Sub.Copies,
PrintReq_Sub.PrintFee, PrintReq_Sub.Postage,
PrintReq_Sub.PostageFee
FROM PrintReq_Sub INNER JOIN [PrintReq_Sub Query1] ON
PrintReq_Sub.PrintSubID = [PrintReq_Sub Query1].PrintSubID
WITH OWNERACCESS OPTION;

Sub Query:
SELECT PrintReq_Sub.PrintSubID
FROM Print_Requisition LEFT JOIN PrintReq_Sub ON
Print_Requisition.PrintReqID = PrintReq_Sub.PrintReqID
WHERE
(((PrintReq_Sub.PrintReqID)=[Print_Requisition].[PrintReqID]));

This has solved the problem of not being able to edit or
add data, but it doesnt dispaly any existing records in the
sub form.
I am not very good with queries, so If you could edit the
above code for me it would be greatly appreciated.

Thanks


-----Original Message-----
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Not completely sure, but this has worked in similar problems:

Put DISTINCTROW in the SELECT clause of a queries: SELECT
DISTINCTROW

....

With joined tables, if you want to edit/add records to
Click to expand...

both tables you
have to have each table's required fields in the SELECT
Click to expand...

clause. If you
only want to edit/add records to one table you will have
Click to expand...

to have only
that table's required fields in the SELECT clause.

You subform RecordSource query:

Use the PrintReq_Sub.PrintSubID instead of [PrintReq_Sub
Query1].PrintSubID.

In your subform's subquery:

Put the Print_Requisition.PrintReqID in the SELECT clause
Click to expand...

instead of the
PrintReq_Sub.PrintReqID, since the Print_Requisition is on
Click to expand...

the LEFT side
of the JOIN - which indicates you want all records from
Print_Requisition. Therefore, you will require a field
Click to expand...

from the "want
all records" table in the SELECT clause to get the
Click to expand...

required result.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQSZx6YechKqOuFEgEQIUsACgyQzgPewBkIbnZ2hoNcSWOBgeR1IAoPeB
e2ORLVSt8LdQjX8DU91IEpvZ
=Cs6z
-----END PGP SIGNATURE-----

I have a main form called PrintRequisition with the
following query as its record source:
SELECT Print_Requisition.PrintReqID,
Print_Requisition.ProjectID, Projects.ProjectName,
Projects.EmployeeID, [LastName] & ", " & [FirstName] AS
Click to expand...
Expr1
FROM Employees RIGHT JOIN (Print_Requisition INNER JOIN
Projects ON Print_Requisition.ProjectID =
Projects.ProjectID) ON Employees.EmployeeID =
Projects.EmployeeID
ORDER BY [LastName] & ", " & [FirstName];

I then have a sub form called PrintReq_Subform with this
query as its record source:
SELECT [PrintReq_Sub Query1].PrintSubID,
PrintReq_Sub.PrintReqID, PrintReq_Sub.Date,
PrintReq_Sub.PrintsFor, PrintReq_Sub.RequestedBy,
PrintReq_Sub.Description, PrintReq_Sub.Type,
PrintReq_Sub.PaperSize, PrintReq_Sub.FirstQuantity,
PrintReq_Sub.Copies, PrintReq_Sub.PrintFee,
PrintReq_Sub.Postage, PrintReq_Sub.PostageFee
FROM PrintReq_Sub INNER JOIN [PrintReq_Sub Query1] ON
PrintReq_Sub.PrintSubID = [PrintReq_Sub Query1].PrintSubID
WITH OWNERACCESS OPTION;

This query refers to a sub query:
SELECT PrintReq_Sub.PrintSubID, PrintReq_Sub.PrintReqID
FROM Print_Requisition LEFT JOIN PrintReq_Sub ON
Print_Requisition.PrintReqID = PrintReq_Sub.PrintReqID
WHERE
Click to expand...
(((PrintReq_Sub.PrintReqID)=[Print_Requisition].[PrintReqID]));
This was the only solution I could find so that the correct
data is displayed in the sub form according to the current
record in the main for.
The problem is that this query/subform only displays the
current data. It will not let me change any existing data
or add new records.

Can someone please tell me what i've done wrong and how to
fix this problem??

Thanks
Click to expand...

.
Click to expand...
Click to expand...
 
Top