We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Records and <1

Petrobras
Petrobras asked
on
Medium Priority
271 Views
Last Modified: 2008-02-01
Experts,

I have a form that is bound to tblGuarantor.  When there are no records I get an error with txtSum.  TxtSum is a sum counter.
I added a dcount of the records but still gives error of "entered an expression that has not value".  I cant have it run the code
of If Me.txtSum > 1 Then because the error.   How do I get around this?

If DCount("*", "tblGuarantor") < 1 Then
        Exit Sub
    Else
    If Me.txtSum > 1 Then
        Me.lblAdditional.Visible = True
    End If
    End If
Comment
Watch Question

Top Expert 2006

Commented:
Hey P,
Open it as a recordset.

IF rs.EOF then
exit sub
else
do stuff

J

Author

Commented:
aha!  but I tried it and it said object required.  I will b e out for a couple hours.  Gotta get some exercise instead of sitting in front of this computer for so damn long which is probably 100x longer than an expert would :)
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
Petrobras,
Have you tried using Nz, skipping the record count check?

If Nz(me.txtSum,0) >1 then
 ... etc
end if
Top Expert 2006

Commented:
well yea!! lol

dim rs as DAO.recordset
dim strSQL as string
strSQL = "Select * from tblGuarantor;"
set rs = currentDB.openrecordset(strSQL)
if rs.EOF then
ExitSub
Else
  Me.lblAdditional.Visible = True
end if
rs.close
set rs = nothing

By the way P,
What sets it back invisible?

J



Author

Commented:
jeff, I hv the lbl set visiBle to NO hardcoded.  will test when I get baCk.  thanks
CERTIFIED EXPERT
Top Expert 2016

Commented:
in the load event of your form, you can just use me.recordsetclone, no need to open another recordset
since you form is bound

private sub form_load()
dim rs as dao.recordset
set rs= me.recordsetclone

if rs.bof or rs.eof then
   msgbox "No records"
   exit sub
   else
    If Me.txtSum > 1 Then
        Me.lblAdditional.Visible = True
    End If
end if

end sub

Author

Commented:
Cap, OK that worked...thanks
It created another issue though and I thought I could fix it but probably have syntax or just simply wrong code. If txtSum or txtCounter or Me.Email is null or 0 then I get an error of expression has no value and highlights one of these.  I only have pasted part of the code so there may be an end if missing somewhere probably at the very end but there is code below it.
What do you suggest here to get around if any of these are null or 0 Me.txtSum Or Me.txtCounter Or Me.Email?  I also tried to implement the dim rs and set rs= but not sure if I can use that in detail_format because it gave an error.
thanks!

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

   If Nz(Me.txtSum Or Me.txtCounter Or Me.Email, "") = 0 Then
    Exit Sub
    Else
    '**********THE ABOVE IS MY IDEA*********
    If Me.txtSum > 1 Then
        Me.lblAdditional.Visible = True
        Result = SetTextBoxProperties(Me)
    End If

'Code will suppress if records >1.  1 records constant even if 1 or 2 records.
    If Me.txtCounter > 1 Then
        Me.Detail.Visible = False
    Else
        Me.Detail.Visible = True
    End If

If Me.Email.FontSize = 10 Then
     If Len(Me.Email.Value) > 26 Then
         Me.Email.FontSize = 8
     End If
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
Hi Petrobras,

Did you see my post earlier?  

You can check for nulls on any of those, but it has to be done individually:

>Nz(Me.txtSum Or Me.txtCounter Or Me.Email, "") = 0
 If Nz(Me.txtSum,0) = 0  Or nz(Me.txtCounter,0) = 0 Or  nz(Me.Email, 0) = 0 then

etc...

Author

Commented:
Hi Miriam, no I did not see the post.  however, I think I tried one of those fields but I will use your or staMnt in just a bit (using phone now).  thanks!

Author

Commented:
Mbizup, I tried the or statement and it does give me the error of "You entered an expression that has no value".

Any other ideas?

thanks

Author

Commented:
If any of those If Nz(Me.txtSum,0) = 0  Or nz(Me.txtCounter,0) = 0 Or  nz(Me.Email, 0) = 0 it still give me an error.  It is in the detail format section of the report.  
CERTIFIED EXPERT
Top Expert 2016

Commented:

 nz(Me.Email, 0) = 0

