keeping track of data cleaning 1

[6ftAndClean]

Are you at work over this festive period? Do you need a distraction from the mundane and workaday? Be charitable and take pity on a befuddled Access newbie!

I have a database of 5 tables. They refer to geographical information for the area covered by the council I work for. They are 'Ward', 'Area' (a subdivision of a Ward), 'Streets' and 'Street_Details' (information about each street). I am involved in data cleaning another (much larger) database containing information about the properties on streets in our area. My database is to be used to keep a track on the progress made in the data cleaning process.

The table 'Street_Details' mainly comprises of a number of yes/no fields which, via checkboxes in a form, are ticked to indicate work completed on data cleaning in the other larger database. When all the check boxes are ticked a final field called 'Cleaning Complete' is set to true via small bit of VB code. When this final field becomes true an image in the form becomes visible to provide a clear indication to the user that data cleaning is complete. So far so good.

The bit I'm stuck on is as follows... Having ticked all the boxes and been rewarded with a 'Data Cleaning Complete' message becoming visible the user then exits the form to return to a previous form listing all the streets for the area currently being worked on. I would like to be able to reflect the data cleaning status of each street in this form (actually a subform). How can I do this?

1. Is there a way to make the Control Source of a check box in this (sub-)form be 'Cleaning Complete' from the 'Street_Details' table? I can't get this to work.

2. Alternatively, how can I get the 'Cleaning Complete' field in the table 'Street_Details' which is the last table in a relationship tree to update the identically named field in the table 'Streets' which is the next table up the tree? They have a 1-to-1 relationship but I can't see how to achieve this. Do I need to employ a Query to achieve this effect? I tried this with a Select Query but again with no joy.

What I would ultimately like to do is be able to register completion of data cleaning on each street, then when all streets in an Area are cleaned for the Area to be registered as completed and finally when all Areas in a Ward are complete for the Ward to register as completed/data cleaned. Does this help? Can a strategy be devised to achieve this?

Thanks in advance for your help.

 

[JonFer]

If you are already running VB code to set the Cleaning Complete for the Street_Details table, just add another step to update this field in the Street table for the matching street key. This could be a single line which runs an update query whose criteria for the street key is the key on the current record of the Street_Details form.

For the other forms, use a query instead of a table as the record source. Add a field to the query whose definition is a sub-query which returns True if all records are complete for all streets included in the area you are at.

For the Area query, it would be like this:

Select Area,
Not Exists (Select * from Streets as a
Where a.Area=Areas.Area and a.Complete=False) as Complete
From Areas

It gets more complicated for the Wards because you have to join two tables in the sub-query but it can be done.

 

[6ftAndClean]

Cheers for the solution JonFer. I had previously tried to implement a similar method for updating the Streets table but with no success. Your method sounds bang on the money. I am new to Access and especially VB and whilst I see what it is I am trying to achieve I haven't managed to implement your solution yet. Can you guide me a bit more closely through the necessary steps please.

I have set up a query called 'Street_Clean' with:

Field - 'Cleaning Complete?'
Table - 'Details'
Update to - '[Streets]![Cleaning complete?]'
Criteria - '[Forms]![Details]![USRN]'

Is this correct?

As for the code to trigger this query... The checkboxes in the form 'Details' call the following code in a module:

*********************************************
Public Sub checkStatus()

'create variables
Dim dataClean As Boolean
Dim siteVisit As Boolean

'check status of all 9 check boxes
'if all 9 checkboxes are ticked then
'data cleaning of this record is complete
'iand chkBox12 is set to true
Form_Details.chkBox12 = True
For i = 1 To 9
If Form_Details("chkBox" & i) Then
Else
Form_Details.chkBox12 = False
i = 9 + 1
End If
Next i

'pick up value of chkBox12 to set
'value of variable dataClean
If Form_Details.chkBox12 = False Then
dataClean = False
Else
dataClean = True
End If

'chkBox10 is an additional checkbox that
'is ticked if a site visit is required
'if ticked variable siteVisit is True
If Form_Details.chkBox10 = False Then
siteVisit = False
Else
siteVisit = True
End If

'set messages to invisible
Form_Details.imageClean.Visible = False
Form_Details.imageSite.Visible = False

'logic argument to set visiblity of messages
'results can be:
'- no message
'- data cleaned
'- site visit required
'N.B. a street cannot be data clean if a site visit required
If dataClean = True And siteVisit = False Then
Form_Details.imageClean.Visible = True
ElseIf dataClean = True And siteVisit = True Then
Form_Details.imageSite.Visible = True
ElseIf dataClean = False And siteVisit = True Then
Form_Details.imageSite.Visible = True
Else

End If

End Sub

*********************************************

The necessary bit of code to run the query should be easily plugged into the end of the existing code. The question is what bit of code?

I have spent the past 2 hours wading through the help files but have not been able to put together any code that stands a chance of running my 'Street_Clean' update query. Please hold my hand a bit on this one and give me some clear pointers on how to go about doing this.

Thanks for your help.

 

[JonFer]

Your query should be based on table Streets since that is what you are updating. Update the "Complete" field to Forms!Form_Details.chkbox12 where the StreetKeyID = Forms!Form_Details!StreetKeyID. I assume you have some key field in your streets table that is also in the street details table. You could also add the criteria that "Complete" is not already equal to Forms!Form_Details.chkbox12 to avoid unnecessary updates. This way, the query will set the Street "Complete" field to True or False based on the current value of chkbox12. A Street could be marked Complete but if a detail is later unchecked, you'd need the Complete value to go back to False.

Modify this part of your code (I simplified your logic to set the dataClean variable, too):

'pick up value of chkBox12 to set
'value of variable dataClean

dataClean = Form_Details.chkBox12
DoCmd.OpenQuery "Street_Clean"

It sounds like you are running this when each checkbox is updated. If the checkboxes are the only fields you update on the form, I think you can run your code with the BeforeUpdate event on the Form instead.

 

[6ftAndClean]

Cheers for the pointers. It still took a bit of doing (its a steep learning curve I'm on) but I got the update query part working well now. I'm now moving on to the bit where I check that all streets in an area are cleaned or not. Thanks for all your advice. I may yet have to be back for more (although will probably need to be a new thread being a new subject).

Have a star!