Newbie Questions

Re: Please help - infopath forms are "overwriting" data instead of creating new rows

Desmo — Tue, 01 Dec 2009 21:49:09 GMT

Exactly the retrieve button is optional, only if historical records need to be updated. the submit button appears as long as a date is selected I think that repeating error occurred after I copied the file to upload onto this forum but it's not an issue on the original - It's probably difficult to diagnose this one without seeing the original access file.. -- I think first I need to talk to my associate who is on the west coast that makes these updates - for all i know they could just be accidentally retrieving the current record and over writing it without even knowing it, Otherwise how can a row of data completely disapear -- I'll try to spend more time on it tomorrow. thanks for taking a look!!

Re: Please help - infopath forms are "overwriting" data instead of creating new rows

Hilary Stoupa — Tue, 01 Dec 2009 19:27:03 GMT

Okay. So they only use the Retrieve button if they are trying to update an existing record? I did notice that the repeating section with your controls is unbound, not sure if that is part of the problem you are having, but it is something you'll want to fix.

Re: Please help - infopath forms are "overwriting" data instead of creating new rows

Desmo — Tue, 01 Dec 2009 19:16:30 GMT

Right, well it's a yes and no answer. Each day basically should equal a new record in the table.. that's why i've got the date set to "no duplicates". But we also want the capability to "edit" data for any particular day. So say for example, this evening I go in and add a forecast for tomorrow, thereby created a new record.. But tomorrorow morning I come in and see things are way off and need to correct that forecast, then I'd want to use the form to "retrieve' the current day's data, change and re-submit it.. thus overwriting it. The odd thing that I'm seeing in the table is each day the previous day's date appears to disapear and it is replaced with the current day's date after all the updates are in.

Re: Please help - infopath forms are "overwriting" data instead of creating new rows

Hilary Stoupa — Tue, 01 Dec 2009 18:42:44 GMT

You should always go through the data connection wizard again when you change the database table. You are doing the right thing as far as that's concerned. My concern is with your query. If you are querying for an existing record, then changing it and resubmitting, you will always overwrite that row. Does that make sense? But it seems from your post that is NOT what you want to do, you want to create a new record with every submit... is that correct?

Re: Please help - infopath forms are "overwriting" data instead of creating new rows

Desmo — Tue, 01 Dec 2009 18:26:18 GMT

Thank you for taking a look. Yes, I've found that whenever I make design changes to the database (i.e. change the PK) I need to go through the data connection wizard or else the changes may not be recognized on the form side. - is that a bad idea? The only query field on the form is actually date... and I have a rule set that probably doesn't make sense, but I have the data "date" field set to be equal to the query "date" field. I have the "date" query field on there because the idea is that the user "queries" that given day's date to pull up the information for that day ... as it is possible data for that particular day may have been entered the night before, and the general idea is to give the user the ability to update/ammend to the existing record for that day. I've attached an example..

Re: Please help - infopath forms are "overwriting" data instead of creating new rows

Hilary Stoupa — Tue, 01 Dec 2009 16:33:37 GMT

When you changed the table from using the Date as the PK, did you walk through the data connection wizard again for your main data connection in the form? I'm thinking you must have, or you would have had other errors / odd behaviors.... Are you using any query fields on your form? Do you mind attaching your forms to a reply post? Under the options tab there is a place you can attach a file...

Please help - infopath forms are "overwriting" data instead of creating new rows

Desmo — Tue, 01 Dec 2009 15:16:06 GMT

Hello, to anyone whom might be be able to help. I am relatively new with infopath and access and I have the feeling that the answer to my issue is something obvious that I am missing.

The scenario is that I'm using two infopath forms for work that are connected directly to an access database that holds two tables. Each form is linked to it's respective table. The purpose of these forms is to allow for daily updates from two different work teams (updates involving morning stats etc) Users of the infopath for use the "date picker" field to select the current day's date, then enter in the appropriate metrics for that day. The data picker value is linked to the "date" data field.

Here's the thing, one form works perfectly and the other doesn't - even though I've basically created them equally. The form that works perfectly is form 2. Each day, the user goes in and fills out the morning stats and hit's "submit". Then you can see the updates were submitted and the table shows a clean history of the past several months of each day's report.

What is frustrating is Form 1, because it's not tracking the history of updates. Rather, each time the user submits the data to form one, instead of creating a whole new record, the data is over writing the previous record. The idea I would like to have here is a continuously updated history of stats so that I can eventually use the data to build graphs off of the metrics and such.

I have my suspicions as to what is causing this. Let me explain some of the settings and I'll include shots below as well. First, I have 1 to 1 relationship between the two tables in access for the "date" field. I presume that this setting means that both tables understand that this is a unique value and the same between the two records.

Originally, I also had the "date" field as the primary key field, but that lead to strange behavior in terms of some being able to submit the entries at certain times and others not being able to. (Though this may have been due to constraints I placed on the submit button). Either way, I've got it now so that there is an "autonumber" generator as the primary key for each table. - Thought I haven't created a relationship for them,.. that's up for discussion. I do have the values for the "date" fields set to be indexed so that there are no duplicates....

There's actually a 3rd form we are using that I created which just pulls in all the data from the two tables in a consolidated format, but that is inconsequential since it works fine.. it's just that half of it is blank b/c there is no history from table 1, only from table 2.

So there's the main problem i'm having, and I've about given up... I can't stop data rows from being overwritten. Also, it if helps, one person is putting in updates in the evening for form 1, then a second person adds updates in the morning for the same date. I hope i've been clear as it is a lot to try to put into words, let me know if any more detail is needed.Thank you all

the non working table

the working table...