1

I a newbie to MS Access and VBA Code, but I have been handling well my needs. The code posted bellow is the only way I got my Click Event working. In order to printout current record from multiple tables (mail merge) it's required that some fields are filled. So before the actual printout code I inserted the following code. Is there a better way to do it? It just doesn't feel right the way I did it.

If IsNull(Screen.ActiveForm![Nome]) Then
MsgBox "Preencher o Nome do Cliente."
Screen.ActiveForm![Nome].SetFocus
Else

  If IsNull(Screen.ActiveForm![Gênero]) Then
  MsgBox "Preencher o Gênero do Cliente."
  Screen.ActiveForm![Gênero].SetFocus
  Else

    If IsNull(Screen.ActiveForm![Estado Civíl]) Then
    MsgBox "Preencher o Estado Civíl do Cliente."
    Screen.ActiveForm![cboecivil].SetFocus
    Else

        If IsNull(Screen.ActiveForm![Profissão]) Then
        MsgBox "Preencher a Profissão do Cliente."
        Screen.ActiveForm![Profissão].SetFocus
        Else

            If IsNull(Screen.ActiveForm![CEP]) Then
            MsgBox "Preencher o CEP do Cliente."
            Screen.ActiveForm![CEP].SetFocus
            Else

                If IsNull(Screen.ActiveForm![Endereço]) Then
                MsgBox "Preencher o nome da Rua do Cliente."
                Screen.ActiveForm![Endereço].SetFocus
                Else

                    If IsNull(Screen.ActiveForm![Número]) Then
                    MsgBox "Preencher o Número da Rua do Cliente."
                    Screen.ActiveForm![Número].SetFocus
                    Else

                        If IsNull(Screen.ActiveForm![Cidade]) Then
                        MsgBox "Preencher a Cidade do Cliente."
                        Screen.ActiveForm![Cidade].SetFocus
                        Else

                            If IsNull(Screen.ActiveForm![UF]) Then
                            MsgBox "Preencher o Estado do Cliente."
                            Screen.ActiveForm![UF].SetFocus
                            Else
                        
                                If IsNull(Screen.ActiveForm![Bairro]) Then
                                MsgBox "Preencher o Bairro do Cliente."
                                Screen.ActiveForm![Bairro].SetFocus
                                Else
                                
                                    If IsNull(Screen.ActiveForm![Complemento]) Then
                                    MsgBox "Preencher o Complemento do Endereço do Cliente."
                                    Screen.ActiveForm![Complemento].SetFocus
                                    Else
                                    
                                        If IsNull(Forms("Painel de Controle").sftblCPF.Form.CPF) Then
                                        MsgBox "Preencher o CPF do Cliente."
                                        Forms("Painel de Controle").sftblCPF.Form.CPF.SetFocus
                                        Else
                                        
                                            If IsNull(Forms("Painel de Controle").sftblRG.Form.Número) Then
                                            MsgBox "Preencher o Número do RG do Cliente."
                                            Forms("Painel de Controle").sftblRG.Form.Número.SetFocus
                                            Else
                                        
                                                If IsNull(Forms("Painel de Controle").sftblRG.Form.Série) Then
                                                MsgBox "Preencher a Série do RG do Cliente."
                                                Forms("Painel de Controle").sftblRG.Form.Série.SetFocus
                                                Else
                                        
                                                    If IsNull(Forms("Painel de Controle").sftblRG.Form.[Orgão Emissor]) Then
                                                    MsgBox "Preencher o Orgão Emissor do RG do Cliente."
                                                    Forms("Painel de Controle").sftblRG.Form.[Orgão Emissor].SetFocus
                                                    Else
                                        
                                                        If Forms("Painel de Controle").sftblCPF.Form.[Principal?] = False Then
                                                        MsgBox "Marcar o CPF Principal do Cliente."
                                                        Forms("Painel de Controle").sftblCPF.Form.[Principal?].SetFocus
                                                        Else
                                                        
                                                            If Forms("Painel de Controle").sftblRG.Form.[Principal?] = False Then
                                                            MsgBox "Marcar o RG Principal do Cliente."
                                                            Forms("Painel de Controle").sftblRG.Form.[Principal?].SetFocus
                                                            Else
 
