Cmd button and msgBox

[naxi]

Hi,



I have a small database which connects projects, organizations and contact persons for organizations.

My search for projects is based on many criterias. For example I need a project with scope 1, priority status 2 and it is approved, or funding year is 2002-2003 , scope is 3 and priority status is 2.......

For all these options I have couple of combo boxes on my search form and one button which I press and get another form which desplays data according to criteria.

Everything works fine, but when there is no data which satisfy criteria, after I presss the button my result list is empty (not without data but without any control on it, just empty screen).
That's why I tried to put message box to show up if my filter string is empty but the message box is ignored. I'm still pretty new in VBA ..........


The code for the button's event onclick is next

Private Sub cmdFindRecords_Click()
Dim strFilter As String
strFilter = ""
If Not IsNull(Me!cboScope) Then strFilter = strFilter & "[Scope]=" & Chr(34) & Me!cboScope & Chr(34) & "AND"
If Not IsNull(Me!cboPriorityStatus) Then strFilter = strFilter & "[PriorityStatus]=" & Me!cboPriorityStatus & "AND"
If Not IsNull(Me!cboApprovalStatus) Then strFilter = strFilter & "[ApprovalStatus]=" & Chr(34) & Me!cboApprovalStatus & Chr(34) & "AND"
If strFilter <> "" Then
strFilter = Left$(strFilter, Len(strFilter) - 3)
DoCmd.OpenForm "ResultForm", , , strFilter, acFormReadOnly
Else
MsgBox "There is no matching data"
End If

End Sub


I work in Access 2000

Any suggestions?

 

[Mile-O]

Try this:

Private Sub cmdFindRecords_Click() 
   Dim strFilter As String 
   strFilter = vbNullString 
   If Not IsNull(Me!cboScope) Then strFilter = strFilter & "[Scope]=" & Chr(34) & Me!cboScope & Chr(34) & " AND " 

   If Not IsNull(Me!cboPriorityStatus) Then strFilter = strFilter & "[PriorityStatus]=" & Me!cboPriorityStatus & " AND " 

   If Not IsNull(Me!cboApprovalStatus) Then strFilter = strFilter & "[ApprovalStatus]=" & Chr(34) & Me!cboApprovalStatus & Chr(34) & " AND "
 
   If strFilter <> vbNullString Then 
      strFilter = Left$(strFilter, Len(strFilter) - 5) 
      DoCmd.OpenForm "ResultForm", , , strFilter, acFormReadOnly 
   Else 
      MsgBox "There is no matching data", vbInformation
   End If 

End Sub

 

[Mile-O]

You are not taking spaces in the construction of your filter into consideration.

Therefore your whole filter will read as one long gargle of words rather than a fluid criteria.

 

[naxi]

Thanks for spaces around AND ......

But the main thing is still not working.

When I open my search form and don't choose anythin as a criteria I get the message, but if I put criteria and there is no mathchig records I get empty result form again (without any controls on it).......???

 

[naxi]

I made one change and now my button opens result form with controls in it which are empty (which is better than before)

Now I just need a way to check if my result form is empty to get message again......

Private Sub cmdFindRecords_Click()
Dim strFilter As String
strFilter = vbNullString
If Not IsNull(Me!cboScope) Then strFilter = strFilter & "[Scope]=" & Chr(34) & Me!cboScope & Chr(34) & " AND "

If Not IsNull(Me!cboPriorityStatus) Then strFilter = strFilter & "[PriorityStatus]=" & Me!cboPriorityStatus & " AND "

If Not IsNull(Me!cboApprovalStatus) Then strFilter = strFilter & "[ApprovalStatus]=" & Chr(34) & Me!cboApprovalStatus & Chr(34) & " AND "

If strFilter = vbNullString Then
MsgBox "There is no matching data", vbInformation
Else
strFilter = Left$(strFilter, Len(strFilter) - 5)
DoCmd.OpenForm "ResultForm", , , strFilter, acFormReadOnly

End If



End Sub


Any ideas?

 

[Mile-O]

On the Open() event of your form, you can check the number of records in the form's recordset - if the result is 0, then you can set the Cancel integer to True.

 

[naxi]

Could you please write the code how to do that (I know programming but not VBA sintax......yet)

Thank you

 

[Mile-O]

What version of Access are you using?

 

[naxi]

Access 2000

 

[Mile-O]

I rarely use 2000, so it would be something like this:

Private Sub Form_Open(Cancel As Integer)

    Dim rsClone As Recordset
    Set rsClone = Me.RecordsetClone
    
    If rsClone.RecordCount = 0 Then
        MsgBox "There are no records.", vbInformation
        Cancel = True
    End If
    
    rsClone.Close

End Sub

 

[naxi]

Actually I got error 13 . That happens only in Access 2000 not in 97. I found some discussions about that but i need some time to figure that out and make corrections..........


Thank you very much