Query Database with On Load Event

M

mdsmitty

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.
 
S

Scott L. Heim [MSFT]

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.
 
M

mdsmitty

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.
 
S

Scott L. Heim [MSFT]

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.
 
M

mdsmitty

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.
 
S

Scott L. Heim [MSFT]

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.
 
M

mdsmitty

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
 
M

mdsmitty

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.
 
Top