using results from a query as the datasource for a form

[Karen]

I have a form that is based on the results of a query. I want to be able to
edit the data in the form but I can't. There aren't any settings, that I
can see, that say edit isn't allowed.

This is what I have:
A table called QC, a query to look for records in QC that don't have
anything in a particular field, a form called needqc that displays the
results from that
query (I can edit the data in here, but I'll disable that later). This form
also has a button (Go to Selected Record) that runs another query.

This is the command in the Event for the button (Go to Selected Record):
DoCmd.OpenForm "qc", acNormal, , , acFormPropertySettings, acWindowNormal

The second query's results are the rest of the fields in the table (QC) but
it only shows the one record that was the 'selected' record on the needqc
form when I clicked the button. The parameters are [form]![needqc]!

and [form]![needqc]![lotno].  This record is then displayed on a form called
qc.  When I try to enter the results of my QC test in the fields on the QC
form, I can't.



I use Access 2002 on a Win 2000 computer. I'm self taught in Access.

Karen

 

[Pavel Romashkin]

It sounds to me that your query returns a read only recordset.
Open the query that the form is based on in the QBE datasheet and try
editing or adding a record. If this fails your query must hava a join
that prevents it from being updateable. If it works, lets look into the
problem further.

Pavel

 

[Karen]

There is a join in the second query. Is that what keeps me from being able
to edit? Is there a way around that?

Karen

It sounds to me that your query returns a read only recordset.
Open the query that the form is based on in the QBE datasheet and try
editing or adding a record. If this fails your query must hava a join
that prevents it from being updateable. If it works, lets look into the
problem further.

Pavel

I have a form that is based on the results of a query. I want to be able to
edit the data in the form but I can't. There aren't any settings, that I
can see, that say edit isn't allowed.

This is what I have:
A table called QC, a query to look for records in QC that don't have
anything in a particular field, a form called needqc that displays the
results from that
query (I can edit the data in here, but I'll disable that later). This form
also has a button (Go to Selected Record) that runs another query.

This is the command in the Event for the button (Go to Selected Record):
DoCmd.OpenForm "qc", acNormal, , , acFormPropertySettings, acWindowNormal

The second query's results are the rest of the fields in the table (QC) but
it only shows the one record that was the 'selected' record on the needqc
form when I clicked the button. The parameters are [form]![needqc]!

and [form]![needqc]![lotno].  This record is then displayed on a form called
qc.  When I try to enter the results of my QC test in the fields on the QC
form, I can't.

I use Access 2002 on a Win 2000 computer. I'm self taught in Access.

Karen[/QUOTE][/QUOTE]

 

[Pavel Romashkin]

In Access, most of the time you can have an updateable query with joins.
This is why I suggested that you analyze your query design in query
builder and make sure the data can be edited in the datasheet view.
If it is not editable you will need to re-design the query.
It is very hard to be more specific without seeing your table structure
and query SQL. If you post these you may get a more accurate answer.
Pavel

There is a join in the second query. Is that what keeps me from being able
to edit? Is there a way around that?

Karen

It sounds to me that your query returns a read only recordset.
Open the query that the form is based on in the QBE datasheet and try
editing or adding a record. If this fails your query must hava a join
that prevents it from being updateable. If it works, lets look into the
problem further.

Pavel

I have a form that is based on the results of a query. I want to be able to
edit the data in the form but I can't. There aren't any settings, that I
can see, that say edit isn't allowed.

This is what I have:
A table called QC, a query to look for records in QC that don't have
anything in a particular field, a form called needqc that displays the
results from that
query (I can edit the data in here, but I'll disable that later). This form
also has a button (Go to Selected Record) that runs another query.

This is the command in the Event for the button (Go to Selected Record):
DoCmd.OpenForm "qc", acNormal, , , acFormPropertySettings, acWindowNormal

The second query's results are the rest of the fields in the table (QC) but
it only shows the one record that was the 'selected' record on the needqc
form when I clicked the button. The parameters are [form]![needqc]!

and [form]![needqc]![lotno].  This record is then displayed on a form called
qc.  When I try to enter the results of my QC test in the fields on the QC
form, I can't.

I use Access 2002 on a Win 2000 computer. I'm self taught in Access.

Karen[/QUOTE][/QUOTE][/QUOTE]

 

[Karen]

This is long winded but I hope not too much so.

I have two tables in the Query; QualityRecord and Prep. The tables have one
field in common and that's CODE, a 15 character text field. [There are
several fields with the same name in each table. (I wanted to copy the data
from the PREP field to the QUALITYRECORD field of the same name when the QC
form is displayed thus keeping a record of what test was performed in case
the tests change at some point in the future.) ]

QUALITYRECORD is the table in which the results of the QC tests will be
stored. The Code, Lotno, madedate, qtymade, unitsmade, madeby, and
prepnotes fields are filled in thru another form by the prep crew. Later
the QC people will use a query to see all records for a code that don't have
anything in the qcdate field (all un-QC'd records). The results of this
query are shown on a form called NEEDQC. They will be able to click on a
record in that list and then click a button (Go To Selected Record) that
runs this query and opens the QC form. Then the QC people will fill in the
testresult fields. There will be validation rules set that look at the
highvalue and lowvalue for each test and determine if the results are within
spec. If the results are not in spec an alert will popup. Much of this is
not done at this time. I need to be able to enter the results first.

PREP is the table where the instructions on how to prepare the product are
stored and where the tests and specs are stored. No data will be written to
this table during this process. This table will be updated thru another
form that I haven't even started on yet.

The QC form needs to show the code, lotno, madedate, madeby, qtymade,
unitsmade, prepnotes fields at/near the top and then list the tests (there
are up to 5) and the highvalue and lowvalue for each. Then the results
field needs to be there to be filled in. Then at the bottom, the qcdate,
qcby, qcnotes fields, sort of like this. The only fields I want to be
editable are test1results to test5results and qcdate, qcby, qcnotes.

code lotno madedate madeby
qtymade unitsmade
prepnotes

prep.test1 test1lowvalue test1highvalue test1results
prep.test2 test2lowvalue test2highvalue test2results
..
..
prep.test5 test5lowvalue test5highvalue test5results

qcdate qcby qcnotes


I simply created my query in the design mode and took CODE from the
QualityRecord table and dragged it to the CODE in the PREP table at the top
of the query design screen to create the join.


The SQL for the query is
SELECT QualityRecord.code, QualityRecord.lotno, QualityRecord.madedate,
QualityRecord.qtymade,
QualityRecord.unitsmade, QualityRecord.madeby, QualityRecord.prepnotes,
QualityRecord.qcdate,
QualityRecord.qcby, QualityRecord.test1, QualityRecord.test1results,
QualityRecord.test2,
QualityRecord.test2results, QualityRecord.test3, QualityRecord.test3results,
QualityRecord.test4,
QualityRecord.test4results, QualityRecord.test5, QualityRecord.test5results,
QualityRecord.qcnotes,
Prep.qccheck, Prep.test1, Prep.test1highvalue, Prep.test1lowvalue,
Prep.test2, Prep.test2highvalue,
Prep.test2lowvalue, Prep.test3, Prep.test3highvalue, Prep.test3lowvalue,
Prep.test4, Prep.test4highvalue,
Prep.test4lowvalue, Prep.test5, Prep.test5highvalue, Prep.test5lowvalue
FROM QualityRecord INNER JOIN Prep ON QualityRecord.code=Prep.code
WHERE (((QualityRecord.code)=forms!needqc!code) And
((QualityRecord.lotno)=forms!needqc!lotno));



Thanks for all of your help!
Karen

In Access, most of the time you can have an updateable query with joins.
This is why I suggested that you analyze your query design in query
builder and make sure the data can be edited in the datasheet view.
If it is not editable you will need to re-design the query.
It is very hard to be more specific without seeing your table structure
and query SQL. If you post these you may get a more accurate answer.
Pavel

There is a join in the second query. Is that what keeps me from being able
to edit? Is there a way around that?

Karen

It sounds to me that your query returns a read only recordset.
Open the query that the form is based on in the QBE datasheet and try
editing or adding a record. If this fails your query must hava a join
that prevents it from being updateable. If it works, lets look into the
problem further.

Pavel

Karen wrote:

I have a form that is based on the results of a query. I want to be able to
edit the data in the form but I can't. There aren't any settings,

that
I

can see, that say edit isn't allowed.

This is what I have:
A table called QC, a query to look for records in QC that don't have
anything in a particular field, a form called needqc that displays the
results from that
query (I can edit the data in here, but I'll disable that later).

This
form

also has a button (Go to Selected Record) that runs another query.

This is the command in the Event for the button (Go to Selected Record):
DoCmd.OpenForm "qc", acNormal, , , acFormPropertySettings, acWindowNormal

The second query's results are the rest of the fields in the table

(QC)
but

it only shows the one record that was the 'selected' record on the needqc
form when I clicked the button. The parameters are [form]![needqc]!

and [form]![needqc]![lotno].  This record is then displayed on a[/QUOTE] form
called[QUOTE]
qc.  When I try to enter the results of my QC test in the fields on[/QUOTE] the
QC[QUOTE]
form, I can't.

I use Access 2002 on a Win 2000 computer. I'm self taught in Access.

Karen[/QUOTE][/QUOTE][/QUOTE]

 

[Pavel Romashkin]

I did get a little confused. You write:

PREP is the table where the instructions on how to prepare the product are
stored and where the tests and specs are stored. No data will be written to
this table during this process.

Then, you say

The only fields I want to be
editable are test1results to test5results and qcdate, qcby, qcnotes

which indicates to me that you do indeed try to write to PREP.
I still don't see anything obvious in your query that would make it read
only. Have you tried editing records in the query in data sheet view?

On the form, you have sets of redundant fields from both tables. Are you
planning to fill in them both? I mean, in your query:

 > QualityRecord.test1, QualityRecord.test1results,

QualityRecord.test2,
QualityRecord.test2results, QualityRecord.test3, QualityRecord.test3results,
QualityRecord.test4,
QualityRecord.test4results, QualityRecord.test5, QualityRecord.test5results

Summing up, I didn't notice anything in the query that should've stopped
it from being writeable. Could you please test it in datasheet view?
Do the rest of the fields, like "code, lotno, madedate, madeby" populate
correctly in the QC form?

Pavel

This is long winded but I hope not too much so.

I have two tables in the Query; QualityRecord and Prep. The tables have one
field in common and that's CODE, a 15 character text field. [There are
several fields with the same name in each table. (I wanted to copy the data
from the PREP field to the QUALITYRECORD field of the same name when the QC
form is displayed thus keeping a record of what test was performed in case
the tests change at some point in the future.) ]

QUALITYRECORD is the table in which the results of the QC tests will be
stored. The Code, Lotno, madedate, qtymade, unitsmade, madeby, and
prepnotes fields are filled in thru another form by the prep crew. Later
the QC people will use a query to see all records for a code that don't have
anything in the qcdate field (all un-QC'd records). The results of this
query are shown on a form called NEEDQC. They will be able to click on a
record in that list and then click a button (Go To Selected Record) that
runs this query and opens the QC form. Then the QC people will fill in the
testresult fields. There will be validation rules set that look at the
highvalue and lowvalue for each test and determine if the results are within
spec. If the results are not in spec an alert will popup. Much of this is
not done at this time. I need to be able to enter the results first.

PREP is the table where the instructions on how to prepare the product are
stored and where the tests and specs are stored. No data will be written to
this table during this process. This table will be updated thru another
form that I haven't even started on yet.

The QC form needs to show the code, lotno, madedate, madeby, qtymade,
unitsmade, prepnotes fields at/near the top and then list the tests (there
are up to 5) and the highvalue and lowvalue for each. Then the results
field needs to be there to be filled in. Then at the bottom, the qcdate,
qcby, qcnotes fields, sort of like this. The only fields I want to be
editable are test1results to test5results and qcdate, qcby, qcnotes.

code lotno madedate madeby
qtymade unitsmade
prepnotes

prep.test1 test1lowvalue test1highvalue test1results
prep.test2 test2lowvalue test2highvalue test2results
.
.
prep.test5 test5lowvalue test5highvalue test5results

qcdate qcby qcnotes

I simply created my query in the design mode and took CODE from the
QualityRecord table and dragged it to the CODE in the PREP table at the top
of the query design screen to create the join.

The SQL for the query is
SELECT QualityRecord.code, QualityRecord.lotno, QualityRecord.madedate,
QualityRecord.qtymade,
QualityRecord.unitsmade, QualityRecord.madeby, QualityRecord.prepnotes,
QualityRecord.qcdate,
QualityRecord.qcby, QualityRecord.test1, QualityRecord.test1results,
QualityRecord.test2,
QualityRecord.test2results, QualityRecord.test3, QualityRecord.test3results,
QualityRecord.test4,
QualityRecord.test4results, QualityRecord.test5, QualityRecord.test5results,
QualityRecord.qcnotes,
Prep.qccheck, Prep.test1, Prep.test1highvalue, Prep.test1lowvalue,
Prep.test2, Prep.test2highvalue,
Prep.test2lowvalue, Prep.test3, Prep.test3highvalue, Prep.test3lowvalue,
Prep.test4, Prep.test4highvalue,
Prep.test4lowvalue, Prep.test5, Prep.test5highvalue, Prep.test5lowvalue
FROM QualityRecord INNER JOIN Prep ON QualityRecord.code=Prep.code
WHERE (((QualityRecord.code)=forms!needqc!code) And
((QualityRecord.lotno)=forms!needqc!lotno));

Thanks for all of your help!
Karen

In Access, most of the time you can have an updateable query with joins.
This is why I suggested that you analyze your query design in query
builder and make sure the data can be edited in the datasheet view.
If it is not editable you will need to re-design the query.
It is very hard to be more specific without seeing your table structure
and query SQL. If you post these you may get a more accurate answer.
Pavel

There is a join in the second query. Is that what keeps me from being able
to edit? Is there a way around that?

Karen

It sounds to me that your query returns a read only recordset.
Open the query that the form is based on in the QBE datasheet and try
editing or adding a record. If this fails your query must hava a join
that prevents it from being updateable. If it works, lets look into the
problem further.

Pavel

Karen wrote:

I have a form that is based on the results of a query. I want to be
able to
edit the data in the form but I can't. There aren't any settings, that
I
can see, that say edit isn't allowed.

This is what I have:
A table called QC, a query to look for records in QC that don't have
anything in a particular field, a form called needqc that displays the
results from that
query (I can edit the data in here, but I'll disable that later). This
form
also has a button (Go to Selected Record) that runs another query.

This is the command in the Event for the button (Go to Selected Record):
DoCmd.OpenForm "qc", acNormal, , , acFormPropertySettings,
acWindowNormal

The second query's results are the rest of the fields in the table (QC)
but
it only shows the one record that was the 'selected' record on the
needqc
form when I clicked the button. The parameters are
[form]![needqc]!

and [form]![needqc]![lotno].  This record is then displayed on a form
called
qc.  When I try to enter the results of my QC test in the fields on the
QC
form, I can't.

I use Access 2002 on a Win 2000 computer. I'm self taught in Access.

Karen[/QUOTE][/QUOTE][/QUOTE]

 

[Karen]

Pavel,

I did try editing in the datasheet view, directly from running the query in
the query view. But i can't edit in that view either.


test1results to test5results are in QualityRecord along with qcdate, qcby
and qcnotes. The Prep table has (test1 to test5) and (test1highvalue to
test5highvalue) and (test1lowvalue to test5lowvalue). The test?results
fields are part of the QC process not the prep process.


I didn't want to change the test1 to test5 in Prep. I only have them in the
query so that i can copy the data from them to the fields of the same name
in QualityRecord.


All of the fields that have data in them populate correctly.

Is it worth deleting the query and starting over? Could the duplicate field
names be a problem? Maybe I'll try a test query leaving out the test1 to
test5 fields. I have another query with a join the is also not editable so
I don't know what I'm doing to make them that way.

Karen

I did get a little confused. You write:

PREP is the table where the instructions on how to prepare the product are
stored and where the tests and specs are stored. No data will be written to
this table during this process.

Then, you say

The only fields I want to be
editable are test1results to test5results and qcdate, qcby, qcnotes

which indicates to me that you do indeed try to write to PREP.
I still don't see anything obvious in your query that would make it read
only. Have you tried editing records in the query in data sheet view?

QualityRecord.test1, QualityRecord.test1results,
QualityRecord.test2,
QualityRecord.test2results, QualityRecord.test3, QualityRecord.test3results,
QualityRecord.test4,
QualityRecord.test4results, QualityRecord.test5,

QualityRecord.test5results

Summing up, I didn't notice anything in the query that should've stopped
it from being writeable. Could you please test it in datasheet view?
Do the rest of the fields, like "code, lotno, madedate, madeby" populate
correctly in the QC form?

Pavel

This is long winded but I hope not too much so.

I have two tables in the Query; QualityRecord and Prep. The tables have one
field in common and that's CODE, a 15 character text field. [There are
several fields with the same name in each table. (I wanted to copy the data
from the PREP field to the QUALITYRECORD field of the same name when the QC
form is displayed thus keeping a record of what test was performed in case
the tests change at some point in the future.) ]

QUALITYRECORD is the table in which the results of the QC tests will be
stored. The Code, Lotno, madedate, qtymade, unitsmade, madeby, and
prepnotes fields are filled in thru another form by the prep crew. Later
the QC people will use a query to see all records for a code that don't have
anything in the qcdate field (all un-QC'd records). The results of this
query are shown on a form called NEEDQC. They will be able to click on a
record in that list and then click a button (Go To Selected Record) that
runs this query and opens the QC form. Then the QC people will fill in the
testresult fields. There will be validation rules set that look at the
highvalue and lowvalue for each test and determine if the results are within
spec. If the results are not in spec an alert will popup. Much of this is
not done at this time. I need to be able to enter the results first.

PREP is the table where the instructions on how to prepare the product are
stored and where the tests and specs are stored. No data will be written to
this table during this process. This table will be updated thru another
form that I haven't even started on yet.

The QC form needs to show the code, lotno, madedate, madeby, qtymade,
unitsmade, prepnotes fields at/near the top and then list the tests (there
are up to 5) and the highvalue and lowvalue for each. Then the results
field needs to be there to be filled in. Then at the bottom, the qcdate,
qcby, qcnotes fields, sort of like this. The only fields I want to be
editable are test1results to test5results and qcdate, qcby, qcnotes.

code lotno madedate madeby
qtymade unitsmade
prepnotes

prep.test1 test1lowvalue test1highvalue test1results
prep.test2 test2lowvalue test2highvalue test2results
.
.
prep.test5 test5lowvalue test5highvalue test5results

qcdate qcby qcnotes

I simply created my query in the design mode and took CODE from the
QualityRecord table and dragged it to the CODE in the PREP table at the top
of the query design screen to create the join.

The SQL for the query is
SELECT QualityRecord.code, QualityRecord.lotno, QualityRecord.madedate,
QualityRecord.qtymade,
QualityRecord.unitsmade, QualityRecord.madeby, QualityRecord.prepnotes,
QualityRecord.qcdate,
QualityRecord.qcby, QualityRecord.test1, QualityRecord.test1results,
QualityRecord.test2,
QualityRecord.test2results, QualityRecord.test3, QualityRecord.test3results,
QualityRecord.test4,
QualityRecord.test4results, QualityRecord.test5, QualityRecord.test5results,
QualityRecord.qcnotes,
Prep.qccheck, Prep.test1, Prep.test1highvalue, Prep.test1lowvalue,
Prep.test2, Prep.test2highvalue,
Prep.test2lowvalue, Prep.test3, Prep.test3highvalue, Prep.test3lowvalue,
Prep.test4, Prep.test4highvalue,
Prep.test4lowvalue, Prep.test5, Prep.test5highvalue, Prep.test5lowvalue
FROM QualityRecord INNER JOIN Prep ON QualityRecord.code=Prep.code
WHERE (((QualityRecord.code)=forms!needqc!code) And
((QualityRecord.lotno)=forms!needqc!lotno));

Thanks for all of your help!
Karen

In Access, most of the time you can have an updateable query with joins.
This is why I suggested that you analyze your query design in query
builder and make sure the data can be edited in the datasheet view.
If it is not editable you will need to re-design the query.
It is very hard to be more specific without seeing your table structure
and query SQL. If you post these you may get a more accurate answer.
Pavel

Karen wrote:

There is a join in the second query. Is that what keeps me from

being
able

to edit? Is there a way around that?

Karen

It sounds to me that your query returns a read only recordset.
Open the query that the form is based on in the QBE datasheet and try
editing or adding a record. If this fails your query must hava a join
that prevents it from being updateable. If it works, lets look

into
the

problem further.

Pavel

Karen wrote:

I have a form that is based on the results of a query. I want to be
able to
edit the data in the form but I can't. There aren't any

settings,
that

I
can see, that say edit isn't allowed.

This is what I have:
A table called QC, a query to look for records in QC that don't have
anything in a particular field, a form called needqc that

displays
the

results from that
query (I can edit the data in here, but I'll disable that

later).
This

form
also has a button (Go to Selected Record) that runs another query.

This is the command in the Event for the button (Go to Selected Record):
DoCmd.OpenForm "qc", acNormal, , , acFormPropertySettings,
acWindowNormal

The second query's results are the rest of the fields in the

table
(QC)

but
it only shows the one record that was the 'selected' record on the
needqc
form when I clicked the button. The parameters are
[form]![needqc]!

and [form]![needqc]![lotno].  This record is then displayed on a form
called
qc.  When I try to enter the results of my QC test in the fields[/QUOTE] on
the[QUOTE]
QC
form, I can't.

I use Access 2002 on a Win 2000 computer. I'm self taught in Access.

Karen[/QUOTE][/QUOTE][/QUOTE]

 

[Karen]

I just got this to work!

I think my problem may have had to do with a lack of primary keys in the
tables. Does that make any sense?

I was fixing up some other issues, such as the lack of primary keys and then
went back to the query to look at taking out those duplicate fields. I
decided to test the query one more time and it worked with no changes.

Hmmm..

Anyway, Thank you Pavel for being so patient with me. I'm going to get
this one of these days.

Karen

Pavel,

I did try editing in the datasheet view, directly from running the query in
the query view. But i can't edit in that view either.


test1results to test5results are in QualityRecord along with qcdate, qcby
and qcnotes. The Prep table has (test1 to test5) and (test1highvalue to
test5highvalue) and (test1lowvalue to test5lowvalue). The test?results
fields are part of the QC process not the prep process.


I didn't want to change the test1 to test5 in Prep. I only have them in the
query so that i can copy the data from them to the fields of the same name
in QualityRecord.


All of the fields that have data in them populate correctly.

Is it worth deleting the query and starting over? Could the duplicate field
names be a problem? Maybe I'll try a test query leaving out the test1 to
test5 fields. I have another query with a join the is also not editable so
I don't know what I'm doing to make them that way.

Karen

I did get a little confused. You write:


Then, you say


which indicates to me that you do indeed try to write to PREP.
I still don't see anything obvious in your query that would make it read
only. Have you tried editing records in the query in data sheet view?

QualityRecord.test5results

Summing up, I didn't notice anything in the query that should've stopped
it from being writeable. Could you please test it in datasheet view?
Do the rest of the fields, like "code, lotno, madedate, madeby" populate
correctly in the QC form?

Pavel

have

one

field in common and that's CODE, a 15 character text field. [There are
several fields with the same name in each table. (I wanted to copy

the

data

the

QC

form is displayed thus keeping a record of what test was performed in case
the tests change at some point in the future.) ]

QUALITYRECORD is the table in which the results of the QC tests will be
stored. The Code, Lotno, madedate, qtymade, unitsmade, madeby, and
prepnotes fields are filled in thru another form by the prep crew. Later
the QC people will use a query to see all records for a code that

don't

have

on

a

in

the

this

is

the

top

and

try

don't

have

anything in a particular field, a form called needqc that displays
the
results from that
query (I can edit the data in here, but I'll disable that later).
This
form
also has a button (Go to Selected Record) that runs another query.

This is the command in the Event for the button (Go to Selected
Record):
DoCmd.OpenForm "qc", acNormal, , , acFormPropertySettings,
acWindowNormal

The second query's results are the rest of the fields in the table
(QC)
but
it only shows the one record that was the 'selected' record on the
needqc
form when I clicked the button. The parameters are
[form]![needqc]!

and [form]![needqc]![lotno].  This record is then displayed on a
form
called
qc.  When I try to enter the results of my QC test in the[/QUOTE][/QUOTE] fields[QUOTE]
on[/QUOTE]
[/QUOTE]

 

[Pavel Romashkin]

I am glad you solved it.
I would recommend always setting up tables with proper relationships
using primary keys first, then designing the db around that. Although
not mandatory, PKs are key to proper db functionality and relationships
are a good way to help yourself to keep track of how the db is working
and make queries automatically set up joins when you design them.

Good luck,
Pavel

I just got this to work!

I think my problem may have had to do with a lack of primary keys in the
tables. Does that make any sense?

I was fixing up some other issues, such as the lack of primary keys and then
went back to the query to look at taking out those duplicate fields. I
decided to test the query one more time and it worked with no changes.

Hmmm..

Anyway, Thank you Pavel for being so patient with me. I'm going to get
this one of these days.

Karen

Pavel,

I did try editing in the datasheet view, directly from running the query in
the query view. But i can't edit in that view either.


test1results to test5results are in QualityRecord along with qcdate, qcby
and qcnotes. The Prep table has (test1 to test5) and (test1highvalue to
test5highvalue) and (test1lowvalue to test5lowvalue). The test?results
fields are part of the QC process not the prep process.


I didn't want to change the test1 to test5 in Prep. I only have them in the
query so that i can copy the data from them to the fields of the same name
in QualityRecord.


All of the fields that have data in them populate correctly.

Is it worth deleting the query and starting over? Could the duplicate field
names be a problem? Maybe I'll try a test query leaving out the test1 to
test5 fields. I have another query with a join the is also not editable so
I don't know what I'm doing to make them that way.

Karen

I did get a little confused. You write:

PREP is the table where the instructions on how to prepare the product are
stored and where the tests and specs are stored. No data will be written to
this table during this process.

Then, you say

The only fields I want to be
editable are test1results to test5results and qcdate, qcby, qcnotes

which indicates to me that you do indeed try to write to PREP.
I still don't see anything obvious in your query that would make it read
only. Have you tried editing records in the query in data sheet view?

On the form, you have sets of redundant fields from both tables. Are you
planning to fill in them both? I mean, in your query:

QualityRecord.test1, QualityRecord.test1results,
QualityRecord.test2,
QualityRecord.test2results, QualityRecord.test3, QualityRecord.test3results,
QualityRecord.test4,
QualityRecord.test4results, QualityRecord.test5, QualityRecord.test5results

Summing up, I didn't notice anything in the query that should've stopped
it from being writeable. Could you please test it in datasheet view?
Do the rest of the fields, like "code, lotno, madedate, madeby" populate
correctly in the QC form?

Pavel


Karen wrote:

This is long winded but I hope not too much so.

I have two tables in the Query; QualityRecord and Prep. The tables

have

one
field in common and that's CODE, a 15 character text field. [There are
several fields with the same name in each table. (I wanted to copy

the

data
from the PREP field to the QUALITYRECORD field of the same name when

the

QC
form is displayed thus keeping a record of what test was performed in case
the tests change at some point in the future.) ]

QUALITYRECORD is the table in which the results of the QC tests will be
stored. The Code, Lotno, madedate, qtymade, unitsmade, madeby, and
prepnotes fields are filled in thru another form by the prep crew. Later
the QC people will use a query to see all records for a code that

don't

have
anything in the qcdate field (all un-QC'd records). The results of this
query are shown on a form called NEEDQC. They will be able to click

on

a
record in that list and then click a button (Go To Selected Record) that
runs this query and opens the QC form. Then the QC people will fill

in

the
testresult fields. There will be validation rules set that look at the
highvalue and lowvalue for each test and determine if the results are within
spec. If the results are not in spec an alert will popup. Much of

this

is
not done at this time. I need to be able to enter the results first.

PREP is the table where the instructions on how to prepare the product are
stored and where the tests and specs are stored. No data will be written to
this table during this process. This table will be updated thru another
form that I haven't even started on yet.

The QC form needs to show the code, lotno, madedate, madeby, qtymade,
unitsmade, prepnotes fields at/near the top and then list the tests (there
are up to 5) and the highvalue and lowvalue for each. Then the results
field needs to be there to be filled in. Then at the bottom, the qcdate,
qcby, qcnotes fields, sort of like this. The only fields I want to be
editable are test1results to test5results and qcdate, qcby, qcnotes.

code lotno madedate madeby
qtymade unitsmade
prepnotes

prep.test1 test1lowvalue test1highvalue test1results
prep.test2 test2lowvalue test2highvalue test2results
.
.
prep.test5 test5lowvalue test5highvalue test5results

qcdate qcby qcnotes

I simply created my query in the design mode and took CODE from the
QualityRecord table and dragged it to the CODE in the PREP table at

the

top
of the query design screen to create the join.

The SQL for the query is
SELECT QualityRecord.code, QualityRecord.lotno, QualityRecord.madedate,
QualityRecord.qtymade,
QualityRecord.unitsmade, QualityRecord.madeby, QualityRecord.prepnotes,
QualityRecord.qcdate,
QualityRecord.qcby, QualityRecord.test1, QualityRecord.test1results,
QualityRecord.test2,
QualityRecord.test2results, QualityRecord.test3, QualityRecord.test3results,
QualityRecord.test4,
QualityRecord.test4results, QualityRecord.test5, QualityRecord.test5results,
QualityRecord.qcnotes,
Prep.qccheck, Prep.test1, Prep.test1highvalue, Prep.test1lowvalue,
Prep.test2, Prep.test2highvalue,
Prep.test2lowvalue, Prep.test3, Prep.test3highvalue, Prep.test3lowvalue,
Prep.test4, Prep.test4highvalue,
Prep.test4lowvalue, Prep.test5, Prep.test5highvalue, Prep.test5lowvalue
FROM QualityRecord INNER JOIN Prep ON QualityRecord.code=Prep.code
WHERE (((QualityRecord.code)=forms!needqc!code) And
((QualityRecord.lotno)=forms!needqc!lotno));

Thanks for all of your help!
Karen

In Access, most of the time you can have an updateable query with joins.
This is why I suggested that you analyze your query design in query
builder and make sure the data can be edited in the datasheet view.
If it is not editable you will need to re-design the query.
It is very hard to be more specific without seeing your table structure
and query SQL. If you post these you may get a more accurate answer.
Pavel

Karen wrote:

There is a join in the second query. Is that what keeps me from being
able
to edit? Is there a way around that?

Karen

It sounds to me that your query returns a read only recordset.
Open the query that the form is based on in the QBE datasheet

and

try
editing or adding a record. If this fails your query must hava a join
that prevents it from being updateable. If it works, lets look into
the
problem further.

Pavel

Karen wrote:

I have a form that is based on the results of a query. I want to be
able to
edit the data in the form but I can't. There aren't any settings,
that
I
can see, that say edit isn't allowed.

This is what I have:
A table called QC, a query to look for records in QC that

don't

have
anything in a particular field, a form called needqc that displays
the
results from that
query (I can edit the data in here, but I'll disable that later).
This
form
also has a button (Go to Selected Record) that runs another query.

This is the command in the Event for the button (Go to Selected
Record):
DoCmd.OpenForm "qc", acNormal, , , acFormPropertySettings,
acWindowNormal

The second query's results are the rest of the fields in the table
(QC)
but
it only shows the one record that was the 'selected' record on the
needqc
form when I clicked the button. The parameters are
[form]![needqc]!

and [form]![needqc]![lotno].  This record is then displayed on a
form
called
qc.  When I try to enter the results of my QC test in the[/QUOTE] fields[QUOTE]
on
the
QC
form, I can't.

I use Access 2002 on a Win 2000 computer. I'm self taught in Access.

Karen[/QUOTE]
[/QUOTE][/QUOTE]