Thanks for the reply.

I was experimenting with getting the values from the base table. My main concern is that even though the base table is open, the record pointers don't necessairly move parallel to each other. A table update on the view doesn't automatically put my base table on the right row does it?

Typically my workaround is to use another field entirely to 'key' the record until I can requery the view. This works in SQL and VFP.

Example:

lcRealName = "Joe Smith"
lcTempkey = SYS(3) + "_" + SYS(3) && Generate random key

SELECT MyTableView
APPEND BLANK
REPLACE MyTableView.namefield WITH lcTempKey
tableupdate
REQUERY()
LOCATE FOR myTableView.namefield = lcTempKey
if !EOF()
REPLACE MyTableView.namefield WITH lcRealName

I will then have a new parent record with a primary key that I can use to attach my child records.

Unfortunately this has some drawbacks with how you build the where clause of the view. Typically you will end up adding something like.... OR myTableview.namefield = ?lcTempKey to your view and setting lcTempKey variable to "ZZZZZZZZZZ" when you are not appending records. Using that method you could also drop the locate as well. The downside is having to keep the extra clause in any views that append records.

In SQL there is an @@Identity variable you can query to get the key but it isn't 100% reliable last I worked with it.

I really find auto-increment keys sort of a mixed blessing. They save some overhead but you lose control and end up having to do other kinds of 'workarounds'.

It would be a huge benefit to VFP developers if Microsoft had a few functions for working with identity keys generated in table updates. ADO is kind enough to provide the key from SQL databases when you update a newly added record. Why can't FoxPro do the same in their views, or at least provide a 'Fetch primary key on insert' option?

Greg



>>I really like that VFP finally has an auto-increment type built-in. So I'm now trying to utilize it and I'm having the similar type of issue I had with MS SQL.
>>
>>In a view I append a blank record but the new key hasn't been generated yet. So I can't tie in the child records. Even if I do a table update it doesn't move the keys in.
>>
>>I know several workarounds... But I just wanted to find out what the 'preferred' way was to handle that situation. Is there a VFP function like there is in MS SQL that you can use to find the key value that was generated?
>>
>>It would also be nice to have a methodology that would be easy to up-size to SQL Server later.
>>
>>Thanks in advance!
>>
>>Greg
>
>Greg, when you use a local view, the underlying table is always open, so after the tableupdate you can get the key value from it...
>
>

>USE mytableview
>APPE BLANK
>tableupdate()
>REPLACE pk WITH mytable.pk
>

>
>For SQL you can do the same thing. I assume you have a data class of some type that does your tablupdate. It can check what type of view it is and do the appropriate code to get the assigned id.
>
>OF course, I am not on the VFP team, but I don't see any other way to do this.
>
>BOb