subreport problems

[genevx]

hi,
im designing a report that gives transaction details for an individual person within an inputted date range. Within this report at the report footer, I've inserted a subreport which is supposed to be giving the month to date (beginning of current month to today) totals for that person. the strange thing is that the subreport totals show if i manually double click and open the main report. but, if i use a button to open the report in preview mode, the subreport never shows up. Any ideas as to why this is?
any help would be greatly appreciated!

genevx

 

[Fizzio]

What command line are you using to open up your report via a button?

 

[genevx]

well it runs through a series of checks first by opening up a data table as a recordset, but i doubt that affects the report since no changes are made. the main command line is just: DoCmd.OpenReport DocName, acPreview.
thanks! =)

 

[Fizzio]

If they have the same recordset (report and recordset) make sure that you close the recordset before opening the report with
rs.close. this may make a difference as it 'frees up' that particular recordset for use again.

 

[genevx]

actually...i created a button with only the simple command: DoCmd OpenReport DocName, acPreview and the subreport total shows. so, that does mean the checks that the button runs through affects the report...but i have no idea what it could possibly be since none of the checks actually change anything. what's even stranger is that even when i put the command at the very beginning of the code of the button that doesn't work, it still returns me nothing from the subreport. oh...and the recordset is closed after each check and remains closed before the OpenReport command is used

 

[Fizzio]

Try creating a new button and copying your code to that (especially if it worked on a 'clean' button) Failing that, post your code.

 

[genevx]

yargh...it still doesn't work...seems like the code is cursed... =P i'll post the code...hope it isn't TOO confusing. btb, i notice you're from newcastle. this is a long shot, but would you happen to know an Andrew Done there? he goes to uni there.

For the code: concentrate on how it gets to "stDocName = rptProdMktMkrInd" and then prints it at the very end. if this is too long, just tell me. =P it's divided into sections depending on what the user enters or does not enter on a form.

Set db = CurrentDb()

stDocName = "rptProdMktMkr"
stDocName2 = "rptProdMktMkrToday"


Set rec = db.OpenRecordset("Trade Table")
d = 1
If cboMktMkrInd > " " And txtSecurity <= " " Then
d = 0
e = 0
Do While Not rec.EOF
If rec("trade_date") >= txtBeginDate And rec("trade_date") <= txtEndDate Then
If rec("Market_Maker_ID") = cboMktMkrInd Then d = 1
End If
If chkToday = True And rec("trade_date") = txtEndDate And rec("Market_Maker_ID") = cboMktMkrInd Then e = 1
rec.MoveNext
Loop
rec.Close
stDocName = "rptProdMktMkrInd"
stDocName2 = "rptProdMktMkrIndToday"
If chkSyndicate = True Then
stDocName = "rptProdMktMkrIndNoSynd"
stDocName2 = "rptProdMktMkrIndTodayNoSynd"
End If
End If

Set rec = db.OpenRecordset("Trade Table")
If txtSecurity > " " And cboMktMkrInd <= " " Then
d = 0
e = 0
Do While Not rec.EOF
If rec("trade_date") >= txtBeginDate And rec("trade_date") <= txtEndDate Then
If rec("Security_Symbol") = txtSecurity And rec("Market_Maker_ID") > "" Then d = 1
End If
If chkToday = True And rec("Trade_date") = txtEndDate And rec("Security_Symbol") = txtSecurity And rec("Market_Maker_ID") > "" Then e = 1
rec.MoveNext
Loop
rec.Close
stDocName = "rptProdMktMkrAllSecInd"
stDocName2 = "rptProdMktMkrAllSecIndToday"
If chkSyndicate = True Then
stDocName = "rptProdMktMkrAllSecIndNoSynd"
stDocName2 = "rptProdMktMkrAllSecIndTodayNoSynd"
End If
End If

Set rec = db.OpenRecordset("Trade Table")
If txtSecurity > "" And cboMktMkrInd > " " Then
d = 0
Do While Not rec.EOF
If rec("trade_date") >= txtBeginDate And rec("trade_date") <= txtEndDate Then
If rec("Security_Symbol") = txtSecurity And rec("Market_Maker_ID") = cboMktMkrInd Then d = 1
End If
If chkToday = True And rec("Trade_Date") = txtEndDate And rec("Security_Symbol") = txtSecurity And rec("Market_Maker_ID") = cboMktMkrInd Then e = 1
rec.MoveNext
Loop
rec.Close
stDocName = "rptProdMktMkrIndSecInd"
stDocName2 = "rptProdMktMkrIndSecIndToday"
If chkSyndicate = True Then
stDocName = "rptProdMktMkrIndSecIndNoSynd"
stDocName2 = "rptProdMktMkrIndSecIndTodayNoSynd"
End If
End If

Set rec = db.OpenRecordset("Trade Table")
If stDocName = "rptProdMktMkr" Then
d = 2
e = 1
Do While Not rec.EOF
If rec("trade_date") >= txtBeginDate And rec("trade_date") <= txtEndDate Then
If rec("Market_Maker_ID") > " " Then d = 1
End If
rec.MoveNext
Loop
rec.Close
If chkSyndicate = True Then
stDocName = "rptProdMktMkrNoSynd"
stDocName2 = "rptProdMktMkrTodayNoSynd"
End If
End If
If d = 0 Then response = MsgBox("Records for '" & cboMktMkrInd & "' do not exist for the security, " & txtSecurity & ", within the time range of " & txtBeginDate & " - " & txtEndDate & ".", , "End of Query")
If chkToday = True And e = 0 Then response = MsgBox("Records for '" & cboMktMkrInd & "' do not exist for the security, " & txtSecurity & ", for the day: " & txtEndDate, , "Data Error")
If d = 1 Then
DoCmd.OpenReport stDocName, acViewPreview
If chkToday = True And e = 1 Then DoCmd.OpenReport stDocName2, acViewPreview
End If
If d = 2 Then response = MsgBox("No Market Maker records exist from " & txtBeginDate & " - " & txtEndDate & ".", , "End of Query")
txtSecurity = ""
cboMktMkrInd = ""

 

[Fizzio]

A long shot about your mate was an understatement! Newcastle has 2 Uni each with a population of about 10k and Newcastle is hardly a little village!

The code is rather long winded but it seems to work for you. I couldn't see anthing wrong with the openreport stDocName or the generation of the DocName. Is this the only report the subreport does not show for? (Or is it the only one with a subreport) What is the controlsource of the subreport?

 

[genevx]

lol...i thought so....i've been to newcastle and it's quite a snazzy place. well...it was worth a shot... btw, newcastle ale rocks! =P
anyway, i was playing around with the database...and no, this isn't the only report that doesn't show. you see, i need reports for three categories of people: market makers, sales traders, and sales reps. When i create subreports for each of the reports, they always shows if I: 1) open it manually by double clicking, or 2) open it with any button EXCEPT the one that it's supposed to be opened with (i.e. put open market maker report command in sales trader button). i can put the DocMd.Openreport command line at the VERY beginning of the button it's supposed to be in and it still will give me nothing. i'm guessing it has to be SOMETHING with the code, since all of the buttons are structured the same way, just with different report names in them. it's quite frustrating...=P