should be nz(Me.Email, "") =""

better use
 
     Len(Me.Email)=0
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
Just realized we're dealing with a report, not a form.  Where did you place Cap's originally suggested code?  I think that should work alone, provided that ALL the code in your Detail Format event is enclosed in the if-then block (ie: the if statement is the first statement, and the End If is the last).  

'**** Cap's Code here
if rs.bof or rs.eof then      
   msgbox "No records"
   exit sub
   else
    If Me.txtSum > 1 Then
        Me.lblAdditional.Visible = True
    End If
    '********* The rest of your code here
    'etc
end if

Author

Commented:
mbizup,

I put Cap's code in the ON LOAD.  I put the rest of my code below it like you said and it does not give me the error like it use to so it is just about fixed.  However, now it gives me a #error for text boxes that have an '=' for the control source.  
like this:  iif([txtCount]<1,1,Count(*))

Do you know why I would get a #error?  I think that will fix it completely if so.

thanks
Also in my
If Me.txtSum > 1 Then
        Me.lblAdditional.Visible = True
    End If
which is found below, does not seem to fire off like it did before.  I am thinking it is because it is no longer on teh detail format but now on load.  

thank you for the help!

here is the entire code I have in this report just so we are on the same page:
Option Compare Database
Private Sub form_load()
'*****If there are NO RECORDS then the following takes care of if records <1
If Nz(Me.txtSum, 0) = 0 Or Nz(Me.txtCounter, 0) = 0 Or Nz(Me.Email, 0) = 0 Then
Exit Sub
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone

If rs.BOF Or rs.EOF Then
   Exit Sub
   Else
    If Me.txtSum > 1 Then
        Me.lblAdditional.Visible = True
    End If
End If

If Me.txtSum > 1 Then
        Me.lblAdditional.Visible = True
        Result = SetTextBoxProperties(Me)
    End If

'Code will suppress if records >1.  1 records constant even if 1 or 2 records.
    If Me.txtCounter > 1 Then
        Me.Detail.Visible = False
    Else
        Me.Detail.Visible = True
    End If
 
   

If Me.Email.FontSize = 10 Then
     If Len(Me.Email.Value) > 26 Then
         Me.Email.FontSize = 8
     End If
    End If

' if the font is smaller, but it still doesn't fit, go smaller still.
'The number of characters that can fit at size 10 is assumed to be 15 here

If Me.Email.FontSize = 9 Then
     If Len(Me.Email.Value) > 30 Then
         Me.Email.FontSize = 8
     End If
End If


If Me.Email.FontSize = 8 Then
     If Len(Me.Email.Value) > 33 Then
         Me.Email.FontSize = 7
     End If
End If


End Sub

Author

Commented:
damn it.  I had not put it between the end if's but after them.  Anyways, the #error is fixed.  

Do you know how to make the lblAdditional part fire?
If Me.txtSum > 1 Then
        Me.lblAdditional.Visible = True
        Result = SetTextBoxProperties(Me)
    End If

I think it may have something to do with that setTextBoxProperties(Me). It blanks it all out if >1 so the lblAdditional is not visible.  lblAdditional is something that appears stating "There is >1 result".  

hmmmm
Top Expert 2006

Commented:
When you step through the code one line at a time, is it stepping past your code? Perhaps on the If rs.eof or rs.bof part?

you have this in there twice also

If rs.BOF Or rs.EOF Then                 <------------Going to Exit Sub here?
   Exit Sub
   Else
    If Me.txtSum > 1 Then
        Me.lblAdditional.Visible = True    <------------(1)
    End If
End If

If Me.txtSum > 1 Then
        Me.lblAdditional.Visible = True     <-----------(2)
        Result = SetTextBoxProperties(Me)
    End If

You could consolidate this part based on character len I think


     If Len(Me.Email.Value) >= 26  and Len(Me.Email.Value) < 30 Then
         Me.Email.FontSize = 9
     elseif Len(Me.Email.Value) >=30  and Len(Me.Email.Value) <33 then
         Me.Email.FontSize = 8
     elseif Len(Me.Email.Value) >33 then
         Me.Email.FontSize = 7
     End If

Author

