Microsoft Access

Re: Relational database Access/Infopath

Hilary Stoupa — Fri, 25 Sep 2009 14:49:26 GMT

I don't think you would be able to do this with out of the box InfoPath functionality. I don't believe that Access has triggers -- SQL Express does, I think, and it is free. You might want to consider moving your DB to SQL. Because, really, a trigger would work nicely for creating a history table.....

Re: Relational database Access/Infopath

Unreal1998 — Mon, 21 Sep 2009 16:22:18 GMT

I'm not getting a particular error per say. I just don't know what is the simplest way to be able to track the changes in the employee table such as when an employee changes office, department, name or unit. Basically I need to track the historical data for the Employee table. So now that I think about it, the EMP table should be the primary table and a historical EMP table (child) would be the repeating table.

Hilary, you seem to know InfoPath well :). He's a question: Is there anyway I can have a main EMP table with EMPID (primary key) and have an EMP History table with RecordID (primary key) and EMPID( foreign key) and when I submit a change in the main EMP table a new row is copied in the EMP History table? Is this something I need to do in Access or can I do this in InfoPath?

Re: Relational database Access/Infopath

Hilary Stoupa — Mon, 21 Sep 2009 16:04:48 GMT

It sounds like you have it set up correctly. What is the exact error you are getting?

Re: Relational database Access/Infopath

Unreal1998 — Mon, 21 Sep 2009 14:03:58 GMT

It's a one to many relationship(Unit-EMP table). I have both tables linked properly in the form. I just don't know how to set up form so I can update the unit of an employee as he/she moves around units. I believe the EMP table should be repeating since I want to update many records.

Re: Relational database Access/Infopath

Hilary Stoupa — Sun, 20 Sep 2009 14:28:18 GMT

So is the Unit to Employee relationship one to many? When you set up the main data connection in your form, are you setting the Unit table to not be repeating?

Relational database Access/Infopath

Unreal1998 — Fri, 18 Sep 2009 18:53:05 GMT

I have an Access 2003 database linked to Infopath 2003 and I have the following relationship. Unit tbl has many Employees(EMP tbl). The Unit # is the Primary Key and the EMP tbl has the EMP # as the Primary Key and the Unit # as the Foreign Key. I have both tables linked in the InfoPath form. My question is the following. I have a repeating table set up and I want to make chances to the Unit for each employee as they move from one department to another. How do I accomplish that? I keep getting this "unable to submit error." Something like "Cannot submit blank row..."

Can anyone advice on the best approach to setting up this relational database. I'm so close to just going with a single EMP table and fighting it out with queries at the end of the day.