Trouble with sub forms

[Guest]

I seem to be having trouble with a subform, I cant get it to entry values in
the fields.
I have a main form that is used to look up customer names and ID numbers, it
using a combo box to do the look up. The subform displays the present
equipment that this customer has. And it also has a field for Income. I cant
seem to get the Income field to entry data. I’m looking at the northwind
sample database and it has a Orders form with a subform and you can add or
change it’s values. The only thing I see that is different is the subform in
the northwind database uses two tables and I’m using a query and a table in
my subform. The query is what I use to find the most current equipment the
customer has and the table is an Income table to record the income from each
piece of equipment.
Any help??

 

[Duane Hookom]

Does "query is what I use to find the most current equipment" allow editing?
Does the record source of the subform allow editing? Could you share your
SQL views with us?

 

[Guest]

The query to find the current equipment:

SELECT Q.EquipmentID, tblEquipment.Description, First(Q.LocationID) AS
FirstOfLocationID, Q.Date_Installed
FROM tblEquipment INNER JOIN (tblEquipmentRotation AS Q INNER JOIN
tblEquipmentRotation AS T ON Q.EquipmentID = T.EquipmentID) ON
tblEquipment.EquipmentID = Q.EquipmentID
GROUP BY Q.EquipmentID, tblEquipment.Description, Q.Date_Installed
HAVING (((Q.Date_Installed)=Max([T].[date_installed])));

And it does not allow editing. The SQL for the Subform looks a little like
this:

SELECT tblIncomeEquipment.[Location ID],
qryEquipmentRotationMain.EquipmentID, qryEquipmentRotationMain.Description,
tblIncomeEquipment.IncomeGross
FROM tblIncomeEquipment RIGHT JOIN qryEquipmentRotationMain ON
tblIncomeEquipment.[Equipment ID] = qryEquipmentRotationMain.EquipmentID;

The find current equipement query works find and is used thought out the
database for reports and forms. The tblIncomeEquipment is a new table I’m
trying to use to keep track of income for each machine. As of now all I do to
keep track of income is just add up all the income from each customer and
entry it in an income table. I would like to keep track of all the income
based on equipment not just customer.
Any help?

 

[Duane Hookom]

In Access you can't edit a query that contains a GROUP BY clause. Also,
First() is generally un-reliable.

You might need to remove the query from your subform record source of use a
function to return the first of location and date installed.

--
Duane Hookom
MS Access MVP

The query to find the current equipment:

SELECT Q.EquipmentID, tblEquipment.Description, First(Q.LocationID) AS
FirstOfLocationID, Q.Date_Installed
FROM tblEquipment INNER JOIN (tblEquipmentRotation AS Q INNER JOIN
tblEquipmentRotation AS T ON Q.EquipmentID = T.EquipmentID) ON
tblEquipment.EquipmentID = Q.EquipmentID
GROUP BY Q.EquipmentID, tblEquipment.Description, Q.Date_Installed
HAVING (((Q.Date_Installed)=Max([T].[date_installed])));

And it does not allow editing. The SQL for the Subform looks a little like
this:

SELECT tblIncomeEquipment.[Location ID],
qryEquipmentRotationMain.EquipmentID,
qryEquipmentRotationMain.Description,
tblIncomeEquipment.IncomeGross
FROM tblIncomeEquipment RIGHT JOIN qryEquipmentRotationMain ON
tblIncomeEquipment.[Equipment ID] = qryEquipmentRotationMain.EquipmentID;

The find current equipement query works find and is used thought out the
database for reports and forms. The tblIncomeEquipment is a new table I'm
trying to use to keep track of income for each machine. As of now all I do
to
keep track of income is just add up all the income from each customer and
entry it in an income table. I would like to keep track of all the income
based on equipment not just customer.
Any help?

--
thank You

Does "query is what I use to find the most current equipment" allow
editing?
Does the record source of the subform allow editing? Could you share your
SQL views with us?

 

[Guest]

Well that sucks. could I do a recordset clone of some kind to load the
subform. Or I think I can run a update query to load the Income table for
each customer.
any Idea on this?
--
thank You

In Access you can't edit a query that contains a GROUP BY clause. Also,
First() is generally un-reliable.

You might need to remove the query from your subform record source of use a
function to return the first of location and date installed.

--
Duane Hookom
MS Access MVP

The query to find the current equipment:

