need OrderID from form when printing Report

[rjw]

I have a form 'Job Entry' which saves the OrderID to the database when I click on Print. Following this three report pages are opened. one after the other. I need to capture the OrderID number on the first page that's opened. I wish to query the databse using VBA to get the three vendorID's that hwere saved as part of the Order. How do I get the OrderID from the 'Job Entry' form and make use of it?

[xCav8r]

To get the OrderID from the form called "Job Entry", from the form's class module in any procedure, type Me.OrderID.

Assuming that there's a relationship between the three vendorIDs that you mentioned and an OrderID, you'll need a query like "SELECT OrderID, 1VendorID, 2VendorID, 3VendorID FROM tblOrders WHERE OrderID='" & Me.OrderID & "';" You'll obviously need to adjust that so it works for your table structure.

Questions:

1. Are you saying that three individual reports are opened when you click print?
2. Are you talking about a custom made button when you say click print?
3. Is OrderID on the report different than OrderID on the form? If so, why can't you just get it from the form?
4. What do you mean by make use of it? What do you want to do except know which vendorIDs have a relationship with the OrderID?

[rjw]

The print button opens 3 separate reports, one right after the other. I want to add the three vendors names and address to each of the reports. At the moment only Vendor 1 or Vendor 2 or vendor 3 are displayed on each report. I was hoping I could simply call Me.OrderID at the beginning of each report and run a VBA module to extract the name and addresses of all three vendors.

rjw

[xCav8r]

Why do you need to extract it? Can't you just use a select query to get it and base the report on that?

[rjw]

I have a textbox on the report called txt_OrderID. The Control Source is set to OrderID. I assume this means that the report has done a Select query of the Orders table and it should show the OrderID when the form loads. However, the form is currently setup to only find an individual Lender_Address. I need all three to be printed out in the form, So I'm trying to do this by adding a module to the form and write some VBA script to do it.


[vba]Dim OrderID As Integer
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordseet
Dim conn As New ADODB.Connection
Dim LLender1_id As String, LLender1_name As String, LLender1_address As String, LLender1_city As String, LLender1_state As String, LLender2_id As String
Dim LLender2_name As String, LLender2_address As String, Llender2_city As String, LLender2_state As String, LLender3_id As String, LLender3_name As String
Dim LLender3_address As String, LLender3_city As String, LLender3_state As String
'get OrderID from FORM Label
OrderID = Forms!lbl_OrderID.Value

' get lender codes
Public Sub getLenderCodes()
Set rs = Server.CreateObject("ADODB.Recordset")
On Error GoTo Error_MayCauseAnError
With cmd
.commandtext = "SELECT * FROM Orders WHERE OrderID =" & OrderID
.commandtype = adCmdUnknown
.ActiveConnection = CurrentProject.Connection
'Request the recordset
.Set rs = .Execute
End With
' Lender Codes
If Not rs.EOF Then
LLender1_id = rs("LenderCode")
LLender2_id = rs("LenderCode2")
LLender3_id = rs("LenderCode3")
End If
rs = Nothing
Error_MayCauseAnError:
On Error Resume Next
End Sub
' get lender1 detail
Public Sub getLender1Detail()
Set rs = Server.CreateObject("ADODB.Recordset")
On Error GoTo Error_MayCauseAnError
With cmd
.commandtext = "SELECT * FROM Lenders WHERE LenderCode =" & Lender1_id
.commandtype = adCmdUnknown
.ActiveConnection = CurrentProject.Connection
'Request the recordset
.Set rs = .Execute
End With
' lender1 details
If Not rs.EOF Then
LLender1_name.Value = rs("LenderContrName")
LLender1_address.Value = rs("Address")
LLender1_city.Value = rs("City")
LLender1_state.Value = rs("StateOrProvince")
End If
rs = Nothing
Error_MayCauseAnError:
On Error Resume Next
End Sub

'get lender2 details
Public Sub getLender2Detail()
Set rs = Server.CreateObject("ADODB.Recordset")
On Error GoTo Error_MayCauseAnError
With cmd
.commandtext = "SELECT * FROM Lenders WHERE LenderCode =" & Lender2_id
.commandtype = adCmdUnknown
.ActiveConnection = CurrentProject.Connection
'Request the recordset
.Set rs = .Execute
End With
' lender2 details
If Not rs.EOF Then
LLender2_name = rs("LenderContrName")
LLender2_address = rs("Address")
Llender2_city = rs("City")
LLender2_state = rs("StateOrProvince")
End If
rs = Nothing
Error_MayCauseAnError:
On Error Resume Next
End Sub

