printing currrent record (again)

[Stuart Green]

I was kindly helped out here with a problem I had wanting to put a print button on a form so that it only prints the record on screen. The code I ended up with that works is as follows

Private Sub printbutton_Click()
DoCmd.OpenReport "rpt_each_property2", acPreview, , "ref=[forms]![frm_each_property]![ref]"

End Sub

Now this works great when I am going through the form frm_each_property on its own, but I would like to use it as a sub form. (I have one unique record of a person who may own several properties). I want to pull up that persons record, navigate through their properties in the sub form until I get to the one that I want and then print off that particular property record. When I try the button as a sub form I am prompted to enter the criteria forms!frm_each_property!ref. Any suggestions please as to how I can do this

 

[FloBob]

Ok here we go. I still use dao recordsets 'they run faster '.
you will have to enable your references Microsoft DAO 3.6 object library. its under tools and references in the VBA editor.

sub whatever_click()

dim main as dao.recorset
dim crit as string


set main = currentdb.openrecordset("select * from sometable where owner = '" & somecontrol & "'")

crit = "" ' I do this for my confort

do until main.eof

if crit = "" then
crit = "ID = " & main!ID
else
crit = crit & " or ID = " & main!id
end if

main.movenext

loop

DoCmd.OpenReport "rpt_each_property2", acPreview, , Crit

end sub


this should work look it over email me if you have problems.

 

[Rich]

You're missing reference to the main form
DoCmd.OpenReport "rpt_each_property2", acPreview, , "ref=[Forms]![MainFormName]![SubFormName]![ref]"

 

[Fizzio]

Just out of interest, which is quicker and the correct syntax (as both seem to work)
DoCmd.OpenReport "rpt_each_property2", acPreview, , "ref=[Forms]![MainFormName]![SubFormName]![ref]"
or
DoCmd.OpenReport "rpt_each_property2", acPreview, , "ref= " & [Forms]![MainFormName]![SubFormName]![ref]

 

[FloBob]

I assumed you were doing somthing different rich has the right Idea on this I believe.

 

[Stuart Green]

What a wonderful place this is! Many thanks all, I am glad the simpler solution prevailed as the DAO stuff had me trembling