SELECT Q.EquipmentID, tblEquipment.Description, First(Q.LocationID) AS
FirstOfLocationID, Q.Date_Installed
FROM tblEquipment INNER JOIN (tblEquipmentRotation AS Q INNER JOIN
tblEquipmentRotation AS T ON Q.EquipmentID = T.EquipmentID) ON
tblEquipment.EquipmentID = Q.EquipmentID
GROUP BY Q.EquipmentID, tblEquipment.Description, Q.Date_Installed
HAVING (((Q.Date_Installed)=Max([T].[date_installed])));

And it does not allow editing. The SQL for the Subform looks a little like
this:

SELECT tblIncomeEquipment.[Location ID],
qryEquipmentRotationMain.EquipmentID,
qryEquipmentRotationMain.Description,
tblIncomeEquipment.IncomeGross
FROM tblIncomeEquipment RIGHT JOIN qryEquipmentRotationMain ON
tblIncomeEquipment.[Equipment ID] = qryEquipmentRotationMain.EquipmentID;

The find current equipement query works find and is used thought out the
database for reports and forms. The tblIncomeEquipment is a new table I'm
trying to use to keep track of income for each machine. As of now all I do
to
keep track of income is just add up all the income from each customer and
entry it in an income table. I would like to keep track of all the income
based on equipment not just customer.
Any help?

--
thank You

Does "query is what I use to find the most current equipment" allow
editing?
Does the record source of the subform allow editing? Could you share your
SQL views with us?

--
Duane Hookom
MS Access MVP

I seem to be having trouble with a subform, I cant get it to entry
values
in
the fields.
I have a main form that is used to look up customer names and ID
numbers,
it
using a combo box to do the look up. The subform displays the present
equipment that this customer has. And it also has a field for Income. I
cant
seem to get the Income field to entry data. I'm looking at the
northwind
sample database and it has a Orders form with a subform and you can add
or
change it's values. The only thing I see that is different is the
subform
in
the northwind database uses two tables and I'm using a query and a
table
in
my subform. The query is what I use to find the most current equipment
the
customer has and the table is an Income table to record the income from
each
piece of equipment.
Any help??

 

[Duane Hookom]

A recordset clone wouldn't be editable. You need to review your
specifications to see how you can get around your issue.

--
Duane Hookom
MS Access MVP

Well that sucks. could I do a recordset clone of some kind to load the
subform. Or I think I can run a update query to load the Income table for
each customer.
any Idea on this?
--
thank You

In Access you can't edit a query that contains a GROUP BY clause. Also,
First() is generally un-reliable.

You might need to remove the query from your subform record source of use
a
function to return the first of location and date installed.

--
Duane Hookom
MS Access MVP

The query to find the current equipment:

SELECT Q.EquipmentID, tblEquipment.Description, First(Q.LocationID) AS
FirstOfLocationID, Q.Date_Installed
FROM tblEquipment INNER JOIN (tblEquipmentRotation AS Q INNER JOIN
tblEquipmentRotation AS T ON Q.EquipmentID = T.EquipmentID) ON
tblEquipment.EquipmentID = Q.EquipmentID
GROUP BY Q.EquipmentID, tblEquipment.Description, Q.Date_Installed
HAVING (((Q.Date_Installed)=Max([T].[date_installed])));

And it does not allow editing. The SQL for the Subform looks a little
like
this:

SELECT tblIncomeEquipment.[Location ID],
qryEquipmentRotationMain.EquipmentID,
qryEquipmentRotationMain.Description,
tblIncomeEquipment.IncomeGross
FROM tblIncomeEquipment RIGHT JOIN qryEquipmentRotationMain ON
tblIncomeEquipment.[Equipment ID] =
qryEquipmentRotationMain.EquipmentID;

The find current equipement query works find and is used thought out
the
database for reports and forms. The tblIncomeEquipment is a new table
I'm
trying to use to keep track of income for each machine. As of now all I
do
to
keep track of income is just add up all the income from each customer
and
entry it in an income table. I would like to keep track of all the
income
based on equipment not just customer.
Any help?

--
thank You


:

Does "query is what I use to find the most current equipment" allow
editing?
Does the record source of the subform allow editing? Could you share
your
SQL views with us?

--
Duane Hookom
MS Access MVP

I seem to be having trouble with a subform, I cant get it to entry
values
in
the fields.
I have a main form that is used to look up customer names and ID
numbers,
it
using a combo box to do the look up. The subform displays the
present
equipment that this customer has. And it also has a field for
Income. I
cant
seem to get the Income field to entry data. I'm looking at the
northwind
sample database and it has a Orders form with a subform and you can
add
or
change it's values. The only thing I see that is different is the
subform
in
the northwind database uses two tables and I'm using a query and a
table
in
my subform. The query is what I use to find the most current
equipment
the
customer has and the table is an Income table to record the income
from
each
piece of equipment.
Any help??