Problem in MDE

[Alankar]

Hi All !,

I am getting a weired message when clicking on OK button on a form in MDE, following code is written in the form.

The message is "The expression on click you enetered as the event property setting produced the following error : That command is not available in MDE/ADE database."

--------------------------------------------Code---------
Option Compare Database

Private Sub cmbFrom_Change()
cmbTo.Value = cmbFrom.Value
End Sub

Private Sub cmdCancel_Click()
On Error GoTo Err_cmdCancel_Click
DoCmd.Close
Exit_cmdCancel_Click:
Exit Sub
Err_cmdCancel_Click:
MsgBox Err.Description
Resume Exit_cmdCancel_Click
End Sub

Private Sub cmdOK_Click()
'Open Report
If cmbFrom.Value <= cmbTo.Value Then
Select Case Previlege
Case "admin"
Call AdminReport
Case "pm"
Call AdminReport
Case "user"
Call UserReport
End Select
Else
MsgBox "From Week and To Week entry wrong !"
End If
End Sub

Private Sub Form_Load()
Select Case Previlege
Case "admin"
Form_TmpInfo.cmbEmp_Label.Visible = True
Form_TmpInfo.cmbEmp.Visible = True
Form_TmpInfo.cmbPrj.Visible = True
Form_TmpInfo.Choose_Project_Label.Visible = True
Form_TmpInfo.cmbPrj.RowSource = "SELECT [tbProject].[PrjID], [tbProject].[PrjName] FROM [tbProject] ORDER BY [PrjID]"
Case "pm"
Form_TmpInfo.cmbEmp_Label.Visible = True
Form_TmpInfo.cmbEmp.Visible = True
Form_TmpInfo.cmbPrj.Visible = True
Form_TmpInfo.Choose_Project_Label.Visible = True
Form_TmpInfo.cmbPrj.RowSource = "SELECT [tbProject].[PrjID], [tbProject].[PrjName] FROM [tbProject] WHERE PrjLeader = '" & EmpIDFT & "' ORDER BY [PrjID]"
Case "user"
End Select
Label4.Visible = True
Label6.Visible = True
cmbFrom.Visible = True
cmbTo.Visible = True
cmbFrom.RowSource = "SELECT distinct Format([tbTimeSheet]![DateRec],'ww') AS Expr1 FROM tbTimeSheet"
cmbTo.RowSource = "SELECT distinct Format([tbTimeSheet]![DateRec],'ww') AS Expr1 FROM tbTimeSheet"
End Sub

Sub AdminReport()
If Not IsNull(cmbEmp.Value) And IsNull(cmbPrj.Value) Then 'Present Employee Report
str1 = "SELECT tbTimeSheet.DateRec, tbTimeSheet.PrjID, tbActivity.ActName, tbTimeSheet.ActCode, tbTimeSheet.Duration, tbTimeSheet.Remark, tbTimeSheet.EmpID" _
& " FROM tbActivity INNER JOIN tbTimeSheet ON tbActivity.ActCode = tbTimeSheet.ActCode" _
& " WHERE ((tbtimesheet.EmpID)='" & cmbEmp.Value & "') AND ((Format([tbTimeSheet]![DateRec],'ww'))>=" & cmbFrom.Value & " And (Format([tbTimeSheet]![DateRec],'ww'))<=" & cmbTo.Value & ")"
DoCmd.OpenReport "REmpRep", acViewDesign
Reports!RempRep.RecordSource = str1
Reports!RempRep.Label20.Caption = "Employee Name : " & DLookup("[EmpName]", "tbEmp", "[EmpID] ='" & cmbEmp.Value & "'") & vbCrLf & "Employee Code : " & cmbEmp.Value & ""
DoCmd.Close acReport, "REmpRep", acSaveYes
DoCmd.Close acForm, "TmpInfo"
DoCmd.OpenReport "REmpRep", acViewPreview
Form_Main.cmdUser3.Enabled = True
Form_Main.cmdUser4.Enabled = True

ElseIf IsNull(cmbEmp.Value) And Not IsNull(cmbPrj.Value) Then 'Present Project Report
str1 = "SELECT tbTimeSheet.DateRec, tbTimeSheet.PrjID, tbActivity.ActName, tbTimeSheet.ActCode, tbTimeSheet.Duration, tbTimeSheet.Remark, tbTimeSheet.EmpID, tbEmp.EmpName" _
& " FROM (tbActivity INNER JOIN tbTimeSheet ON tbActivity.ActCode = tbTimeSheet.ActCode) INNER JOIN tbEmp ON tbTimeSheet.EmpID = tbEmp.EmpID" _
& " WHERE ((tbtimesheet.prjID)='" & cmbPrj.Value & "') AND ((Format([tbTimeSheet]![DateRec],'ww'))>=" & cmbFrom.Value & " And (Format([tbTimeSheet]![DateRec],'ww'))<=" & cmbTo.Value & ")"
DoCmd.OpenReport "RPrjRep", acViewDesign
Reports!RPrjRep.RecordSource = str1
Reports!RPrjRep.Label20.Caption = "Project Name : " & DLookup("[PrjName]", "tbProject", "[PrjID] ='" & cmbPrj.Value & "'") & " ( Project ID : " & cmbPrj.Value & " )" & vbCrLf & "Project In-charge :" & DLookup("[PrjLeader]", "tbProject", "[PrjID] ='" & cmbPrj.Value & "'")
DoCmd.Close acReport, "RPrjRep", acSaveYes
DoCmd.Close acForm, "TmpInfo"
DoCmd.OpenReport "RPrjRep", acViewPreview
Form_Main.cmdUser3.Enabled = True
Form_Main.cmdUser4.Enabled = True

