Auto Run Query

[samusa]

I have two Data Access Pages and two tables. I have update query that is updating table 2 to table 1. I want to autorun this query when I close DAP or when i click save button on DAP.Could some one help me out.I would greatly appreciate any suggestions

thank you

Sam

 

[fneily]

You might try this: On the save button, go to microsoft script editor, scroll down to the command button's name, expand it, click on onchange. You can write a SQL statement like the following to do an update.

<SCRIPT language=vbscript event=onchange for=Acq_acreage>
<!--
dim sSQL
sSQL = "Update Table1 Set Table1.Field1 = Table2.Field1, etc.
From Table2 Where Table1.Primarykey = Table2.PrimaryKey"
MSODSC.Connection.Execute sSQL
-->
</SCRIPT>

You might want to check the syntax on the Update.

 

[fneily]

Came across this to run a query from a Data Access page:

Dim rst
Dim vbs_adOpenDynamic
Dim vbs_adLockOptimistic
vbs_adOpenDynamic=2
vbs_adLockOptimistic=3
rst.open "query1", msodsc.Connection, vbs_adOpenDynamic,
vbs_adLockOptimistic
msodsc.Execute rst
rst.Close
Set rst = Nothing

 

[samusa]

I tried this but it is neither saving to table2 nor updates table 1. However, if i use update query in VBscript on separate command button it is working. In that case i click save button first and then new command button that updates table 1.But i want to get table 1 updated with any event like when I close page, Click Home etc. please help

Sam

 

[fneily]

First, which post did you try? Probably the second, correct????
You have to be careful where you put the code. On your Save button the code will go AFTER the saving code.
On a close, first you must save, then update, then close.

 

[samusa]

I tried post 1 and placed the code after Save code. But it did not work, Althogh it is saving to table2 but not updating table 1.

About your Post 2, should i use it onchange event of save button. Please advice

Sam

 

[fneily]

I wasn't too sure of the SQL syntax in my first post. That's why I asked you to check it first.
Try the second post. Yes, it goes on the onchange event of save button. Don't forget to change "query1" to your query name.

 

[samusa]

I tried second one as well but i get message script error(accdp://152725472/).

Sam

 

[fneily]

I forgot a line. Put
set rst=createobject("adodb.recordset")
before the rst.open statement. So

Dim rst
Dim vbs_adOpenDynamic
Dim vbs_adLockOptimistic
vbs_adOpenDynamic=2
vbs_adLockOptimistic=3
set rst=createobject("adodb.recordset")
rst.open "query1", msodsc.Connection, vbs_adOpenDynamic,
vbs_adLockOptimistic
msodsc.Execute rst
rst.Close
Set rst = Nothing

 

[samusa]

No luck so for...

Sam

 

[fneily]

I need to know any error messages, what you put for "query1", etc.

 

[fneily]

Also, how many fiels are you updating?

 

[fneily]

That previous post should say, how many fields are you updating? Also, are you updating or inserting?

 

[samusa]

Thanks for your help.
I am not getting any message since i put "set rst=createobject("adodb.recordset")"However when I click on Save button, it doesn't save in either table. I am using update query and its name is qrytest. I also tried SQL code in place of query name but met with same result.I am updating 4 fields from table 2 to table 1.

Sam

 

[fneily]

A few things. Is your Save button created by the wizard? If it is, then you'll notice the code is in JavaScript. So adding VBScript won't work. You'll have to change the Save code to VBScript.

In the beginning of this post, you said you had the update and save working on separate buttons. What does your update code look like since it works?

I found the code that I posted at this URL:

http://www.tech-archive.net/Archive...lic.access.dataaccess.pages/2004-09/0036.html

You can check it yourself.

Also, I have in production, so I know it works, the following on a command button:
dim sSQL
sSQL = "INSERT INTO REAGLOG(ParcelID,FieldName,NewData,Person) VALUES('" & CINT(ParcelID.value) & "','Acq_acreage','" & CSTR(Acq_acreage.value) & "','" & CSTR(holdID) & "')"
MSODSC.Connection.Execute sSQL

I wonder if you could combine your update with my insert to do both operations. First insert a new record, then update the other table.

Also, if you look through the forums on DAP's, you're becoming the expert DAPPER.

Other than that, I'll have to find time to do alot of testing.

 

[samusa]

Yes my save button is created with wizard. Here is update code that works fine on command button (onclick event)

<SCRIPT language=vbscript event=onclick for=test1>
<!--
dim sSQL
sSQL = "Update tblstudentinfo Right Join tblhistory ON tblstudentinfo.studentID=tblhistory.studentID SET tblstudent.teamName=tblhistory.teamName .........
WHERE tblstudentinfo.studentID=tblhistory.studentID"

MSODSC.Connection.Execute sSQL
-->
</SCRIPT>


Sam