When there are no records in database, the code doesn't work.

[bsarman]

When ContactStatusID is null the code doesn't work.

Private Sub Command85_Click()
'Open Pending Records For This Month
Form.Filter = "[Rep#]=" & Trim(Me![Combo38].Column(0)) & " AND ([runmonth]) = #6/1/2004#" & " AND ([ContactStatusId])= 2"
Form.FilterOn = True
Form.Refresh
End Sub

I have 4 click events like above.
([ContactStatusId])= 1 (this opens not called records)
([ContactStatusId])= 2 (this opens pending records)
([ContactStatusId])= 3 (this opens closed records)

If there are records for ContactStatusId 1,2 or 3 in the database, I can click on the command buttons of each and am able to switch between, not called, pending or closed records. But the problem is; when there's no record for a pending or closed record, I can not switch between not called, pending or closed records. When all the not called records are open and I click on pending or closed, it works, and shows that there are no records for 2 or 3. But from there I can't go back to not called records when I click on the command buttons.
Note: By default all records are open before a sales call is made to them.

Can anyone tell me what I can add to the code to remedy this problem?

Thanks.

 

[Zion7]

Hi bsarman,
it appears your code, "demands" criteria, for the filter.
Thus, you must accomodate this situation with optional criteria.
Nz() for example. Or, I use the wild card "*", when there's a chance, a criterion may not be availabe, this will show all records for that particular field. I don't know if that's an option in your case.
Dim scbo38 as String
eg If IsNull(Combo38) Then
scbo8 = "*"
End If

...AND [contactStatusID]Like" & scbo8

Not positive this is the problem, but a viable option, maybe for future purposes.
Either way, hope it helps, good LUck!

 

[willir]

bsarman

A couple of things...

First, you may wish to test ComboBox38 has a value in it. There are others ways - this works for me.

' If numeric
If Nz(Me.Combo30, 0) > 0 Then


' If text / string
If Len(Nz(Me.Combo38, "")) > 0 Then


' Variation...
If Not IsNull(Me.Combo38) Then

Next, you can use DCount to check to see if there is at least one record. DCount works by "Count field", table and optional where condition.

I do not have enough to go by for specifics, but here is a stab at if if Me.Comb39 is a string.

Dim strWhere as String, strQ as String

If Nz(Me.Combo38, 0) > 0 Then

     strQ = CHR$(34)  ' double quote
     strWhere = "[Rep#] = " & strQ & Trim(Me![Combo38) & strQ  _
     & " AND ([runmonth]) = #6/1/2004#" _ 
     & " AND ([ContactStatusId])= 2"

     If DCount("[YourField]", "YourTable", strWhere) > 0 Then
        Me.Filter = strWhere
        Me.FilterOn = True
     End If
End If

If Comb38 is numeric, then the first strWhere line becomes

[tt]
strWhere = "[Rep#] = " & Me.Combo38 _
[/tt]

Alas, you have to test before executing.

...Moving on.
On the other side of the If Then, you can use and Else clause

If .... Then
   'Your code
Else
   MsgBox "No data found"
   Me.FilterOn = False
End If

Richard

 

[bsarman]

Thanks for the help. I'll give them a try today. In the mean time, I created a few dummy records where there were no records just to make it work