Hi JOM,
I spent some time this evening working up a sample that uses the same names
for the form, report, fields and controls on the report. If you send me a
private e-mail message, with a valid reply address, I'll be happy to send you
a zipped copy of my sample.
I didn't quite understand your query, so the example I present below uses my
crosstab query. The report that I used is designed to accomodate a crosstab
query result that has a maximum of 15 columns. It uses labels without
captions named "lblDay1", "lblDay2", "lblDay3", etc. and unbound textboxes
named "txtDay1", "txtDay2", "txtDay3", etc.
First, you should add Option Explicit as the second line of code to all of
your code modules. I'm pretty sure this is missing in your modules because
you use the variable strWhereEmpl without having first declared it. Here is a
"Gem Tip" that I wrote which explains why Option Explicit is a good thing
and, more importantly, how to configure the VB editor to always add it to new
code modules:
Always use "Option Explicit"
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions
Try the following code as the click event procedure for the cmdOK command
button on your ServiceLevel form. Note that I have removed the line of code
that read:
strWhereEmpl = "EmpID = " & [Forms]![ServiceLevel]![cmbDaily]
and I have shortened the line of code that used to read:
DoCmd.OpenReport stDocName, acPreview, , strWhereEmpl
so that it now reads:
DoCmd.OpenReport stDocName, acPreview
'********Begin ServiceLevel form code***********
Option Compare Database
Option Explicit
Private Sub cmdOk_Click()
On Error GoTo ProcError
Dim stDocName As String
If IsNull(Me.cmbDaily) Then
MsgBox "Select an employee to Preview."
Me.cmbDaily.SetFocus
Exit Sub
End If
'Check to see that ending date is later than beginning date.
If IsDate(BeginDate) And IsDate(EndDate) Then
If EndDate < BeginDate Then
MsgBox "The ending date must be later than the beginning date."
EndDate.SetFocus
Exit Sub
End If
Else
MsgBox "Please use a valid date for the beginning date and the
ending date values."
Exit Sub
End If
stDocName = "Daily Service Level Summary"
DoCmd.OpenReport stDocName, acPreview
ExitProc:
Exit Sub
ProcError:
Select Case Err.Number
Case 2501
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdHoursWorked_Click..."
End Select
Resume ExitProc
End Sub
'********End ServiceLevel form code***********
Add the following code to your Daily Service Level Summary report:
'********Begin Daily Service Level Summary report code***********
Option Compare Database
Option Explicit
Private Sub Report_Activate()
DoCmd.Maximize
End Sub
Private Sub Report_Close()
DoCmd.Restore
End Sub
Private Sub Report_NoData(Cancel As Integer)
On Error GoTo ProcError
MsgBox "There is no data available for the dates selected.", _
vbInformation, "No Data..."
Cancel = True
ExitProc:
Exit Sub
ProcError:
MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
"Error in Daily Service Level Summary NoData event procedure"
Resume ExitProc
End Sub
Private Sub Report_Open(Cancel As Integer)
On Error GoTo ProcError
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim i As Integer
Dim j As Integer
Dim intColCount As Integer
Dim strName As String
Dim s1 As String
Set db = CurrentDb
s1 = Me.RecordSource
Set qdf = db.QueryDefs(s1)
qdf.Parameters(0) = [Forms]![ServiceLevel]![cmbDaily]
qdf.Parameters(1) = [Forms]![ServiceLevel]![BeginDate]
qdf.Parameters(2) = [Forms]![ServiceLevel]![EndDate]
Set rs = qdf.OpenRecordset
intColCount = rs.Fields.Count
j = 1
' Fill in information for the necessary controls.
For i = 1 To intColCount - 1
strName = rs.Fields(i).Name
Me.Controls("lblDay" & j).Caption = strName
Me.Controls("txtDay" & j).ControlSource = strName
j = j + 1
Next i
'Move position of Row totals Label & Textbox if less than 13 months of data
is displayed
If intColCount < 15 Then
i = 0.65 * intColCount * 1440 'New left margin in twips (1 inch = 1440
twips)
'Me!lblTotalsByRow.Left = i
'Me!txtTotalsByRow.Left = i
'Me!txtGrandTotalCalc.Left = i
'Adjust widths of lines in Page Header and Report Footer sections
'Me!lnePageHeader.Width = i + 950
'Me!lneReportFooter.Width = i + 950
End If
ExitProc:
On Error Resume Next
rs.Close
Set rs = Nothing
qdf.Close
Set qdf = Nothing
db.Close
Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
"Error in Daily Service Level Summary Open event procedure"
Resume ExitProc
End Sub
'********End Daily Service Level Summary report code***********
If my answer has helped you, please answer yes to the question that reads
"Did this post answer the question?" at the bottom of the message thread.
Tom
QWaos168@XScom cast. Dnet (<--Remove all capitalized letters and spaces).
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
:
Tom,
Thanks for the reply, I did try everything but something weird is going on.
The query runs ok, but when I try to run it it gives me an error Microsoft
Jet Databases Engine does not recognize " as a valid field name or expression
If I Put the beginning date as 08/01/05 and ending date as 08/31/05 it runs
ok, but If I try to put a different date like 08/01/05 and ending date as
08/15/05, then I get the error, I have looked at the reports to makes sure
that I don't filters turned on but still only works with those 2 dates put in
ie the 08/01/05 and 08/31/05
I have no data function on the report but this is not being called. So if I
put those dates and an employee did not work, then it pulls all the records
that fall under that category.....................
Here is my actual preview code that is supposed to open the report
Private Sub cmdOk_Click()
On Error GoTo Err_cmdOk_Click
Dim stDocName As String
If IsNull(Me.cmbDaily) Then
MsgBox "Select an employee to Preview."
Me.cmbDaily.SetFocus
Exit Sub
End If
'Check to see that ending date is later than beginning date.
If IsDate(BeginDate) And IsDate(EndDate) Then
If EndDate < BeginDate Then
MsgBox "The ending date must be later than the beginning date."
EndDate.SetFocus
Exit Sub
End If
Else
MsgBox "Please use a valid date for the beginning date and the
ending date values."
Exit Sub
End If
strWhereEmpl = "EmpID = " & Forms![ServiceLevel]!cmbDaily
stDocName = "Daily Service Level Summary"
DoCmd.OpenReport stDocName, acPreview, , strWhereEmpl
Exit_cmdOk_Click:
Exit Sub
Err_cmdOk_Click:
MsgBox Err.Description
Resume Exit_cmdOk_Click
End Sub
*************************************************************
the run time
error am getting is 3070
************************************************************
