Can't edit record in a query field

[Guest]

I can't edit certain records in query fields.

I have, for example, a table tblClients with a lookup field to a table
tblClientType (which has just one field, ClientType). I have no problem
choosing a different ClientType value for a record in datasheet view of the
tblClients table.

I also have a qryClients select query based on both tblClients and
tblClientType in which the query field for ClientType takes its value
directly from tblClientType. I created this query as a basis for a
corresponding frmClients form, following what I thought was good practice in
avoiding having a form control that wld otherwise be based directly on a
table lookup field (eg. ClientType). But if I try to change a value for
ClientType either using the drop-down list in the datasheet column of the
query or in the corresponding combo box on the form, I get an error message
about not creating duplicate values in the index, primary key or
relationship, etc.

I'd be very glad if someone cld help me figure out what's I'm doing wrong.

 

[John Spencer]

You want to change the value in TblClients.ClientType field. You can get
the value from tblClientType.

If you have a combobox to display and enter this information.
--The row source of the combobox would be a query on tblClientType showing
the available values.
--The CONTROL source would be tblClients.ClientType field.

If tblClientType consists of two fields - IdNumber, ClientTypeDescription,
then you would have a query of
SELECT IdNumber, ClientTypeDescription FROM tblClientType Order By
ClientTypeDescription
Your combobox would have 2 columns and be bound to the first column
The width of the first column would be set to 0 so that is was hidden

Hopefully that is enough information to get you started.

 

[Guest]

Thanks John for this. I'll set about doing what you suggest, but can I maybe
first pick up on a more general issue about using queries?

I've been creating select queries to join two or more tables, but, from your
advice, do I get the impression that it's a also good idea each time that you
create a new table to straightaway create a corresponding (single-table)
query for that table? So that for every table in the database there is also a
matching query for use in controls, etc, like the one you talk about here?

 

[John Spencer]

No, you don't automatically create a query for every table. You create a
query when you want to limit the fields or records returned or set order the
records that are returned or do some manipulation of the data.

If you aren't worried about the order of the data in the combobox and want
all the fields, then you can use the table as the source.

When I am working with lookup tables, I often have an "archived" field that
enables me to only return currently active values for valid choices when
adding new records. I want to keep the "old" (archived) values for those
records that are using them. With a query I can filter out the archived
values when needed.

 

[Guest]

Thanks John, this is sheds a lot of light on things for me.

I've been following a rather mechanical rule to treat all lookup fields with
great caution, and you're helping me develop a more commonsense view of what
is really needed and when.

I've now cleaned out a lot of superflous queries and things are working more
smoothly! I appreciate the trouble you've taken.