Hi Mary-Ann,

As long as no duplicates can occur, even in the deleted records, you are fine. It's a PITA, isn't it? ;-)


>Well, after many 'combinations' I have something working. Tell me how bad this is :-) I have a child table that basically stores the components of a formula for each product. On the update tab, I made the key fields the product's item number, the product's revision number and (each component has an itemnumber or more specifically, a list number)so I chose that one thinking that that would be the 'unique' value. Follow me? At any rate, it is updating fine so far. I've deleted records in the child table, added to it and modified it. I'm trying to test it to death to see if anything crops up. Can you foresee a problem with this? Thanks so much for all your help.
>
>
>>Hi Mary-Ann,
>>
>>Yes, for every record in the result set (view). Why don't you post the structures of the tables involved and the goal of the view and we'll see what we can work out.
>>
>>>So are you saying that I should have a unique value in a field for EVERY record. If it is the 'child' table that contains the product name, and one component of the formula in each record, in addition, each record that contains the component should also have a different value in a field that is unique to it?
>>>
>>>
>>>>Hi Mary-Ann,
>>>>
>>>>If you could post the SQL from the view, perhaps a strategy could be devised to remedy the situation painlessly :-)
>>>>
>>>>Regardless, a product name does not sound like a good choice for a keyfield. For one, it can be changed at will by the user; for two, it's not unique. Now, is there any combination of fields from the main table in the view that you can guarantee would be unique in the view? You can check off more than one key field, you know, to create a compound key. This might be a stopgap solution.
>>>>
>>>>All in all, though, you need to think down the road into creating a surrogate key field (a field that is maintained by the system and has no user informational data) and put a unique value into that field when a new record is created. Maybe using a sequential number or a GUID (see Rick Srahl's web-site for good GUID code).
>>>>
>>>>>
>>>>>So, I have several tables, one is the 'main' table. They all have a field named 'product' (product name). Each table is indexed on this field. the main table has one product name that is different in each record. One of the other tables (one to many relationship) has the same product name in several records as each record is a 'component' of the formulation for the product. Is that the problem? I don't get it. Seems like I Have to have the product in each record. help.
>>>>>
>>>>>
>>>>>>Hi Mary-Ann,
>>>>>>
>>>>>>As Ed points out, the key value for the KeyField is not unique. You see, for a view to update or delete correctly, one field has to be identified as the key field. This field has to be absolutely unique. To save you some time, this also means that you are going to want to use INSERT INTO to prepopulate this field with a unique value when inserting records.
>>>>>>
>>>>>>As Ed also points out, if you can create a Primary or Candidate key on the field without an error, it's usable as the keyfield in a view.
>>>>>>
>>>>>>
>>>>>>>"The key defined by the KeyField Property for table dbc.table is not unique". Seems like a simple message, but I don't really understand what it means.
>>>>>>>
>>>>>>> Get this error when trying to delete or update from the view. The table has one key field that it's indexed on, the view is indexed on the same key field name. Have clicked on the key field on the update tab and those fields to update. Don't understand the problem.