'MailMerge code inserted Here.

End If
    End If
        End If
            End If
                End If
                    End If
                        End If
                            End If
                                End If
                                    End If
                                        End If
                                            End If
                                                End If
                                                    End If
                                                        End If
                                                            End If
                                                                End If
Felipe Raposo
  • 13
  • 2
  • Have a look at VBA's AND and OR functions. – Robert Harvey Aug 11 '21 at 14:38
  • Also, consider "Early Exit." That means when one of your conditions is true, you can simply return from the method or function instead of using all those ELSE clauses. – Robert Harvey Aug 11 '21 at 14:39
  • If there are multiple required fields, it's kinder to give your user a list of all of the empty ones, so they don't get in a cycle of filling one, then getting a message to fill another, then getting a message about a second field, and so on. A list of all empty but required fields is more useful to them. – Tim Williams Aug 11 '21 at 15:51

3 Answers3

2

Put all the field names into an array

Dim fieldNames As Variant

Private Sub Form_Load()
    fieldNames = Array("Nome", "Gênero", "Estado Civíl", "Profissão", ...)
End Sub

then use a loop to do the checks

Dim fieldName As String
Dim i As Long

For i = LBound(fieldNames) To UBound(fieldNames)
    fieldName = fieldNames(i)
    If IsNull(Screen.ActiveForm(fieldName).Value) Then
        MsgBox "Preencher o " & fieldName & " do Cliente."
        Screen.ActiveForm(fieldName).SetFocus
        Exit Sub
    End If
Next i

If you need individually composed messages, you can use a second array with messages:

Dim fieldNames As Variant
Dim messages As Variant

Private Sub Form_Load()
    fieldNames = Array("Nome", "Gênero", "Estado Civíl", "Profissão", ...)
    messages = Array("Preencher o Nome do Cliente.", "Preencher o Gênero ...", ...)
End Sub

then use a loop again to do the checks

Dim fieldName As String
Dim i As Long

For i = LBound(fieldNames) To UBound(fieldNames)
    fieldName = fieldNames(i)
    If IsNull(Screen.ActiveForm(fieldName).Value) Then
        MsgBox messages(i)
        Screen.ActiveForm(fieldName).SetFocus
        Exit Sub
    End If
Next i

Btw., you can use an ElseIf instead of an Else followed by an If. This will chain the conditions instead of nesting them

If IsNull(Screen.ActiveForm![Nome]) Then
    MsgBox "Preencher o Nome do Cliente."
    Screen.ActiveForm![Nome].SetFocus
ElseIf IsNull(Screen.ActiveForm![Gênero]) Then
    MsgBox "Preencher o Gênero do Cliente."
    Screen.ActiveForm![Gênero].SetFocus
ElseIf IsNull(Screen.ActiveForm![Estado Civíl]) Then
    MsgBox "Preencher o Estado Civíl do Cliente."
    Screen.ActiveForm![cboecivil].SetFocus
ElseIf IsNull(Screen.ActiveForm![Profissão]) Then
    MsgBox "Preencher a Profissão do Cliente."
    Screen.ActiveForm![Profissão].SetFocus
...
End If

See: If...Then...Else Statement (Visual Basic)

Olivier Jacot-Descombes
  • 104,806
  • 13
  • 138
  • 188
1

If you make the fields themselves required on the table then the record can't be saved until it is completed. If there are fields that are required at different steps, make sure the tables are normalized in a way that each step doesn't have required fields that are required on different steps.

When it comes time to make the form with all the required fields in one place, make a query that pulls all the fields from all the tables needed in one query. Base the form on that query. You can edit fields in a properly formed query so this will just work when you get it right.

Once you have your backend defined properly this way the front end interface has built in warnings that won't allow the form to save if required fields are missing. There is a total of zero VBA code required to get this to work.

In the end you will have a more normalized database with better safety controls to avoid invalid states. You will also find performance improvements that come with properly indexing, relating, and constraining the tables.

