Solved

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

Posted on 2013-01-31
6
207 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

813 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

12 Experts available now in Live!

Get 1:1 Help Now