Solved

Records and <1

Posted on 2006-10-22
64
176 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
  • 28
  • 14
  • 12
  • +1
64 Comments
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
Hey P,
Open it as a recordset.

IF rs.EOF then
exit sub
else
do stuff

J
0
 

Author Comment

by:Petrobras
Comment Utility
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
Comment Utility
Petrobras,
Have you tried using Nz, skipping the record count check?

If Nz(me.txtSum,0) >1 then
 ... etc
end if
0
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
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
Comment Utility
jeff, I hv the lbl set visiBle to NO hardcoded.  will test when I get baCk.  thanks
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility

 nz(Me.Email, 0) = 0

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

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

Expert Comment

by:mbizup
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
an example of the control source that has a #error:
=IIf(IsNull([State]),[Country],[State] & ", " & [Country])
also
=IIf([txtCounter]<1,1,Count(*))
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility

=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
Comment Utility
is txtSum in the detail section?
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
forget my last post.....need some coffee
0
 

Author Comment

by:Petrobras
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Also, when you let the report run, besides the #errors, are there values in the fields for txtSum or State or County?
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:Petrobras
Comment Utility
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
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
Petrobras,

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

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

Expert Comment

by:Rey Obrero
Comment Utility
or

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

Expert Comment

by:mbizup
Comment Utility
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
Comment Utility
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
Comment Utility
did you try my previous post?
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
=IIf([State] Is Null),[Country],[State] & ", " & [Country])

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

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


???


0
 

Author Comment

by:Petrobras
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
can you upload the one with the parent report?
0
 

Author Comment

by:Petrobras
Comment Utility
ok hang on a sec...
0
 

Author Comment

by:Petrobras
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
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
Comment Utility
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 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 200 total points
Comment Utility
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
miriam,
just curious, why you use Nz in a dcount?
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
Will that change much now?  
what do you mean?
0
 

Author Comment

by:Petrobras
Comment Utility
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
Comment Utility
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
Comment Utility
I will play around with it tonight.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now