InfoPath Dev

I have a form with a data connection (to an Access database with 7 tables). I cannot submit the form if I add an item with fields that are not in the 'main'/'primary' table.

I can change and submit data from the main table (any time), and in fields that link to other tables - (if there is data already there). However when I add a new item (new repeating section), I cannot submit the form. This worked ok for me when I built a test form.

I get a data validation error. There is no indication which field or where the error is coming from.

Can anyone help?

-- Jay Whittaker --

When previewing the form, I believe if you click Tools (or one of the other menu options) and then click "Go to next error", InfoPath will tell you where the error is. If the error is not in the current view, you can expand the detail and it will show you where the error is.

I tried that (go to next error) to get more detail. It tells me that in the ID fields (the fields that I use to link the tables) that only integers are allowed. The data type in the connection is recorded as optional_int, the type in the database source is int (length 4). This should work. I am not sure where this is coming from. I had it working in a trial solution that I built. Just not in the real thing.

-- Jay Whittaker --

I have now changed my source database and remade the database connection, however the field attributes still show as Optional_int. I would think that this is ok, but its worth a try. I find it strange that two microsoft products are so different. SQL server changes the data types of the original access database and infopath needs its own rules and types to operate. The worst thing is that I cannot change the types - they are locked when imported, to solve this problem. Anyone have any ideas?

-- Jay Whittaker --

Another update,
I created an entirely new form linked to the database. It seemed to work when adding a totally new item (ID field is auto-generated), however when adding a new item to a list of items that already exist, the ID field remains blank. I checked this on the SQL server and the ID field does not auto generate. I cannot change the settings to make it do so. I am unsure at this stage whether the problem is in Infopath or SQL server, Due to the inconsistent nature of the problem when testing.

-- Jay Whittaker --

Well,
It looks like I have solved my own problem.
I changed the ID field in SQL server to be an 'identity' field. This meant that both primary and foreign keys were optional_int type. It now works fine. Seems that the import from Access to SQL server is not as effective as it could be.

-- Jay Whittaker --