Solved

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

Posted on 2013-01-31
6
220 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 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel Total at footer of invoice 5 22
exchange, office 365 13 37
How do a DCount on a report 1 17
Overwriting Named Ranges 13 34
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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 view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

730 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