Syntax help with Me.RecordSource and Cancel option validation

[Birky]

Hello,

I’m hoping you can help me out with two issues I’m having with my code. I have an Access Report named Report_Project_ Event_Log which I have calling a Form named “Custom_Code_lo okup” which allows a user to select data for the report. I have a hidden txt object within this form named txtContinue which is either set to “no” or “yes”. I believe I have everything running correctly except for my cancel code below. When the Cancel button is select I have confirmed that the txtContinue object is getting updated with a “no” but the below code is errorring out telling me that the application can not find the form “Custom_Code_lo okup”. I’m sure it has to do with my syntax and I’m hoping you can see what I’m doing wrong?

I am also having an issue setting the where clause to the reports RecordSource. Again I have a hidden txt object within the form named txtWhereClause which is where my code is building the WHERE itself. Again the object is getting populated appropriately within I just need to have it placed in the reports RecordSource. I am attempting to use the “Me.RecordSourc e = Forms!Custom_Co de_lookup!txtWh ereClause” code but it is not working. Again I believe this to be syntax for everything else is working fine.

Can you help me identify where I am going wrong on these two lines of code? Any help would be greatly appreciated.

Thanks
Birky

Private Sub Report_Open(Cancel As Integer)
On Error GoTo Error_Handler

Me.Caption = "Select a Project"

DoCmd.OpenForm FormName:="Custom_Code_lookup", windowmode:=acDialog

'Cancel the report if "Cancel" was selected on the form.
If Forms!Custom_Code_lookup!txtContinue = "no" Then
    Cancel = True
    GoTo Exit_Procedure
End If

Me.RecordSource = Forms!Custom_Code_lookup!txtWhereClause

Exit_Procedure:
    Exit Sub

Error_Handler:
 MsgBox "An error has occured: " & "Error Number " & Err.Number & ", " & Err.Description, Buttons:=vbCritical, Title:="Select a Project"
Resume Exit_Procedure
Resume
End Sub

[MMcCarthy]

The problem is you open the form as a dialog so the following code doesn't get written until the form is closed at which time the controls txtContinue and txtWhereClause are no longer available. The only way around this is to declare two GLOBAL variables in a module to take the values of these textboxes. Pass the values in on the On Close event of the form and use the variables instead of the textboxes in the code.

[Birky]

Mary,

I am not closing the form, I am just hiding it once the selection has taken place.

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

Me!txtContinue = "no"
' Me.Visible = False

Exit_Command2_Click:
    Exit Sub
Err_Command2_Click:
    MsgBox Err.Number & ", " & Err.Description
    Resume Exit_Command2_Click
    Resume
End Sub

Private Sub Command9_Click()
On Error GoTo Err_Command9_Click

RebuildWhereClause
Me!txtContinue = "yes"
' Me.Visible = False

Exit_Command9_Click:
    Exit Sub
Err_Command9_Click:
    MsgBox Err.Number & ", " & Err.Description
    Resume Exit_Command9_Click
End Sub

Sub RebuildWhereClause()
On Error GoTo Err_RebuildWhereClause

Dim varWhereClause As Variant
Dim strWhereAnd As String
Dim strSelectionTitle As String
Dim strComma As String

varWhereClause = Null
strWhereAnd = ""
strSelectionTitle = ""
strComma = ""

If Not (Me!Project_Name & "" = "") And Not (Me!Project_Name = 0) Then
    varWhereClause = (varWhereClause + strWhereAnd) & " (Event_Log.Project_Name = """ & Me!Project_Name.Column(0) & """)"
    strWhereAnd = " AND "
    strSelectionTitle = strSelectionTitle & strComma & "Project_Name = " & Me!Project_Name.Column(0)
    strComma = ", "
End If

If strWhereAnd = "" Then
    varWhereClause = Null
Else
    varWhereClause = " WHERE " + varWhereClause
End If

Me![txtWhereClause] = varWhereClause
Me![txtSelectionTitle] = strSelectionTitle

Exit_RebuildWhereClause:
    Exit Sub
Err_RebuildWhereClause:
    MsgBox Err.Number & " , " & Err.Description
    Resume Exit_RebuildWhereClause
    Resume
End Sub

Are the values not accessible when the object is hidden and not closed? Do I still have to declare the global variables or am I using the wrong syntax in my code?

[MMcCarthy]

A dialog form remains in focus until close. Remove the dialog bit and see if you still have a problem.

Mary