' get lender 3 details
Public Sub getLender3Detail()
Set rs = Server.CreateObject("ADODB.Recordset")
On Error GoTo Error_MayCauseAnError
With cmd
.commandtext = "SELECT * FROM Lenders WHERE LenderCode =" & Lender3_id
.commandtype = adCmdUnknown
.ActiveConnection = CurrentProject.Connection
'Request the recordset
.Set rs = .Execute
End With
' lender3 details
If Not rs.EOF Then
LLender3_name = rs("LenderContrName")
LLender3_address = rs("Address")
LLender3_city = rs("City")
LLender3_state = rs("StateOrProvince")
End If
rs = Nothing
Error_MayCauseAnError:
On Error Resume Next
End Sub

' populate form labels
Public Sub populateLabels()
On Error GoTo Error_MayCauseAnError
' LENDER 1
Forms!lender1_name.Value = LLender1_name
Forms!Lender1_address.Value = LLender1_address
Forms!Lender1_city_state.Value = LLender1_city & ", " & LLender1_state


' LENDER 2
Forms!Lender2_name.Value = LLender2_name
Forms!lender2_address.Value = LLender2_address
Forms!Lender2_city_state.Value = Llender2_city & ", " & LLender2_state



' LENDER 3
Forms!Lender3_name.Value = LLender3_name
Forms!Lender3_address.Value = LLender3_address
Forms!Lender3_city_state.Value = LLender3_city & ", " & LLender3_state
Error_MayCauseAnError:
On Error Resume Next
End Sub[/vba]


Right now I've written these Subs and simply was going to call them in sequence. probably this could be one Sub. Anyways, I can't get the value of the orderID from

OrderID = Forms!lbl_OrderID.Value

This needs to run in order to populate the unbound labels

[Zack Barresse]

rjw, [uvba]did you know..[/uvba]

[xCav8r]

Thanks, Zack!!!!!

[Zack Barresse]

Thanks, Zack!!!!!

No prob!

I'm here all week.

[xCav8r]

rjw, if the report already contains the field order id, then you don't need to grab it from the form. And, your problem is you're trying to get a property of a label that a label doesn't have. Is it perhaps stored in the caption property?

[rjw]

I've added a report on open event procedure:

[vba]
Private Sub Report_Open(Cancel As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset, rs1 As DAO.Recordset, rs2 As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb
'query qryLenderCodes = "SELECT TOP 1 Orders.LenderCode, Orders.LenderCode2, Orders.LenderCode3 FROM Orders ORDER BY Orders.OrderID DESC"
Set rs = db.OpenRecordset("qryLenderCodes")
If Not rs.EOF Then
With rs

'query qryLenderInfo = "SELECT Lenders.LenderContrName as ContrName1, Lenders.Address as Address1, Lenders.City as City1, Lenders.StateOrProvince as StateOrProvince1 FROM Lenders
'WHERE Lenders.LenderCode=" & Orders.LenderCode
If Not [Orders.LenderCode] Then
Set rs1 = db.OpenRecordset("qryLenderInfo")
rs1.FindFirst "Lenders.LenderCode=" & [Orders.LenderCode]
If Not rs1.EOF Then
Me.Text88 = Lenders.ContrName1
Me.Text89 = Lenders.Address1
Me.Text90 = Lenders.City1 & ", " & Lenders.StateOrProvince1
End If
Set rs1 = Nothing
End If


If Not [Orders.LenderCode2] Then
'WHERE Lenders.LenderCode=" & Orders.LenderCode2
Set rs2 = db.OpenRecordset("qryLenderInfo")
rs2.FindFirst "Lenders.LenderCode=" & [Orders.LenderCode2]
If Not rs2.EOF Then
Me.Text91 = Lenders.ContrName2
Me.Text92 = Lenders.Address2
Me.Text93 = Lenders.City2 & ", " & Lenders.StateOrProvince2
End If
Set rs2 = Nothing
End If

If Not [Orders.LenderCode3] Then
'WHERE Lenders.LenderCode=" & Orders.LenderCode3
Set rs3 = db.OpenRecordset("qryLenderInfo")
rs3.FindFirst "Lenders.LenderCode=" & [Orders.LenderCode3]
If Not rs3.EOF Then
Me.Text94 = Lenders.ContrName3
Me.Text95 = Lenders.Address3
Me.Text96 = Lenders.City3 & ", " & Lenders.StateOrProvince3
End If
Set rs3 = Nothing
End If

End With

End If
Set rs = Nothing

End Sub
[/vba]

It gets down to this line:

"If Not [Orders.LenderCode] Then "

and I get this error:

Microsoft Office Access can't find the field '|' referred to in your expression.

This is no where in my code and it doesn't happen when I run the report without this module.

I really need help on this. Is there anyone familiar enough with access 2003 that can help me pull this together?

PLEASE HELP

[xCav8r]

What is Orders.LenderCode? Are you trying to refer to data that is bound to the report or that you obtained through DAO? If so, it looks like you might benefit from reading about referencing objects in DAO. http://msdn.microsoft.com/archive/de...essObjects.asp

[rjw]

comes from orders table with column name LendedCode

john