How do I write an "updeateable query"

[KjWhal]

I can write an UPDATE statement fine, what I don't quite understand though, is updateable statements involving forms.

To populate a form you have the form record source set to some SELECT statement and then you place your controls and set the control sources to the fields in the SELECT statement. This I understand, what I don't understand is the mechanism that allows updates.

This may seem obvious but do I need to reference the key for that particular record as well? How does this work?

 

[The_Doc_Man]

When you bind a form to a record, the key for the table is just one of the many fields. But... Access is smart enough to know which one is the prime key - if you have one.

The EASIEST way to make a updateable query is through the query design grid. When you use that, you are going to avoid much of the syntax that kills query updateability.

Then use a Forms wizard to build the basic form for you. Again, using the wizard avoids the more complex stuff you might otherwise try. Now go back and update the form design based on the initial shot developed for you by that wizard. (This is why, even though they are dumber than a box of garden tools, wizards are your friends.)

When you avoid the complicated stuff, your query and form are usually updateable.

Now, what would happen to make a form - or its underlying query - NOT updateable. Besides the obvious "set the query properties to NO UPDATE", of course...

Summation queries that don't tie back to a unique record are not usually updateable.

JOIN queries in a many-to-one or one-to-many setup can be non-updateable if there is some issue related to whether the query displays a bunch of data from the ONE side and you try to update that info. Since it doesn't come from a single place, Access can sometimes get confused.

Multi-layered queries that pass through a non-updateable query cannot be updated.

Queries that reference external files SOMETIMES cannot be updated. Depends on the file and its permissions.

I'm sure there are other causes, but I think that covers a lot of the basic ones.

 

[KjWhal]

The EASIEST way to make a updateable query is through the query design grid. When you use that, you are going to avoid much of the syntax that kills query updateability.

When you avoid the complicated stuff, your query and form are usually updateable.

What sort of complicated stuff? What is the query supposed to look like? I can't even use the query design grid if I don't know if I'm supposed to pull something specific or not.

 

[boblarson]

Sounds like you need more training than we can give here. Here are a few websites that have some good tutorials on Access, including queries.

http://www.cob.ohio-state.edu/~muhanna_1/837/MSAccess/tutorials.html

http://www.fgcu.edu/support/office2000/access/

 

[KjWhal]

Sounds like you need more training than we can give here. Here are a few websites that have some good tutorials on Access, including queries.

http://www.cob.ohio-state.edu/~muhanna_1/837/MSAccess/tutorials.html

http://www.fgcu.edu/support/office2000/access/

Thanks for the links.

Just to bounce this off someone though

I have 3 tables,

tblClient: ClientID (primary key), FirstName,LastName
tblPrograms: ClientID, OrgID, ProID, Semester, Duplicate (all of these fields is the primary key), Role (Role is a foreign key LookupID)
tblInfo: LookupID (primary key), LookupValue

I want to write a query that displays

First & ", " & Last, Role (display the LookupValue not the ID) and have it be updateable. How do I accomplish this?

 

[boblarson]

The answer is - you cannot have an updateable query with an expression in it. Sorry, it won't happen. You can display that info in a separate unbound control on a form, but it can't be bound to the table.

 

[The_Doc_Man]

That's the "complicated stuff" I mentioned.

When you establish a one-to-one relationship between fields in a record and columns in the datasheet view for the query, and the rows then correspond to records returned by that query, you have a very good chance of having an updatable query.

As Bob points out, including an expression means that one of the fields in the query is no longer one-to-one with a field in a record. That is, suppose you tried to update the computed (expression-based) field. Where would you store the udpated value? Surely you wouldn't expect Access to reverse-compute the formula in order to store the updated value?

Complicated stuff: Lookups will kill you every time. If the query shows you the text equivalent of a code, but the code is what is actually stored, then you had better not try to type in a new text value. (It is encoded, remember?)

The more complicated you want your computations to be, the more likely they are to prevent generation of an updateable query. Summations, simple expressions, domain aggregates, SQL aggregates, .. all of these can have the result of creating an intractable query that cannot be updated. Anything where the "map" (datasheet view of the qery) isn't the territory (datasheet view of the table or tables). And all it takes is a single field.