Solved

Opening a form with RecordSet based on OpenArgs

Posted on 2006-06-16
9
507 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

0
Comment
Question by:BHR
  • 6
  • 3
9 Comments
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16922306
DoCmd.OpenForm stDocName, , QryArgs
0
 

Author Comment

by:BHR
ID: 16922500
Nope. That didn't work.  Still getting the same error.
0
 
LVL 34

Accepted Solution

by:
jefftwilley earned 500 total points
ID: 16922674
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16922855
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 34

Expert Comment

by:jefftwilley
ID: 16923235
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
 

Author Comment

by:BHR
ID: 16923250
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16923501
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
 

Author Comment

by:BHR
ID: 16923631
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16923669
Glad to help. good luck
J
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now