InfoPath Dev

Problem 1: not sure how to add new records into the table

thanks.

Hello and welcome!

For problem 1, in order to submit to Access, you need to have based the form off the database when you initially designed it. If you did that, and the controls on the form are bound to the fields in the datafields group in the main data source, you should be able to submit to your table, which will add new records.

For problem 2, you can add another field, a Boolean, called isSubmitted. You can add a rule to your submit button to set isSubmitted to true when the button is clicked. You can set conditional formatting on your button to hide if if isSubmitted is true. InfoPath 2007 has the option when you set up email submit to either attach the form or just include the contents, but I don't recall if that was available in 2003.

Hope this helps, if it isn't clear enough or you need further help, let me know, okay?

Thanks for the quick reply.

Sorry I'm not very good at this. Do mean

Problem 1: At the button properties i change the action to new record?  I have only one button label as submit. When it's click it will write to the database and also send a copy of the form to the user as comfirmation.

Problem 2: issubmitted an extra field in my DB OR a field i should declare in the function submit::OnClick(eventObj)?!? where would i fid this hide

Am not to good at coding. any refrence.

Thanks.

Hello:

No, I don't think anything you are doing will require code. First, let's add that field.

1. Change your task pane to display the Data Source task pane
2. Select the main data source
3. Right click the root folder (probably myFields)
4. Select Add
5. Name the field isSubmitted (or another name, if you like)
6. Change the Data type  to True/false (boolean)
7. Select OK

Now you have another field that you can use to know whether the form has been submitted. Next, let's set some rules.

1. Right click the button you are using for submitting
2. Change the Action to Submit
3. Select the Submit Options button
4. Select Perform custom action using Rules
5. Select the Rules button
6. Select the Add button
7. Give your Rule a meaningful name, like "Submit to database"
8. Select Add Action ...
9. Change the action to Submit using a data connection
10. Make sure the data connection is your main data connection
11. Select OK
12. Select OK  again

This will set up the submission to the database. Next, we'll set a rule to set the value of isSubmitted.

1. Select the Add button again to add another rule
2. Give your rule a meaningful name, like "Set isSubmitted"
3. Select Add Action ...
4. Change the action to Set a field's value
5. Select your isSubmitted field in the field area
6. Type the word true in the value area
7. Select OK
8. Select OK again

Finally, we add the rule for email submission. All of these actions could have been added to a single rule, but sometimes when you are new, it is easier to only have a single action in every rule. That way if something doesn't work the way you anticipate, you can deselect different rules to track down the problem. So, to submit our form to email:

1. Select the Add button again to add another rule
2. Give your rule a meaningful name, like "Send email"
3. Select Add Action ...
Change the action to Submit using a data connection
Make sure the data connection is your email submit connection
Select OK
Select OK  again, and once more to close that window

For the grand finale, we'll set that conditional formatting on the button.

1. In the Button Properties, select the Display tab
2. Select the Conditional Formatting  button
3. Select the Add button
4. In the first dropdown, select your isSubmitted field
5. The second dropdown should say "Is equal to"
6. In the third dropdown, select Type text
7. Type in true
8. Select the Hide this control checkbox

Hi Hilary,

in you second set of steps, step 10 says: Make sure the data connection is your main data connection.

How do I do that? how do I set up a main data connection to write data to my SQL Server 2008 DB?

The choices I am given when I try to set one up in the data connection wizard under "How do you want to submit your data?" are:

To a Web service

To a document library on a SharePoint site

As an e-mail message

To the hosting environment, such as an ASP.NET page or hosting application

Well none of them sound correct, I tried the last one and there was no place to setup DB connection information. Do I need to write a web service that will handle the handshake of data to my SQL Server 2008 DB? Sounds combersome, why is it not built into InfoPath?

What am I doing wrong?

Thanks

Hi, David:

These steps were written out with the intent that form will have been designed off the database, in order to use InfoPath's built in submit to SQL or Access functionality. InfoPath does not provide the option to submit to database otherwise, I'm afraid.

If you walk through your main data connection again, there will be a message in the last window of the wizard as to why you can't enable submit. It may be your table(s) have a data type that InfoPath doesn't care for, or no primary key ... So under Data Connections, select your main data connection and select Modify, and walk through the wizard again.

Ok I am getting somewhere. I added a primary key of 3 fields. I was able to enable submit. when I put a record in with the exact 3 fields that already exist in the db it works as expected - it does not let me put in a dup record.

But when I change the last of the 3 fields to be different - so now the primary 3 part key is unique - it still tells me that there is nothing to put in the db because it already exists even though it does not.

Any ideas?

thanks

Hi Hilary,

Looks like there are 2 ways it should work. they way you list above and there is also an option under "Send for data to a single destination" of Database.

but neither of the 2 methods really work. no mater what I type into my 3 fields that are part of my 3 part primary key InfoPath thinks the record already exists even though it does not.

Does InfoPath have a multi-part primary key bug?

Thanks

David Wipf

I'm a little bit confused, I'm afraid. What are you trying to do? Create a new record by modifying the key of an existing record?

I have a table with 3 fields, every record in the DB should be unique, thus the 3 part primary key.

I want the InfoPath form to allow the user to enter new records into the table.

So they type in the 3 fields and hit the submit button.

If the record already exists it should say so and not put the duplicate record into the DB.

If the record does not exist it should add it to the table in the DB.

No matter how I set it up InfoPath thinks the record already exists even though it does not.

What happens if you try using the "new record" feature built into IP? That is, add a button that has the action of "new record"?

I just changed the button from "Submit" to "New Record". there is noplace to hookup a db connection.

I hit the button and it does nothing, does not complain, does nto say anything.

And does not put the record into the DB.

A lot depends on how your form is set up. When you create a new blank form off a database, there will be a few buttons added to the blank form with some guidance text to help you get started. New Record will clear your fields bound to the datafields group in your main data source, so that you can fill them out and then use the Submit command to submit to the DB. Here's a link to a Microsoft Office online article that may help you.