ElseIf Not IsNull(cmbEmp.Value) And Not IsNull(cmbPrj.Value) Then 'Present Employee+Project Report
str1 = "SELECT tbTimeSheet.DateRec, tbTimeSheet.PrjID, tbActivity.ActName, tbTimeSheet.ActCode, tbTimeSheet.Duration, tbTimeSheet.Remark, tbTimeSheet.EmpID" _
& " FROM tbActivity INNER JOIN tbTimeSheet ON tbActivity.ActCode = tbTimeSheet.ActCode" _
& " WHERE ((tbtimesheet.prjID)='" & cmbPrj.Value & "') AND ((tbtimesheet.EmpID)='" & cmbEmp.Value & "') AND ((Format([tbTimeSheet]![DateRec],'ww'))>=" & cmbFrom.Value & " And (Format([tbTimeSheet]![DateRec],'ww'))<=" & cmbTo.Value & ")"
DoCmd.OpenReport "REmpRep", acViewDesign
Reports!RempRep.RecordSource = str1
Reports!RempRep.Label20.Caption = "Employee Name : " & DLookup("[EmpName]", "tbEmp", "[EmpID] ='" & cmbEmp.Value & "'") & vbCrLf & "Employee Code : " & cmbEmp.Value & ""
DoCmd.Close acReport, "REmpRep", acSaveYes
DoCmd.Close acForm, "TmpInfo"
DoCmd.OpenReport "REmpRep", acViewPreview
Form_Main.cmdUser3.Enabled = True
Form_Main.cmdUser4.Enabled = True
ElseIf IsNull(cmbEmp.Value) And IsNull(cmbPrj.Value) Then 'Error Message
MsgBox "Please select atleast one Employee or Project !"
End If
End Sub

Sub UserReport()
str1 = "SELECT tbTimeSheet.DateRec, tbTimeSheet.PrjID, tbActivity.ActName, tbTimeSheet.ActCode, tbTimeSheet.Duration, tbTimeSheet.Remark, tbTimeSheet.EmpID" _
& " FROM tbActivity INNER JOIN tbTimeSheet ON tbActivity.ActCode = tbTimeSheet.ActCode" _
& " WHERE (((tbTimeSheet.EmpID)='" & EmpIDFT & "') AND ((Format([tbTimeSheet]![DateRec],'ww'))>=" & cmbFrom.Value & " And (Format([tbTimeSheet]![DateRec],'ww'))<=" & cmbTo.Value & "))"
DoCmd.Close acForm, "TmpInfo"
DoCmd.OpenReport "REmpRep", acViewDesign
Reports!RempRep.RecordSource = str1
Reports!RempRep.Label20.Caption = "Report for " & EmpNameFT & "(Employee Code : " & EmpIDFT & ")"
DoCmd.Close acReport, "REmpRep", acSaveYes
DoCmd.OpenReport "REmpRep", acViewPreview
Form_Main.cmdUser3.Enabled = True
Form_Main.cmdUser4.Enabled = True
End Sub
-----------------------------------Code-----------

Could anyone please suggest me what is the problem !


Regards,
Alankar

[westconn1]

does it highlight a line when you get that message?

pete

[Alankar]

does it highlight a line when you get that message?

pete

No it don't show any errorneous line of code.

In the called functions when I used a msgbox only (replacing all the code in the function) then, it worked fine.

I think the problem is with the report recordsource property changing etc. But I have to do this anyhow.

Help !,

Regrads,
Alankar

[westconn1]

can you set break points to see how far it gets before it fails?

pete

[Ecniv]

westcon:
If it is an MDE there won't be viewable code.

The bit in bold may cause a problem.
I have added two lines in italics below that should message box before and after you alter the recordseource. Can you not do this on the main open report? Just wondering!
The bit in red - I was wondering if that closes the current form - if so you won't be able to refer to any controls on it from that point onwards.

Sub AdminReport()
If Not IsNull(cmbEmp.Value) And IsNull(cmbPrj.Value) Then 'Present Employee Report
   str1 = "SELECT tbTimeSheet.DateRec, tbTimeSheet.PrjID, tbActivity.ActName, tbTimeSheet.ActCode, tbTimeSheet.Duration, tbTimeSheet.Remark, tbTimeSheet.EmpID" _