Commented:
OK I debugged it, which I did not do before and it says Method or data member not found and highlights RecordsetClone within set rs = Me.RecordsetClone
here:
Option Compare Database
Private Sub form_load()
'*****If there are NO RECORDS then the following takes care of if records <1
If Nz(Me.txtSum, 0) = 0 Or Nz(Me.txtCounter, 0) = 0 Or Nz(Me.Email, 0) = 0 Then
Exit Sub
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone


Stepping through:  I put the red line (not sure what else to call it) but I did not get any errors so I could not step through. I put it at the very top too. I do not know how else to make it step through.  

Top Expert 2006

Commented:
Use this instead of the recordsetclone thing.

dim rs as DAO.recordset
dim strSQL as string
strSQL = "Select * from tblGuarantor;"
set rs = currentDB.openrecordset(strSQL)
if rs.EOF then
ExitSub
Else
  Me.lblAdditional.Visible = True
end if
rs.close
set rs = nothing

put it all back together and see if you can get past the Method or Datamember error
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
Jeff's suggestion should work.  forms have recordsetclone's...reports don't.  Another workaround for a report is:

Dim rs As DAO.Recordset
Set rs = Currentdb.OpenRecordset(Me.RecordSource)
 

Author

Commented:
Well, that gives me a #error.  I think my biggest problem is that if there is >1 record I will have to have that record suppressed and only display the first record.  This is my major issue now.  The following code does work but only if records are >1.  The problem is if no records then get error "expression has no value" and highlights Me.txtSum>1.  So, the issue is if records <1 then how can I bypass the detail_format event or test for null I really dont know what it is.  

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    If Me.txtSum > 1 Then
        Me.lblAdditional.Visible = True
        Result = SetTextBoxProperties(Me)
    End If

'Code will suppress if records >1.  1 records constant even if 1 or 2 records.
    If Me.txtCounter > 1 Then
        Me.Detail.Visible = False
    Else
        Me.Detail.Visible = True
    End If
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
Try using an error handler to gracefully exit the detail format event when this error is encountered (meaning there are no records):

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
On Error GoTo eh
    If Me.txtSum > 1 Then
        Me.lblAdditional.Visible = True
        Result = SetTextBoxProperties(Me)
    End If

'Code will suppress if records >1.  1 records constant even if 1 or 2 records.
    If Me.txtCounter > 1 Then
        Me.Detail.Visible = False
    Else
        Me.Detail.Visible = True
    End If

 Exit Sub
eh:
If Err.Number = -2147352567 Then
    'No records
    Exit Sub
Else
   msgbox "Error Number: " & err.number
End If

Author

Commented:
Mbizup, that worked nice!  I have the #error in any textboxes that contain and = in the control source though.  I only get this #error when there are no records.  I guess it is in the on load event but not sure.  Do you have an idea on this one?  I thought that it was fixed earlier but I dont think it really was.

here is the entire code in this report:

Option Compare Database
Private Sub Form_Load()
Dim rs As DAO.Recordset
Dim strSQL As String
strSQL = "Select * from tblGuarantor;"
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.EOF Then
Exit Sub
Else
  Me.lblAdditional.Visible = True
End If
rs.Close
Set rs = Nothing


 If Len(Me.Email.Value) >= 26 And Len(Me.Email.Value) < 30 Then
         Me.Email.FontSize = 9
     ElseIf Len(Me.Email.Value) >= 30 And Len(Me.Email.Value) < 33 Then
         Me.Email.FontSize = 8
     ElseIf Len(Me.Email.Value) > 33 Then
         Me.Email.FontSize = 7
     End If


End Sub

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
On Error GoTo eh
    If Me.txtSum > 1 Then
        Me.lblAdditional.Visible = True
        Result = SetTextBoxProperties(Me)
    End If

'Code will suppress if records >1.  1 records constant even if 1 or 2 records.
    If Me.txtCounter > 1 Then
        Me.Detail.Visible = False
    Else
        Me.Detail.Visible = True
    End If

 Exit Sub
eh:
If Err.Number = -2147352567 Then
    'No records
    Exit Sub
Else
   'MsgBox "Error Number: " & Err.Number
End If

End Sub

Author

Commented:
an example of the control source that has a #error:
=IIf(IsNull([State]),[Country],[State] & ", " & [Country])
also
=IIf([txtCounter]<1,1,Count(*))
CERTIFIED EXPERT
Top Expert 2016

Commented:

