Update multiple records from form?

[Rhys Daniell]

I have a 'form' page used for data entry in two tables. What's the procedure to handle the records?

The record for the first table is handled fine by the standard code. For the second record I want to add a record if one doesn't exist (regardless of whether the main table is in Insert mode or Update mode) or update an existing record when the page edit is complete.

TIA
Rhys

[Alan Telford]

Rhys,

There are embed points for ValidateInsert/ValidateUpdate/ValidateDelete.
This will be called based on the record action (ins/chg/del).

If you want a common point, then you can call your own local procedure/routine form all 3 places.
That is what I'm doing when I want to update a secondary table.

Alan

[Rhys Daniell]

Thanks but when I attempt to update the secondary file (using Access:Filename.update()) fields in this file which are not displayed on the page have blank values so the update fails.

I can use SetSessionValue() and GetSessionValue() to handle other fields but I have to remember to maintain this code if the record design changes. Is there some other method which handles all thefields in a record?

[Bruce]

Hi Rhys,

You're gonna have to manually check the secondary record anyway (to determine if the record already exists) and that implies a FETCH and so the Form fields are gonna be overwritten at that point.

You can then update the form fields from the session queue using the.SessionQueueTofile method. But that _might_ bring unwelcome fields along that have been left behind from an earlier visit to the session queue.

Ok, so I think your code is in 2 parts.
a) you need to prime the fields when the form is opened, and
b) you need to save them when the form is closed.

So on the for, in the preInsert, preUpdate, and preDelete (?) methods you need to prime the record.  They'll look something like this;

Access:Secfile.Open()
Access:Secfile.UseFile()
! prime record, and fetch to see if it exists, if it doesn't exist then set a session variable so we know we're doing an Insert later on.
sec:id = whatever
If Access:Secfile.Fetch(dec:idKey) <> 0
  p_web.SetSessionValue('secaction','insert')
  ! add any field priming-on-insert here
  sec:id = whatever
Else
  p_web.SetSessionValue('secaction','change')
End
p_web.FileToSessionQueue(secfile)
Access:SecFile.Close()

Now for part b
this code in the ValidateInsert et al, or PostInsert et al.

Access:Secfile.Open()
Access:Secfile.UseFile()
if p_web.GetSessionValue('secaction') = 'change'
  sec:id = whatever
  If Access:Secfile.Fetch(dec:idKey) <> 0
    p_web.SessionQueueToFile(secfile)
    Access:SecFile.Update()
  End
elsif p_web.GetSessionValue('secaction') = 'insert'
  p_web.SessionQueueToFile(secfile)
  Access:SecFile.Insert()
end
Access:SecFile.Close()

Cheers
Bruce