Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VBA code openrecordset method on form's open event property

Posted on 2009-05-19
12
Medium Priority
?
1,015 Views
Last Modified: 2013-11-27
My code is working for the most part.  The problem is that when the query "z nmdf iitdata review mr and adjust" outputs no results, my form "z supplier qty adjustment" still opens, which is not what I want.  I want the result to show a message box stating "No records found".  Could someone please review the code below to tell me where I am going wrong.  Thanks.
Private Sub Form_Open(Cancel As Integer)
 
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim vnotfound As Boolean
 
Set db = CurrentDb
Set qdf = db.QueryDefs("z nmdf iitdata review mr and adjust")
 
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next
 
Set rst = qdf.OpenRecordset
 
If Not (rst.BOF And rst.EOF) Then
    vnotfound = True
Else
    vnotfound = False
End If
 
If vnotfound = True Then
    rst.Close
    DoCmd.CancelEvent
    DoCmd.Close acForm, "z supplier qty adjustment"
    MsgBox "No records found."
ElseIf vnotfound = False Then
    DoCmd.OpenForm "z supplier qty adjustment"
    DoCmd.Maximize
    DoCmd.ApplyFilter "z supplier qty adjustment filter"
    Me.FilterOn = True
End If
 
rst.Close
Set rst = Nothing
Set qdf = Nothing
Set db = Nothing
Set prm = Nothing
 
End Sub

Open in new window

