Solved

Opening a form with RecordSet based on OpenArgs

Posted on 2006-06-16
9
530 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

770 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