Query data from an external MS Access database

Posted on 2011-04-20
Last Modified: 2012-05-11
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?


Question by:nnrsc
    LVL 28

    Expert Comment

    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.

    Author Comment

    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
    LVL 28

    Expert Comment

    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. :)

    Author Comment

    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.
    LVL 28

    Expert Comment

    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.

    Author Comment

    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.
    LVL 28

    Accepted Solution

    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.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Before you can digitally sign infopath forms, you must have a digital certificate. Microsoft Certificate Services will need to be enabled on a Windows Server 2008 to facilitate the creation and verification of the digital certifciates on the web ser…
    Digital marketing agencies have encountered both the opportunities and difficulties that emerge from working with a wide-ranging organizations.
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now