Tricky Dynamic Form for Updating Existing Data

[JoeM]

I have a query that returns data in a layout similar to what is shown below.

Company	Form Name	Receives Form?	Date Form Complete	Notes	Autonum
ABC Co.	Form 123	Yes	8/16/2012	Faxed copy to client	1
ABC Co.	Form 456	No			2
ABC Co.	Form 789	No			3
XYZ Inc.	Form 123	No			4
XYZ Inc.	Form 456	No			5
XYZ Inc.	Form 789	Yes	7/17/2012		6

I am trying to create a dynamic form where users can only update existing records. Essentially, there is a record selection form, where the user will select which client they wish to update (i.e. ABC Co.). I then have VBA code to build the SQL code to select just that client's record from the above query (and that is used as the Control Source of my Form).

So, in the Form Header section of the Form, I just show the Company Name. The other four fields are placed in the Details section (not displaying the Autonum field). Since ABC Co. has three records, there are three records returned in the Detail section.

Now, here comes the tricky part. The "Receives Form?" field is a check box that they can toggle on/off. When it is toggled on, I want it to enable the "Date Form Complete" and "Notes" fields for updating. However, when it is toggled off, I want these two fields to be disabled. And of course, I only want the toggling on/off to affect that particular record. The issue that I am having is that my toggling seems to affect all three records on the Form.

Here is what my VBA code looks like:

Private Sub Form_Load()
'   Run check box defaults
    Call Detail_Defaults
End Sub

Private Sub chkReceivesForm_Click()
    Call Detail_Defaults
End Sub

Sub Detail_Defaults()
    If Me.chkReceivesForm = 0 Then
        Me.dteCompletionDate.Enabled = False
        Me.strNotes.Enabled = False
    Else
        Me.dteCompletionDate.Enabled = True
        Me.strNotes.Enabled = True
    End If
End Sub

Does anyone have any idea how I can modify this so selecting a check box only enables/disables the fields associated with that single record, and doesn't affect the other ones?

Thanks

[pbaldy]

This type of code doesn't work well in forms, as you've found. You could add it to the current event, so it would be correct record by record. Have you looked at Conditional Formatting? That would get the overall look correct (some records enabled, some not).

[JoeM]

You could add it to the current event, so it would be correct record by record.

Thanks. I'll have to play around with that.

Have you looked at Conditional Formatting? That would get the overall look correct (some records enabled, some not).

I know I can change the appearance of it using Conditional Formatting, but its not possible to actually enable/disable fields using Conditional Formatting, is it?

[JoeM]

I just tried putting my code in the Current Event, and it exhibited the same type of behavior.
Do I need to incorporate some sort of loop so that it goes through each record to handle them individually?
If so, any idea on how might do that?

Or can I re-structure my form in such a way to accomplish what I want?

[pbaldy]

Yes, disabling is an option with Conditional Formatting. On my PC it's the option to the far right. The current event should work, but the formatting would be relative to the selected record, which is why I think CF is the better option.

[Missinglinq]

CF is really the only option, in a Continuous or Datasheet View Form, if you want the individual Record's appearance to reflect its Enabled/Disabled status.

If you didn't care about the appearance, you could use the Lock Property in the Form_Current event, and the Records would be appropriately editable/non-editable, according to the Checkbox status on the Record you are currently on, but the Controls on all Records would look the same.

The reason for this behavior is that there is only one [B]dteCompletionDate Control and only one strNotes Control! What you are seeing on multiple Records are multiple instances of each Control, but there is only one Control of each name, and the way it is Formatted, on the Current Record, is the way all instances will be Formatted.

Linq ;0)>

[JoeM]

Thanks guys. I will use these tips and play around with it a bit more when I am back in the office on Monday, and let you know how it all works out.

[JoeM]

Yes, disabling is an option with Conditional Formatting. On my PC it's the option to the far right. The current event should work, but the formatting would be relative to the selected record, which is why I think CF is the better option.

Paul,

That is a cool feature, I never knew Conditional Formatting had an Enable/Disable option.
It works exactly like I want it to, and is a whole heck of a lot easier than trying to control it using Events.

Thanks a million!

linq,

Thanks for your insights too!

[pbaldy]

Happy to help!

[JoeM]

A quick follow-up (and I am afraid I can already guess the answer...).

Using Conditional Formatting to disable worked great on my Text Boxes.
I was also hoping to use Conditional Formatting to disable a single Check Box.
It appears that one cannot apply Conditional Formatting to Check Boxes on a Form.
Is that, indeed, the case?

Thanks

[pbaldy]

In the immortal words of Ed McMahon, "you are correct sir".

[JoeM]

"Hi-oh!"

Not a big deal. They can click away all they want at that one, it just won't do anything.

[pbaldy]

I think we both just dated ourselves.

[Missinglinq]

You're only old if you remember when the show was in B&W and was on the air for 90 minutes!