3 separate queries needed to get report info

[rjwelte]

I have an existing form that collects information on Vendors - three to be exact that apply to this work order. When I print the form I have 3 separate reports that are printed, one after the other. I'd like to change one of them to print out all three vendor names, addresses, citys and states. This requires collecting 3 variables from the original form - vendorID1, vendorID2 and vendorID3. What do I do to get this report to print all 3 separate queiries and populate each set of the name, address, city and state field(s)? I'm new at this, so I need a step by step if you van give it to me. Thanks


rjw

 

[rjwelte]

Hi Pat:

I'm writing a module to do three queries one after the other. the page I'm printing from is called 'Job Entry' and it captures the value 'OrderID'. I need this when i run my report 'Vendor Report'. How can I obtain the OrderID using VBA? This will allow me to lookup the order and get all three vendorID's at once.

rjw

 

[rjwelte]

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 Report loads. However, the Report 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.


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


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

 

[richary]

As Pat says, its unnecessary to use VBA in this case. Can you not change the query upon which your report is based?

It would be far easier to have a SELECT query that grabbed all vendor information for your order ID and then, as Pat suggests add a group header for VendorID (set the ForceNew Page property to After section).

The report detail is then all your vendor fields.

richary

 

[rjwelte]

I would change the query, if it could return all the Lender information by accessing a single record in the Lenders table. However it needs to access three separate records to return all three lenders information. Since this is a new order and its being saved just before all this information is printed, I didn't want to alter the behaviour of the original reports. So I'm opting to add a module as follows:

I've added a report_open event procedure. Listed here are the two queries I've added to the database as well.

[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 ContrName, Lenders.Address as Address, Lenders.City as City, Lenders.StateOrProvince as StateOrProvince 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.ContrName

Me.Text89 = Lenders.Address

Me.Text90 = Lenders.City & ", " & Lenders.StateOrProvince

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.ContrName

Me.Text92 = Lenders.Address

Me.Text93 = Lenders.City & ", " & Lenders.StateOrProvince

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.ContrName

Me.Text95 = Lenders.Address

Me.Text96 = Lenders.City & ", " & Lenders.StateOrProvince

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

 

[rjwelte]

Pat is there anyway we can talk. I really need your help here. I've go a summer job as an intern. I have VB experience, but this is VBA/Access and it's really thowing me for a loop.


rjw

 

[Kamran]

You can get three differnt criteria in Query using one of the bellow
1. Select a,b,c from temp where a in [1,2,3]
or
Select a,b,c from temp where a=form.f1
union
Select a,b,c from temp where a=form.f2
union
Select a,b,c from temp where a=form.f3