VB code in Access Data Pages

[Jason Lopez]

I have a form that I have been able to convert to a Access Data Page. Most
of the form works in that HTML. However, there is one difference: the VB
code that is put in to requery a control based on the result of the previous
is not functioning correctly. When I load teh HTML, I get the following
error message: "Data provider failed while executing a provider command."
That error is then followed by "Invalid SQL statement; expected 'DELETE',
'INSERT', 'PROCEDURE', 'SELECT', OR 'UPDATE'."

Essentially, I have two controls. Each use a SQL statement as part of their
combo box functions. Control1 works fine in the HTML and Access form.
Control2 is where the problems begin. Control2 uses the following code in
VB to requery its own SQL statement for each time that the value of Control1
changes. So, for example, I put ValueA in Control1. Control2 will perform
its query (or at least it is supposed to) based on the value of Control1.
The SQL statement for Control2 of the original form looks like this:

SELECT tblRole.ID, tblRole.Role FROM tblRole WHERE
(((tblRole.Group_ID)=Forms![Current Proxy List]!ProxyGroup)) ORDER BY
tblRole.ID, tblRole.Role

The "Forms![Current Proxy List]!ProxyGroup" is Control1.

Hope this helps to get an idea of where I am stuck at. If I can find where
it is or what it is that I need to change to make this work properly, that
would be great.

Jason Lopez

 

[Jason Lopez]

Is there a way to somehow turn a SQL query into a set of VB code? Or is
there some way to get the query to run properly from a data access page?

Jason Lopez

 

[PaulD]

Typically a DAP already has a connection to a database, the MSODSC object.
With that said I'm not really sure what you are trying to do but here is
some partial code from a sample query I use to populate the title in a
pop-up dialog box

dim mysql
dim myrs
mysql = "SELECT Task.NAME FROM Task WHERE ((Task.ID=" & noteid & "));"
set myrs = MSODSC.Connection.Execute(mysql)
document.title = myrs.Fields("name").Value
myrs.close

The noteid variable is passed as a window.dialogarguments in the begining
of the script


Here is sample of an update query

<SCRIPT language=vbscript event=onclick for=btnActive>
<!--
dim sqlUpdate
sqlUpdate = "UPDATE COMPONENT SET [STATUS] = 0 WHERE ID = "
sqlUpdate = sqlUpdate & lblid.innerText
msodsc.connection.Execute(sqlUpdate)
-->
</SCRIPT>

Paul D

: Is there a way to somehow turn a SQL query into a set of VB code? Or is
: there some way to get the query to run properly from a data access page?
:
: Jason Lopez
:
: : >I have a form that I have been able to convert to a Access Data Page.
Most
: >of the form works in that HTML. However, there is one difference: the VB
: >code that is put in to requery a control based on the result of the
: >previous is not functioning correctly. When I load teh HTML, I get the
: >following error message: "Data provider failed while executing a provider
: >command." That error is then followed by "Invalid SQL statement; expected
: >'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', OR 'UPDATE'."
: >
: > Essentially, I have two controls. Each use a SQL statement as part of
: > their combo box functions. Control1 works fine in the HTML and Access
: > form. Control2 is where the problems begin. Control2 uses the following
: > code in VB to requery its own SQL statement for each time that the value
: > of Control1 changes. So, for example, I put ValueA in Control1.
Control2
: > will perform its query (or at least it is supposed to) based on the
value
: > of Control1. The SQL statement for Control2 of the original form looks
: > like this:
: >
: > SELECT tblRole.ID, tblRole.Role FROM tblRole WHERE
: > (((tblRole.Group_ID)=Forms![Current Proxy List]!ProxyGroup)) ORDER BY
: > tblRole.ID, tblRole.Role
: >
: > The "Forms![Current Proxy List]!ProxyGroup" is Control1.
: >
: > Hope this helps to get an idea of where I am stuck at. If I can find
: > where it is or what it is that I need to change to make this work
: > properly, that would be great.
: >
: > Jason Lopez
: >
:
:

 

[Jason Lopez]

That is actually what I am looking for. Script or VBA code. Either one
works for me to test it.

My DAP already does have the connection to the database. Combo box 1 has no
difficulty in running that particular query to populate the field. Combo
box 2 uses input from combo box 1 to filter out non-applicable choices.
That is where my difficulty was coming in: combo box 2 was not performing
the query to filter out the non-applicable selections.

I will try the code and see if it works.

Jason Lopez

Typically a DAP already has a connection to a database, the MSODSC object.
With that said I'm not really sure what you are trying to do but here is
some partial code from a sample query I use to populate the title in a
pop-up dialog box

dim mysql
dim myrs
mysql = "SELECT Task.NAME FROM Task WHERE ((Task.ID=" & noteid & "));"
set myrs = MSODSC.Connection.Execute(mysql)
document.title = myrs.Fields("name").Value
myrs.close