HackSlash
  • 4,944
  • 2
  • 18
  • 44
0

First of all, I would like to thank you all for the explanations. You have no idea how helpful it was. I ended up using the second example Olivier advised. Due to the need of prompt text variety, I could not use the first option.

The fields are not essentially necessary for the DataBase, but they are mandatory for some buttons events I built to export data to MailMerge Document. Which, by the way, it was really hard to do, since I have data from multiple tables (as subforms) in this form and I needed only the current record to be merged. To make it work I created a parameter query indexed to the form's current ClientID, than VBA code to insert that data into to a pre-created single record table where my MailMerged Documents pulls the information from. I also used a code to create a ClientFolder's Name if not created already. I don't know if this procedure is safe for the DataBase, but I really could not find any other way to do it. I will post the full code bellow, so other people can check and use it.

HackSlash, I struggled a lot with this Form because every time I tried to use a query as source, I wasn't able to edit it. With that, I used the table that had most needed information and a lot of subforms (for two reasons). First, some fields that I need on the Form has one-to-many relationship (like ClientComments, ClientePhoneNumbers, ...), second, since I didn't know I was able to edit a query source, I had to use subforms as a Text Field to place the related information on the form. I will definitely check the article you posted and try out sourcing this form with a query. One more time, Thank you very much!

Private Sub cmdProcuração_Click()

If IsNull(Screen.ActiveForm![Nome]) Then
MsgBox "Preencher o Nome do Cliente."
Screen.ActiveForm![Nome].SetFocus
 ElseIf IsNull(Screen.ActiveForm![Gênero]) Then
 MsgBox "Preencher o Gênero do Cliente."
 Screen.ActiveForm![Gênero].SetFocus
    ElseIf IsNull(Screen.ActiveForm![Estado Civíl]) Then
    MsgBox "Preencher o Estado Civíl do Cliente."
    Screen.ActiveForm![cboecivil].SetFocus
        ElseIf IsNull(Screen.ActiveForm![Profissão]) Then
        MsgBox "Preencher a Profissão do Cliente."
        Screen.ActiveForm![Profissão].SetFocus
            ElseIf IsNull(Screen.ActiveForm![CEP]) Then
            MsgBox "Preencher o CEP do Cliente."
            Screen.ActiveForm![CEP].SetFocus
                ElseIf IsNull(Screen.ActiveForm![Endereço]) Then
                MsgBox "Preencher o nome da Rua do Cliente."
                Screen.ActiveForm![Endereço].SetFocus
                    ElseIf IsNull(Screen.ActiveForm![Número]) Then
                    MsgBox "Preencher o Número da Rua do Cliente."
                    Screen.ActiveForm![Número].SetFocus
                        ElseIf IsNull(Screen.ActiveForm![Cidade]) Then
                        MsgBox "Preencher a Cidade do Cliente."
                        Screen.ActiveForm![Cidade].SetFocus
                            ElseIf IsNull(Screen.ActiveForm![UF]) Then
                            MsgBox "Preencher o Estado do Cliente."
                            Screen.ActiveForm![UF].SetFocus
                                ElseIf IsNull(Screen.ActiveForm![Bairro]) Then
                                MsgBox "Preencher o Bairro do Cliente."
                                Screen.ActiveForm![Bairro].SetFocus
                                    ElseIf IsNull(Screen.ActiveForm![Complemento]) Then
                                    MsgBox "Preencher o Complemento do Endereço do Cliente."
                                    Screen.ActiveForm![Complemento].SetFocus
                                        ElseIf IsNull(Forms("Painel de Controle").sftblCPF.Form.CPF) Then
                                        MsgBox "Preencher o CPF do Cliente."
                                        Forms("Painel de Controle").sftblCPF.Form.CPF.SetFocus
                                            ElseIf IsNull(Forms("Painel de Controle").sftblRG.Form.Número) Then
                                            MsgBox "Preencher o Número do RG do Cliente."
                                            Forms("Painel de Controle").sftblRG.Form.Número.SetFocus
                                                ElseIf IsNull(Forms("Painel de Controle").sftblRG.Form.Série) Then
                                                MsgBox "Preencher a Série do RG do Cliente."
                                                Forms("Painel de Controle").sftblRG.Form.Série.SetFocus
                                                    ElseIf IsNull(Forms("Painel de Controle").sftblRG.Form.[Orgão Emissor]) Then
                                                    MsgBox "Preencher o Orgão Emissor do RG do Cliente."
                                                    Forms("Painel de Controle").sftblRG.Form.[Orgão Emissor].SetFocus
                                                        ElseIf Forms("Painel de Controle").sftblCPF.Form.[Principal?] = False Then
                                                        MsgBox "Marcar o CPF Principal do Cliente."
                                                        Forms("Painel de Controle").sftblCPF.Form.[Principal?].SetFocus
                                                            ElseIf Forms("Painel de Controle").sftblRG.Form.[Principal?] = False Then
                                                            MsgBox "Marcar o RG Principal do Cliente."
                                                            Forms("Painel de Controle").sftblRG.Form.[Principal?].SetFocus
                                                            Else

