Continous Forms - Looping through records & Manipulating Fields On Form

[LarryB]

Good morning all,

I have a bound continuous tabular form, that pulls in my data that I need.
However, based on data content in one field of a record, I want a checkbox in the same record enabled, so the user can check it if necessary.

I have created a record set using the form as shown below, and I am looping through each record. To show that my code is referring to the field with required data content, I display it as a message box and it works, yet my checkbox does not enable......

I have the code in the form_load event, however, for testing purposes I have it behind a button.

If I am seeing this properly, the code behind the button enables the checkbox for ALL records once the criteria in the required field is true, and based on the last record, which has no data content, it disables the checkbox in ALL records.

I also have the PK ID for each record hidden in the form. Can I utilize that to target the checkbox of each individual record??

Form Detail
Form does not allow additions or deletions. Edits allowed
All fields are disabled and locked
I only want the check box to unlock if data is found in the "RequiredField" as referred to below. I have also tried if not isNull(requiredfield.value) then enable checkbox, which yields the same results

Here is my code

     Dim rstMyForm As DAO.Recordset
    
    Set rstMyForm = Forms!MyForm.Form.Recordset
    
    rstMyForm.MoveFirst
    
    Do While Not rstMyForm.EOF
        If Not RequiredField.Value = "" Then
            Checkbox.Enabled = True
            MsgBox "Time Value is " & RequiredField.Value
        Else
            Checkbox.Enabled = False
        End If
        rstMyForm.MoveNext
    Loop

Many thanks

Larry

 

[JHB]

First is the checkboks bound to a field in the recordset for the form?
Second, is the name of the checkbox actually "Checkbox"?

 

[LarryB]

Hi JHB, thanks for your prompt reply.

The checkbox is definitely bound.

As for the name, I tend to change my field and variable names before posting! Just habit! The checkbox is actually called "Pricing_Signoff" Here's the real stuff!

     Dim rstPricingForm As DAO.Recordset
    
    Set rstPricingForm = Forms!frmPricingSignoff.Form.Recordset
    
    rstPricingForm.MoveFirst
    
    Do While Not rstPricingForm.EOF
        If Not CA_SignoffTime.Value = "" Then
            Pricing_SignOff.Enabled = True
            MsgBox "Time Value is " & CA_SignoffTime.Value
        Else
            Pricing_SignOff.Enabled = False
        End If
        rstPricingForm.MoveNext
    Loop

 

[vbaInet]

Remember that your form is bound to a table or query so if you want to make any data changes, do it in the table/query by firing off an UPDATE query, and if necessary requery the form. You don't need to loop through the form's recordset.

 

[LarryB]

Hi VBA,

Thanks for the reply, however at this point updating the data is not what I want to achieve.

The bound checkbox I mention is disabled by default in the form design. On form_load or the test button I have on the form, only if another bound text box on the same record has data do I want this check box to enable.

Instead my loop enables the bound checkbox on all records of my tabular continuous form which is not what I want.

For arguments sake, leaving the checkbox enabled in my form design, allows me to check the box, which updates the checkbox value in my table, but as I have code behind the checkbox after update event, updates other bound form controls in that record and hence updates the table.

My data updates are fine, what I am trying to achieve for each record is to disable the checkbox based on criteria in another field of the same record.

 

[vbaInet]

So your problem is about enabling and disabling the checkboxes (i.e. a control) and nothing to do with fields? Your title is a bit misleading

What you need to use is Conditional Formatting. There's an Enable property there that allows you to Enable/Disable controls depending on the condition applied. This is the only way when you're dealing with continuous forms.

 

[LarryB]

Ah my apologies VBA, it is Friday after all I must be slacking and gunning for the exits!!!

Nice one! I actually only started using Conditional Formatting over the past few weeks to set font color on a control if a condition is met.

I never thought of using it here but the same principles will apply for what I want to achieve.

Thanks VBA, lunch soon and a couple Friday pints. I'll cheers ye!!!!

 

[vbaInet]

Just have one on me

 

[LarryB]

Hold that Cheers!!

I want to nail this one first!

Ok so, basic use I have already found is to change a format of a font color

From what I can see I can't set conditional formatting on a checkbox, so I have turned my attention to the control that needs to meet the condition in order to enable the check box

Basic us of CF is:

Expression is [CA_SignoffTime] <> ""

This is saying if this control is not blank then.....

using the same control, I have selected the .enabled property of the check box control, and it adds in as follows, I get an invalid syntax

«Expr» [Pricing_SignOff].Enabled = true

I am not sure if I am using it right and I'll read up on it more

 

[vbaInet]

You don't write any code to enable/disable a control in CF, not unless you can in Access 2014 (which I don't use). The Enable/Disable thing is a button which you press or depress in CF to indicate whether it's enabled/disabled if it meets the criteria.

[I][COLOR="Blue"]Expression Is[/COLOR][/I]
Len([CA_SignoffTime] & "") <> 0

And remember to remove the code you had in the event too.

 

[LarryB]

just spotted the enable / disable button in CF.....

that will work for your standard controls, however CF is not available for Checkboxes.

I'll keep digging

 

[vbaInet]

If that's the case then you're out of luck. Like I mentioned, with continuous forms, the only way of effecting such a state change is through CF. If you really want to disable/disable them then consider using a textbox where the user is restricted to entering Yes/No or -1/0.

 

[LarryB]

Thanks for the help VBA.

It is user preference that the final check cannot be set until other conditions are met.

I have read a few forums with the exact same issue, and the recommendations is to use a text box with code behind the on click event.

I'll post up how I get on

Cheers!

 

[vbaInet]

What do you mean by the "final check"? Are you talking about the checkbox on the last row?

 

[LarryB]

sorry... Final check - generally speaking, as in final part of a process or procedure

 

[vbaInet]

Ok. You could instead write code to SetFocus elsewhere if the condition hasn't been met. SetFocus to the previously selected control.