By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,401 Members | 793 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,401 IT Pros & Developers. It's quick & easy.

Syntax help with Me.RecordSource and Cancel option validation

P: 52
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2. On Error GoTo Error_Handler
  3.  
  4. Me.Caption = "Select a Project"
  5.  
  6. DoCmd.OpenForm FormName:="Custom_Code_lookup", windowmode:=acDialog
  7.  
  8. 'Cancel the report if "Cancel" was selected on the form.
  9. If Forms!Custom_Code_lookup!txtContinue = "no" Then
  10.     Cancel = True
  11.     GoTo Exit_Procedure
  12. End If
  13.  
  14. Me.RecordSource = Forms!Custom_Code_lookup!txtWhereClause
  15.  
  16. Exit_Procedure:
  17.     Exit Sub
  18.  
  19. Error_Handler:
  20.  MsgBox "An error has occured: " & "Error Number " & Err.Number & ", " & Err.Description, Buttons:=vbCritical, Title:="Select a Project"
  21. Resume Exit_Procedure
  22. Resume
  23. End Sub
Mar 20 '07 #1
Share this Question
Share on Google+
3 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Mar 21 '07 #2

P: 52
Mary,

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command2_Click()
  2. On Error GoTo Err_Command2_Click
  3.  
  4. Me!txtContinue = "no"
  5. ' Me.Visible = False
  6.  
  7. Exit_Command2_Click:
  8.     Exit Sub
  9. Err_Command2_Click:
  10.     MsgBox Err.Number & ", " & Err.Description
  11.     Resume Exit_Command2_Click
  12.     Resume
  13. End Sub
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command9_Click()
  2. On Error GoTo Err_Command9_Click
  3.  
  4. RebuildWhereClause
  5. Me!txtContinue = "yes"
  6. ' Me.Visible = False
  7.  
  8. Exit_Command9_Click:
  9.     Exit Sub
  10. Err_Command9_Click:
  11.     MsgBox Err.Number & ", " & Err.Description
  12.     Resume Exit_Command9_Click
  13. End Sub
Expand|Select|Wrap|Line Numbers
  1. Sub RebuildWhereClause()
  2. On Error GoTo Err_RebuildWhereClause
  3.  
  4. Dim varWhereClause As Variant
  5. Dim strWhereAnd As String
  6. Dim strSelectionTitle As String
  7. Dim strComma As String
  8.  
  9. varWhereClause = Null
  10. strWhereAnd = ""
  11. strSelectionTitle = ""
  12. strComma = ""
  13.  
  14. If Not (Me!Project_Name & "" = "") And Not (Me!Project_Name = 0) Then
  15.     varWhereClause = (varWhereClause + strWhereAnd) & " (Event_Log.Project_Name = """ & Me!Project_Name.Column(0) & """)"
  16.     strWhereAnd = " AND "
  17.     strSelectionTitle = strSelectionTitle & strComma & "Project_Name = " & Me!Project_Name.Column(0)
  18.     strComma = ", "
  19. End If
  20.  
  21. If strWhereAnd = "" Then
  22.     varWhereClause = Null
  23. Else
  24.     varWhereClause = " WHERE " + varWhereClause
  25. End If
  26.  
  27. Me![txtWhereClause] = varWhereClause
  28. Me![txtSelectionTitle] = strSelectionTitle
  29.  
  30. Exit_RebuildWhereClause:
  31.     Exit Sub
  32. Err_RebuildWhereClause:
  33.     MsgBox Err.Number & " , " & Err.Description
  34.     Resume Exit_RebuildWhereClause
  35.     Resume
  36. 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?
Mar 21 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
A dialog form remains in focus until close. Remove the dialog bit and see if you still have a problem.

Mary
Mar 21 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.