=IIf(IsNull([State]),[Country],[State] & ", " & [Country])
--------------------^ remove this ")",  place it at the end

=IIf(IsNull([State],[Country],[State] & ", " & [Country]))



   
Top Expert 2006

Commented:
is txtSum in the detail section?
CERTIFIED EXPERT
Top Expert 2016

Commented:
forget my last post.....need some coffee

Author

Commented:
Cap:  I dont think I can do that because it says wrong numbder of arguments

Jeff:  Yes it is in the detail section
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
Are country and state bound to table fields, or are their control sources calculated?  Give this a try:

=iif(dcount("IDOrSomeOtherField", "tblGuarantor") = 0 , "", IIf(IsNull([State]),[Country],[State] & ", " & [Country]))

=iif(dcount("IDOrSomeOtherField", "tblGuarantor") = 0 , "", IIf([txtCounter]<1,1,Count(*)))

Author

Commented:
Mbizup, i still get the #error.
here is what I used:
=IIf(DCount("DealID","tblGuarantor")=0,"",IIf(IsNull([State]),[Country],[State] & ", " & [Country]))
I have DealID in the detail section and when records are >1 it does give me the dealID but <1 then it is blank.  

any other ideas?  

Author

Commented:
I think it is impt to say that I have been using this report for a long time and no #errors.  I am guessing it is somewhere within the code for when <1 records.  The iif statments are OK I believe because I do not get #errors if >1 recoreds.
Top Expert 2006

Commented:
P,
In this code, you're checking first to see if txtSum has a value, if it does everything's ok. Then you check TxtCounter and if it is greater than 1 then you make the whole detail section invisible. Can txtSum be >1 and txt Counter be > 1 also? So this means you're only allowing one record??  How are you summing just one record. Forgive me if I'm just not getting it here.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
On Error GoTo eh
    If Me.txtSum > 1 Then
        Me.lblAdditional.Visible = True
        Result = SetTextBoxProperties(Me)
    End If

'Code will suppress if records >1.  1 records constant even if 1 or 2 records.
    If Me.txtCounter > 1 Then
        Me.Detail.Visible = False
    Else
        Me.Detail.Visible = True
    End If

 Exit Sub
eh:
If Err.Number = -2147352567 Then
    'No records
    Exit Sub
Else
   'MsgBox "Error Number: " & Err.Number
End If

End Sub
Top Expert 2006

Commented:
Also, when you let the report run, besides the #errors, are there values in the fields for txtSum or State or County?

Author

Commented:
Jeff:
1. ''Can txtSum be >1 and txt Counter be > 1 also? So this means you're only allowing one record??  
Yes, Yes
2. ''How are you summing just one record.
It still sums it but just suppresses the view of it or makes the detail section of the second record not visible.  I append a report at the end of report stating "this is the complete list".  I have a lbl appearing stating "More than one Guarantor...check end of report".
3. ''Also, when you let the report run, besides the #errors, are there values in the fields for txtSum or State or County?
Yes, the values appear...no #errors at all

hope that clears it up a little
Top Expert 2006

Commented:
So move the code to populate these to your detail format sub

an example of the control source that has a #error:


If IsNull(me.[State]) then
 me.MyControl.value = me.[Country]
else
 me.MyControl.value = me.[State] & ", " & me.[Country]
end if

if me.[txtCounter]<1 then
    me.MyControl.value = 1
else
    me.MyControl.value = Count(*))   <---recordset count?
end if
CERTIFIED EXPERT
Top Expert 2016

Commented:
Petrobras,
may i suggest that you upload your db

http://www.ee-stuff.com

and give detailed explanation on what you want to do.
Top Expert 2006

Commented:
Yea...I'd like to see this monster too!! didn't you suggest that a couple of days ago Cap! lol
J

Author

Commented:
Jeff,

gave me a syntax here: me.txtCounter.value = Count(*))  ' <---recordset count?
(I added the txtCounter
If Me.[txtCounter] < 1 Then
    Me.txtCounter.Value = 1
Else
    me.txtCounter.value = Count(*))  ' <---recordset count?
End If

I will work on a db upload in a bit.
Top Expert 2006

Commented:
if you want txtCounter to just display the number of records in your recordset, use the method you used in your on_load event to get the recordcount.