The noteid variable is passed as a window.dialogarguments in the begining
of the script


Here is sample of an update query

<SCRIPT language=vbscript event=onclick for=btnActive>
<!--
dim sqlUpdate
sqlUpdate = "UPDATE COMPONENT SET [STATUS] = 0 WHERE ID = "
sqlUpdate = sqlUpdate & lblid.innerText
msodsc.connection.Execute(sqlUpdate)
-->
</SCRIPT>

Paul D

: Is there a way to somehow turn a SQL query into a set of VB code? Or is
: there some way to get the query to run properly from a data access page?
:
: Jason Lopez
:
: : >I have a form that I have been able to convert to a Access Data Page.
Most
: >of the form works in that HTML. However, there is one difference: the
VB
: >code that is put in to requery a control based on the result of the
: >previous is not functioning correctly. When I load teh HTML, I get the
: >following error message: "Data provider failed while executing a
provider
: >command." That error is then followed by "Invalid SQL statement;
expected
: >'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', OR 'UPDATE'."
: >
: > Essentially, I have two controls. Each use a SQL statement as part of
: > their combo box functions. Control1 works fine in the HTML and Access
: > form. Control2 is where the problems begin. Control2 uses the
following
: > code in VB to requery its own SQL statement for each time that the
value
: > of Control1 changes. So, for example, I put ValueA in Control1.
Control2
: > will perform its query (or at least it is supposed to) based on the
value
: > of Control1. The SQL statement for Control2 of the original form looks
: > like this:
: >
: > SELECT tblRole.ID, tblRole.Role FROM tblRole WHERE
: > (((tblRole.Group_ID)=Forms![Current Proxy List]!ProxyGroup)) ORDER BY
: > tblRole.ID, tblRole.Role
: >
: > The "Forms![Current Proxy List]!ProxyGroup" is Control1.
: >
: > Hope this helps to get an idea of where I am stuck at. If I can find
: > where it is or what it is that I need to change to make this work
: > properly, that would be great.
: >
: > Jason Lopez
: >
:
:

 

[goode2fox]

that is so funny

Is there a way to somehow turn a SQL query into a set of VB code? Or is
there some way to get the query to run properly from a data access page?

Jason Lopez

I have a form that I have been able to convert to a Access Data Page. Most
of the form works in that HTML. However, there is one difference: the VB
code that is put in to requery a control based on the result of the
previous is not functioning correctly. When I load teh HTML, I get the
following error message: "Data provider failed while executing a provider
command." That error is then followed by "Invalid SQL statement; expected
'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', OR 'UPDATE'."

Essentially, I have two controls. Each use a SQL statement as part of
their combo box functions. Control1 works fine in the HTML and Access
form. Control2 is where the problems begin. Control2 uses the following
code in VB to requery its own SQL statement for each time that the value
of Control1 changes. So, for example, I put ValueA in Control1. Control2
will perform its query (or at least it is supposed to) based on the value
of Control1. The SQL statement for Control2 of the original form looks
like this:

SELECT tblRole.ID, tblRole.Role FROM tblRole WHERE
(((tblRole.Group_ID)=Forms![Current Proxy List]!ProxyGroup)) ORDER BY
tblRole.ID, tblRole.Role

The "Forms![Current Proxy List]!ProxyGroup" is Control1.

Hope this helps to get an idea of where I am stuck at. If I can find
where it is or what it is that I need to change to make this work
properly, that would be great.

Jason Lopez

 

[seumsack]

that is so funny

Is there a way to somehow turn a SQL query into a set of VB code? Or is
there some way to get the query to run properly from a data access page?

Jason Lopez

I have a form that I have been able to convert to a Access Data Page. Most
of the form works in that HTML. However, there is one difference: the VB
code that is put in to requery a control based on the result of the
previous is not functioning correctly. When I load teh HTML, I get the
following error message: "Data provider failed while executing a provider
command." That error is then followed by "Invalid SQL statement; expected
'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', OR 'UPDATE'."

Essentially, I have two controls. Each use a SQL statement as part of
their combo box functions. Control1 works fine in the HTML and Access
form. Control2 is where the problems begin. Control2 uses the following
code in VB to requery its own SQL statement for each time that the value
of Control1 changes. So, for example, I put ValueA in Control1. Control2
will perform its query (or at least it is supposed to) based on the value
of Control1. The SQL statement for Control2 of the original form looks
like this:

SELECT tblRole.ID, tblRole.Role FROM tblRole WHERE
(((tblRole.Group_ID)=Forms![Current Proxy List]!ProxyGroup)) ORDER BY
tblRole.ID, tblRole.Role

The "Forms![Current Proxy List]!ProxyGroup" is Control1.

Hope this helps to get an idea of where I am stuck at. If I can find
where it is or what it is that I need to change to make this work
properly, that would be great.

Jason Lopez