• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 676
  • Last Modified:

Query data from an external MS Access database

I am new to InfoPath. I want to develop a form in InfoPath 2007 and then publish it to a SharePoint 2010 site.

I have created the form in InfoPath. It has a text box, a button, and a repeating table. Aslo I have added an external data source, which is an MS Access database. My idea is that users enter a part number in the text box and click the button. All the records in the database that match the part number entered into the text box will populate in the repeating table.

I have set up a rule for the button with a condition that the value in the text box is equal to the part number field in relevant table of the database and an action to query the data connection.
The repeating table is binding to the relevant table in the Access database.

With that, I tried with preview to test the form. Upon pasting over a part number and clicking the button, nothing happens in the repeating table. I have made sure that there are matching records in the database.

I also put in a drop-down list in the form to test the data connection. The drop-down is displaying info correctly. So I ruled out the data connection factor.

Therefore, the problem seems to be that I didn't configure the form properly.

Being new to InfoPath, I have no clue where to go from here. Can anyone point me to the right direction?

Thanks...


0
nnrsc
Asked:
nnrsc
  • 4
  • 3
1 Solution
 
clayfoxCommented:
nice to see you are troubleshooting properly.

Typically I would have a repeating table of your access part data.
Then in properties and conditional formatting add a filter where if part number does not equal or contain the text box value hide.

This will then only show what matches.

I would query after a value and you may need a reset button, which I query a partnumber 0, which returns no records. That way the form is reset without closing and reopening the form and they can select and query again.

Hope that helps.
0
 
nnrscAuthor Commented:
Thanks for the lights.

I have added a filter to take care of the no-match situation and put in a Reset button. But the query still not working. When I enter a part number that I know for sure there are matching records in the database, nothing populates in the repeating table. However, if I enter something I know there is no mathcing records, the message I defined pops up saying no matches found.

The Reset button works properly as well.

Must be that I have overlooked something. I notice there is an Edit Form Code button in the Button Properties. Clicking on that brings up the Script Editor. Am I supposed to write some code there? It now shows as:

//=======
// The following function handler is created by Microsoft Office InfoPath.
// Do not modify the name of the function, or the name and number of arguments.
//=======
function CTRL2_5::OnClick(eventObj)
{
      // Write your code here
}
0
 
clayfoxCommented:
no need for code.

by default you should drag the query fields and the data fields on a view.

Then enter a number in the query field for part number, run the query action and it should return the results in the data portion.

One thing you may be missing is your text field, should have a rule to set the query field part number with the value entered.

That way the query has the data it needs.

aas a test expose all of your fields and play with it and experiment until you have a good understanding. That is what it takes to become an expert. :)
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
nnrscAuthor Commented:
I have played around for a couple of hours and still got nowhere. Can you point me to instructions posted somewhere so that I can follow step by step on how to set up a form  to query external database? I guess I will need a 101 course.

Or take a look at my setup. Maybe I didn't make it right way.

My Access database only has one table. Nothing else.

In my form, I have:
1. a text box for user to enter search values
2. two buttons, Go to execute the search, Reset to set the text box value to 0
3. a repeating table bound to the table in my database

I have tired setting up below rules for the text box alone, the text box and the Go button, and the Go button alone:
1. If value entered in the text box is equal to the PartNuimber field in the table, Query using a data connection
2. If not equal , give a message box saying no matches found

So far I haven't got it work. The repeating table simply does not respond when I hit the Go button.
0
 
clayfoxCommented:
Do you have the data connection to Access set to query on open?
Can you verify it has data in it before you enter something in the text box?

You probably do not need a text box.

Just use main / query data / partno field

enter a value and query and that should return if you have an exact match.

If you want the user to enter part of the number you would have to query the whole table first and then use a filter like I said before.
0
 
nnrscAuthor Commented:
I do have the connection to the table since in one test I was able to get the whole recordset in the source table populated in the repeating table.

If no text box, where users can enter their inputs? The repeating table only shows the Heading row in Preview.
0
 
clayfoxCommented:
Use the query data for the user to enter the query values directly.

Like I have said you can put that on your view and play with different query values and see what you want your query values to be behind the scenes so that users get the information they need.  Then you can work on the UI and make it pretty and controlled.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now