Query Database with On Load Event

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mdsmitty - 17 May 2005 19:52 GMT

I need to have my form query a database when it is opened. I have a Text
field called Account_Number which is populated programmatically when the form
is opened. How do I query the database for the corresponding demographics
using the On Load Event to populate the rest of the demographic fields on the
form?

I don't want the user to have to use a Query Button to execute this action.
I am sure I can accomplish this using code but I am very new to writing
VBScript (language selected for this form) and can use some help in the right
direction.

Thanks in advance.

Reply to this Message

Scott L. Heim [MSFT] - 17 May 2005 20:57 GMT

Hi,

The following sample code, placed in the OnLoad event, should provide the
results you desire:

    Dim xNode
    Set xNode =
xDocument.DOM.selectSingleNode("//dfs:myFields/dfs:queryFields/q:authors/@au
_lname")
    xNode.Text = "Green"
    XDocument.QueryAdapter.Query

I hope this helps!

Best Regards,

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights

Reply to this Message

mdsmitty - 18 May 2005 19:48 GMT

Hi Scott

Unfortunately with my lack of knowedge on this subject I need a few things
clarified. I copied the code you sent and obviously it won't work as is, so
I attempted to plug in the database name, field names, etc. and tested the
form in Preview. I recieved errors stating that, for the lack of technical
lingo, things were in the wrong place and am still unable to figure this out.

If you could let me know what goes where it would be greatly apprecitated.
I noted my trouble spots with *'s.

Dim xNode
Set xNode = *what goes here?*
xDocument.DOM.selectSingleNode("//dfs:myFields/dfs:queryFields/q:authors/@au
_lname*what goes here?*")
xNode.Text = "Green"
XDocument.QueryAdapter.Query

I am accessing the OnLoad Event via Tools->Programming->On Load Event. I
noticed that Alex responed with a different line of code. I attempted
testing his code with the same type of errors.

Thanks again for your help.

> Hi,
>
[quoted text clipped - 16 lines]
>
> This posting is provided "AS IS" with no warranties, and confers no rights

Reply to this Message

Scott L. Heim [MSFT] - 18 May 2005 21:15 GMT

Hi,

No problem! What I was attempting to do was describe my sample - in the
end, this is not important. So let me clarify: my understanding is that you
simply need your form to pre-populate when opened just as if the user had
clicked the "Run Query" button - correct? If this is correct, then after
you populate the Account_Number field, the only other thing you should need
in the code is:

XDocument.QueryAdapter.Query

I hope this clarifies things a bit! (Sorry for the confusion...)

Best Regards,

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights

Reply to this Message

mdsmitty - 18 May 2005 22:20 GMT

Sorry to bug you again Scott...I have another idea to get my desired result.
The code you sent seemed to work but i thought of another issue that may come
up using the On Load Event. If the Account number is not loaded before the
On Load Event runs then it won't know what to query.

What is the best way to use the Account_Number field and have the query run
based on the information within that field without using a Query Button?

Thanks again for your help and patiance.

> Hi,
>
[quoted text clipped - 15 lines]
>
> This posting is provided "AS IS" with no warranties, and confers no rights

Reply to this Message

Scott L. Heim [MSFT] - 19 May 2005 13:52 GMT

Hi,

No problem. Although I am a bit confused by the question: if you are using
code to populate the Account_Number field, why would it not be populated
before you execute the query in the Load event?

Are you populating the Account_Number field in the Load event?

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights

Reply to this Message

mdsmitty - 19 May 2005 14:31 GMT

That was the issue I was not sure of...our programmer was doing all this
behind the scenes and frankly, that is all over my head. After speaking to
him he told me that he was populating the account number before the form was
loaded therefore the On Load Event would be able to pick up the account
number and query the database for the demographics. I don't have an answer
as to why the code didn't work yesterday but I have a feeling I was doing
something wrong or missing a step. The main issue I think was the fact that
I was testing several methods and had too many variables while testing that
affected one and other. Once I created a "fresh/new" test form the On Load
Event worked.

To be honest, I don't know how the programmer is populating the account
number but he did say it was before the form was loaded.

Thanks again for your help...if you have any other questions, let me know.

> Hi,
>
[quoted text clipped - 8 lines]
>
> This posting is provided "AS IS" with no warranties, and confers no rights

Reply to this Message

mdsmitty - 19 May 2005 13:59 GMT

Scott

i got it to work...I think with all the screwing around adding and deleting
code on the form I was testing yesterday caused most of the errors. I
created a new test form this morning and was able to get the form to work
both with the On Load Event as well as using a Rule to query and populate the
demographics after the form was opened.

Thanks again for all your help and patience.

Mike

> Hi,
>
[quoted text clipped - 15 lines]
>
> This posting is provided "AS IS" with no warranties, and confers no rights

Reply to this Message

Alex [MSFT] - 17 May 2005 22:00 GMT

In the OnLoad event handler, use the code similar to the following:

Dim myDataAdapter
myDataAdapter =
XDocument.DataAdapters("ChangeThisToYourDataConnectionName")
myDataAdapter.Query()

Good luck,
Alex @ Microsoft

-----Original Message-----
From: mdsmitty [mailto:mdsmitty@discussions.microsoft.com]
Posted At: Tuesday, May 17, 2005 11:52 AM
Posted To: microsoft.public.infopath
Conversation: Query Database with On Load Event
Subject: Query Database with On Load Event

I need to have my form query a database when it is opened. I have a
Text
field called Account_Number which is populated programmatically when the
form
is opened. How do I query the database for the corresponding
demographics
using the On Load Event to populate the rest of the demographic fields
on the
form?

I don't want the user to have to use a Query Button to execute this
action.
I am sure I can accomplish this using code but I am very new to writing
VBScript (language selected for this form) and can use some help in the
right
direction.

Thanks in advance.

Reply to this Message