0
Comment
Question by:sxxgupta
[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
  • 5
  • 4
  • 3
12 Comments
 
LVL 20

Expert Comment

by:clarkscott
ID: 24425951
Else vnotfound = False Then
    DoCmd.OpenForm "z supplier qty adjustment"
    DoCmd.Maximize
    DoCmd.ApplyFilter "z supplier qty adjustment filter"
    Me.FilterOn = True
End If
 
rst.Close
Set rst = Nothing
Set qdf = Nothing
Set db = Nothing
Set prm = Nothing
if vnotfound = true then
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 24425959
sorry -  I hit the wrong key.....
Let's try this again....
Else vnotfound = False Then
    DoCmd.OpenForm "z supplier qty adjustment"
    DoCmd.Maximize
    DoCmd.ApplyFilter "z supplier qty adjustment filter"
    Me.FilterOn = True
End If
 

rst.Close
Set rst = Nothing
Set qdf = Nothing
Set db = Nothing
Set prm = Nothing
if vnotfound = true then
     docmd.close
end if

Scott C
0
 

Author Comment

by:sxxgupta
ID: 24426097
Hi Scott:
I think I have your suggestion in.......but still having problems.  I slightly modded the code below.  Please take a look.  My query shows not records but the form still opens and the filter is applied, which is incorrect.

Private Sub Form_Open(Cancel As Integer)
 
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim vnotfound As Boolean
 
Set db = CurrentDb
Set qdf = db.QueryDefs("z nmdf iitdata review mr and adjust")
 
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next
 
Set rst = qdf.OpenRecordset
 
If Not (rst.BOF And rst.EOF) Then
    vnotfound = True
Else
    vnotfound = False
End If
 
If vnotfound = True Then
    DoCmd.Close
    MsgBox "No records found."
End If
 
If vnotfound = False Then
    DoCmd.OpenForm "z supplier qty adjustment"
    DoCmd.Maximize
    DoCmd.ApplyFilter "z supplier qty adjustment filter"
    Me.FilterOn = True
End If
 
rst.Close
 
Set rst = Nothing
Set qdf = Nothing
Set db = Nothing
Set prm = Nothing
 
End Sub

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:sxxgupta
ID: 24426163
Hi Scott:
I am going home for the day.  4:15pm EST here.  I will follow up on the post tomorrow to see if any suggestions you have that will help my code.  Thank you.  Maybe the sequence of my coding is incorrect..................
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 24426276
Please note.... the CLOSE should be the last thing to run (if vfound = false).  You cannot close a form and expect the 'remaining' code to run as you expect.

Scott C
0
 
LVL 11

Accepted Solution

by:
datAdrenaline earned 2000 total points
ID: 24427489
sxxgupta ...

Is the form in question BOUND to the query you using to check for records? ... If not ... is it true that the query your form is bound to will be empty if the query used to test for a record count is empty? ... Next question; Does the form you are opening allow the user to enter records? ...

If you want to pursue, what I consider an easier way, then let me know ...

Otherwise ... I have these questions for you ...

Is the code you posted behind the form named "z supplier qty adjustment" ... if it is (which is what I assume), then there is no need to try to open it again in your code ... for that matter there is no need to close the form either, setting Cancel = True will do that for you.

What is your code to open this form? I ask because if the Cancel is set to true, then the initial call to open the form {DoCmd.OpenForm "z supplier qty adjustment"} will raise an error, so you need to supress the error before that call is made.

Additionally, I recommend setting the Filter in the OnLoad() event, then there is no need to manage that code block since it will not event get executed if the OnOpen() event is cancelled ...

So ... here is my thought ... dump your whole though process, and with some assumptions (the main one being the recordsource of the form is in sync with the query you are testing), I would procede by simply using the OnLoad() event exclusively ...

Private Sub Form_Load()
   
    Dim blShouldClose As Boolean
   
    'test to see if the form should close
    If Me.Recordset Is Nothing Then
        blShouldClose = True
    Else
        If Me.CurrentRecord = 0 Or Me.NewRecord Then
            blShouldClose = True
        End If
    End If
   
    'React accordingly
    If blShouldClose = True Then
        MsgBox "No Records to View, Form will Close"
        DoCmd.Close acForm, Me.Name, acSaveNo
    Else
        DoCmd.Maximize
        DoCmd.ApplyFilter "z supplier qty adjustment filter"
        Me.FilterOn = True
    End If
   
End Sub

------

If you don't like this method, then I would go with something like this ....

In the caller to open the form do something like this {note: you can easily create better Error Handling, I just want to make you aware the you will need to handle the error upon setting the Cancel to True}:

On Error Resume Next
DoCmd.OpenForm "z supplier qty adjustment"
On Error GoTo 0

Private Sub Form_Open(Cancel As Integer)

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Dim prm As DAO.Parameter
 
    Set db = CurrentDb
    Set qdf = db.QueryDefs("z nmdf iitdata review mr and adjust")

    For Each prm In qdf.Parameters
        prm.Value = Eval(prm.Name)
    Next

    Set rst = qdf.OpenRecordset
   
    If (rst.BOF And rst.EOF) = True Then
        Cancel = True
        MsgBox "No records found."
    Else
        DoCmd.Maximize
        DoCmd.ApplyFilter "z supplier qty adjustment filter"
        Me.FilterOn = True
    End If
   
    rst.Close
   
    Set rst = Nothing
    Set qdf = Nothing
    Set db = Nothing
    Set prm = Nothing

End Sub

-------------------

Hope all this info helps out!!! ... Let us know!
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 24427502
... Just a note ... in the first option (using the Form_Load() exclusively) ... here are the assumptions I made ..

Is the form in question BOUND to the query you using to check for records?: yes

is it true that the query your form is bound to will be empty if the query used to test for a record count is empty? Yes (if applicable)

Does the form you are opening allow the user to enter records?: NO .. (But I check the new record property just in case)
0
 

Author Comment

by:sxxgupta
ID: 24430229
So answering your questions:
1.  The form in question is not bound to the query I am using to check for records.  There is another subform within this form that is bound to the query.
2.  The form that I am opening should allow the user to enter records and add additional data into fields after the filter filters out the relevant records to write to.
0
 

Author Closing Comment

by:sxxgupta
ID: 31583193
Thank you very much.  I guess I was making the code more difficult than needed.  The second portion of your code did the trick.  I have another question taking this one step further.  Would you be interested?
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 24433427
Hello sxxgupta,

I am glad the information I provided helped you progress with your project! ... Excellent!!

Also ... with respect to:
>>  have another question taking this one step further. Would you be interested? <<

You bet I am interested! ...
0
 

Author Comment

by:sxxgupta
ID: 24433951
Hi datAdrenaline:
I was able to figure out the solution to my next proposed question.  But again, thank you and thank you.  As I progress on my project, I will post questions if I hit any road blocks.  Stay tuned......
Sanjay
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 24437695
Excellent news! ... Good luck on your project!

Brent
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

705 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