Building custom button.

[IP and Access 2003 - custom button.]

Access 2003 database
Infopath 2003

Im trying to create custom update and new records buttons using access query
"like calling a store procedure".
The code i wrote is in Microsoft Script editor.

I have found a nice example on Microsoft website.
http://msdn.microsoft.com/en-us/kb/kb00827007.aspx#10

For my testing i create a really simple table with only two fields
userid = autonumber and username as text "50 character".
I also create a sql query to insert new user in the table, tested it and it
works.

When i preview the form and press the button i don't have any error but the
database does not get updated.

The code wrote in the button even is:

var UserName =
XDocument.DOM.selectSingleNode("/dfs:myFields/my:TextField").text;
XDocument.QueryAdapter.Command = 'EXEC "NewEmployee" ' +UserName;
XDocument.Query();

Does anyone know why this code doesn't work on updating the table?

 

[S.Y.M. Wong-A-Ton]

Perhaps instead of:

XDocument.QueryAdapter.Command = 'EXEC "NewEmployee" ' +UserName;

you need to use:

XDocument.QueryAdapter.Command = "EXEC NewEmployee '" +UserName + "'";

Difference: You need single quotes around UserName. I did something similar,
but then in InfoPath 2007 in this article:
http://www.bizsupportonline.net/inf...ction-execute-sql-server-stored-procedure.htm

 

[IP and Access 2003 - custom button.]

Thank you very much for you reply Mong-A-Ton,

The database is pretty simple it only have an autonumber field "empID" and a
text field "EmpName".

Strange thing about it is that if i run the query from Microsoft Access the
record is created with a new EmpID and the username i typed in EmpName.

If call the query from IP, the record is not created.

BUT if after that i go back in access and run the query again, the new
record will be create and EmpID will have jump by the number of time i called
the query from IP.

ex:
i run query from Access with value "George".
I the go in IP insert "Jackson" in the textbox and press the button 5 times.
I then go back to Access and run the query again with the value "Bill"
the database would show something like

UserID UserName
1 George
6 Bill

And i didn't delete any records in the table.

Perhaps instead of:

XDocument.QueryAdapter.Command = 'EXEC "NewEmployee" ' +UserName;

you need to use:

XDocument.QueryAdapter.Command = "EXEC NewEmployee '" +UserName + "'";

Difference: You need single quotes around UserName. I did something similar,
but then in InfoPath 2007 in this article:
http://www.bizsupportonline.net/inf...ction-execute-sql-server-stored-procedure.htm

---
S.Y.M. Wong-A-Ton
http://www.bizsupportonline.net/infopath2007/ (InfoPath 2007 articles,
tutorials, and solutions)

Access 2003 database
Infopath 2003

Im trying to create custom update and new records buttons using access query
"like calling a store procedure".
The code i wrote is in Microsoft Script editor.

I have found a nice example on Microsoft website.
http://msdn.microsoft.com/en-us/kb/kb00827007.aspx#10

For my testing i create a really simple table with only two fields
userid = autonumber and username as text "50 character".
I also create a sql query to insert new user in the table, tested it and it
works.

When i preview the form and press the button i don't have any error but the
database does not get updated.

The code wrote in the button even is:

var UserName =
XDocument.DOM.selectSingleNode("/dfs:myFields/my:TextField").text;
XDocument.QueryAdapter.Command = 'EXEC "NewEmployee" ' +UserName;
XDocument.Query();

Does anyone know why this code doesn't work on updating the table?

 

[IP and Access 2003 - custom button.]

Perhaps instead of:

XDocument.QueryAdapter.Command = 'EXEC "NewEmployee" ' +UserName;

you need to use:

XDocument.QueryAdapter.Command = "EXEC NewEmployee '" +UserName + "'";

Difference: You need single quotes around UserName. I did something similar,
but then in InfoPath 2007 in this article:
http://www.bizsupportonline.net/inf...ction-execute-sql-server-stored-procedure.htm

---
S.Y.M. Wong-A-Ton
http://www.bizsupportonline.net/infopath2007/ (InfoPath 2007 articles,
tutorials, and solutions)

Access 2003 database
Infopath 2003

Im trying to create custom update and new records buttons using access query
"like calling a store procedure".
The code i wrote is in Microsoft Script editor.

I have found a nice example on Microsoft website.
http://msdn.microsoft.com/en-us/kb/kb00827007.aspx#10

For my testing i create a really simple table with only two fields
userid = autonumber and username as text "50 character".
I also create a sql query to insert new user in the table, tested it and it
works.

When i preview the form and press the button i don't have any error but the
database does not get updated.

The code wrote in the button even is:

var UserName =
XDocument.DOM.selectSingleNode("/dfs:myFields/my:TextField").text;
XDocument.QueryAdapter.Command = 'EXEC "NewEmployee" ' +UserName;
XDocument.Query();

Does anyone know why this code doesn't work on updating the table?

 

[S.Y.M. Wong-A-Ton]

I've never tried it, but I don't think this method works for a Query in
Access. However, it would work for a stored procedure in SQL Server. A Query
in Access is not the same as a stored procedure in SQL Server.

The article you referenced in your first post uses a stored procedure in SQL
Server; not Access.
---
S.Y.M. Wong-A-Ton
http://www.bizsupportonline.net/blog/

Thank you very much for you reply Mong-A-Ton,

The database is pretty simple it only have an autonumber field "empID" and a
text field "EmpName".

Strange thing about it is that if i run the query from Microsoft Access the
record is created with a new EmpID and the username i typed in EmpName.

If call the query from IP, the record is not created.

BUT if after that i go back in access and run the query again, the new
record will be create and EmpID will have jump by the number of time i called
the query from IP.

