Form based on query with option group

[Emily Edgington]

I am using Access 2000. I have a form that is based on a query, and have
added an option group on that form, bound to a field in my query. It is
reflecting the current value of the field, but will not let me change the
value by selecting another option value (msg 'This Recordset is not
updateable'). I'm assuming this is because I have based my form on a query
rather than a table. However, I wanted it based on a query so that I could
show one record at a time - I assumed I had to use a prompt in a query to
make this happen.

Truly, what I want is to have a form that will allow the user to search
based on several criteria options, display one record at a time, and allow
them to change certain fields on the form to update the tables behind it. I
am new to Access, as you can probably tell. Please tell me if I am going in
the wrong direction. Is it not possible to update a table from a form that
is based on a query?

Thanks!

 

[Roger Carlson]

It is certainly possible to update a form based on a query -- as long as it
is an updateable query. Queries can become non-updateable for a variety of
reasons. Perhaps you can tell us what the table(s) are and what the query
is?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Emily Edgington]

The database is going to be used to maintain stale-dated checks in order to
escheat them to the various states. The tables involved in the query are:

Checks_Main - contains all checks and includes check status (field to which
option group is bound)
Latest_Comments - table created by make-table query, which shows the latest
dated comment added for each check (not necessarily every check will have a
record)
Check_Comments - table of all comments added for each check (may be multiple
records for each check, but not necessarily every check will have a record)

Checks_Main is the first table. Latest_Comments is linked to it on two
fields (Bank Acct & Check #), joined to include all records from Checks_Main
and only those records from Latest_Comments where equal (option 2 in Join
Properties). Check_Comments is linked to Latest_Comments in the same fashion
on the same two fields.

Query is a select query called Checks_and_Latest_Comments and includes
fields from Checks_Main and Check_Comments. For testing purposes, it is
currently set to return only one check number (bank acct and check # are both
specified).

Hope that's what you meant - I tried to think of everything I thought to be
relevant.

Thanks!

 

[brlubman]

The database is going to be used to maintain stale-dated checks in order to
escheat them to the various states. The tables involved in the query are:

Checks_Main - contains all checks and includes check status (field to which
option group is bound)
Latest_Comments - table created by make-table query, which shows the latest
dated comment added for each check (not necessarily every check will have a
record)
Check_Comments - table of all comments added for each check (may be multiple
records for each check, but not necessarily every check will have a record)

Checks_Main is the first table. Latest_Comments is linked to it on two
fields (Bank Acct & Check #), joined to include all records from Checks_Main
and only those records from Latest_Comments where equal (option 2 in Join
Properties). Check_Comments is linked to Latest_Comments in the same fashion
on the same two fields.

Query is a select query called Checks_and_Latest_Comments and includes
fields from Checks_Main and Check_Comments. For testing purposes, it is
currently set to return only one check number (bank acct and check # are both
specified).

Hope that's what you meant - I tried to think of everything I thought to be
relevant.

Thanks!

 

[Roger Carlson]

Well, there's still a lot I don't understand about your database. But I see
quite a few problems.

First of all, I'm concerned about your Latest_Comments table. It really
shouldn't be necessary to create that with a make table query if you already
have the information in the Check_Comments table. Duplicated information
like that is not good database practice.

As for your problem with non-updateability, it could be that you don't have
a unique index on your join fields (Bank Acct & Check #). Are those two
fields defined as a primary key? If not, the recordset created by the join
won't be updateable.

I rarely base forms on queries that have a Join anyway. (It can happen, but
it's not common.) The normal practice is to create a Form/Subform, where
the main form is based on the One-Side table and the subform is based on the
Many-side table of the relationship.

I think you should start with that. On my website
(www.rogersaccesslibrary.com), is a small Access database sample called
"FormSubform.mdb" which illustrates how the form/subform work. You can
ignore the Domain function stuff. You can find it here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=346

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L