Solved

Creating a form to show all locations (for a particular year only)

Posted on 2013-01-31
6
189 Views
Last Modified: 2013-02-20
hi Folks
As always would appreciate your help with this...
I have attached a database. This is what I want..
The form (Painting Schedule Central block sub-form) - I only want to show the current year's data, but I want all locations showing so that the user sees a full list of all the locations and can then pick out what locations during what months have been painted. However the complete list of locations are listed in the tblLocations. How can I set this form up (Painting Schedule Central block sub-form) so that
(a) it only shows the current year's  data (b) it is easy to quickly add new locations with ID and description to this form (and these new locations and descriptions to the tblLocations) .
thanks as always
Painting-vers-EE.accdb
0
Comment
Question by:agwalsh
  • 3
  • 3
6 Comments
 
LVL 84
ID: 38841884
I only want to show the current year's data, but I want all locations showing so that the user sees a full list of all the locations and can then pick out what locations during what months have been painted.
Do you mean you want to see ALL locations in a grid style (as you seem to be showing in your subform), regardless of whether they are in the Painting Schedule Central Block table? If so, then the only way to do that is to use temporary table.

So for example if you want to show all data for 2011, you would build a temporary table that include ALL locations, and a column for each month for 2011.

You'd then have to modify the records in that table, based on your STORED data (i.e. the data in the Painting Schedule Central Block table) - so you'd update each record in the temp table to indicate if they already have data for that year:

Dim rst As DAO.Recordset
dim rstTemp as DAO.Recordset

Set rstTemp = Currentdb.OpenRecordset("SELECT * FROM MyTempTable')

Do Until rstTemp.EOF
  Set rst = Currentdb.OpennRecordset("SELECT * FROM [Painting Schedule Central Block] WHERE LocationID=" & rstTemp("LocationID") " AND Year=" & YourYearValue)
  If Not (rst.EOF and rst.BOF) Then
    rstTemp("January") = rst("January")
    rstTemp("February") = rst("February")
    etc etc
  End If

  rstTemp.MoveNext
Loop


You'd then base your subform on the temp table. When the user has finished editing records, you'd need to move the data from the Temp table back into the live table.

the reason for these convoluted manipulations is due to the way your data is stored. If you could store the data in a more normalized fashion, you'd find things like this to be much easier. You'll also find that you'll have more and more issues down the road (i.e. when writing reports and such) if you don't go ahead and bite the bullet and normalize the tables now.
0
 

Author Comment

by:agwalsh
ID: 38854163
@lsmconsulting. - This is the way the perso  wants to see it..they want to see all the locations at one go and then just add painted locations on the fly...

"Do you mean you want to see ALL locations in a grid style (as you seem to be showing in your subform), regardless of whether they are in the Painting Schedule Central Block table? If so, then the only way to do that is to use temporary table."
So to take your suggestion on board of normalising the tables - one table for location and then a sub-form for each painting transaction..I presume..how can I normalise it and also get this format? thank you
0
 
LVL 84
ID: 38854263
one table for location and then a sub-form for each painting transaction..I presume..how can I normalise it and also get this format?
Essentially that is correct. We'd have to know more about the usage of the database to fully understand the best way to structure it, however.

Even with a normalized structure you'd have to resort to a temporary table to get the format you want, unless you simply store a new record for every Location for each year, and store a new (blank) record for each "transaction".
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:agwalsh
ID: 38854448
Alternatively I could just tell them that they have to enter new locations via the new  locations form and then it would be picked up in the sub-form...
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 38854476
Essentially that's correct. If you add a new Location, it would show up - but it would not show up for different Years. In order to do that, you'd have to add a new record to the [Painting Schedule Central Block] table for EACH Year, for EACH Location. Then, when you filter for a specific Year, the form would show all Locations for that year.

I also noticed some data issues in that table. Referring to the two records for the Location identified as "B" - the LocationID for those two records are different. I'm not sure if this is just a typo, or if this is test data that does not accurately represent live data, but the LocationID should be the marker for the Location. There's also a "PaintingID" field in that form ... not sure what that is.

So to summarize - yes, you could just enter a new Location, but in order to use the grid layout you'd also have to enter a new record in the [Painting Schedule Central Block] for that Location. You'd also need to enter a NEW record in that table for each Location every year.
0
 

Author Closing Comment

by:agwalsh
ID: 38908882
Yes, get the user to enter the details in the form :-)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now