Right people, here is the Function I am trying to create!
The line I am having trouble with is the 3rd one down;
With Forms![Reports Menu v2]
I have checked all my If's/End If's and For's/Next's but
seem to be overlooking something else.
Good Luck and Thanx Nikos,
Steve.
Public Function Generate_Report()
' This function generates a query using selections
made on form Reports Menu v2
Call Update_Status_Bar(, , "Generating Report")
strSQL = "SELECT Received.[Received Date], Received.
[Shipper Short Code], Received.Received, Received.[Receipt
Type], Received.[Domestic / I&C ?], Received.Loaded,
Received.Duplicated, Received.Rejected, Rejected.[Reject
Code], Rejected.[MPR(s)] " & _
"FROM Received INNER JOIN Rejected ON
Received.[Record ID] = Rejected.[Record ID] "
With Forms![Reports Menu v2]
' Received Dates must be input.
If IsNull(![Start Date]) Or IsNull(![End Date])
Then
Beep
MsgBox "Please Input a Start Date and End
Date!", vbExclamation
Exit Function
End If
' If execution gets here, dates have been entered.
strSQL = strSQL & "WHERE [Received Date] Between
#" & Format(![Start Date], "mm/dd/yy") & "# And #" & Format
(![End Date], "mm/dd/yy") & "# "
' All other options are.......optional
' Check if the All Shippers checkbox is checked
If !chkAll_Shippers = False Then
' Start off the part of SQL to add the
selected shippers.
strSQL = strSQL & "AND [Shipper Short Code] In
("
' The checkbox isnt checked. Seperate
Shippers should of been selected.
Dim intCur_SSC As Integer ' Used to Loop
through lstShippers
Dim intSSC_Count As Integer ' Stores a
count of Selected Shippers.
For intCur_SSC = 0 To !lstShippers.ListCount
Call Update_Status_Bar(intCur_SSC + 1, !
lstShippers.ListCount + 1, "Scanning for Selected
shippers")
If !lstShippers.Selected(intCur_SSC) =
True Then
' This shipper is selected, add it to
the list
strSQL = strSQL & "'" & !
lstShippers.Column(0, intCur_SSC) & "',"
intSSC_Count = intSSC_Count + 1
End If
Next intCur_SSC
' After adding the SSC to the SQL, a comma is
left at the end.
' Remove it and add the closing bracket.
strSQL = Left(strSQL, Len(strSQL) - 1) & ") "
End If
' Check if the Both Receipt Types Checkbox is
selected.
If !chkAll_Receipt = False Then
Call Update_Status_Bar(, , "Adding Receipt
Type")
' It isnt, add the Receipt type.
strSQL = strSQL & "AND [Receipt Type] = '" & !
[Receipt Type] & "' "
End If
' Check if the All Reasons checkbox is selected.
If !chkAll_Reasons = False Then
strSQL = strSQL & "AND [Reject Code] In ("
' It isnt, add the Selected Reasons.
Dim intCur_Reason As Integer ' Used to loop
through the reasons.
Dim intReason_Count As Integer ' Used to
count the number of selected reasons
For intCur_Reason = 0 To !lstReasons.ListCount
Call Update_Status_Bar(intCur_Reason + 1, !
lstReasons.ListCount + 1, "Scanning for Rejects/Reasons")
If !lstReasons.Selected(intCur_Reason) =
True Then
strSQL = strSQL & !lstReasons.Column
(0, intCur_Reason) & ","
End If
Next intCur_Reason
End If
End With
End Function
-----Original Message-----
Hi,
I have a form and a module that uses the With Statement to
get information from the form;
With Forms![Reports Menu v2]
I keep getting an error on the With statement when running
the code;
Run-time error '-2146500594 (800f000e)':
Method 'Item' of object 'Forms' failed
The code was running fine earlier, but this error has just
decided to appear
Can anyone help me before frustation causes a
fatal 'accident' to my PC?
Cheers,
Steve
.
