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_lookup” 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_lookup”. 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.RecordSource = Forms!Custom_Code_lookup!txtWhereClause” 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