Need to make code loop

[Leesha]

Hi,
I have a form that has the default view as "continuous forms". The code below is run on the OnLoad event. It is only looking at the [storename] in first row and then defines whether [txtNew] is enabled for all of the remaining records vs looking at each individual record. I"m thinking I need to make the code loop? If this is the case I'm not sure how to set it up.

Thanks,
Leesha

If IsNull(DLookup("walmart", "qryImportCloseNewStore")) Then
MsgBox "There are no stores with closed days in this import"
Exit Sub



ElseIf Me.StoreName = "Auntie Anne's" Then
Me.txtNew.Enabled = False
ElseIf Me.StoreName <> "Auntie Anne's" Then
Me.txtNew.Enabled = True
End If

[HansV]

If you set the Enabled property of a control using VBA code, it will apply to all records at once. Looping through the records won't help.

To make the Enabled property of txtNew depend on the record, you need to use Conditional Formatting. This is actually easier, since it doesn't require code.
- Open the form in design view.
- Select the txtNew text box.
- Make sure that the Enabled property (in the Data tab of the Property Sheet) is set to Yes (this is the default setting).
- Activate the Format tab of the ribbon (under Form Design Tools).
- Click Conditional Formatting.
- Click New Rule.
- Select 'Expression Is' from the dropdown.
- Enter the following in the box next to it:

[StoreName]="Auntie Anne's"

- Click the 'Enabled' button.

S0101.png

- Click OK, then OK again.

[Leesha]

Wow Han's I didn't know I could do this! I would never thought of using conditional formatting. The only problem is that the conditional formatting is not enabled. txtNew is actually a check box. At one point it was a text box and since I had other code related to it I didn't change the name from txtnew to chkNew. Does conditional formatting not apply to a check box?
Thanks!
Leesha

[HansV]

Sadly, no. Conditional formatting only applies to text boxes and combo boxes.

Perhaps you could replace txtNew with a combo box with the following properties:

- Column Count: 2
- Column Widths: 0
- Control Source: the same as the original txtNew
- Row Source Type: Value List
- Row Source: -1;Yes;0;No

You can apply Conditional Formatting to this combo box as described in my previous reply.

[Leesha]

Thanks Han's! This did the trick. Since I'm always striving to understand what you give me vs just copy and use it I'm wondering how setting the column width to 0 allowed the second column to show. Typically when I have two columns I would set the width to 0";1" to hide the first column and only show the second column. I expected that with column width set to 0, nothing would show.
Thanks!
Leesha

[HansV]

Specifying 0 (or 0") tells Access to set the width of the first column to 0 (thus hiding it), and to use the default width for the second column (keeping it visible). So the effect is the same as using 0";1".

[Leesha]

Cool! Thanks!