2. ASPRunnerPro General questions
  3. Get new key value before Insert New Record
This topic is locked

Get new key value before Insert New Record

 		10/14/2008 10:54:37 AM
ASPRunnerPro General questions
Stu author

Unfortunately my PK isn't an Autoincrement and another field in my table has to link to a master record value.
When the user presses Add New and is ready to Save the Record I need to run a query to find the last PK number in my database and add one to it to generate the next unique key.
Suppose my table name is "FAULTS" and the PK name is "FAULTREF".
The field name that has to be given the new PK is called "FAULTREF" (in table "FAULTS" on my Add New Record form)
What would my code be in the "Before Record Added" Event to get my next unique PK?
Also, in the Master Record I have a field called "LOCATION" which needs to be copied into the corresponding field "LOCATION" on the Add New Record form. Again, what would be my code to make a copy of that value.
Thanks,
Stu
J
 		Jane 10/15/2008

Hi,
to calcualte new FAULTREF use Before record addedevent on the Events tab.

Here is a sample code:

str = "select max(FAULTREF) as FAULTREF_max from FAULTS"

Set rstmp = server.CreateObject("ADODB.Recordset")

rstmp.open str,dbConnection

dict("FAULTREF") = rstmp(0)

rstmp.close

set rstmp=nothing


To fill Location field with value from master table use following code:

str = "select Location from MasterTableName where MasterKey=" & Session(strTableName & "_masterkey1")

Set rstmp = server.CreateObject("ADODB.Recordset")

rstmp.open str,dbConnection

dict("Location") = rstmp("Location")

rstmp.close

set rstmp=nothing
Stu author 10/15/2008

Jane,
Thanks so much. I started to twig how to do this and so I used:-
Set rs1 = Server.CreateObject("ADODB.Recordset")

rs1.Open "SELECT MAX([Fault Number]) AS MaxF FROM dbo.[Fault Reports]", dbConnection

session("NextKey")=rs1(0)+1 (Because I need to allocate the next highest free number)

___
That worked fine and I cheat in making my SQl statement by writing a View in SQL Server and doing a copy & paste into the code.
Regards,
Stu

Hi,

to calcualte new FAULTREF use Before record addedevent on the Events tab.

Here is a sample code:
To fill Location field with value from master table use following code:
Stu author 10/15/2008

I am still stuck on the syntax reference to the Master Key that was used to open a Detail page and then to opened the Add Page.
In the item Session(strTableName & "_masterkey1"), I don't know if "masterkey1" is a reserved word or is supposed to be filled with the real name of the master Key preceded by "" because I can't get anything to work. I use Response.Write to try and display my value and get nothing.
Example:-
Master Table: "dbo.Invoices"

Detail Table: "dbo.Invoice Items"
There is a common Master and Detail key field name called "Reference". ie the field "Reference" is in both Master and Detail Records.
When I want to Add a record to"dbo.Invoice Items" then how would I get the value of the Master Key from the Master Table "dbo.Invoice" so I can place it in the Add Record dialogue. BTW I need it from the Master Table because the Detail may contain no records so I can't use the key from there.
The explicit Session string name would be appreciated so I know whether you use a generic reserved value of "_masterkey1"
regards,
Stuart

Jane,

Thanks so much. I started to twig how to do this and so I used:-
Set rs1 = Server.CreateObject("ADODB.Recordset")

rs1.Open "SELECT MAX([Fault Number]) AS MaxF FROM dbo.[Fault Reports]", dbConnection

session("NextKey")=rs1(0)+1 (Because I need to allocate the next highest free number)

___
That worked fine and I cheat in making my SQl statement by writing a View in SQL Server and doing a copy & paste into the code.
Regards,
Stu
J
 		Jane 10/16/2008

Hi,
Session(strTableName & "_masterkey")is filled automatically if you have set up master-detail relationship on the Datasource tables tab.