Need some help setting up a form connected to Access database - InfoPath Dev
in

InfoPath Dev

Use our Google Custom Search for best site search results.

Need some help setting up a form connected to Access database

Last post 07-13-2018 08:09 AM by Hilary Stoupa. 13 replies.
Page 1 of 1 (14 items)
Sort Posts: Previous Next
  • 07-05-2018 08:49 AM

    Need some help setting up a form connected to Access database

     Hi community,

     

    It is my first post. I have used InfoPath before, but only forms linked to SharePoint lists. Now I have an Access database that saves outsourcing activities of the company. I want to design forms that adds new records to the database (new outsourcing proposals).

    Ideally, the next step would be to start an approval process through MS Visio and MS SharePoint Designer and have a senior lead approve. My biggest problem is that I have a database with complex relationships and I want to use list boxes for selecting some outsourcing functions and selecting entities to outsource to.

    As such, I have in each table a primary key and sometimes several foreign keys, since I also have sub-delegation entities and sub-tasks (you see the complexity).


    What would be the best way to set this up? A detailed explanation and walkthrough would be appreciated.

     

    Thanks... 

  • 07-09-2018 08:19 AM In reply to

    Re: Need some help setting up a form connected to Access database

    Have you tried creating a form from the Access db? Is there a specific issue you need more help with? I'm not sure I understand why list boxes are an issue - perhaps you could add some info on what you've tried and what is not working the way you anticipate?
    Hilary Stoupa

  • 07-09-2018 08:36 AM In reply to

    Re: Need some help setting up a form connected to Access database

     Hi Hillary,

     

    I know how to create forms in Access, but I would like to have forms in InfoPath as I want others to fill in these forms. I do not want employees to have to go make changes in the database.

    I chose the database template within InfoPath and connected to my data sources. However, I want to know the proper way to connect to my tables as I cannot add all tables in one connection because some have one-to-many relationships and it throws an error, so I added them separately.

    In any case, I would just like to know how to have a query field to lookup a function name from the functions table and use this to create a new record (a new delegation in the delegation table with its own ID) when the user submits. Therefore, I would have the employee choose a function and entity to outsource to, and when the user submits this creates a new record in the delegations table, since all my tables are connected. Every time I try to do this I choose the function table and function name field to lookup the data  but it says that I should choose a field where I should lookup the data and not where it is stored. How does this work generally, if I have connected tables? What fields do I choose? It seems I cannot just add list boxes or any new fields for that matter when I already connected to data sources? 

     

    Kind regards,

    Jovan 

  • 07-09-2018 08:43 AM In reply to

    Re: Need some help setting up a form connected to Access database

    You should be able to have one to many relationships. Many to many would throw an error. Generally, you'd add an additional secondary receive data connections to tables you wish to use to provide values to a drop down or a list box. Then you'd use that data connection for the options. Does that make sense?
    Hilary Stoupa

  • 07-10-2018 02:17 AM In reply to

    Re: Need some help setting up a form connected to Access database

     Hi Hillary,

     

    Yes I put in new fields which take values from my secondary source. However, when I click on submit I get the error that there is no new data to be submitted. I am not using the data fields as repeating tables, so I guess that is why. Should I make custom rules then?

    How do I do this?

     

    Kind regards,

     

    Jovan 

  • 07-10-2018 08:55 AM In reply to

    Re: Need some help setting up a form connected to Access database

    May I see your form? You can attach it under the Options tab in a reply.
    Hilary Stoupa

  • 07-11-2018 02:32 AM In reply to

    Re: Need some help setting up a form connected to Access database

     Here it is

  • 07-11-2018 08:19 AM In reply to

    Re: Need some help setting up a form connected to Access database

    None of the fields on the form that the user can fill out are bound to data fields in your main data source. Your controls need to be bound to the Access data fields for there to be any new data to submit.
    Hilary Stoupa

  • 07-11-2018 08:32 AM In reply to

    Re: Need some help setting up a form connected to Access database

     Hi Hilary,

     

    I did realize that. So how do I bind them then? My current fields are just new fields I made as a lookup to my secondary data source. Do I need to bind these fields instead to their real data fields?

    How do I make sure the database updates automatically? 

    Kind regards,

     

    Jovan 

  • 07-11-2018 08:59 AM In reply to

    Re: Need some help setting up a form connected to Access database

    Here's a copy of your form - I added a repeating section by dragging the data fields onto the form, and set up the FunctionID as a dropdown w/ rules to populate the name & type - save the file locally, right click and select design to open in design mode. I'm not really sure how you want all this to relate in your form - as I see you have the entity as the first control in the controls you added, but looking at the table structure it appears to be a child of the function.
    Hilary Stoupa

  • 07-12-2018 01:56 AM In reply to

    Re: Need some help setting up a form connected to Access database

     Hi Hilary,

    I have added a screenshot of my database. How do I make these connections properly then? I notice I cannot submit when I have a many to one relationship between one of the tables.

     

    Kind regards,

     

    Jovan  


  • 07-12-2018 08:23 AM In reply to

    Re: Need some help setting up a form connected to Access database

    I think your DB schema may be too complex for InfoPath - I don't see a way you can model the connections you have in your diagram correctly when setting up the db in InfoPath - especially the cases like the Entity Functions table
    Hilary Stoupa

  • 07-13-2018 12:33 AM In reply to

    Re: Need some help setting up a form connected to Access database

     Hi Hilary,

     

    Thank you for your help. I see that InfoPath is great to fill out data fields of a table, but when there is more complex relationships it does not allow for cascading updates to the tables, which is a pity.

    Thanks,

     Jovan 

  • 07-13-2018 08:09 AM In reply to

    Re: Need some help setting up a form connected to Access database

    It will in simpler parent child relationships - like when there is a parent table with one or more child tables - but I think the structure here is beyond what it will support.
    Hilary Stoupa

Page 1 of 1 (14 items)
Copyright © 2003-2018 Qdabra Software. All rights reserved.
View our Terms of Use.