Solved

Opening a form with RecordSet based on OpenArgs

Posted on 2006-06-16
9
546 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access creating new db - relationships 8 57
Access Form - Button Format Issue 13 51
Access Need to add combo box to sub form 10 52
how to get hundreds part from the number 1 31
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…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

752 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