I have a database with a back-end of SQL Server and from-end of
Access
Data Project 2003. I have created a report using nothing but VBA
(first time doing it that way)and I can get only one record to show
on
the report. I know it will go through and get the informaiton for
the
first few record but it will display only one. It doesn't even go
through all the records it will go through in this order: Report
Header, Page Header, GroupHeader, Details and then go through it 2
more times in that order. I still only get one record, I should
have
about 10. All the fields are unbound. Below is a sample of what
the
report should look like and the next section is my code for the
report.
The first four lines are centered and is all apart of the rpt hdr
ELMHS
Campus
WNER (unbound textbox that gets its info from a form)
Open (unbound textbox that gets its info from a form)
For dates between 1/1/05 and 1/1/06 (unbound textbox info from a
form)
Building Location Task Priority Assigned Shop Note (Page Hdr)
Work Order #: 12323 Date Entered 1/2/05 (Group Header)
Bldg1 Rm 1 fix nob low door shop no note
(Details)
bldg2 Rm 2 fix light high ligh shop fall hazard
bldg3 Rm 3 toilet broke high plumbing
Work Order # 11134 Date Entered: 1/16/05 (Group Header
bldg10 Rm 33 fix board medium board shop (Details)
CODE FOR REPORT
Option Compare Database
Option Explicit
Dim sqlrst As ADODB.Recordset
Dim sqlcmd As ADODB.Command
Dim sqlstr As String
Dim ctrl As Control
Dim ctrlWrkOdrNo, ctrlBldg, ctrlLoc, ctrlPriority, ctrlShop As
Control
Dim ctrlWrkOdrType, ctrlStatus, ctrlTask, ctrlNote, ctrlWrkOdrMonth
As Control
Dim ctrlfrmStatus, ctrlfrmWrkOdrType, ctrlfrmBeginDate,
ctrlfrmEndDate As Control
Dim chkrpthdr As Integer
Dim chkwrkodrnum As String
____________________________________________________________________
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Not sqlrst.EOF And Not sqlrst.BOF Then
DetailDisp
End If
End Sub
_____________________________________________________________________
Private Sub DetailDisp()
If Not sqlrst.EOF And Not sqlrst.BOF Then
txtbxBldg = sqlrst!maintwoLocationBuilding
txtbxExactLoc = sqlrst!maintwoLocationExact
txtbxTask = sqlrst!maintwoTask
txtbxPriority = sqlrst!maintwoPriority
txtbxShop = sqlrst!maintwoShop
txtbxNote = sqlrst!maintwoNote
sqlrst.MoveNext
End If
End Sub
____________________________________________________________________
Private Sub HdrWrkOdrNo_Format(Cancel As Integer, FormatCount As
Integer)
If Not sqlrst.EOF And Not sqlrst.BOF Then
WrkOdrNoHdrDisp
End If
End Sub
____________________________________________________________________
Private Sub WrkOdrNoHdrDisp()
If Not sqlrst.EOF And Not sqlrst.BOF Then
txtbxWrkOdrNumEtrDate = "Work Order #: " & sqlrst!
maintwoWorkOrderNo & " Date Entered: " & FormatDateTime
(sqlrst!maintwoDateTimeCalled, vbShortDate)
End If
End Sub
____________________________________________________________________
Private Sub Report_Open(Cancel As Integer)
Set ctrlfrmStatus = Forms!frmWOStatus!drpbxStatus
Set ctrlfrmWrkOdrType = Forms!frmWOType!drpbxWrkOdrType
Set ctrlfrmBeginDate = Forms!frmDateRange!txtbxBeginDate
Set ctrlfrmEndDate = Forms!frmDateRange!txtbxEndDate
chkrpthdr = 0
GetRcds
End Sub
_____________________________________________________________________
Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As
Integer)
If chkrpthdr = 0 Then
txtbxWrkOdrType = ctrlfrmWrkOdrType & " Work Order(s)"
txtbxStatus = "Work Order Status: " & ctrlfrmStatus
txtbxDateRange = "For Dates Between " & Forms!frmDateRange!
txtbxBeginDate & "and " & Forms!frmDateRange!txtbxEndDate
chkrpthdr = 1
End If
End Sub
____________________________________________________________________
Private Sub GetRcds()
sqlstr = "Select maintwoWorkOrderNo, maintwoDateTimeCalled,
maintwoLocationBuilding, maintwoLocationExact, "
sqlstr = sqlstr & "maintwoTask, maintwoPriority,
maintwoWrkOrderType, maintwoShop, "
sqlstr = sqlstr & "maintwoTaskStatus, maintwoNote "
sqlstr = sqlstr & "from tblMaintenanceWorkOrder "
sqlstr = sqlstr & "where maintwoTaskStatus= '" &
ctrlfrmStatus
& "'" & ""
sqlstr = sqlstr & " and maintwoWrkOrderType= '" &
ctrlfrmWrkOdrType & "'" & " "
sqlstr = sqlstr & " and maintwoDateTimeCalled between '" &
ctrlfrmBeginDate & "'" & " "
sqlstr = sqlstr & " and '" & ctrlfrmEndDate & "'" & " "
sqlstr = sqlstr & " and maintwoDivision <> 'gsh' "
sqlstr = sqlstr & " Union All "
sqlstr = sqlstr & " Select maintwoWorkOrderNo,
maintwoDateTimeCalled, maintwoLocationBuilding,
maintwoLocationExact, "
sqlstr = sqlstr & " maintwoTask, maintwoPriority,
maintwoWrkOrderType, maintwoShop, "
sqlstr = sqlstr & " maintwoTaskStatus, maintwoNote "
sqlstr = sqlstr & " from tblMaintenanceWorkOrderAll "
sqlstr = sqlstr & " where maintwoTaskStatus= '" &
ctrlfrmStatus & "'" & ""
sqlstr = sqlstr & " and maintwoWrkOrderType= '" &
ctrlfrmWrkOdrType & "'" & " "
sqlstr = sqlstr & " and maintwoDateTimeCalled between '" &
ctrlfrmBeginDate & "'" & " "
sqlstr = sqlstr & " and '" & ctrlfrmEndDate & "'" & " "
sqlstr = sqlstr & " and maintwoDivision <> 'gsh'"
sqlstr = sqlstr & " Order By maintwoWorkOrderNo Desc"
Set sqlrst = New ADODB.Recordset
With sqlrst
Set .ActiveConnection = CurrentProject.Connection
.Source = sqlstr
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.Open
End With
If Not sqlrst.EOF Then
sqlrst.MoveFirst
Else
MsgBox "There is no data to report, Closing...", , "No Data"
DoCmd.Close
End If
End Sub
Please zip all files prior to uploading to Files section.
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/AccessDevelopers/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