ex:
i run query from Access with value "George".
I the go in IP insert "Jackson" in the textbox and press the button 5 times.
I then go back to Access and run the query again with the value "Bill"
the database would show something like

UserID UserName
1 George
6 Bill

And i didn't delete any records in the table.

Perhaps instead of:

XDocument.QueryAdapter.Command = 'EXEC "NewEmployee" ' +UserName;

you need to use:

XDocument.QueryAdapter.Command = "EXEC NewEmployee '" +UserName + "'";

Difference: You need single quotes around UserName. I did something similar,
but then in InfoPath 2007 in this article:
http://www.bizsupportonline.net/inf...ction-execute-sql-server-stored-procedure.htm

---
S.Y.M. Wong-A-Ton
http://www.bizsupportonline.net/infopath2007/ (InfoPath 2007 articles,
tutorials, and solutions)

Access 2003 database
Infopath 2003

Im trying to create custom update and new records buttons using access query
"like calling a store procedure".
The code i wrote is in Microsoft Script editor.

I have found a nice example on Microsoft website.
http://msdn.microsoft.com/en-us/kb/kb00827007.aspx#10

For my testing i create a really simple table with only two fields
userid = autonumber and username as text "50 character".
I also create a sql query to insert new user in the table, tested it and it
works.

When i preview the form and press the button i don't have any error but the
database does not get updated.

The code wrote in the button even is:

var UserName =
XDocument.DOM.selectSingleNode("/dfs:myFields/my:TextField").text;
XDocument.QueryAdapter.Command = 'EXEC "NewEmployee" ' +UserName;
XDocument.Query();

Does anyone know why this code doesn't work on updating the table?

 

[IP and Access 2003 - custom button.]

Humm,

I was hoping that this wouldn't be the problem .

I have database with 1-many relationship that i want to insert in IP.
Problem is that IP won't allow the default submit because of the one-many
relationship.
I tough if i use access query instead of hardcoding the sql query in IP it
would simplify my life.

By any chance could you tell me how to hardcode a simple sql query that
would insert a value in a field.

Also do you know if with custom submit button it is possible to bypass IP
restriction to Memo and text field?

 

[S.Y.M. Wong-A-Ton]

You'd have to write code for both. I can remember newsgroup users doing it in
the past (a very long time ago), so you might still be able to find code for
that. Try searching on "ADO" and VBScript or JScript in this group. You can
also search on "Access" and "Memo". In addition, there might still be
posts/answers in the InfoPathDev forum (www.infopathdev.com/forums) that deal
with these IP/Access issues, so it might also be worthwhile heading over
there and doing a search.

 

[IP and Access 2003 - custom button.]

Hi S.Y.M,

I still have issues updating my access database using InfoPath.
Here's the command im using

XDocument.QueryAdapter.Command = "INSERT INTO Emp (EmpUsr) Values
('YanThiv')";
XDocument.Query();

Emp is the Table Name, EmpUsr is the field name, there is only 1 field to
fill the other is autonumber.

I do not get any error, every time i would trigger this command, the
autonumber would increase but the data is not sent to the database.

ex: my UserID is now at 53, i trigger the function 5 time then i insert a
new value directly in access the new UserId will be set to 58 but there won't
be records between userid: 53 and userid 58.

 

[S.Y.M. Wong-A-Ton]

Hi,

I don't use InfoPath 2003 anymore, so cannot test things out for you. But I
tried similar code with InfoPath 2007 and Access 2007, and it behaved as you
described. Somehow the records are not being committed.

I'd recommend you use ADO (http://support.microsoft.com/kb/183606) code to
insert records into your Access database.

 

[IP and Access 2003 - custom button.]

Hi S.Y.M
Thanks you for testing it for me.
It seems there is not much information on custom programming button for IP.
I checked the ADO link you sent me and went on further to do some research.
I forgot to specify earlier but i don't have access to a webserver.
I believe to use ADO with IP you need to create webservice which im not sure
but don't it need a webserver to run.

I can't believe it can be so complicated to find solution to a simple
problem "writing a custom function to write to an access database".
One would think it should be easy to do.

Basically what i was trying to do with Infopath is to have the user get the
infopath file from a shared directory and have the app write on a access
database also located on the shared directory.

If i was to go with Sql server would it be easier to achieve that without a
webserver?

 

[S.Y.M. Wong-A-Ton]

Hi,

The ADO link contained other links that eventually lead to code. Here's one
page with VB code: http://msdn.microsoft.com/en-us/library/ms807027.aspx
If you're writing JScript code, you've got to convert it. You can use the
ActiveXObject object to create objects (see
http://msdn.microsoft.com/en-us/library/7sw4ddf8(VS.85).aspx)

No, you do not require a web server if you write ADO code. You require a web
server if you're going to write ADO code in a web service method and then
call that web service method from within InfoPath.

Where having to use a web server is concerned, there is no difference
between SQL Server and Access. What I wrote previously applies to both.

My suggestion is that you write ADO code directly in InfoPath. The only
thing you might have to do is give your form template full trust (if it
starts giving you security errors).

Hope this helps.

 

[LittleMonkey]

Thanks alot for you help S-Y-M.

I spend so much time trying to figure out how to do an insert in infopath
and yet it was so easy when you look at it.

set cnn = CreateObject("ADODB.Connection")
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Espace Clo.mdb"

strSQL = "INSERT INTO FicheEspaceClos (UsineId, EspaceClosName,
EspaceClosEmplacement) VALUES ('" & intUsineId & "', '" &strEspaceClosName
&"', '" & strEspaceClosEmplacement & "')"

XDocument.UI.Alert(strSQL)
cnn.Execute(strSQL)

My insert now works like a charm.