?
Solved

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

Posted on 2013-01-31
6
Medium Priority
?
232 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 85
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 85
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

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 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

777 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