CODEWITHSUNDEEP
ms-accessvbaconditional-statementsms-access-2010
AccessNewbie2010
AccessNewbie2010

Reputation: 3

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

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

Upvotes: 0

Views: 123

Answers (1)

Jens
Jens

Reputation: 879

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.

Upvotes: 2

Related Questions