copy record in field in 1 table to another table

[AlastairOz]

I have a parent table called "Sites"
I have a child table called "AssetLights"
I have both on a form with a relationship set.
I select a record from the parent "sites" using a control.

Then when adding a new record to child "assetslights",
I want to copy the value of the ID field from the parent to the child.
There is a relationship set between the 2 tables.

I am new to this, so any help would be appreciated.

This is what I have so far, now I am stuck:

LOCAL tParentRec
SELECT sites

tParentRec = IIF(EOF(), -1, RECNO())
COUNT
IF tParentRec > 0
GOTO (tParent)
ENDIF

** I think this gets the current record number of the
** table sites

 

[Olaf Doschke]

What do you want with the Recno()?

The parent record is already the currently selected, if the Sites table is bound to the control for selecting the site, so you don't need to go there, you are already there.

The Sites ID simply is Sites.ID (or whatever the PK field is named), so to fill in that ID into the child table into a new record you do

If NOT EOF("Sites")
Insert Into AssetLights (SiteID) Values (Sites.ID)
Else
Messagebox("please select a site first.",0,_screen.caption)
Endif

If the AssetLights record already is created as a new blank record, you do a REPLACE instead of INSERT:

Replace SiteID with Sites.ID in AssetLights.

(Assuming SitesID is the FK field name within the AssetLights table).

Bye, Olaf.

 

[AlastairOz]

Thanks,
I tried this suggestion but was unable to get it to work,
When I append blank, the record pointer always jumps from the new record to the one of the records linked to the current siteID.
I guess this is correct behavior, but I am not getting the site.siteid inserted into the assetlights table first.
Any help would be appeciated

 

[Olaf Doschke]

Well, if you don't APPEND BLANK but INSERT INTO with the site ID, the record pointer should stay on the new record, as it belongs to the group.

Bye, Olaf.

 

[AlastairOz]

Thanks, that worked.
Now all I have to do is get the record pointer to stay or move to the new record.
At the moment, all it wants to do is skip to the first record in the table.

 

[Olaf Doschke]

Hi Alastair,

There's code to be some code moving recordpointer in the sites table or at least selecting it causing the relation to move recordpointer in the child table.

But in fact just selecting the parent table does not make the child table change it's active record, even with a relation set.

Bye, Olaf.

 

[JRB-Bldr]

Going back to my visual explanation of what is going on...

ParentTable ChildTable
A B C A 4 Y Z
A R 1 T
A M N 6
D X Y D 1 A 3
D X 2 C

Your INSERT has just now added a new record to the Bottom of the table where its RECNO() is now greater than those records 'above' it.

So you end up with:

ParentTable ChildTable
A B C A 4 Y Z
A R 1 T
A M N 6
A Z 1 T <-- New Record
D X Y D 1 A 3
D X 2 C

As Olaf says, by selecting the Parent record, you can, by default, 'see' into the first Child record - NOT the most recently added one. Again you need to separately modify the record pointer to the Child table to get to the New record.

Good Luck,
JRB-Bldr

 

[brigmar]

If you use SET ORDER TO .... DESCENDING in your child table before setting the relation, then the most recently added record in the child table will be the one selected when you move the parent table record pointer.

CREATE CURSOR m1 ( pk i autoinc, value c(5) )
INSERT INTO m1 (value) VALUES ("item1")
INSERT INTO m1 (value) VALUES ("item2")
CREATE CURSOR c1 ( pk i autoinc, fk i, value c(5) )
INSERT INTO c1 (fk,value) VALUES (1,"1-1")
INSERT INTO c1 (fk,value) VALUES (1,"1-2")
INSERT INTO c1 (fk,value) VALUES (2,"2-1")
INSERT INTO c1 (fk,value) VALUES (2,"2-2")
INDEX on fk TAG fk
SET ORDER TO FK [b]DESCENDING[/b]
SELECT m1
SET RELATION TO pk INTO c1

 

[Olaf Doschke]

By Default the record is NOT moved within a child table of a relation, there's got to be something actively selecting the parent, which will move to the first child, a grid with the parent alias set as it's recordsource could be the reason for that, or actively selecting the parent table.

Having a descending order on the child table is a workaround, I'd only use as a second best option.

Here's a sample without any form and bound controls, it shows the pure behaviour of the relation, and that is not moving the child record pointer:

Create Cursor curSites (iSiteID I Autoinc, cSomeField C(10))
Index On iSiteID Tag xSite
Index On cSomeField Tag xSome

Create Cursor curAssetLights (iAssetLightID I Autoinc, iSiteID I, cSomeOtherField C(10))
Index On iSiteID Tag xSite
Index On iAssetLightID Tag xAsset

Set Order To Tag xSite In curSites
Set Order To Tag xSite In curAssetLights

Insert Into curSites (cSomeField) Values ("Site 1")
Insert Into curSites (cSomeField) Values ("Site 2")

Insert Into curAssetLights (iSiteID, cSomeOtherField) Values (1, "Asset 1-1")
Insert Into curAssetLights (iSiteID, cSomeOtherField) Values (1, "Asset 1-2")
Insert Into curAssetLights (iSiteID, cSomeOtherField) Values (2, "Asset 2-1")

Select curSites
Set Relation To iSiteID Into curAssetLights

Goto 1 In curSites
? curSites.iSiteID, curAssetLights.iSiteID, curAssetLights.cSomeOtherField

* Code for the New Assetlights record:
Insert Into curAssetLights (iSiteID, cSomeOtherField) Values (curSites.iSiteID, "Asset 1-3")

* check record pointers afterwards:
? curSites.iSiteID, curAssetLights.iSiteID, curAssetLights.cSomeOtherField

* We're staying on the new AssetLight record and not going to the top of the child records "Asset 1-1"

* That's even though the parent table curSites is the active one.
* the child pointer just moves, if we do select the child and then reselect the parent alias:Go Top
Go Top in curSites
* check record pointers afterwards:
? curSites.iSiteID, curAssetLights.iSiteID, curAssetLights.cSomeOtherField

This shows there's got to be something moving recordpointer in Sites to get your behavior. The relation is only triggered by moving within the parent table, nothing else triggers moving within the child table.

Your only code to create the new Asset Light should be the INSERT, nothing more, nothing less, no GOTO, nothing which would move within the Sites table. And then you stay with the new record in the AssetLights table. There is no magic to that, that's default behaviour of Insert.

Bye, Olaf.

 

[Olaf Doschke]

replace "if we do select the child and then reselect the parent alias" by "if we move within the parent alias".

Just selecting the parent alias does not trigger the relation and move within the child table, that's what I tried before GO TOP.

Bye, Olaf.