Editable Views (Queries)

[Kevin Myers]

In MS Access 2000 I am developing a form for editing and viewing the
combined data from half a dozen tables. When I include all but one of the
tables in the source query for the form, everything is fine and I can edit
all of the desired fields. However, when I include the last table the query
suddenly appears to become read-only, and I can no longer edit fields from
any of the tables.

The join criteria for this last table are different from the others as
follows:

1) A left outer join must be used when adding this final table because it
does not include rows corresponding to some of the rows in the other tables,
and all of the rows from the other tables are required in the result set.

2) The join criteria for the last table involves comparing a couple of its
columns to substrings of a column in one of the other tables, i.e.:
where county.country_id=well.country.id and
county.state_api=mid(well.api,1,2) and county.county_api=mid(well.api,3,3)

Can anyone confirm whether one or both of the above ideosyncracies will
force a query to become read-only as the source for a form?

Can anyone point me to some documentation that describes the exact
circumstances under which a query will become read-only as the source for a
form?

Finally, assuming that one of the above issues does in fact force the source
query to become read only, can anyone suggest a reasonable work-around?
Please note that the database structure is supplied by a third party, and
cannot be changed. All I can do is change the the form or its source query.
Note that the only reason for joining to this particular table is to
*display* the value of a single column from that table. This particular
value does *not* need to be edited. However, I am very new at Access form
development, and haven't yet figured out any way that I can display the
column values from this last table on the form without including that table
in my source query, and thus hitting the apparant read-only query problem.

Thanks in advance for any help.

s/KAM

[rkc]

"Kevin Myers" <Ke********@austin.rr.com> wrote in message
news:10*************@corp.supernews.com...

In MS Access 2000 I am developing a form for editing and viewing the
combined data from half a dozen tables. When I include all but one of the
tables in the source query for the form, everything is fine and I can edit
all of the desired fields. However, when I include the last table the query suddenly appears to become read-only, and I can no longer edit fields from
any of the tables.

The join criteria for this last table are different from the others as
follows:

1) A left outer join must be used when adding this final table because it
does not include rows corresponding to some of the rows in the other tables, and all of the rows from the other tables are required in the result set.

2) The join criteria for the last table involves comparing a couple of its
columns to substrings of a column in one of the other tables, i.e.:
where county.country_id=well.country.id and
county.state_api=mid(well.api,1,2) and county.county_api=mid(well.api,3,3)

Can anyone confirm whether one or both of the above ideosyncracies will
force a query to become read-only as the source for a form?

Can anyone point me to some documentation that describes the exact
circumstances under which a query will become read-only as the source for a form?

Finally, assuming that one of the above issues does in fact force the source query to become read only, can anyone suggest a reasonable work-around?
Please note that the database structure is supplied by a third party, and
cannot be changed. All I can do is change the the form or its source query. Note that the only reason for joining to this particular table is to
*display* the value of a single column from that table. This particular
value does *not* need to be edited. However, I am very new at Access form
development, and haven't yet figured out any way that I can display the
column values from this last table on the form without including that table in my source query, and thus hitting the apparant read-only query problem.

There are at least two ways I can think of to display the troublesome field
on the form without including it in the recordsource. One is to simply look
it up in the OnCurrent event of the form using the same criteria you would
if included in the query. Two, it may be possible to include the field as a
subform linked to the main form. You can format a subform to look like
nothing more than another textbox control easy enough.

[Kevin Myers]

Please see below...

"rkc" <rk*@yabba.dabba.do.rochester.rr.bomb> wrote in message
news:np********************@twister.nyroc.rr.com.. .

<snip>

There are at least two ways I can think of to display the troublesome field on the form without including it in the recordsource. One is to simply look it up in the OnCurrent event of the form using the same criteria you would
if included in the query. Two, it may be possible to include the field as a subform linked to the main form. You can format a subform to look like
nothing more than another textbox control easy enough.

Thanks very much for this information. Can you please elaborate further on
how to use the OnCurrent event for this purpose? I am already supplying
some code in the Form_Current event handler, and I know how to assign a
value to a text box so that it can be displayed. What I don't know is how
to obtain the desired value from within this method. The required SQL would
look something like this:

