Forms VBA code for form completely blank

[Khalil Handal]

Hi to All,


I have form Form1 with a command button cmdMemberName with the following VBA code:

Private Sub cmdMemberName_Click()

On Error GoTo ProcError

Dim strWHERE As String
    
    If cboBookStatusId = 2 Then
     strWHERE = "[fkBookId] = " & Forms!frmBooksUsers.txtpkBookId
        DoCmd.OpenForm "frmNameOfMember", , , strWHERE, acFormReadOnly
    Else
        MsgBox "The selected book is not loaned" 
    End If
        
ExitProc:
    Exit Sub
ProcError:
    MsgBox "Error " & Err.Number & ": " & Err.Description & ", " _
          & vbCritical, "Error in cmdMemberName_Click Procedure..."
    Resume ExitProc

End Sub

The second form frmNameOfMember will open completely blank if there is no record to display.
How can I have a message box informing the user that no records exist?

Thanks in advance
Khalil

[ranman256]

something like this to test for the record:

if Dcount("[id]", "table","[id]=" & txtID ) = 0 then
   msgbox "There is no record."
else
   docmd.openform "fMyForm,,,"[id]=" & txtID
endif

[Micron]

see http://allenbrowne.com/casu-20.html

includes work arounds.

[Khalil Handal]

Hi,
I read Allen Browne article at:
http://allenbrowne.com/casu-20.html
Condition (a) applied for my case:
"The form is opened with a WhereCondition that yields no records."


I used the procedure suggested in the site and have the following code:

Private Sub Form_Open(Cancel As Integer)


    If Me.RecordsetClone.RecordCount = 0 Then
        Cancel = True
        MsgBox "No records"
    End If


End Sub

Clicking the cmdMemberName on Form1 shows the Message box of the form frmNameOfMember. Clicking the OK button of the dialogue box ends up with Run-time error 13: Type mismatch
When I click Debug it shows the error procedure of cmdMemberName button on Form1:

any Ideas?

[davegri]

Try this way:

            MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdMemberName_Click" & "."

or to include the critical icon

          MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Error in procedure cmdMemberName_Click..."

[Micron]

Just to add an explanation, you are trying to pass a literal text/string value as a comma separator (",") that's part of the function argument syntax. The comma separator in the function arguments should not be quoted.

[Khalil Handal]

I tried the code for the error message and have following:

Error 2501: The OpenForm action was canceled

Clicking the OK button of the message closes it.
Can I have something more simple for the user?

[davegri]

You can use the error handler for the procedure causing the error to ignore it:

Err_Handler:
    Select Case Err
        Case 2501   'user cancelled, ignore
        Case Else
            MsgBox Err & ", " & Err.Description & " Called from form " & frm.Name
    End Select
    Resume Exit_Handler
End Sub

[Khalil Handal]

Thank you all.