Opening a form with RecordSet based on OpenArgs

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

BHRAsked:
Who is Participating?
 
jefftwilleyConnect With a Mentor Commented:
Are you stepping through the code when you try to open your form? What is the value for the opening arguement when you trace here
>  Private Sub Form_Open()
        Me.RecordSource = Me.OpenArgs
    End Sub

You might want to consider moving your a case statement to the review form with the query names and pass a simple value for opening arguement like "1" or "A".

select case me.OpenArgs
       Case 1
            Me.RecordSource  = "Qry_ByCaseNum"
        Case 2
            Me.RecordSource  = "Qry_ByDatereviewed"
        Case 3
            Me.RecordSource  = "Qry_ByDateOpened"


something like that.
J
0
 
jefftwilleyCommented:
DoCmd.OpenForm stDocName, , QryArgs
0
 
BHRAuthor Commented:
Nope. That didn't work.  Still getting the same error.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
jefftwilleyCommented:
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
0
 
jefftwilleyCommented:
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
0
 
BHRAuthor 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.
0
 
jefftwilleyCommented:
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
0
 
BHRAuthor 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...
0
 
jefftwilleyCommented:
Glad to help. good luck
J
0
All Courses

From novice to tech pro — start learning today.