How do I loop through Access 2010 form controls and print only if condition met for all controls

Question

I am an Access newbie and have been tasked with building a database. I've run into an issue that I haven't been able to answer through several internet searches. I have conditional formatting set up on my form so that required fields have a control backcolor of yellow. Which fields highlight depends on the selected outcome of the encounter. Once an entry is made in the required field, the backcolor switches to white. What I'd like to do is have the user hit a "print" button and, if ANY backcolor is still yellow, have a MsgBox popup alerting them to fill in the missing field. If ALL backcolors are white, then I'd like Access to print the report. I cobbled together the following code (which I admit, I don't really understand), but it doesn't work properly. If a backcolor is yellow, the MsgBox pops up, however so does the report print preview. How can I fix the code? Many thanks in advance!!!

For Each Control In Me.Controls If Control.ControlType = acTextBox Or Control.ControlType = acComboBox Or Control.ControlType = acListBox Then If Control.BackColor = vbYellow Then MsgBox "You must complete the highlighted fields before printing the form." ElseIf Control.BackColor = vbWhite Then DoCmd.OpenReport "rptClientEncounter", acViewPreview, , "[EncounterID] = " & [EncounterID] End If End If

Next Control

Jens · Accepted Answer · 2014-08-22 08:27:38Z

You were almost there:

For Each Control In Me.Controls If Control.ControlType = acTextBox Or Control.ControlType = acComboBox Or Control.ControlType = acListBox Then If Control.BackColor = vbYellow Then MsgBox "You must complete the highlighted fields before printing the form.", vbCritical Or vbOKOnly, "complete fields" Exit Sub End If End If Next DoCmd.OpenReport "rptClientEncounter", acViewPreview, , "[EncounterID] = " & [EncounterID]

You first loop through all your controls and check if there is a yellow background, and if so, stop the sub routine. If the routine has not exited, it means everything has got a white background, and the report command can be executed.

Thank you SO MUCH! I've been swamped at work and only just now had a chance to read your response. Works like a charm!!!

Collectives™ on Stack Overflow

How do I loop through Access 2010 form controls and print only if condition met for all controls

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related