Create New DB Record Using VBScript

[ridawg]

Hey,

I would like to create a new database record using VBScript. The default New
Record action doesn't function exactly they way I would like it to.

Has anyone done this before? I've written my own delete function using
VBScript and removeChild. Does something similar exist - like createChild?

Thanks for the help!

 

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

Didn't you already ask this question once? And haven't you already seen
this article?
http://enterprise-solutions.swits.n...tically-add-db-record-vbscript&c=infopath2003

You can use appendChild.

 

[ridawg]

I read the article but I've run into problems.

My form is set up so users can search for and edit existing records and
enter new records. I followed the instructions in your article but I was
constantly receiving error messages. The data validation I set up for the
entry of a new record was being triggered when a user submitted changes to an
existing record.

All I would like to do is mimic the New Record action and switch to a New
Entry View. It seems like there should be a way to do this in the code.

 

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

I'm not quite sure what you mean. What kind of errors were you receiving?

 

[ridawg]

Here's the scenario:

When a user updates an existing record the validation I set on the new
record fields (the fields I created under the new record group) is triggered.
I obviously need the validation to insure the user enters all the required
information for a new record.

I'm wondering - should I not enter any validation information on the fields
in the new record group because when you clone an existing record and map the
new record fields to the cloned fields the validation will be triggered?

My form is setup like so
1. Search View (default) From here the user can select to see the Details of
an existing record or enter a New Record.
2. Details View - here the user can edit an existing record.
3. New View - here the user can enter a new record.

The data for the form comes from 3 related tables.

 

[ridawg]

Ok, I think I've figured out what is going on. I still have a couple of more
tests I need to run though.

In your article you mention setting a rule on the Form Options. I didn't do
this because it was returning all the records. My database is large and I
didn't want to do that. But by not setting this if you opened up my form and
selected New Record you received an error message regarding the validation
even if you entered all the fields correctly. I'm guessing this really is in
reference to a record not being available to clone and not the data
validation I setup for my fields. Just a guess.

This is why I think it has something to do with a record not being available
to clone. I set up the form options as follows so only 1 record is returned
when the form is opened.
1. Setup a second data connection called default_record. Edited the SQL
statement to return the max id - max(id).
2. Next I set the field value of the query field id to the value returned
from step 1.
3. Finally, I did the step outlined in your article - Query using the Main
Connection.

This returns just one record. As soon as I did this I stopped receiving the
error message when I selected New Record when no records had yet been
retrieved.

Currently, I have all my validation code for the New Record setup in my code
- because based on the error message I was receiving I thought this was the
problem. So I'm going to play around with it some more. I'll get back to you
with what I figure out. Thanks for the help!

 

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

Thank you for the explanation.

If cloning is causing problems and you do not want the pre-querying, you can
also use CreateDOM() and then load the XML as text into a node.

 

[ridawg]

Ok, I've finished playing around with this and here's what I've discovered.

I can't use the data validation functionality on the New Record fields. I
need to do the validation in the code. Not a big deal - my validation is very
simply. When the user clicks save I run through a couple of if statements to
validate the data before I execute the code you outlined in your article.

Also on the New Record button I added some rules to set the value of all the
new record fields to blank. It seems after you hit the save button and switch
back to the default view if you select the New Record button again it was
displaying the data for the record just saved to the database. I wanted to
default the New Record view fields to always be blank initially.

I was also able to add a related table that is repeating table. I had to
adjust my query that is setup to execute when the form is opened so that the
default record also has a record in this related table. Added some similar
code to your article in regards to cloning the node and appending it.

So I was able to accomplish everything I wanted I'm just not comfortable
putting this into production. I don't feel comfortable having to hope a
record exists in the database that has records in each related table. I guess
I could put a dummy record in the database but I would rather not.

So once again I wish I knew a way to trigger the default New Record action.
This default action works regardless of whether a record exists in the
database and regardless of whether a record exists that has a record in each
related table.

Can you explain the CreateDom() method you suggested a little more? Do you
have an example?

Thanks for the help! I just posted another question in regards to your
article on Converting an InfoPath form into Word.

 

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

CreateDOM() creates a DOM Document, which you can then use to load XML into.
Do a search in this newsgroup on CreateDOM to find samples of its usage.