Solved

Records and <1

Posted on 2006-10-22
64
220 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
0
Comment
Question by:Petrobras
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 28
  • 14
  • 12
  • +1
64 Comments
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17784011
Hey P,
Open it as a recordset.

IF rs.EOF then
exit sub
else
do stuff

J
0
 

Author Comment

by:Petrobras
ID: 17784079
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 :)
0
 
LVL 61

Expert Comment

by:mbizup
ID: 17784082
Petrobras,
Have you tried using Nz, skipping the record count check?

If Nz(me.txtSum,0) >1 then
 ... etc
end if
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 34

Expert Comment

by:jefftwilley
ID: 17784122
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



0
 

Author Comment

by:Petrobras
ID: 17784138
jeff, I hv the lbl set visiBle to NO hardcoded.  will test when I get baCk.  thanks
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 17784208
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
0
 

Author Comment

by:Petrobras
ID: 17784568
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
0
 
LVL 61

Expert Comment

by:mbizup
ID: 17784744
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...
0
 

Author Comment

by:Petrobras
ID: 17784832
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!
0
 

Author Comment

by:Petrobras
ID: 17784990
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
0
 

Author Comment

by:Petrobras
ID: 17785008
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.  
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 17785027

 nz(Me.Email, 0) = 0

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

better use
 
     Len(Me.Email)=0
0
 
LVL 61

Expert Comment

by:mbizup
ID: 17785395
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
0
 

Author Comment

by:Petrobras
ID: 17785567
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
0
 

Author Comment

by:Petrobras
ID: 17785587
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
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17785634
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

0
 

Author Comment

by:Petrobras
ID: 17785714
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.  

0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17785896
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
0
 
LVL 61

Expert Comment

by:mbizup
ID: 17785912
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)
 
0
 

Author Comment

by:Petrobras
ID: 17786112
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
0
 
LVL 61

Expert Comment

by:mbizup
ID: 17786124
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
0
 

Author Comment

by:Petrobras
ID: 17788420
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
0
 

Author Comment

by:Petrobras
ID: 17788425
an example of the control source that has a #error:
=IIf(IsNull([State]),[Country],[State] & ", " & [Country])
also
=IIf([txtCounter]<1,1,Count(*))
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 17788473

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

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



   
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17788481
is txtSum in the detail section?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 17788605
forget my last post.....need some coffee
0
 

Author Comment

by:Petrobras
ID: 17788619
Cap:  I dont think I can do that because it says wrong numbder of arguments

Jeff:  Yes it is in the detail section
0
 
LVL 61

Expert Comment

by:mbizup
ID: 17788690
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(*)))
0
 

Author Comment

by:Petrobras
ID: 17788816
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?  

0
 

Author Comment

by:Petrobras
ID: 17788903
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.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17788951
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
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17788974
Also, when you let the report run, besides the #errors, are there values in the fields for txtSum or State or County?
0
 

Author Comment

by:Petrobras
ID: 17789047
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
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17789104
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
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 17789142
Petrobras,
may i suggest that you upload your db

http://www.ee-stuff.com

and give detailed explanation on what you want to do.
0
 
LVL 34

Expert Comment

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

Author Comment

by:Petrobras
ID: 17789332
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.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17789359
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
0
 
LVL 34

Assisted Solution

by:jefftwilley
jefftwilley earned 100 total points
ID: 17789368
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
0
 

Author Comment

by:Petrobras
ID: 17789527
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...

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 17789608
Petrobras,

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

why not just use
=IIf([txtCounter]<1,1,[txtCounter])
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 17789638
or

=IIf([txtCounter]<1,1,DCount("*","tblGuarantor"))
0
 
LVL 61

Expert Comment

by:mbizup
ID: 17789731
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]))
0
 

Author Comment

by:Petrobras
ID: 17789761
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
0
 
LVL 61

Expert Comment

by:mbizup
ID: 17789768
did you try my previous post?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 17789810
Petrobras,

add the field State and Country to the report, if you don't want to show it set the visible property to NO
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17789818
=IIf([State] Is Null),[Country],[State] & ", " & [Country])

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

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


???


0
 

Author Comment

by:Petrobras
ID: 17790753
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
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 17790799
can you upload the one with the parent report?
0
 

Author Comment

by:Petrobras
ID: 17790894
ok hang on a sec...
0
 

Author Comment

by:Petrobras
ID: 17791240
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...
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 17800869
Petrobras,
Why do you need to show the blank sub report?
Normally sub report that don't have any Data will not show.
0
 

Author Comment

by:Petrobras
ID: 17805475
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

0
 
LVL 61

Accepted Solution

by:
mbizup earned 200 total points
ID: 17805779
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.
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 200 total points
ID: 17805820
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 17805862
miriam,
just curious, why you use Nz in a dcount?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 17805895
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.
0
 

Author Comment

by:Petrobras
ID: 17806069
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...
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 17806121
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
0
 

Author Comment

by:Petrobras
ID: 17806669
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?  
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 17806679
Will that change much now?  
what do you mean?
0
 

Author Comment

by:Petrobras
ID: 17806689
One other thing is that it see multiple guarantors when there is only one.  I might give miriams a try too...
0
 

Author Comment

by:Petrobras
ID: 17806837
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));
0
 

Author Comment

by:Petrobras
ID: 17807118
I will play around with it tonight.
0

Featured Post

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which ā€¦
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final pā€¦

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question