& " FROM tbActivity INNER JOIN tbTimeSheet ON tbActivity.ActCode = tbTimeSheet.ActCode" _
& " WHERE ((tbtimesheet.EmpID)='" & cmbEmp.Value & "') AND ((Format([tbTimeSheet]![DateRec],'ww'))>=" & cmbFrom.Value & " And (Format([tbTimeSheet]![DateRec],'ww'))<=" & cmbTo.Value & ")"
DoCmd.OpenReport "REmpRep", acViewDesign
msgbox "Before recordseource set"
Reports!RempRep.RecordSource = str1
msgbox "After recordseource set"

Reports!RempRep.Label20.Caption = "Employee Name : " & DLookup("[EmpName]", "tbEmp", "[EmpID] ='" & cmbEmp.Value & "'") & vbCrLf & "Employee Code : " & cmbEmp.Value & ""

    DoCmd.Close acReport, "REmpRep", acSaveYes
    DoCmd.Close acForm, "TmpInfo"
    DoCmd.OpenReport "REmpRep", acViewPreview
    Form_Main.cmdUser3.Enabled = True
    Form_Main.cmdUser4.Enabled = True

ElseIf IsNull(cmbEmp.Value) And Not IsNull(cmbPrj.Value) Then 'Present Project Report
   str1 = "SELECT tbTimeSheet.DateRec, tbTimeSheet.PrjID, tbActivity.ActName, tbTimeSheet.ActCode, tbTimeSheet.Duration, tbTimeSheet.Remark, tbTimeSheet.EmpID, tbEmp.EmpName" _
& " FROM (tbActivity INNER JOIN tbTimeSheet ON tbActivity.ActCode = tbTimeSheet.ActCode) INNER JOIN tbEmp ON tbTimeSheet.EmpID = tbEmp.EmpID" _
& " WHERE ((tbtimesheet.prjID)='" & cmbPrj.Value & "') AND ((Format([tbTimeSheet]![DateRec],'ww'))>=" & cmbFrom.Value & " And (Format([tbTimeSheet]![DateRec],'ww'))<=" & cmbTo.Value & ")"
   DoCmd.OpenReport "RPrjRep", acViewDesign
   Reports!RPrjRep.RecordSource = str1
   Reports!RPrjRep.Label20.Caption = "Project Name : " & DLookup("[PrjName]", "tbProject", "[PrjID] ='" & cmbPrj.Value & "'") & " ( Project ID : " & cmbPrj.Value & " )" & vbCrLf & "Project In-charge :" & DLookup("[PrjLeader]", "tbProject", "[PrjID] ='" & cmbPrj.Value & "'")
   DoCmd.Close acReport, "RPrjRep", acSaveYes
   DoCmd.Close acForm, "TmpInfo"
   DoCmd.OpenReport "RPrjRep", acViewPreview
   Form_Main.cmdUser3.Enabled = True
   Form_Main.cmdUser4.Enabled = True

ElseIf Not IsNull(cmbEmp.Value) And Not IsNull(cmbPrj.Value) Then 'Present Employee+Project Report
str1 = "SELECT tbTimeSheet.DateRec, tbTimeSheet.PrjID, tbActivity.ActName, tbTimeSheet.ActCode, tbTimeSheet.Duration, tbTimeSheet.Remark, tbTimeSheet.EmpID" _
& " FROM tbActivity INNER JOIN tbTimeSheet ON tbActivity.ActCode = tbTimeSheet.ActCode" _
& " WHERE ((tbtimesheet.prjID)='" & cmbPrj.Value & "') AND ((tbtimesheet.EmpID)='" & cmbEmp.Value & "') AND ((Format([tbTimeSheet]![DateRec],'ww'))>=" & cmbFrom.Value & " And (Format([tbTimeSheet]![DateRec],'ww'))<=" & cmbTo.Value & ")"
DoCmd.OpenReport "REmpRep", acViewDesign
Reports!RempRep.RecordSource = str1
Reports!RempRep.Label20.Caption = "Employee Name : " & DLookup("[EmpName]", "tbEmp", "[EmpID] ='" & cmbEmp.Value & "'") & vbCrLf & "Employee Code : " & cmbEmp.Value & ""
DoCmd.Close acReport, "REmpRep", acSaveYes
DoCmd.Close acForm, "TmpInfo"
DoCmd.OpenReport "REmpRep", acViewPreview
Form_Main.cmdUser3.Enabled = True
Form_Main.cmdUser4.Enabled = True
ElseIf IsNull(cmbEmp.Value) And IsNull(cmbPrj.Value) Then 'Error Message
MsgBox "Please select atleast one Employee or Project !"
End If
End Sub

Also - if all the statements are the same except where you change the where clause bits, souldn't it be easier to store the main sql once, and add a where clause as applicable under it? I haven't gone through each of the statemenets, perhaps they are different - this was just a thought.

[Alankar]

Hi Hack,

I used msgbox before and after opening the report in design mode code. But still it is same error, I think before running the code Access is validating the code written in the event procedure and seige execution as it finds open report in design view code. Because in MDE Report can't be opened in Design view.

For the time being I am using MDB file.

Regards,
Alankar

[westconn1]

the only way to find where it errors to is to write to a log file every few lines so that you can read the log after the execution stops, make sure you describe the position of each entry in the log so that you can easily tell where the code broke

pete