InfoPath Dev

Hello,

I posted this in the General forum, but I thought I would repost here for different input...

I am relatively new to using infopath. Here is what I am trying to accomplish:

I have a database with a table of Part Numbers and related information such as description, revision, etc. The part numbers are keyed with a unique ID and the part number values themselves cannot repeat (database field rule). This is a Document/Revision Control database.

I have an Infopath request form that is setup as a document workflow. It has a repeating table for part numbers and revision description, etc.

What I want is the ability for the repeating table to:

1. Add new records to the part number table that do not exist.

2. Update exisiting records, only modifying certain fields such as revision description, etc.

Setup for 1 is easy and done.

But, when I try to add a part number that already exists, I get an error upon submit that tells me that I cannot add it because it would violate the database rule of reating values for the [PartNo] field.

Here is an example of the database tables:

RequestTable (for the general form info such as due date, project info and who the form gets routed to for approval):

[RequestID] [Requestor] [DueDate] ...

PartNumbersTable:

[P/NID] [RequestID] [PartNo] [Description] [Type] [Rev] [NextRev] [RevDesc] [Matl] ....

All ID fields are unique keys and [PartNo] is indexed with no duplicates allowed.

The repeating table is completely based on the PartNumbersTable, with exception of course the ID fields.

As you can see, it is pretty straight forward for adding records...no real problem there. The only problem is inputting data that already exists but some fields need updating such as [RevDesc] for existing records that are being revised.

I am trying to avoid creating another form for updating records. I tried creating a duplicated PartNumbersTable2 in the database, but that doesn't update the main PartNumbersTable which. I have looked into using a query field for PartNo, but that cannot be in a repeating table.

The desired result is a form with repeating table, and all the user needs to do is input a part number...if record is new then the remaining fields are blank...if record exists then certain records autopopulate and the user just updates required fields like [NextRev] and [RevDesc]...

Any advice?

Thanks again Patrick. For clarity, I am just connected to an Access 2007 Database and have no real knowlege of manipulating SQL databases or dealing with SQL Management Studio. I really am looking for simple and easy and something that does not cost much more time or money. The PartNumbersTable that I am trying to deal with is used by other tables in the database. I cannot modify the key or modify the rules of the PartNo field for database integrity. I am merely adding a method of inputting and updating data, and giving it an email based workflow.

I guess the next question is...will a repeating table even work for what I am trying to accomplish?

Ok...

So what I have found out is I need a way to do a repetitive query in the same form. How to accomplish this?

I tried binding the first field in my repeating table (PartNumber) to a query field, but wouldn't you know it filled out all data fields on the form...and when I inserted another row to the repeating table, it autopopulated with the previous row's information.

I am also in the process of deciding just how much form information I really need to store in the database. Alot of the information is for workflow tracking...and tied to the database as a rolling action item list...any ideas on how to better accomplish this?