If And Then help

[Delta729]

I'm a complete novice with Access and especially VBA here and could really use some assistance.

I have a form called "frmQuickProject" with a subform called "frmTaskDetailsQuick". The subform contains two command buttons "cmdOne" and "cmdTwo". The main form has two combo boxes (1) QuickTitle (2) Originator; and a text box (3) ClientNumber.

I've disabled the two command buttons in the subform and would like set them to enable when all three of three boxes listed above are dirty. I can figure out how to write IF And statement or where the best spot for this code would be. Could someone help me out or point me to a good (easy to understand article) regarding this.



Thanks,

[ranman256]

I have NEVER in 20 years of Access programming used the ON DIRTY event. I do use afterupdate and check for IS NULL.
Do you mean you have empty boxes and then when the user fills them in it activates the buttons??
But if you do want on dirty, the if is similar.

if not isnull(QuickTitle) and not isnull(Originator) and not isnull(ClientNumber) then
  cmdOne.enabled = true
  cmdTwo.enabled = true
else
  cmdOne.enabled = false
  cmdTwo.enabled = false
endif

endif

[ItsMe]

Like ranman mentioned, you need to do data validation in the AfterUpdate event handler of each control. You might, also, want to check for empty strings. If I recall correctly, controls can contain empty strings. It would be good to check for that, also. Maybe using "" as a default value for these three controls is an option. Then, you would only need to check for the empty string.

[June7]

I don't allow empty strings in tables so I can assume bound controls never have empty string value.

[ItsMe]

I don't allow empty strings in tables so I can assume bound controls never have empty string value.

The way I recall it is the user can create an empty string in the control by place data into the control and then removing it. In the old MDB files you could use a default value of "" in tables but I think they changed that. Anyway, I test for "" because you never know what the user is going to do.

EDIT: I mocked up a test to see if I could get an empty string in there. I tested an Unbound Control and a bound control. If I added data and then deleted it, I had a Null. If I typed "" I would get the appearance of Null but the value of the field/control was "Empty String".

Unlike the olden days, the only way to get the empty string was to type "" into the control.

[Delta729]

Thanks for the responses. Ranman, it's a data entry form and all three (QuickTitle), (Originator), and (ClientNumber) start off empty. Once data has been filled in or selected in all three of these boxes is when the command buttons in the subform would enable. Would your code go into the main form afterupdate event or the subform?

[June7]

I just did a test of typing "" into a bound control. I get the Access error message "cannot be zero-length string".

Code to enable buttons in subform? Try code in the main form AfterUpdate or subform container OnEnter event.

[ItsMe]

I just did a test of typing "" into a bound control. I get the Access error message "cannot be zero-length string".

Interesting, I was ignoring the fact that there is a property at the table level to not allow Zero Length Strings. Now I have to think if I want to use this option or not. Hmmmm

[Delta729]

Great, got it working. Took a little bit to figure out how to "correctly" reference the subform controls.

Private Sub Form_AfterUpdate()
If Not IsNull(QuickTitle) And Not IsNull(Originator) And Not IsNull(ClientNumber) Then
Forms.frmQuickProject.frmTaskDetailsQuick.Form.cmd One.Enabled = True
Forms.frmQuickProject.frmTaskDetailsQuick.Form.cmd Two.Enabled = True
Else
Forms.frmQuickProject.frmTaskDetailsQuick.Form.cmd One.Enabled = False
Forms.frmQuickProject.frmTaskDetailsQuick.Form.cmd Two.Enabled = False
End If
End Sub

Thank you all for your help.

[ItsMe]

...Private Sub Form_AfterUpdate()...

I am not perfectly familiar with your application. But, you may want to place your code in the afterupdate of each of the three controls.

[Delta729]

I have a follow up question regarding this and use of a message box. By default, the cmdOne & cmdTwo are set too enabled = False until data is filled in. Is it possible to have a message box pop up if not enabled and if someone clicks on the cmdOne or cmdTwo. This is the for click code for cmdTwo button.

Private Sub cmdTwo_Click()
DoCmd.SetWarnings False
Me.Time = 0.2
Me.Status.Value = "Completed"
DoCmd.RunSQL "UPDATE tblProjects Set Status='Completed' WHERE ID=" & Me.Project
Me.Refresh
DoCmd.Close acForm, "frmQuickProject", acSaveYes
DoCmd.SetWarnings True
End Sub

Please let me know if possible.

[June7]

If the button is not enabled then there cannot be a 'click' event and code will not trigger. So the answer is no. Not enabled means the button won't do anything.

[Delta729]

Thats what I thought, just wanted to confirm.

Thanks