Print a report from current recordset with no fields

[themushroomking]

Hi folks. Desperate and weekend in sight, i can not stop until i fix this. Please bear with me...

- I am on a subform that fetches some data from excel files. (Functions like a search form)
- With the submit button, i put the search results into the table with a recordset. (code below)

ALL THIS WORKS FINE! Now, i want to print a report. From the same record im just added with the code below.
How can i do this? As im not currently on a form that has no record_ID (because its for searching only)

How do i print out the form, with the current "item_ID" (the one i just added)

Private Sub Submit_Click()


Dim db As Database
Dim rec As Recordset
Set db = CurrentDb
Set rec = db.OpenRecordset("Select * from inboundreturns_items")
rec.AddNew
rec("returnID") = Forms!inboundreturns.returnID.Value
rec("ireq") = Me.ireq
rec("item") = Me.item
rec("tasknumber") = Me.tasknumber
rec("country") = Me.country
rec("engineername") = Me.engineer
rec("connote") = Me.connote
rec("status") = Me.status
rec.Update
Set rec = Nothing
Set db = Nothing
End Sub




(FYI: The report is based on the table inboundreturns_items)

[aytee111]

You can populate a field with: DLookup("Max(Item_ID)","inboundreturns_items","ret urnID=" & Forms!inboundreturns.returnID.Value

[themushroomking]

Awesome! Thanks aytee111 for the swift reply. Im grateful.

So i enter your code into the code i put up there? Or after that along with my report printout code?
Something like:


Private Sub Submit_Click()


Dim db As Database
Dim rec As Recordset
Set db = CurrentDb
Set rec = db.OpenRecordset("Select * from inboundreturns_items")
rec.AddNew
rec("returnID") = Forms!inboundreturns.returnID.Value
rec("ireq") = Me.ireq
rec("item") = Me.item
rec("tasknumber") = Me.tasknumber
rec("country") = Me.country
rec("engineername") = Me.engineer
rec("connote") = Me.connote
rec("status") = Me.status
rec.Update
Set rec = Nothing
Set db = Nothing



DLookup("Max(Item_ID)","inboundreturns_items","ret urnID=" & Forms!inboundreturns.returnID.Value


DoCmd.OpenReport "report1", acPreview, , "item_ID = " & Me!returnID
Exit_print_report_Click:
Exit Sub
Err_print_report_Click:
MsgBox Err.Description
Resume Exit_print_report_Click

End Sub

[aytee111]

You need a variable name, or a textbox, some place to store the value.
Dim ItemID as Long (if that is what it is)
ItemID=Dlookup...
Then open the report either with this ItemID or the return ID - not sure what you are doing there.

[themushroomking]

Thanks for sticking with me. Im not the logically smart person there is :P
I tried a bunch of things. I thought i had it almost..but somethings off....

Now im stuck with this. It says "cant find the field "item_ID" referred to in your expression"
I did like you said and added a field to the search form to store the value. Called "returnidfield"


....
Dim returnidfield As Long
DLookup ("Max(item_ID)"), "inboundreturns_items", "returnID=" & Forms!inboundreturns.returnID.Value

DoCmd.OpenReport "report1", acPreview, , "item_ID = " & Me!item_ID
Exit_print_report_Click:
Exit Sub
Err_print_report_Click:
MsgBox Err.Description
Resume Exit_print_report_Click


You were right, i made a mistake. Im trying to print according to item_ID.

[aytee111]

Dim ItemID AS Long
ItemID=DLookup ("Max(item_ID)"), "inboundreturns_items", "returnID=" & Forms!inboundreturns.returnID.Value
DoCmd.OpenReport "report1", acPreview, , "item_ID = " & ItemID

[aytee111]

Just noticed that the syntax for your DLookup is incorrect: Dlookup(......) - your closing bracket is in the wrong place, it should be at the end after the word Value, not after item_ID.

[themushroomking]

WOW! That totally worked! Youre a true saviour.
Thanks for sticking it out with me here. A thousand thanks!!!!!

For anyone interrested or in need of help, here is the final code:


Private Sub Submit_Click()

Dim db As Database
Dim rec As Recordset
Set db = CurrentDb
Set rec = db.OpenRecordset("Select * from inboundreturns_items")
rec.AddNew
rec("returnID") = Forms!inboundreturns.returnID.Value
rec("ireq") = Me.ireq
rec("item") = Me.item
rec("tasknumber") = Me.tasknumber
rec("country") = Me.country
rec("engineername") = Me.engineer
rec("connote") = Me.connote
rec("status") = Me.status
rec.Update
Set rec = Nothing
Set db = Nothing

Dim ItemID As Long
ItemID = DLookup("Max(item_ID)", "inboundreturns_items", "returnID=" & Forms!inboundreturns.returnID.Value)

DoCmd.OpenReport "report1", acPreview, , "item_ID = " & ItemID
Exit_print_report_Click:
Exit Sub
Err_print_report_Click:
MsgBox Err.Description
Resume Exit_print_report_Click

End Sub

[aytee111]

YW! Glad it works