We help IT Professionals succeed at work.

Opening a form with RecordSet based on OpenArgs

BHR
BHR asked
on
658 Views
Last Modified: 2012-06-27
I have 2 forms in an Access 2000 database, FrmDBMonMain & FrmDBMon_Review.  On FrmDBMonMain  I have an option group with 3 different options. I’m trying to get it so that when the user makes an option selection & click a button on FrmDBMonMain, FrmDBMon_Review will open up using a different recordsource based on the option the user had selected.  FrmDBMon_Review  has no recordsource in its form properties.  On FrmDBMonMain I have the following code:

Private Sub GotoDBMon_review_Click()
On Error GoTo Err_GotoDBMon_review_Click

    Dim stDocName As String
    Dim QryArgs As String
   
    Select Case Frame73
        Case 1
            QryArgs = "Qry_ByCaseNum"
        Case 2
            QryArgs = "Qry_ByDatereviewed"
        Case 3
            QryArgs = "Qry_ByDateOpened"
    End Select
   
    stDocName = "FrmDBMon_Review"
       
    DoCmd.OpenForm stDocName, , , , , , QryArgs
    DoCmd.Close acForm, "FrmDBMonMain", acSaveNo

Exit_GotoDBMon_review_Click:
    Exit Sub

Err_GotoDBMon_review_Click:
    MsgBox Err.Description
    Resume Exit_GotoDBMon_review_Click
   
End Sub

Where Frame73 is the Option group, GotoDBMon_review  is the button, & each of the “Qry_By…” are different  queries on the same table.

On the FrmDBMon_Review I have the code:

Private Sub Form_Open()
    Me.RecordSource = Me.OpenArgs
End Sub

When I make a option selection & click the button though, I get the error “OpenForm action was canceled”.  I don’t see anything which would be canceling the action.  If I take out the error handling of the GotoDBMon_review_Click() function, it errors out & sends me to debugger to the line of the FrmDBMonMain code:  
 
    DoCmd.OpenForm stDocName, , , , , , QryArgs

Comment
Watch Question

Top Expert 2006

Commented:
DoCmd.OpenForm stDocName, , QryArgs
BHR

Author

Commented:
Nope. That didn't work.  Still getting the same error.
Top Expert 2006
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Top Expert 2006

Commented:
Tested this and it works

---------------------------------------------------------------------
    Dim stDocName As String
    stDocName = "frm_Customer"
    strOption = Me.Frame0.Value
    Select Case strOption
    Case 0
    DoCmd.OpenForm stDocName, , , , , , "Qry_ByCaseNum"
    Case 1
    DoCmd.OpenForm stDocName, , , , , , "Qry_ByDatereviewed"
    Case 2
    DoCmd.OpenForm stDocName, , , , , , "Qry_ByDateOpened"
    End Select

---------------------------------------------------------------------
Private Sub Form_Open(Cancel As Integer)
Dim strArg As String
strArg = Me.OpenArgs
Me.RecordSource = strArg
End Sub
Top Expert 2006

Commented:
Sorry....thought I'd put your names and such in here...
---------------------------------------------------------------------
   
Private Sub GotoDBMon_review_Click()
On Error GoTo Err_GotoDBMon_review_Click

    Dim stDocName As String
    stDocName = "FrmDBMon_Review"
    strOption = Me.Frame73.Value
    Select Case strOption
    Case 0
    DoCmd.OpenForm stDocName, , , , , , "Qry_ByCaseNum"
    Case 1
    DoCmd.OpenForm stDocName, , , , , , "Qry_ByDatereviewed"
    Case 2
    DoCmd.OpenForm stDocName, , , , , , "Qry_ByDateOpened"
    End Select

    DoCmd.Close acForm, "FrmDBMonMain", acSaveNo

Exit_GotoDBMon_review_Click:
    Exit Sub

Err_GotoDBMon_review_Click:
    MsgBox Err.Description
    Resume Exit_GotoDBMon_review_Click
   
End Sub
-------------------------------------------------------------------------------
---------------------------------------------------------------------
Private Sub Form_Open(Cancel As Integer)
Dim strArg As String
strArg = Me.OpenArgs
Me.RecordSource = strArg
End Sub

J
BHR

Author

Commented:
I started stepping through the code & ran into a compile error.  I deleted & replaced the On Open code which gave me this:
Private Sub Form_Open(Cancel As Integer)
    Me.RecordSource = Me.OpenArgs
End Sub

(The "Cancel as Integer' was missing before.)

Now when I run it, it errors out on the line:
    Me.RecordSource = Me.OpenArgs

I've set Me.OpenArgs on a watch & the OpenArgs string is coming through ok, but I'm still getting the error:
    Runtime error '2001':
    You canceled the previous operation.
Top Expert 2006

Commented:
There is another event that's trying to run when this form is opening. Maybe a on_load or on close, something like that? Check the events on both forms and make sure they don't conflict. debug and step from the GotoDBMon_review_Click().

You don't have a recordsource set on your review form correct? Does it ever open?
J
BHR

Author

Commented:
I think I've figured it out partially.  There is some problem with my queries.  Oddly, they run by themselves but not when interacting with the forms.  I've edited one of them down and was able to open the FrmDBMon_Review using that selection.  I think it has to do with basing certain values in the queries on values from the FrmDBMonMain form then using those results to open the other form.  

Anyway, your suggestion of stepping through the code while opening the form & tracing the values helped so I'll award you the points jefftwilley.  

Well back to the queries...
Top Expert 2006

Commented:
Glad to help. good luck
J

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.