Pre-filling values on a form

[ToddLower]

I have a form, the table is a list of Yes/No fields. If you answer the first one Yes I would like the rest of the associated fields to be marked Yes. If you answer No to the first field I would like the other fields to be able to be filled in.

This table are the results of an inspection.

1. In Compliance Yes/No


2. Is The Grease Interceptor accessible for Inspection? Yes/No
3. Is the Interceptor within its capacity? Yes/No
4. Does the Sample Box have Excessive Oil and Grease? Yes/No
5. Is the Discharge (Effluent) Line Clear? Yes/No
6. Are the Baffle Tubes Plugged, Submerged, Damaged or Missing? Yes/No

I have tried a few things but haven't had any luck so far. If I use an expression in the control source field I am no longer able to manually input a value for the field. I tried using an expression in the default value but it doesn't seem to update when I enter a value in the first field.

Any suggestions would be a great help.

[Micron]

Several methods if this is about auto filling a form. One way could be via using the AfterUpdate event of the first control. The logic would then be

If Me.first = -1 Then
  With Me
    .chk2 = -1 <<can use True instead of -1
    .chk3 = -1
    .etc
  End with
End If

I prefer to loop over controls so that if I have to add a field/control to support an additional question I don't have to alter this code. To do that I'd use the control Tag property and loop over all the checkboxes (or whatever they are) and if the tag holds the value I designed it with, set the value of the control accordingly.

[ToddLower]

Thank you for your help.
It did take me a bit to implement the solution in as much as I haven't done any VBA coding or any coding at all in about 30 years.
In the end it worked a treat. The form will be much more usable now that it will auto fill those fields associated with an "in compliance" inspection.

[Micron]

If interested, you could post your code solution for review. Have you considered 'what if' - the user clicks the box when they didn't mean to and then they uncheck it. What then?

[ToddLower]

I tried to post a screen shot of the code, but it comes out too small to read. I have done a copy/paste after the image. Not as pretty but it can be read in any case.

I did consider adding the removal if you deselect the check box, but in the end I decided it might be nice to be able to select all of the items and then deselect just the item that the business had failed.

The biggest difficulty I had with the coding was understanding the naming convention for the fields (variables). For example I have a field named "BMP (Compliance)" which is converted to "BMP__Compliance_" in VBA.



Option Compare Database




Private Sub BMP__Compliance__AfterUpdate()
If BMP__Compliance_ = True Then
With BMP__Compliance_
Food_Grinder = True
Collection_Log = True
Hood_Log = True
Training_Log = True
Drain_Screens = True
Food_Waste_Practices = True
Dry_Wiping_Practices = True
Emerg_Spill_Mat = True
posters = True
End With
End If
End Sub


Private Sub GCD__Compliance__AfterUpdate()
If GCD__Compliance_ = True Then
With GCD__Compliance_
GCD_accessible = True
GCD_Capacity = True
Sample_Box = True
Effluent_Line_Clear = True
Baffle_Tubes = True
End With
End If

[ToddLower]

What the heck? That is not what it looked like in the reply window when I wrote the response.

And again, thank you for your help with this.

[Welshgasman]

What the heck? That is not what it looked like in the reply window when I wrote the response.

And again, thank you for your help with this.

Put code tags around the code.
Not sure how that With block works

[Micron]

Use code tags (# on posting toolbar) with indentation and not pics of code:

Option Compare Database

Private Sub BMP__Compliance__AfterUpdate()
    If BMP__Compliance_ = True Then
        With BMP__Compliance_
            Food_Grinder = True
            Collection_Log = True
            Hood_Log = True
            Training_Log = True
            Drain_Screens = True
            Food_Waste_Practices = True
            Dry_Wiping_Practices = True
            Emerg_Spill_Mat = True
            posters = True
        End With
    End If
End Sub


Private Sub GCD__Compliance__AfterUpdate()
    If GCD__Compliance_ = True Then
        With GCD__Compliance_
            GCD_accessible = True
            GCD_Capacity = True
            Sample_Box = True
            Effluent_Line_Clear = True
            Baffle_Tubes = True
        End With
    End If

As I said previously, if you need to add a question/aspect you have to revise this code as well as edit form design. If you make use of the control Tag property it is so much easier. I have to go out now so if you want to explore that method, select all of these controls in design view and put a word in the Tag property (forget which tab of the property sheet it is on) that means something to you. When I get back I'll see what's up here.

Do not use special characters (except for underscore) and no spaces in object names. Follow some sort of naming convention. No time like the present to get on board with that.
Naming conventions - http://access.mvps.org/access/general/gen0012.htm
https://www.access-programmers.co.uk...d.php?t=225837

What not to use in names
- http://allenbrowne.com/AppIssueBadWord.html