Dim rs As DAO.Recordset
Dim strSQL As String
dim MyCount as Long
strSQL = "Select * from tblGuarantor;"
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.EOF Then
Exit Sub
Else
  MyCount = rs.recordcount
End If
rs.Close
Set rs = Nothing
Top Expert 2006
Commented:
change that to

Dim rs As DAO.Recordset
Dim strSQL As String
dim MyCount as Long
strSQL = "Select * from tblGuarantor;"
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.EOF Then
MyCount = 0
Else
  MyCount = rs.recordcount
End If
rs.Close
Set rs = Nothing

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
For some reason I can not ul to EE Stuff.  I think it is because of the network here at work.  I can from home.  Anyways, I ul to another place:
http://briefcase.wt.net/cgi-perl/ListContent?user=ett&cmd=listsub&sdir=public&id=fda2c695d6d7087aadb9b8440310b3f3

Open the only report and this is the one that has the #error when there are no records.  To add a record then must go to tblGuarantor and type 231 as DealID and then text for name and such.  

'Yea...I'd like to see this monster too!!
taint no monster but have been working on it for a couple years. adding stuff.

thank you...

CERTIFIED EXPERT
Top Expert 2016

Commented:
Petrobras,

=IIf([txtCounter]<1,1,Count(*))   'why do you want to use Count(*)

why not just use
=IIf([txtCounter]<1,1,[txtCounter])
CERTIFIED EXPERT
Top Expert 2016

Commented:
or

=IIf([txtCounter]<1,1,DCount("*","tblGuarantor"))
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
Also try the following control source to handle the error in the calculated state/province:

=IIf(DCount("*","tblGuarantor")=0,"",IIf(IsNull([State]),[Country],[State] & ", " & [Country]))

Author

Commented:
Cap, that got rid of the #error for that one.  Not sure why I wanted to use that.  

If I could get this one fixed then that woudl be the end of it:
=IIf(IsNull([State]),[Country],[State] & ", " & [Country])

I tried Jeff's idea and it says cant assign value to STATE or just that line:
If IsNull(Me.[State]) Then
 Me.StateCountry.Value = Me.[Country]
Else
 Me.StateCountry.Value = Me.[State] & ", " & Me.[Country]
End If
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
did you try my previous post?
CERTIFIED EXPERT
Top Expert 2016

Commented:
Petrobras,

add the field State and Country to the report, if you don't want to show it set the visible property to NO
Top Expert 2006

Commented:
=IIf([State] Is Null),[Country],[State] & ", " & [Country])

=IIf(nz([State]) ="",[Country],[State] & ", " & [Country])

=IIf([State] = "",[Country],[State] & ", " & [Country])


???


Author

Commented:
Cap, OK I added the fields and that solved the #error.  I see what the problem was now.  
Seems like this qstn is near the end then something else pops up.  Dang it!!  It would be too difficult to open another question and so I will keep this one going to keep the train of thought.  

The problem I have now is probably minor.  When records are <1 then the subreport rptAppSection7_Guarantor does not appear within the Parent.  But when  >1 it does appear and all is fine.  So, the problem now is <1 sub rpt rptAppSection7_Guarantor doesnt appear...it is just a blank space within the report.  The subreport appears by itself separately with no issues when <1.  

This is the code I have in the Parent that turns on and off the sub rpt
'make visible Guarantors subreport if >1
    If DCount("*", "tblGuarantor", "[DealID]= Forms!frmDeals!DealID") > 1 Then
        Me.rptAppSection7_Guarantor_Multi.Visible = True ' THIS ONE WORKS GOOD AND IS UNRELATED TO QSTN
        Me.rptAppSection7_Guarantor.Visible = True  'I ADDED THIS BUT SUBRPT IS STILL INVISIBLE
    Else
        Me.rptAppSection7_Guarantor_Multi.Visible = False ' THIS ONE WORKS GOOD AND IS UNRELATED TO QSTN
        Me.rptAppSection7_Guarantor.Visible = True    'I ADDED THIS BUT SUBRPT IS STILL INVISIBLE
       
    End If

Any ideas on this one?  I know this one is becoming a pain in the ***, sorry.  I still dont have it is in workable format but is very close.  

So again to summarize:
When records are <1 then the subreport rptAppSection7_Guarantor does not appear within the Parent.

I think it is somewhere in here within the code of sub report rptAppSection7_Guarantor that we have been working with (or maybe it needs to be in Parent?):

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