On Error GoTo ErrorHandler
'A seguir comandos para modificar a tabela existente com os dados atuais do formulário (Organizados em uma Consulta)
DoCmd.SetWarnings False
DoCmd.RunSQL "SELECT * INTO [tblExportarDocumentos] FROM [Exportar Contatos]" '(FROM QUERY)
DoCmd.SetWarnings True

Dim strSql As String

'Instrução SQL direto da tabela criada
strSql = "SELECT * FROM [tblExportarDocumentos]"

Dim strDocumentName As String  'Nome do Documento Template com a subpasta
strDocumentName = "\Documentos\Procuração RCT.docx"

Dim strNewName As String  'Nome usado para Salvar o Documento
strNewName = "Procuração - " & Nome.Value

Call OpenMergedDoc(strDocumentName, strSql, strNewName)
Exit Sub
ErrorHandler:
MsgBox "Error #" & Err.Number & " occurred. " & Err.Description, 
vbOKOnly, "Error"
Exit Sub
End If
    
End Sub

Private Sub OpenMergedDoc(strDocName As String, strSql As String, s 
trMergedDocName As String)
On Error GoTo WordError

Const strDir As String = "C:\Users\Jcnra\Documents\Banco de Dados RCT" 
'Localização da pasta com o Banco de Dados


  Dim objWord As New Word.Application
  Dim objDoc As Word.Document
  objWord.Application.Visible = True
  Set objDoc = objWord.Documents.Open(strDir & strDocName)
  objWord.Application.Visible = True

'A seguir, a função do Mail Merge. Em Name: Colocar o endereço exato do arquivo do Banco de Dados
'Em SQLStatement: Colocar a mesma função sql acima
objDoc.MailMerge.OpenDataSource _
    Name:="C:\Users\Jcnra\Documents\Banco de Dados RCT\Backup Banco de 
Dados RCT.accdb", _
    LinkToSource:=True, AddToRecentFiles:=False, _
    Connection:="", _
    SQLStatement:="SELECT * FROM [tblExportarDocumentos]"

'A seguir, condição para criar pastas no diretório, caso já não existam
If Dir(strDir & "\Clientes\" & Nome.Value, vbDirectory) = "" Then
    MkDir (strDir & "\Clientes\" & Nome.Value)
    Else
    End If

objDoc.MailMerge.Destination = wdSendToNewDocument
objDoc.MailMerge.Execute

'Comando para salvar o Documento criado
objWord.Application.Documents(1).SaveAs (strDir & "\Clientes\" & 
Nome.Value & "\" & strMergedDocName & ".docx")

objWord.Application.Documents(2).Close wdDoNotSaveChanges
objWord.Visible = True
objWord.Activate
objWord.WindowState = wdWindowStateMaximize

'Liberar as variáveis
Set objWord = Nothing
Set objDoc = Nothing
   
Exit Sub
WordError:
    MsgBox "Err #" & Err.Number & "  occurred." & Err.Description, 
vbOKOnly, "Word Error"
    objWord.Quit

End Sub
Felipe Raposo
  • 13
  • 2