retrieve textbox value from current report in vba open report function

[ccgirl5]

UPDATED: not sure if I need to create another post, but updating this one. (removed the subreport)




I created a report which opens from a Form using an on_click, do cmd event procedure

DoCmd.OpenReport stDocName, acViewReport, , "[qrycatlist].[evtdate]= 
Forms!    [hsubform]!evtdate and [qrycatlist].[evtcategory]= Forms!
[hsubform]!evcats and [qrycatlist].[evtod]= Forms![hsubform]!evtod") 
All is based on date, category, time The report is named: caterpt1

Within the header of the report (catrpt), I have a textbox that "counts" the number of people in the category based on the reports date, time, category.


The txtbox field called (tcounts) has a number of 26 (for this report). Using an expression builder I created the following

    tcounts=DCount("*","[qrycatlist]","[qrycatlist].[evtdate]= [tdate] and
[qrycatlist].[evtcategory]= [tcats]")

Can the Private Sub Report_Open(Cancel As Integer) refer to the txtbox field name tcounts on the report????????


And if it can, how do I get it to refer to the txtbox tcount. I've searched and searched and can't find the answer.


How do I get the function to retrieve the "tcounts" field.

Private iTotal As Integer


Private Sub Report_Open(Cancel As Integer)
 get total record count
    iTotal = Report!tcounts
 end Sub

OR

   Private Sub Report_Open(Cancel As Integer)
  ' get total record count
iTotal = DCount("*", "qrycatlist") <----this needs to have the where
     values of the report.  (date, time, category)
End Sub

The iTotal value is not working. I don't think I am typing it in correctly or not using the right function.


I keep getting errors 2465 (can't find the field)


If i were to use the 2nd private sub report open, how would I pull the values from the main report?


I can't get the function to use it!!! Almost there but can't seem to get it all to jive. HELP!!!


The purpose of this report is to list the people in the event based on category, date and time and create extra lines based on the total number of people. (50 lines show 26 with people, and remaining blank lines with numbers) IE to write in .


i have a function after the one above the uses the iTotal. (see post #3)

[ranman256]

Instead of iTotal, fill the textbox on the form,
txtBox= Dcount....

[ccgirl5]

Thank you for the reply. I need the itotal number to figure out how many "blank" numbered rows needed to fill the rest of the page.

The rest of the code is below.

If there are lets say 26 people, I need to show lets say 24 more numbered, but blank rows. I tried many different ways to do this, and found this way the best. It continues the rows until 50, but formats the row's information "white." I also created away to continue to number the rows until 50 (but that is also on the main report page..that's why the visible is there below)

iLines = 50

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



  Static iLine As Integer
  iLine = iLine + 1
  If iLine < iTotal Then
    
  ElseIf iLine = iTotal Then
    
    If iLine < iLines Then Me.NextRecord = False
  Else
  Dim lngNextID As Long
 lngNextID = Val(iLine)
  
    
   Me!enum.ForeColor = vbWhite
    Me!ename.ForeColor = vbWhite

    Me!enum2.Visible = True
 '   Me.ero2 = iLines
 Me.enum2= lngNextID
  
    If iLine < iLines Then Me.NextRecord = False
  End If
End Sub

[NTC]

I recommend you manage your record set count at the query level, not at the report level.

It is relatively simple to manage the record set count if it is always suppose to be 50; first have a '50 table' that is 50 records numbered 1 to 50; outer join the 50 table to the data records to create a 50 record set for your report.

[ccgirl5]

That's a brilliant idea!!!!!! Thanks so much!!! Very simple and less coding complication.

[ccgirl5]

I recommend you manage your record set count at the query level, not at the report level.

It is relatively simple to manage the record set count if it is always suppose to be 50; first have a '50 table' that is 50 records numbered 1 to 50; outer join the 50 table to the data records to create a 50 record set for your report.

This worked, until I tried to use criteria in the where clause

is there a way to show all the field on the tbl50 (number 1-50) and the info for the event table, where the event table has where statement. ?


++++++++++++++figured it out, see below++++++++++++

I figured it out. I used a select inside the query and used the form!properties in the subselect to get the criteria needed.

so when I clicked on the form it passed the form information to the select in the query. Kinda tricky, but got it.