Farooq Ahmed
|
| Posted: 10/02/2005, 12:06 AM |
|
I want to copy two fieldslas of last row from USERS table to TEST table. My fields are USER_ID and EMAIL in both tables. My database is myscore and connection is NEWCONN. I am using ASP, Access and CCS and form name is LOGIN. I tried following code but it is not working (don't transfer data from users table to test table). What is wrong with following code. Could anyone solve this problem or suggest any other code. My requirement is if user enters a new record and press add, it should copy same data to second table:
Function users_AfterUpdate()
Dim SQL
Dim Connection
SQL = "INSERT INTO test(user_id, email) "&_
"VALUES ("& CCToSQL(login.user_id.Value,ccsInteger) &","& CCToSQL(login.email.Value,ccsText) &")"
Set Connection = New clsDBnewconn
Connection.Open
Connection.Execute(SQL)
Connection.Close
Set Connection = Nothing
End Function
|
|
|
 |
Edd
Posts: 547
|
| Posted: 10/02/2005, 7:40 AM |
|
Farooq,
In the BeforeInsert store the variables in a global area when they are available to you. i.e. outside the function.
Then in the AfterInsert
Dim SQL
Dim Connection
SQL = "INSERT INTO test(user_id, email) "&_
"VALUES ("& CCToSQL(user_id,ccsInteger) &","& CCToSQL(email,ccsText) &")"
If TheNameOfYourForm.Errors.Count = 0 Then
Set Connection = New clsDBnewconn
Connection.Open
Connection.Execute(SQL)
Connection.Close
Set Connection = Nothing
End If
Regards,
Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
 |
|
Farooq Ahmed
|
| Posted: 10/02/2005, 9:27 AM |
|
I used your code and used the extra variables outside the BeforeInsert function i.e.:
Dim user_id
Dim email
then inside the BeforeInsert function, I used
Dim SQL
Dim Connection
and complete code.
When I tried to insert the record, it entered it in first table but in second table it increment ID field ( which is primary key and autonumber of second table -- test), but both USER_ID and EMAIL fields are empty.
Please also note that in first table USER_ID is primary key but in second table it is number field.
Could you solve the problem.
Thanks for your response
|
|
|
|
Edd
Posts: 547
|
| Posted: 10/02/2005, 4:47 PM |
|
Farooq,
Are you saying that the User_Id in the first table is an Auto Incriment Key? - If so that explains why you would not have a value for the User_ID.
Let us redo this based upon the fact that you are using MS Access.
Dim email
Function BeforeInsert
'Store the value for future use
email = login.email.Value
End Function
Function AfterInsert
Dim SQL
Dim Connection
Dim user_id
If TheNameOfYourForm.Errors.Count = 0 Then
Set Connection = New clsDBnewconn
Connection.Open
' In SQL you would just look up @@IDENITY but I don't thing that works in MSAccess
' This will work fine for small amount of users
user_id = CCDLookup("Max(USER_ID)","FirstTableName","",clsDBnewconn)
If Not IsNEmpty(user_id) Then
SQL = "INSERT INTO test(user_id, email) "&_
"VALUES ("& CCToSQL(user_id,ccsInteger) &","& CCToSQL(email,ccsText) &")"
End If
Connection.Execute(SQL)
Connection.Close
Set Connection = Nothing
End If
End Function
Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
Farooq Ahmed
|
| Posted: 10/02/2005, 8:50 PM |
|
No success at all 
I tried with different options of code but it didn't work. Now it is not increment the autonumber key in the second table. Since one week my whole work has stuck due to this small problem.
Any further help please :)
|
|
|
|
Edd
Posts: 547
|
| Posted: 10/02/2005, 9:48 PM |
|
Farooq,
Can you Zip your project and your Access Database to me.
My Address is epowell at syntech.com.au.
And let me have a look at it.
Regards, Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
|
