2. ASPRunnerPro General questions
  3. Autopopulate Table
This topic is locked

Autopopulate Table

 		12/14/2006 8:14:39 PM
ASPRunnerPro General questions
P
 Philip author

I have 2 tables:

Table_A: is used for login and holds masterdata as name, adress

Table_B: holds name, adress and some additional fields

Both table have one identical key field
My goal is that users can log on; when they add a record to table_B then name and adress are autopopulated from table_A.
How do I have to arrange this by using events?
Regards,

Philip
J
 		Jane 12/15/2006

Philip,
you can do it using AddOnLoad event.

Here is a sample code:

Sub AddOnLoad()

str = "select name, adress from Table_A where UserName='" & Session("UserID") &"'"

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

rsTemp.open str, dbConnection
Session("name") = rsTemp("name")

Session("adress") = rsTemp("adress")

End Sub



where UserName is your actual field name in the Table_A where login name is stored.
Then use Session("name") and Session("adress") as default values for the name and address fields on Table_B on the "Edit as" settings dialog on the Visual Editor tab.
P
 		Philip author 12/15/2006

Jane
Thanks - I managed to get running this code (UserId in my table is always a number - I had to leve the " ' "):
[indent]

Sub AddOnLoad()

'** Insert a record into another table ****

str = "select * from masterdata where H_Lizenz_AOA=" & Session("UserID")

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

rsTemp.open str, dbConnection
Session("HF_Name_AOA") = rsTemp("HF_Name_AOA")

Session("HF_Vorname_AOA") = rsTemp("HF_Vorname_AOA")
End Sub
[/indent]
Question: What do I have to add to autopoulate my table from a second table (means table A and table B are source for table C) ?
Philip
J
 		Jane 12/18/2006

Philip,
just add this code snippet to your event:

str2 = "select * from SecondTable where FieldName=" & Session("UserID")

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

rsTemp2.open str2, dbConnection
Session("FieldName2") = rsTemp2("FieldName2")
P
 		Philip author 12/18/2006

Jane
thanks for the code - i will try that.
QUESTION: I managed to autopopulate from one table, but it works not always. Problem: in 90% of cases when I click the add link the window opens but with empty fields. I click the refresh and fields are populated. What can I do to have it populated 100% ?
Philip