On Error GoTo eh
    If Me.txtSum > 1 Then
        Me.lblAdditional.Visible = True
        Result = SetTextBoxProperties(Me)
    End If

'Code will suppress if records >1.  1 records constant even if 1 or 2 records.
    If Me.txtCounter > 1 Then
        Me.Detail.Visible = False
    Else
        Me.Detail.Visible = True
    End If

 Exit Sub
CERTIFIED EXPERT
Top Expert 2016

Commented:
can you upload the one with the parent report?

Author

Commented:
ok hang on a sec...

Author

Commented:
here it is:
http://briefcase.wt.net/cgi-perl/ListContent?user=ett&cmd=listsub&sdir=public&id=f07ba5603570621f38e9df9385e3fe21

I have it set up right now with one record so all is well.  If you delete the record  in tblGuarantors making it <1 and go back to rptApplication you will see nothing when you should see rptAppSection7.  The one that says rptAppSection7_Multi is not pertinent to this question but I left it there because it should show the complete list of records when >1.  

**if you delete a record you must use DealID of 231 when entering a record.  

thank you...
CERTIFIED EXPERT
Top Expert 2016

Commented:
Petrobras,
Why do you need to show the blank sub report?
Normally sub report that don't have any Data will not show.

Author

Commented:
Cap, I did not see this response.  had an issue with email.  
<Why do you need to show the blank sub report?
I need to show it because I am mimicking an application.  If that section does not show then it would not be correct.  It needs to show up with no data if no records.  I will have to duplicate this many times so if you could show me this one time how to do it then I will replicate.  

thank you

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013
Commented:
I had a similar requirement a while ago.  I handled it by creating a blank, unbound report showing just the labels and empty boxes.   I used conditional code in the command button that opened the report to determine which report to use.  Something like this:

Private Sub cmdOpenReport_Click
If  Nz(Dcount("*", "MyTable"),0) = 0 Then
    DoCmd.OpenReport "rptMyBlankReport", acPreview    '**Open the blank report if no records
Else
    DoCmd.OpenReport "rptMyBoundReport", acPreview   '** Otherwise use the bound report
end if
end Sub

Maybe not the most elegant approach, but it did the trick.
CERTIFIED EXPERT
Top Expert 2016
Commented:
CERTIFIED EXPERT
Top Expert 2016

Commented:
miriam,
just curious, why you use Nz in a dcount?
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
Good call, Rey.  It's not needed. Aircode mistake induced by fatigue from a late lunch.  :-(

P:  DCount doesn't return a null when no records are found like DLookup, DMax, DMin do.  It returns a zero.

Author

Commented:
Cap, question: if there are no records in tblGuarantorA, I assume this is the one to use, then does the rptApplication still show this section?  I go to the table and delete the data but I do not see the rptAppSection7_Guarantor section appearing within rptApplication.  

Is this how it is intended to perform?
I see your post Miriam but have not tried it as of yet.  I will wait for Cap to respond.  Work is busy right this sec...
CERTIFIED EXPERT
Top Expert 2016

Commented:
you need that dummy table tblGuarantorA and it must contain blank records.
take note of Query2, which is now the record source of  rptApplication and rptAppSection7_Guarantor

query2  is a union query of  tblGuarantor and tblGuarantorA

delete the records from tblGuarantor but not the blank record from tblGuarantorA

Author

Commented:
Cap,

The application is a pared down version of what I have in my db.  There are 15 or so different section just like this one in rptApplication.  
<take note of Query2, which is now the record source of  rptApplication and rptAppSection7_Guarantor
Will that change much now?  
CERTIFIED EXPERT
Top Expert 2016

Commented:
Will that change much now?  
what do you mean?

Author

Commented:
One other thing is that it see multiple guarantors when there is only one.  I might give miriams a try too...

Author

Commented:
What I mean is that you only had a very small portion of the rptapplication.  Knowing that there are more sections, I am only assuming that might change what code you have but maybe not.  

I was a little bit confused by this statement:
<use qry2 as the records source of rptApplication
I have a different record source for rptApplication on my side.

SELECT tblDeals.* FROM tblDeals WHERE (((tblDeals.DealID)=forms!frmDeals!DealID));

Author

Commented:
I will play around with it tonight.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.