select county_name from county
where county.country_id=well.country_id and
county.state_API=val(mid(well.API,1,2)) and
county.county_API=val(mid(well.API,3,3));

Problems:

1. From within Form_Current, how can I bind this query to my form such that
the values from the well table correspond to the record that is presently
displayed on my form (which comes from a joined query that includes the well
table)?

2. How can I execute this query from within Form_Current?

3. How can I extract the desired value from the result of the query so that
I can shove the value into my text box for display?

Thanks again,
s/KAM

[rkc]

"Kevin Myers" <Ke********@austin.rr.com> wrote in message
news:10*************@corp.supernews.com...

Please see below...

"rkc" <rk*@yabba.dabba.do.rochester.rr.bomb> wrote in message
news:np********************@twister.nyroc.rr.com.. .

<snip>

There are at least two ways I can think of to display the troublesome field

on the form without including it in the recordsource. One is to simply

look

it up in the OnCurrent event of the form using the same criteria you would if included in the query. Two, it may be possible to include the field

as a

subform linked to the main form. You can format a subform to look like
nothing more than another textbox control easy enough.
Thanks very much for this information. Can you please elaborate further

on how to use the OnCurrent event for this purpose? I am already supplying
some code in the Form_Current event handler, and I know how to assign a
value to a text box so that it can be displayed. What I don't know is how
to obtain the desired value from within this method. The required SQL would look something like this:

select county_name from county
where county.country_id=well.country_id and
county.state_API=val(mid(well.API,1,2)) and
county.county_API=val(mid(well.API,3,3));

Problems:

1. From within Form_Current, how can I bind this query to my form such that the values from the well table correspond to the record that is presently
displayed on my form (which comes from a joined query that includes the well table)?

2. How can I execute this query from within Form_Current?

3. How can I extract the desired value from the result of the query so that I can shove the value into my text box for display?

I suppose you could try and shove that criteria into a DLookup and make that
the Control Source property of the textbox for display.

What I would probably do is open a recordset based on that query in the
OnCurrent event of the form and set the textbox value to the result of the
recordset. Example aircode (no error handling) would be:

dim rs as dao.recordset
dim sql as string
sql = "select county_name from county " & _
"where county.country_id=well.country_id " & _
"and " & _
"county.state_API=val(mid(well.API,1,2)) " & _
"and " & _
"county.county_API=val(mid(well.API,3,3));"

set rs = currentdb.openrecordset (sql)
with rs
if not .eof then
txtCounty_Name = .Fields("county_name") & vbNullString
end if
end with

rs.close
set rs = nothing

[rkc]

"rkc" <rk*@yabba.dabba.do.rochester.rr.bomb> wrote in message
news:ij********************@twister.nyroc.rr.com.. .

sql = "select county_name from county " & _
"where county.country_id=well.country_id " & _
"and " & _
"county.state_API=val(mid(well.API,1,2)) " & _
"and " & _
"county.county_API=val(mid(well.API,3,3));"

oops. Lame cut&paste job.
The sql string ,using the control values, would be more like:

"select county_name from county where " & _
"county.country_id= " & _
me!country_id & _
"and county.state_API= " & _
val(mid(me!API,1,2)) & _
"and county!county_API= " & _
val(mid(me!API,3,3)) & ";"

[Kevin Myers]

Managed to get this working based on DLookup. I'd have never found that
function without your help. Thanks!

s/KAM
"rkc" <rk*@yabba.dabba.do.rochester.rr.bomb> wrote in message
news:Ls*******************@twister.nyroc.rr.com...

"rkc" <rk*@yabba.dabba.do.rochester.rr.bomb> wrote in message
news:ij********************@twister.nyroc.rr.com.. .

sql = "select county_name from county " & _
"where county.country_id=well.country_id " & _
"and " & _
"county.state_API=val(mid(well.API,1,2)) " & _
"and " & _
"county.county_API=val(mid(well.API,3,3));"

oops. Lame cut&paste job.
The sql string ,using the control values, would be more like:

"select county_name from county where " & _
"county.country_id= " & _
me!country_id & _
"and county.state_API= " & _
val(mid(me!API,1,2)) & _
"and county!county_API= " & _
val(mid(me!API,3,3)) & ";"