Repeating data

[Megacitizen]

I have a series of subforms to record the number of occurrences of specific actions over a set number of weeks, one subform equals one week.

My issue is that when I put a number into any of the fields in the first subform, that number is automatically repeated in all 11 subforms for that particular action. In reality, there are likely to be different numbers for each action each week.

How do I code the subforms to ensure each sf can show unique data to the others?

 

[Dmak]

Can you upload a screenshot?

 

[Severin]

You need a year field and a week field in your activities table. Then you need to change the record source on your week subforms from activities to a query that filters the specific week.

...and then you need to remember that you need to add the fields to the week subforms. You can set visible to false and then set the default value for the week and for the year.

 

[Megacitizen]

QUOTE=Severin;1193916]You need a year field and a week field in your activities table. Then you need to change the record source on your week subforms from activities to a query that filters the specific week.

...and then you need to remember that you need to add the fields to the week subforms. You can set visible to false and then set the default value for the week and for the year.[/QUOTE]

Before I start altering my tables (with a sledgehammer, knowing me) do I need just the one Week field in my Activities table, or one for each relevant subform? - 11 in total. All the subforms in question are already named & titled according to the week they refer to.

I've had to rename the Week field "WeekNo" to get it through Access naming conventions. Not quite sure how this would affect the overall picture.

View attachment Megacities Stats.accdb

 

[Megacitizen]

Now I'm really confused (but then that's not too difficult to achieve).

Does my query have just the specific week or does it include the other fields in the relevant form?

 

[Severin]

Hi, when I get a moment I will have a look!~)

 

[Severin]

Go through your week subforms (I've done the first 3) and do the following:

1. Change the Recordsource on the forms to Activities
2. Add the to yellow fields, it's a hidden year field and a hidden weekno field.

In the following I use week1 as example:
3. Add Default value 2012 to YearInCity to property on field
4. Add Default value 1 to WeekNo to property on field
5. Add YearInCity="2012" and WeekNo="1" to Filter property on the form

Set the following properties on the form:
6. Set Filter On Load to Yes
7. Set Cycle property to Current Record
8. Set Record Selectors and Navigation Buttons to No
9. Set Scroll Bars to Neither

A nice tip is double-clicking to change property settings!~)

 

[Severin]

By the way I changed from filtering through a query to filtering directly on the form. I think that will be easier for you to update, when you have to add new weeks!~)

 

[Megacitizen]

Thanks for your help, Severin, much appreciated.

I have spent most of the morning working bit by bit on the forms, but we're having Server issues at the moment (again!!) so I have no access to the DB until it's sorted and back up running. This does mean I may have to come in on Monday to complete the job and train a couple of peeps on using the DB as designed. I was hoping to have Monday off to pack before flying home to UK on Tuesday. Such is life.

 

[Galaxiom]

You really should not need all those subforms as individual forms designs.

Typically they would be implimented as instances of the same form with different RecordSources and Caption properties applied as they load.

 

[Megacitizen]

Quick question.

The YearInCity field is currently set to 2012, Would I be right in saying it needs to be changed to 2014 when we start our data entry for our next Focus City? We currently work in one city every other year.

 

[Severin]

yes, that's right!~)

 

[Megacitizen]

Right, I'll have to make a note of that and make a few bods aware of the score.

The server is back up and I've now completed the form. I can now train a few inputters in the intricacies of my masterpiece . Just got to wait for the first ones to get back from London this weekend.

Thanks to everyone for their help and support over the past few months, it's been much appreciated. A trip to the Virtual Bar (which board is that on again ) is on the cards - my shout !!!

Severin, I can get you a real McCoy though when I'm in London in a few weeks time !!!

 

[Megacitizen]

Just when I thought it was all completed, I've just discovered the following fault.

When I move from record to record on my main form, I've noticed the subforms recording the Stats are remaining as they are. Also, if I try overwriting them as a new record, they alter on all previous forms.

I need them blanking out so that the user can key in the next set of stats on the next Main form.

The last time I had this issue, with a combobox text field, the issue was caused by a lack of relationships between the relevant tables. Would this be a likely cause in this instance?

 

[Severin]

yes... a relation to the id on the main form..

Upload the database as it is now and I will show you how to do this!~)

 

[Severin]

..actually all you need to do is add a field to the Activities table called: SchoolID make the data type number and leave it at standard setting.

Then go into the design of the Statistics Form. All the week subforms has to have their Link Master Fields property and Link Child Fields property set to: SchoolID

Then you should be ready to go..

See you in London!~)

 

[Megacitizen]

..actually all you need to do is add a field to the Activities table called: SchoolID make the data type number and leave it at standard setting.

Then go into the design of the Statistics Form. All the week subforms has to have their Link Master Fields property and Link Child Fields property set to: SchoolID

Then you should be ready to go..

See you in London!~)

Thanks for that, will have to wait until Monday to sort it all out, our server is down again and will be all weekend so I can't access it on the remote until then.

 

[Megacitizen]

OK, latest issue!!!

Getting the subforms to accept unique data worked on Friday but not today, whilst refreshing the forms to blank when moving to next record does work now.

When I enter a figure into the first subform, it enters ok. When I enter a different figure into the 2nd SF, the first figure automatically changes to match the first figure. When I enter a figure into the 3rd SF, the corresponding fields for SF's 4-11 are automatically filled with that number, whilst the first two SFs remain unchanged. When I move onto SF 4 and onwards, ALL fields, including SFs 1 & 2 are altered. I have not changed any codings from what I entered on Friday.

What's happening

View attachment Megacities Stats.accdb

 

[kipcliff]

I am not seeing a field for dates in the db you posted.

I would have structured a table like so:

tblActivityRecord
fkSchoolID
ActivityDate
fkActivityID
ActivityQty

Regardless, you need to account for dates if you want your subforms to act right.

The more I look at it, the more it looks like a form that is built to function as a report. What is the nature of your input?

 

[kipcliff]

Well, let's just keep it quick and dirty for now. I looked at the copy that Severin attached. It looks like you may have changed an older version, because the most recent db you attached has none of Severin's changes in it. Find the correct version, add your last set of changes, and you should be fine.